如何用SQL窗口函数计算客户的3个月滚动邮件发送量总和？

我有一张结构如下的表，需要计算每个客户的3个月滚动邮件发送量总和，比如客户1在2023-06-30的3个月滚动总和为5（2+null+3）。我尝试用带分区和范围的SQL查询，但对窗口函数理解不到位，附上我的尝试代码：

SELECT cust_id,
  eom_date, 
  SUM(email_sent),
  SUM(email_sent) OVER (cust_id ORDER BY time_eom_date RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) total_email_3month
FROM my_table
GROUP BY 1, 2

原始表结构 #

预期输出结果 #

问题分析与修正 #

你的尝试代码存在几个关键问题：

1. 窗口函数缺少PARTITION BY关键字，正确的分区语法应为PARTITION BY cust_id，而非直接写cust_id。
2. 排序字段写错，应该是eom_date而非time_eom_date。
3. RANGE BETWEEN 3 PRECEDING是数值范围偏移，不适用于日期类型，需用时间区间定义窗口。
4. 原始表已按客户+月份存储单条记录，无需额外分组求和。

正确SQL查询 #

SELECT 
    cust_id,
    eom_date,
    email_sent,
    COALESCE(SUM(COALESCE(email_sent, 0)) OVER (
        PARTITION BY cust_id 
        ORDER BY eom_date 
        RANGE BETWEEN INTERVAL '2 months' PRECEDING AND CURRENT ROW
    ), 0) AS total_email_3month
FROM my_table
ORDER BY cust_id, eom_date DESC;

代码说明 #

• PARTITION BY cust_id：按客户分组，保证每个客户的滚动计算独立进行。
• ORDER BY eom_date：按日期排序，确定窗口的时间顺序。
• RANGE BETWEEN INTERVAL '2 months' PRECEDING AND CURRENT ROW：定义窗口为当前日期及往前推2个月的区间，刚好覆盖3个连续月份（当前月+前两个月）。
• COALESCE(email_sent, 0)：将null转为0，避免求和时忽略空值导致结果偏差。
• 外层COALESCE(..., 0)：确保窗口内全为空值时，结果显示0而非null，匹配预期输出。

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