如何基于时间范围实现分组窗口聚合？——计算客户30天内产品购买总量

嘿，我懂你碰到的这个棘手问题了——你之前用PARTITION BY CustomerID, Dat-30的思路确实不对，因为这种分区是把每个日期减去30天后的值作为分组依据，根本没法覆盖连续的30天时间范围。咱们得用窗口函数里的范围窗口框架来实现这个动态累计需求，核心思路是：针对每个客户，计算当前行日期及往前30天内所有记录的Amount总和。

下面根据不同数据库类型给出具体的SQL写法，你可以根据自己使用的数据库选择对应的版本：

1. PostgreSQL #

PostgreSQL原生支持在窗口框架中使用日期区间，写法最直观：

SELECT
  CustomerID,
  Amount,
  Dat,
  SUM(Amount) OVER (
    PARTITION BY CustomerID  -- 按客户分组
    ORDER BY Dat::DATE       -- 按购买日期排序
    -- 定义窗口范围：当前日期往前30天到当前行
    RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
  ) AS ProductsIn30Days
FROM your_table_name;

这里Dat::DATE是把Dat字段转换成标准日期类型，确保日期排序和区间计算的准确性。

2. MySQL（8.0+版本） #

MySQL的窗口函数对日期类型的RANGE支持需要转成数值型，我们可以用TO_DAYS()把日期转换成自公元0年以来的天数，再指定30天的范围：

SELECT
  CustomerID,
  Amount,
  Dat,
  SUM(Amount) OVER (
    PARTITION BY CustomerID
    ORDER BY TO_DAYS(Dat)
    -- 往前30天对应的天数差值是30
    RANGE BETWEEN 30 PRECEDING AND CURRENT ROW
  ) AS ProductsIn30Days
FROM your_table_name;

3. SQL Server #

SQL Server需要用DATEADD来计算30天前的日期，同时确保日期格式正确：

SELECT
  CustomerID,
  Amount,
  Dat,
  SUM(Amount) OVER (
    PARTITION BY CustomerID
    ORDER BY CONVERT(DATE, Dat)
    -- 动态计算当前日期往前30天的边界
    RANGE BETWEEN DATEADD(DAY, -30, CONVERT(DATE, Dat)) PRECEDING AND CURRENT ROW
  ) AS ProductsIn30Days
FROM your_table_name;

验证示例数据 #

拿你给出的示例来看，比如CustomerID=1、Dat=25.3.2020的记录：

• 该客户在24.3.2020的两条记录（Amount=2和3）都在25.3.2020往前30天范围内，加上当前的2，总和正好是7，和你预期的结果一致。

如果你的数据库是其他类型（比如Oracle），可以告诉我，我再调整对应的写法～

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