基于重叠日期的滚动累计求和SQL高效实现方案咨询

问题背景 #

有一张包含约300万条按日期统计的客户销售数据表，需求为：对每个CustomerID的每一行，计算Order_Date处于[Order_Date_m365, Order_Date]区间内的Spend_Value总和（Order_Date_m365为当前行Order_Date减去365天）。此前尝试自连接得到错误结果，窗口函数的日期区间写法未找到，循环方案效率极低，寻求常规SQL处理方案。

现有基础查询语句：

SELECT CustomerID,Order_Date_m365,Order_Date,Spend_Value
FROM   dbo.CustomerSales 

解决方案 #

方案1：支持动态日期范围的窗口函数（适配SQL Server 2012+、PostgreSQL 11+等） #

通过窗口函数的分区（按CustomerID）、排序（按Order_Date），直接指定日期范围为当前日期往前365天至当前行，计算滚动总和：

PostgreSQL 版本

SELECT 
    CustomerID,
    Order_Date,
    Order_Date - INTERVAL '365 days' AS Order_Date_m365,
    Spend_Value,
    SUM(Spend_Value) OVER (
        PARTITION BY CustomerID
        ORDER BY Order_Date
        RANGE BETWEEN INTERVAL '365 days' PRECEDING AND CURRENT ROW
    ) AS Rolling_12M_Spend
FROM dbo.CustomerSales

SQL Server 版本

SELECT 
    CustomerID,
    Order_Date,
    DATEADD(DAY, -365, Order_Date) AS Order_Date_m365,
    Spend_Value,
    SUM(Spend_Value) OVER (
        PARTITION BY CustomerID
        ORDER BY Order_Date
        RANGE BETWEEN DATEADD(DAY, -365, Order_Date) AND CURRENT ROW
    ) AS Rolling_12M_Spend
FROM dbo.CustomerSales

方案2：通用条件聚合窗口函数（兼容多数SQL引擎） #

若你的SQL引擎不支持动态日期范围的窗口语法，可通过条件判断在窗口内筛选符合日期区间的记录，计算总和：

SELECT 
    CustomerID,
    Order_Date,
    DATEADD(day, -365, Order_Date) AS Order_Date_m365,
    Spend_Value,
    SUM(CASE 
            WHEN Order_Date >= DATEADD(day, -365, cs.Order_Date) 
            THEN Spend_Value 
            ELSE 0 
        END) OVER (
            PARTITION BY CustomerID
            ORDER BY Order_Date
            ROWS UNBOUNDED PRECEDING
        ) AS Rolling_12M_Spend
FROM dbo.CustomerSales cs

性能优化建议 #

• 创建CustomerID+Order_Date的联合索引，包含Spend_Value字段，让窗口函数的分区、排序直接利用索引，大幅提升300万行数据的处理效率：

  CREATE NONCLUSTERED INDEX IX_CustomerSales_CustomerID_OrderDate 
  ON dbo.CustomerSales (CustomerID, Order_Date) 
  INCLUDE (Spend_Value);
  

• 绝对避免使用循环或游标，这类方法在大数据量下性能极差，窗口函数是最优选择。
• 若存在同一CustomerID+Order_Date的重复记录，建议先按CustomerID和Order_Date聚合Spend_Value，再计算滚动总和。

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