데이터베이스 질문과 답변
SELECT *
, DAYS(DAY2) - DAYS(DAY1) +1 AS DAY_CNT
FROM (
SELECT '20240304' AS DAY1, '20240305' AS DAY2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '20240311' AS DAY1, '20240315' AS DAY2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '20240322' AS DAY1, '20240331' AS DAY2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '20240401' AS DAY1, '20240402' AS DAY2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '20240403' AS DAY1, '20240405' AS DAY2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '20240408' AS DAY1, '20240412' AS DAY2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '20240418' AS DAY1, '20240420' AS DAY2 FROM SYSIBM.SYSDUMMY1
) AS T
위 데이터의 연속된 총 일수를 계산하려고합니다.
어떻게 해야할까요??
| Day1 | Day2 | Cnt | 연속된 총 일수 |
| ... | |||
| 20240322 | 20240331 | 10 | 15 |
| 20240401 | 20240402 | 2 | 15 |
| 20240403 | 20240405 | 3 | 15 |
| 20240408 | 20240412 | 5 | 5 |
| ... |
by 마농
[2024.02.28 23:07:09]
Oracle 에서 테스트했습니다. DB2 에서 동작할지는 모르겠네요.
WITH t AS
(
SELECT '20240304' day1, '20240305' day2 FROM dual
UNION ALL SELECT '20240311', '20240315' FROM dual
UNION ALL SELECT '20240322', '20240331' FROM dual
UNION ALL SELECT '20240401', '20240402' FROM dual
UNION ALL SELECT '20240403', '20240405' FROM dual
UNION ALL SELECT '20240408', '20240412' FROM dual
UNION ALL SELECT '20240418', '20240420' FROM dual
)
-- Oracle --
SELECT day1, day2
, dt2 - dt1 + 1 cnt1
, MAX(dt2) OVER(PARTITION BY grp)
- MIN(dt1) OVER(PARTITION BY grp)
+ 1 cnt2
FROM (SELECT day1, day2
, TO_DATE(day1, 'yyyymmdd') dt1
, TO_DATE(day2, 'yyyymmdd') dt2
, SUM(flag) OVER(ORDER BY day1) grp
FROM (SELECT day1, day2
, CASE LAG(day2) OVER(ORDER BY day1)
WHEN TO_CHAR(TO_DATE(day1, 'yyyymmdd') - 1, 'yyyymmdd')
THEN 0 ELSE 1 END flag
FROM t
) a
) a
ORDER BY day1
;
by 잉어
[2024.02.29 12:45:38]
DB2도 적용가능하네요
감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.