요구사항.
값의 변화시점 확인하는 쿼리.
질문 내용
WITH t AS (
SELECT '20180718131317' dd, 'a' cc from dual union all
SELECT '20180718131318', 'a' from dual union all
SELECT '20180718131319', 'b' from dual union all <==
SELECT '20180718131320', 'b' from dual union all
SELECT '20180718131321', 'b' from dual union all
SELECT '20180718131322', 'b' from dual union all
SELECT '20180718131323', 'b' from dual union all
SELECT '20180718131324', 'b' from dual union all
SELECT '20180718131325', 'b' from dual union all <==
SELECT '20180718131326', 'a' from dual union all
SELECT '20180718131327', 'a' from dual union all
SELECT '20180718131328', 'a' from dual union all
SELECT '20180718131329', 'a' from dual union all
SELECT '20180718131330', 'a' from dual union all
SELECT '20180718131331', 'a' from dual union all
SELECT '20180718131332', 'a' from dual union all
SELECT '20180718131333', 'a' from dual union all
SELECT '20180718131334', 'a' from dual union all
SELECT '20180718131335', 'a' from dual union all
SELECT '20180718131336', 'b' from dual union all <==
SELECT '20180718131337', 'b' from dual union all
SELECT '20180718131338', 'b' from dual union all
SELECT '20180718131339', 'b' from dual union all
SELECT '20180718131340', 'b' from dual union all
SELECT '20180718131341', 'b' from dual union all <==
SELECT '20180718131342', 'a' from dual union all
SELECT '20180718131343', 'a' from dual union all
SELECT '20180718131344', 'a' from dual
)
구하고 싶은 데이터는
'b'가 시작하는 시각 : '20180718131319'
'b'가 끝나는 시각 : '20180718131325'
다시 'b'가 시작되는 시각 : '20180718131336'
'b'가 끝나는 시각 : '20180718131341'
입니다.
쿼리는 어떻게 생성해야 하는지요?
응답코드
WITH t AS
(
SELECT '20180718131317' dd, 'a' cc FROM dual
UNION ALL SELECT '20180718131318', 'a' FROM dual
UNION ALL SELECT '20180718131319', 'b' FROM dual -- s
UNION ALL SELECT '20180718131320', 'b' FROM dual
UNION ALL SELECT '20180718131321', 'b' FROM dual
UNION ALL SELECT '20180718131322', 'b' FROM dual
UNION ALL SELECT '20180718131323', 'b' FROM dual
UNION ALL SELECT '20180718131324', 'b' FROM dual
UNION ALL SELECT '20180718131325', 'b' FROM dual -- e
UNION ALL SELECT '20180718131326', 'a' FROM dual
UNION ALL SELECT '20180718131327', 'a' FROM dual
UNION ALL SELECT '20180718131328', 'a' FROM dual
UNION ALL SELECT '20180718131329', 'a' FROM dual
UNION ALL SELECT '20180718131330', 'a' FROM dual
UNION ALL SELECT '20180718131331', 'a' FROM dual
UNION ALL SELECT '20180718131332', 'a' FROM dual
UNION ALL SELECT '20180718131333', 'a' FROM dual
UNION ALL SELECT '20180718131334', 'a' FROM dual
UNION ALL SELECT '20180718131335', 'a' FROM dual
UNION ALL SELECT '20180718131336', 'b' FROM dual -- s
UNION ALL SELECT '20180718131337', 'b' FROM dual
UNION ALL SELECT '20180718131338', 'b' FROM dual
UNION ALL SELECT '20180718131339', 'b' FROM dual
UNION ALL SELECT '20180718131340', 'b' FROM dual
UNION ALL SELECT '20180718131341', 'b' FROM dual -- e
UNION ALL SELECT '20180718131342', 'a' FROM dual
UNION ALL SELECT '20180718131343', 'a' FROM dual
UNION ALL SELECT '20180718131344', 'a' FROM dual
)
SELECT cc
, MIN(dd) sdd
, MAX(dd) edd
FROM (SELECT dd, cc
, ROW_NUMBER() OVER(ORDER BY dd)
- ROW_NUMBER() OVER(PARTITION BY cc ORDER BY dd) gb
FROM t
)
-- WHERE cc = 'b'
GROUP BY cc, gb
ORDER BY sdd
;
활용
참조
'Database > Oracle' 카테고리의 다른 글
[Oracle] Trigger 상태 확인 (0) | 2022.06.28 |
---|---|
[Oracle] 테이블(Table) 삭제하기- DROP TABLE (0) | 2022.06.19 |
[Oracle] 값의 변화 부분을 확인하는 쿼리. (0) | 2022.06.13 |
[Oracle] 테이블별 사용량 조회 (0) | 2022.06.07 |
[Oracle] 테이블별 레코드 수 확인하기 (0) | 2022.06.07 |
댓글