Oracle SQL 按客户ID分组实现90个自然日回溯滚动求和

需求说明 #

现有业务表含3个字段：交易日期Calender_Date、客户IDCustomer_ID、交易金额Amount，要求按客户ID分组，以每行交易日期为基准，计算向前回溯90个自然日内的交易金额滚动总和。

样例输入 #

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

实现SQL语句 #

假设业务表表名为customer_trans，Calender_Date字段为Oracle原生DATE类型，可直接使用窗口函数实现需求：

SELECT 
    Calender_Date,
    Customer_ID,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY Customer_ID 
        ORDER BY Calender_Date 
        RANGE BETWEEN INTERVAL '90' DAY PRECEDING AND CURRENT ROW
    ) AS SumofLookbackRolling90DayAmount
FROM 
    customer_trans
ORDER BY 
    Calender_Date, Customer_ID;

注意事项 #

如果Calender_Date字段存储格式为字符串，需先转换为DATE类型再计算，修改窗口函数内ORDER BY部分即可：

SUM(Amount) OVER (
    PARTITION BY Customer_ID 
    ORDER BY TO_DATE(Calender_Date, 'DD-MON-RR', 'NLS_DATE_LANGUAGE = AMERICAN')
    RANGE BETWEEN INTERVAL '90' DAY PRECEDING AND CURRENT ROW
) AS SumofLookbackRolling90DayAmount

内容的提问来源于stack exchange，提问作者NewTechGuy