데이터베이스 질문과 답변
안녕하세요~ 쿼리 질문좀 드립니다.
아래와 같은 데이터가 있는데 하단에 결과값처럼 나오게 하려하는데
잘 안되네요..
고수님들의 답변 부탁드려요~
| item_cd | start_dt | end_dt | sts |
| a | 20010101 | 20010201 | S |
| a | 20010201 | 20010301 | E |
| a | 20010301 | 20010401 | E |
| a | 20010501 | 99992131 | S |
| b | 20010101 | 20010201 | S |
| b | 20010201 | 20010301 | E |
| b | 20010401 | 20010501 | S |
| b | 20010501 | 20010601 | E |
결과값 |
|||
| item_cd | start_dt | end_dt | |
| a | 20010101 | 20010401 | |
| a | 20010501 | 99991231 | |
| b | 20010101 | 20010301 | |
| b | 20010401 | 20010601 |
by sdalf
[2014.01.27 17:05:28]
WITH T AS (
SELECT 'a' ITEM_CD,'20010101' START_DT,'20010201' END_DT,'S' STS FROM DUAL UNION ALL
SELECT 'a','20010201','20010301','E' FROM DUAL UNION ALL
SELECT 'a','20010301','20010401','E' FROM DUAL UNION ALL
SELECT 'a','20010501','99992131','S' FROM DUAL UNION ALL
SELECT 'b','20010101','20010201','S' FROM DUAL UNION ALL
SELECT 'b','20010201','20010301','E' FROM DUAL UNION ALL
SELECT 'b','20010401','20010501','S' FROM DUAL UNION ALL
SELECT 'b','20010501','20010601','E' FROM DUAL
)
SELECT
ITEM_CD,MIN(START_DT) START_DT,MAX(END_DT) END_DT
FROM
(
SELECT ITEM_CD,START_DT,END_DT,STS
,SUM(N1) OVER(PARTITION BY ITEM_CD ORDER BY START_DT) GROUP_N
FROM
(
SELECT T.*
,CASE WHEN START_DT <= LAG(END_DT) OVER(PARTITION BY ITEM_CD ORDER BY START_DT) THEN 0 ELSE 1 END N1
FROM T
)
)
GROUP BY ITEM_CD,GROUP_N
ORDER BY ITEM_CD
by 마농
[2014.01.27 17:07:29]
WITH t AS
(
SELECT 'a' item_cd, '20010101' start_dt, '20010201' end_dt, 'S' sts FROM dual
UNION ALL SELECT 'a', '20010201', '20010301', 'E' FROM dual
UNION ALL SELECT 'a', '20010301', '20010401', 'E' FROM dual
UNION ALL SELECT 'a', '20010501', '99992131', 'S' FROM dual
UNION ALL SELECT 'b', '20010101', '20010201', 'S' FROM dual
UNION ALL SELECT 'b', '20010201', '20010301', 'E' FROM dual
UNION ALL SELECT 'b', '20010401', '20010501', 'S' FROM dual
UNION ALL SELECT 'b', '20010501', '20010601', 'E' FROM dual
)
-- 두가지 기준으로 문제를 풀 수 있겠네요.
-- 1. start_dt, end_dt 를 기준으로 한 풀이 방법 --
SELECT item_cd
, MIN(start_dt) start_dt
, MAX(end_dt) end_dt
FROM (SELECT item_cd, start_dt, end_dt, sts
, SUM(flag) OVER(PARTITION BY item_cd ORDER BY start_dt) grp
FROM (SELECT item_cd, start_dt, end_dt, sts
, DECODE(LAG(end_dt) OVER(PARTITION BY item_cd ORDER BY start_dt)
, start_dt, 0, 1) flag
FROM t
)
)
GROUP BY item_cd, grp
ORDER BY item_cd, grp
;
-- 2. sts 를 기준으로 한 풀이 방법 --
SELECT item_cd
, MIN(start_dt) start_dt
, MAX(end_dt) end_dt
FROM (SELECT item_cd, start_dt, end_dt, sts
, SUM(DECODE(sts, 'S', 1)) OVER(PARTITION BY item_cd ORDER BY start_dt) grp
FROM t
)
GROUP BY item_cd, grp
ORDER BY item_cd, grp
;
) 버튼을 클릭하여 작성 하시면 됩니다.