데이터베이스 질문과 답변
WITH t_before AS (
SELECT '00001' AS USER_ID,
'2020.07.10' AS START_DATE,
'2020.07.25' AS END_DATE
FROM DUAL
UNION ALL
SELECT '00001' AS USER_ID,
'2020.07.20' AS START_DATE,
'2020.12.31' AS END_DATE
FROM DUAL
UNION ALL
SELECT '00001' AS USER_ID,
'2020.08.18' AS START_DATE,
'2020.12.31' AS END_DATE
FROM DUAL
UNION ALL
SELECT '00001' AS USER_ID,
'2020.10.14' AS START_DATE,
'2020.12.31' AS END_DATE
FROM DUAL
UNION ALL
SELECT '00001' AS USER_ID,
'2020.10.22' AS START_DATE,
'2020.12.31' AS END_DATE
FROM DUAL
)
SELECT * FROM t_before;
WITH t_after AS (
SELECT '00001' AS USER_ID,
'2020.07.10' AS START_DATE,
'2020.07.19' AS END_DATE
FROM DUAL
UNION ALL
SELECT '00001' AS USER_ID,
'2020.07.20' AS START_DATE,
'2020.08.17' AS END_DATE
FROM DUAL
UNION ALL
SELECT '00001' AS USER_ID,
'2020.08.18' AS START_DATE,
'2020.10.13' AS END_DATE
FROM DUAL
UNION ALL
SELECT '00001' AS USER_ID,
'2020.10.14' AS START_DATE,
'2020.10.21' AS END_DATE
FROM DUAL
UNION ALL
SELECT '00001' AS USER_ID,
'2020.10.22' AS START_DATE,
'2020.12.31' AS END_DATE
FROM DUAL
)
SELECT * FROM t_after;
t_before 테이블의 데이터의 START_DATE ~ END_DATE 기간이 중복되는 ROW가 있을때
t_after 처럼 END_DATE가 START_DATE가 가장 가까운 일자의 전날이 되도록
조회하고 싶은데 어떤 방법을 사용해야 좋을까요?
by 샤랄라
[2020.10.26 14:53:23]
with t_before as
(select '00001' as user_id, '2020.07.20' as start_date, '2020.12.31' as end_date from dual union all
select '00001' as user_id, '2020.08.18' as start_date, '2020.12.31' as end_date from dual union all
select '00001' as user_id, '2020.10.14' as start_date, '2020.12.31' as end_date from dual union all
select '00001' as user_id, '2020.10.22' as start_date, '2020.12.31' as end_date from dual
)
select user_id
, start_date
, nvl(to_char(to_date(lead(start_date) over(partition by user_id, end_date order by start_date), 'yyyy.mm.dd')-1, 'yyyy.mm.dd'), end_date)
as end_date
from t_before;
by 미어캣
[2020.10.26 15:17:37]
답변감사드립니다.
제가 데이터를 잘 살펴보고 샘플데이터를 올렸어야 했는데 END_DATE가 항상 같지는 않더라구요.
그래서 2020.07.10 ~ 2020.07.25인 ROW를 추가해봤더니 이 데이터의 END_DATE는 가장 가까운 START_DATE인 2020.07.20의 전날인 2020.07.19가 아닌 2020.07.25가 그대로 나오더라구요.
이 경우를 보니 해당 데이터들이 시작과 끝기간이 중복되는지를 찾아야 할것 같은데 중복관련해서는 어떤 방법을 사용해야 할까요?
by 샤랄라
[2020.10.26 15:58:26]
select user_id , start_date , case when end_date >= af_start_date then to_char(to_date(af_start_date, 'yyyy.mm.dd')-1, 'yyyy.mm.dd') else end_date end as end_date from (select user_id , start_date , end_date , lead(start_date) over(partition by user_id order by start_date) af_start_date from t_before );
by 마농
[2020.10.27 16:20:09]
WITH t_before AS
(
SELECT '00001' user_id, '2020.07.10' start_date, '2020.07.25' end_date FROM dual
UNION ALL SELECT '00001', '2020.07.20', '2020.12.31' FROM dual
UNION ALL SELECT '00001', '2020.08.18', '2020.12.31' FROM dual
UNION ALL SELECT '00001', '2020.10.14', '2020.12.31' FROM dual
UNION ALL SELECT '00001', '2020.10.22', '2020.12.31' FROM dual
)
SELECT user_id
, start_date
, LEAST( end_date
, NVL(TO_CHAR(TO_DATE(
LEAD(start_date) OVER(PARTITION BY user_id ORDER BY start_date)
, 'yyyy.mm.dd') - 1, 'yyyy.mm.dd'), end_date)
) end_date
FROM t_before
;
) 버튼을 클릭하여 작성 하시면 됩니다.