데이터베이스 질문과 답변
테이블
---------------------------------------------------------------------------------------
상품 날짜 순번
A 20170905 001
A 20170907 002
A 20170909 003
A 20170910 004
A 20170911 005
A 20170913 006
A 20170914 007
A 20170918 008
A 20170920 009
A 20170921 010
----------------------------------------------------------------------------------------------------------
쿼리 결과
상품 이전날짜 기준날짜 이후날짜
A 201700905 201700907
A 201700905 201700907 201700909
A 201700907 201700909 201700910
A 201700909 201700910 201700911
A 201700910 201700911 201700913
A 201700911 201700913 201700914
A 201700913 201700914 201700918
A 201700914 201700918 201700920
A 201700918 201700920 201700921
A 201700920 201700921
WITH t AS
(
SELECT 'A' cd, '20170905' dt, '001' seq FROM dual
UNION ALL SELECT 'A', '20170907', '002' FROM dual
UNION ALL SELECT 'A', '20170909', '003' FROM dual
UNION ALL SELECT 'A', '20170910', '004' FROM dual
UNION ALL SELECT 'A', '20170911', '005' FROM dual
UNION ALL SELECT 'A', '20170913', '006' FROM dual
UNION ALL SELECT 'A', '20170914', '007' FROM dual
UNION ALL SELECT 'A', '20170918', '008' FROM dual
UNION ALL SELECT 'A', '20170920', '009' FROM dual
UNION ALL SELECT 'A', '20170921', '010' FROM dual
)
-- 1. Analytic Function --
SELECT cd
, seq
, LAG (dt) OVER(PARTITION BY cd ORDER BY dt) dt_lag
, dt
, LEAD(dt) OVER(PARTITION BY cd ORDER BY dt) dt_lead
FROM t
;
고수님 순번으로 조인으로 해서 할수있는방법은 없을까요? (이전날짜) 순번 - 1 , 기준날짜 ,(이후날짜) 순번 + 1 이렇게 가능할까요?
-- 2. Self Join, Outer Join --
SELECT a.cd
, a.seq
, b.dt dt_1
, a.dt
, c.dt dt_2
FROM t a
, t b
, t c
WHERE a.cd = b.cd(+)
AND a.cd = c.cd(+)
AND b.seq(+) = LPAD(a.seq - 1, 3, '0')
AND c.seq(+) = LPAD(a.seq + 1, 3, '0')
ORDER BY cd, seq
;
-- 3. Model --
SELECT *
FROM t
MODEL
PARTITION BY (cd)
DIMENSION BY (seq)
MEASURES (dt dt_1, dt, dt dt_2)
RULES ( dt_1[ANY] = dt[LPAD(CV() - 1, 3, '0')]
, dt_2[ANY] = dt[LPAD(CV() + 1, 3, '0')]
)
;
) 버튼을 클릭하여 작성 하시면 됩니다.