PostgreSQL中按日期区间计算每日30天累计分数的实现问题

我懂你的痛点——手动改日期跑查询太麻烦，想要一次性算出每个用户每一天对应的过去30天（含当天）累计分数对吧？你之前的第二个SQL语句逻辑有问题，咱们用窗口函数就能完美解决这个滑动累计的需求。

问题分析 #

你之前尝试的语句：

select sum(Mark), Name, School_day from Table1 where between School_day-29 and School_day group by Name, School_day order by School_day

这里的where子句语法错误（缺少关联列名），而且就算修正语法，group by Name, School_day也只会计算当天的分数总和，没法实现“过去30天累计”的效果——因为分组后只能拿到单天的聚合值，关联不到该日期之前的历史数据。

正确的SQL写法（PostgreSQL） #

利用PostgreSQL的滑动窗口函数，就能批量计算每个用户每日的30天累计分数：

SELECT
  Name,
  School_day,
  SUM(Mark) OVER (
    PARTITION BY Name
    ORDER BY School_day
    RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
  ) AS thirty_day_cumulative_mark
FROM Table1
ORDER BY Name, School_day;

语句解释 #

• PARTITION BY Name：按用户分组，确保每个用户的累计计算独立进行，不会和其他用户的数据混淆
• ORDER BY School_day：按日期排序，保证窗口内的记录是按时间顺序排列的，确保累计逻辑符合时间流向
• RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW：定义滑动窗口的范围——从当前日期往前推29天（加上当天正好30天）到当前行，自动对这个范围内的Mark值求和

补充说明 #

如果你的School_day是date类型，上面的语句直接可用；如果是带时间戳的timestamp类型，逻辑也完全适用。要是存在某用户某天没有记录的情况，窗口函数会自动跳过无数据的日期，只计算有记录日期的累计值。

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