쿼리 질문: 컬럼 값이 변경되는 시점 구하기. 0 1 1,256

by 아리수 [SQL Query] [2019.01.15 20:06:35]


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'
 입니다. 
 
쿼리는 어떻게 생성해야 하는지요? 
 

by 마농 [2019.01.16 08:26:38]
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입