특정 ID별로 사건발생일(EVT_DATE)이 누적되어 있고, 각 레코드의 사건발생일 90일전 부터 누계된 사건을 COUNT해 보자.
즉 사건발생일까지 이전 90일이라는 구간별 누적된 사건수를 조회해보자.
WITH TBL
AS
(
SELECT 'A' AS ID, '20210123' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210123' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210219' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210322' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210427' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210507' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210507' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210507' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210711' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210729' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210823' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210901' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20210922' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20211003' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20211012' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20211123' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20211209' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20220103' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20220217' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20220329' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'A' AS ID, '20220411' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210123' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210123' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210219' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210322' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210427' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210507' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210507' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210507' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210711' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210729' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210823' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210901' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20210922' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20211003' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20211012' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20211123' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20211209' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20220103' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20220217' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20220329' AS EVT_DATE FROM DUAL
UNION ALL
SELECT 'B' AS ID, '20220411' AS EVT_DATE FROM DUAL
)
SELECT T.ID
, T.EVT_DATE
, T.FRM_DATE
, COUNT(*) OVER (PARTITION BY T.ID ORDER BY T.JUL_DATE RANGE BETWEEN 90 PRECEDING AND CURRENT ROW) AS FRM_CNT1
, COUNT(*) OVER (PARTITION BY T.ID ORDER BY T.JUL_DATE RANGE BETWEEN 90 PRECEDING AND CURRENT ROW)
- (T.EVT_PER_DAY_CNT - T.EVT_PER_DAY_NO) AS FRM_CNT2
FROM (SELECT T.ID
, TO_CHAR(TO_DATE(T.EVT_DATE, 'YYYYMMDD'), 'YYYY-MM-DD') AS EVT_DATE
, TO_CHAR(TO_DATE(T.EVT_DATE, 'YYYYMMDD') - 90, 'YYYY-MM-DD') AS FRM_DATE
, TO_NUMBER(TO_CHAR(TO_DATE(T.EVT_DATE, 'YYYYMMDD'), 'J')) AS JUL_DATE
, COUNT(*) OVER (PARTITION BY T.ID, T.EVT_DATE ORDER BY T.EVT_DATE) AS EVT_PER_DAY_CNT
, ROW_NUMBER() OVER (PARTITION BY T.ID, T.EVT_DATE ORDER BY T.EVT_DATE) AS EVT_PER_DAY_NO
, ROW_NUMBER() OVER (ORDER BY T.EVT_DATE, T.ID) AS EVT_ORD
FROM TBL T) T
ORDER BY T.EVT_ORD;
ID DATE
------ ----------
A 20210123
A 20210123
A 20210219
A 20210322
A 20210427
A 20210507
A 20210507
A 20210507
A 20210711
A 20210729
A 20210823
A 20210901
A 20210922
A 20211003
A 20211012
A 20211123
A 20211209
A 20220103
A 20220217
A 20220329
A 20220411
ID EVT_DATE FRM_DATE JUL_DATE EVT_PER_DAY_CNT EVT_PER_DAY_NO EVT_ORD
------ --------------- --------------- ---------- ------------------ ----------------- --------
A 2021-01-23 2020-10-25 2459238 2 1 1
A 2021-01-23 2020-10-25 2459238 2 2 2
B 2021-01-23 2020-10-25 2459238 2 1 3
B 2021-01-23 2020-10-25 2459238 2 2 4
A 2021-02-19 2020-11-21 2459265 1 1 5
B 2021-02-19 2020-11-21 2459265 1 1 6
A 2021-03-22 2020-12-22 2459296 1 1 7
B 2021-03-22 2020-12-22 2459296 1 1 8
A 2021-04-27 2021-01-27 2459332 1 1 9
B 2021-04-27 2021-01-27 2459332 1 1 10
A 2021-05-07 2021-02-06 2459342 3 1 11
A 2021-05-07 2021-02-06 2459342 3 2 12
A 2021-05-07 2021-02-06 2459342 3 3 13
B 2021-05-07 2021-02-06 2459342 3 1 14
B 2021-05-07 2021-02-06 2459342 3 2 15
B 2021-05-07 2021-02-06 2459342 3 3 16
A 2021-07-11 2021-04-12 2459407 1 1 17
B 2021-07-11 2021-04-12 2459407 1 1 18
A 2021-07-29 2021-04-30 2459425 1 1 19
B 2021-07-29 2021-04-30 2459425 1 1 20
A 2021-08-23 2021-05-25 2459450 1 1 21
B 2021-08-23 2021-05-25 2459450 1 1 22
A 2021-09-01 2021-06-03 2459459 1 1 23
B 2021-09-01 2021-06-03 2459459 1 1 24
A 2021-09-22 2021-06-24 2459480 1 1 25
B 2021-09-22 2021-06-24 2459480 1 1 26
A 2021-10-03 2021-07-05 2459491 1 1 27
B 2021-10-03 2021-07-05 2459491 1 1 28
A 2021-10-12 2021-07-14 2459500 1 1 29
B 2021-10-12 2021-07-14 2459500 1 1 30
A 2021-11-23 2021-08-25 2459542 1 1 31
B 2021-11-23 2021-08-25 2459542 1 1 32
A 2021-12-09 2021-09-10 2459558 1 1 33
B 2021-12-09 2021-09-10 2459558 1 1 34
A 2022-01-03 2021-10-05 2459583 1 1 35
B 2022-01-03 2021-10-05 2459583 1 1 36
A 2022-02-17 2021-11-19 2459628 1 1 37
B 2022-02-17 2021-11-19 2459628 1 1 38
A 2022-03-29 2021-12-29 2459668 1 1 39
B 2022-03-29 2021-12-29 2459668 1 1 40
A 2022-04-11 2022-01-11 2459681 1 1 41
B 2022-04-11 2022-01-11 2459681 1 1 42
ID EVT_DATE FRM_DATE FRM_CNT1 FRM_CNT2
------ --------------- --------------- ---------- -----------
A 2021-01-23 2020-10-25 2 1
A 2021-01-23 2020-10-25 2 2
B 2021-01-23 2020-10-25 2 1
B 2021-01-23 2020-10-25 2 2
A 2021-02-19 2020-11-21 3 3
B 2021-02-19 2020-11-21 3 3
A 2021-03-22 2020-12-22 4 4
B 2021-03-22 2020-12-22 4 4
A 2021-04-27 2021-01-27 3 3
B 2021-04-27 2021-01-27 3 3
A 2021-05-07 2021-02-06 6 4
A 2021-05-07 2021-02-06 6 5
A 2021-05-07 2021-02-06 6 6
B 2021-05-07 2021-02-06 6 4
B 2021-05-07 2021-02-06 6 5
B 2021-05-07 2021-02-06 6 6
A 2021-07-11 2021-04-12 5 5
B 2021-07-11 2021-04-12 5 5
A 2021-07-29 2021-04-30 5 5
B 2021-07-29 2021-04-30 5 5
A 2021-08-23 2021-05-25 3 3
B 2021-08-23 2021-05-25 3 3
A 2021-09-01 2021-06-03 4 4
B 2021-09-01 2021-06-03 4 4
A 2021-09-22 2021-06-24 5 5
B 2021-09-22 2021-06-24 5 5
A 2021-10-03 2021-07-05 6 6
B 2021-10-03 2021-07-05 6 6
A 2021-10-12 2021-07-14 6 6
B 2021-10-12 2021-07-14 6 6
A 2021-11-23 2021-08-25 5 5
B 2021-11-23 2021-08-25 5 5
A 2021-12-09 2021-09-10 5 5
B 2021-12-09 2021-09-10 5 5
A 2022-01-03 2021-10-05 4 4
B 2022-01-03 2021-10-05 4 4
A 2022-02-17 2021-11-19 4 4
B 2022-02-17 2021-11-19 4 4
A 2022-03-29 2021-12-29 3 3
B 2022-03-29 2021-12-29 3 3
A 2022-04-11 2022-01-11 3 3
B 2022-04-11 2022-01-11 3 3
참조 : https://blog.daonelab.com/post/24/420/