如何在分组统计的SQL查询中添加近3天累计求和列？

原SQL查询语句 #

SELECT
 date,
 COUNT(o.row_number) FILTER (WHERE o.row_number > 1 AND date_ddr IS NOT NULL AND telephone_number <> 'Anonymous') repeat_calls_24h
FROM
  (
  SELECT
    telephone_number,
    date_ddr,
    ROW_NUMBER() OVER(PARTITION BY ddr.telephone_number ORDER BY ddr.date) row_number
  FROM
    table_a
  ) o
GROUP BY 1

原查询结果 #

目标结果 #

解决方案 #

你需要在原查询外层添加窗口函数实现近3天累计求和，同时通过条件判断确保仅当存在至少3天数据时返回结果，否则留空。修正后的完整SQL如下：

WITH daily_repeat_calls AS (
  SELECT
    date,
    COUNT(o.row_number) FILTER (WHERE o.row_number > 1 AND date_ddr IS NOT NULL AND telephone_number <> 'Anonymous') repeat_calls_24h
  FROM
    (
    SELECT
      telephone_number,
      date_ddr,
      ROW_NUMBER() OVER(PARTITION BY ddr.telephone_number ORDER BY ddr.date) row_number
    FROM
      table_a
    ) o
  GROUP BY 1
)
SELECT
  date,
  repeat_calls_24h,
  -- 仅当当前是第3天及以后时，计算近3天累计值
  CASE
    WHEN ROW_NUMBER() OVER(ORDER BY date) >= 3 THEN
      SUM(repeat_calls_24h) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    ELSE NULL
  END AS "Repeat Calls 3d"
FROM daily_repeat_calls
ORDER BY date;

关键逻辑说明 #

1. 用CTEdaily_repeat_calls封装原查询逻辑，让代码结构更清晰；
2. ROW_NUMBER() OVER(ORDER BY date)标记当前日期的序列位置，前2天直接返回NULL；
3. SUM(...) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)实现对当前行及之前两行的数值求和，即近3天的累计值；
4. 保持按日期排序，确保窗口函数的计算顺序正确。

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