PostgreSQL中针对非连续数据的30天移动平均查询方案

这问题我太有共鸣了！之前用ROWS BETWEEN处理非连续日期的移动平均时踩过大坑——明明要算30天的平均，结果因为中间缺了一周的数据，窗口里只取到了23行，完全不符合预期。

咱得换个思路，用基于日期范围的窗口函数来解决，核心就是让窗口筛选的是「当前日期往前30天内的所有有效记录」，而不是固定行数。下面分步骤给你捋清楚：

第一步：先过滤无效数据 #

首先把response = 2的记录排除，只保留代表“是”和“否”的0和1：

WHERE response != 2

第二步：用RANGE窗口计算移动平均 #

不同数据库的日期语法略有差异，但核心逻辑都是用RANGE替代ROWS，基于created_at的日期范围来定义窗口：

PostgreSQL 实现

PostgreSQL直接支持用INTERVAL来定义日期范围，非常直观：

SELECT
  created_at,
  response,
  AVG(response) OVER (
    ORDER BY created_at
    RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
  ) AS rolling_30d_avg
FROM answers
WHERE response != 2
ORDER BY created_at;

MySQL 8.0+ 实现

MySQL的RANGE窗口不直接支持日期类型，需要转成时间戳（秒数）来计算，30天等于30*24*60*60=2592000秒：

SELECT
  created_at,
  response,
  AVG(response) OVER (
    ORDER BY UNIX_TIMESTAMP(created_at)
    RANGE BETWEEN 2592000 PRECEDING AND CURRENT ROW
  ) AS rolling_30d_avg
FROM answers
WHERE response != 2
ORDER BY created_at;

SQL Server 实现

用DATEADD函数直接计算30天前的日期，作为窗口的起始范围：

SELECT
  created_at,
  response,
  AVG(response) OVER (
    ORDER BY created_at
    RANGE BETWEEN DATEADD(DAY, -30, created_at) PRECEDING AND CURRENT ROW
  ) AS rolling_30d_avg
FROM answers
WHERE response != 2
ORDER BY created_at;

扩展：补全缺失日期的情况 #

如果你的需求是每天都要有一条记录，哪怕当天没有数据也要显示移动平均，那还需要先生成连续的日期序列，再和原数据左连接。比如PostgreSQL的例子：

WITH date_series AS (
  -- 生成覆盖所有有效数据的连续日期
  SELECT generate_series(
    (SELECT MIN(created_at)::DATE FROM answers WHERE response != 2),
    (SELECT MAX(created_at)::DATE FROM answers WHERE response != 2),
    INTERVAL '1 day'
  )::DATE AS date
),
filtered_data AS (
  SELECT
    created_at::DATE AS date,
    response
  FROM answers
  WHERE response != 2
)
SELECT
  ds.date,
  -- 这里的窗口会自动包含前30天内的所有有效数据，哪怕当天没有记录
  AVG(fd.response) OVER (
    ORDER BY ds.date
    RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
  ) AS rolling_30d_avg
FROM date_series ds
LEFT JOIN filtered_data fd ON ds.date = fd.date
ORDER BY ds.date;

这种方法的优势是完全基于日期跨度筛选数据，不管中间有没有缺失的记录，都能准确计算出过去30天的真实平均值。

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