从SQL/ Oracle中给定的当前日期开始超过90个日历日期(向后看)的滚动和

Question

**Calender_Date**   **Customer_ID**     **Amount**
01-Jan-21          20211003              59
01-Jan-21          20211005              100
27-Jan-21          20211003              25
25-Feb-21          20211003              188
23-Mar-21          20211005              67
24-Apr-21          20211009              43
25-May-21          20211013              21
26-May-21          20211013              89
11-Jul-21          20211009              65
28-Aug-21          20211003              90

需要:考虑Customer_ID -超过90日历回顾日期的滚动总和(从每个给定的当前日期开始)。

输出需要或期望的结果

**Calender_Date**   **Customer_ID** **Amount**  **SumofLookbackRolling90DayAmount**
1-Jan-21           20211003            59           59
1-Jan-21           20211005            100          100
27-Jan-21          20211003            25           84
25-Feb-21          20211003            188          272
23-Mar-21          20211005            67           167
24-Apr-21          20211009            43           43
25-May-21          20211013            21           21
26-May-21          20211013            89           110
11-Jul-21          20211009            65           108
28-Aug-21          20211003            90           90

Answer 1

您可以使用具有范围窗口的解析函数:

SELECT t.*,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY calendar_date
RANGE BETWEEN INTERVAL '90' DAY PRECEDING
AND INTERVAL '0' DAY FOLLOWING
) AS rolling_sum
FROM   table_name t

对于您的样本数据:

CREATE TABLE table_name (Calendar_Date, Customer_ID, Amount) AS
SELECT DATE '2021-01-01', 20211003,  59 FROM DUAL UNION ALL
SELECT DATE '2021-01-01', 20211005, 100 FROM DUAL UNION ALL
SELECT DATE '2021-01-27', 20211003,  25 FROM DUAL UNION ALL
SELECT DATE '2021-02-25', 20211003, 188 FROM DUAL UNION ALL
SELECT DATE '2021-03-23', 20211005,  67 FROM DUAL UNION ALL
SELECT DATE '2021-04-24', 20211009,  43 FROM DUAL UNION ALL
SELECT DATE '2021-05-25', 20211013,  21 FROM DUAL UNION ALL
SELECT DATE '2021-05-26', 20211013,  89 FROM DUAL UNION ALL
SELECT DATE '2021-07-11', 20211009,  65 FROM DUAL UNION ALL
SELECT DATE '2021-08-28', 20211003,  90 FROM DUAL;

输出:

tbody> <<tr>

CALENDAR_DATE	CUSTOMER_ID	AMOUNT	ROLLING_SUM
01-JAN-21	20211003	59	59
27-JAN-21	20211003	25	84
25-FEB-21	20211003	188	272
28-AUG-21	20211003	90	90
01-JAN-21	20211005	100	100
23-MAR-21	20211005	67	167
24-APR-21	20211009	43	43
11-JUL-21	20211009	65	108
25-MAY-21	20211013	21	21
26-MAY-21	20211013	89	110