如何在SQL中使用窗口函数计算过去30天活跃用户数-SQL-PHP中文网

窗口函数中ROWS BETWEEN 29 PRECEDING AND CURRENT ROW按行数而非自然日滚动，无法准确统计过去30天去重用户数；PostgreSQL等支持RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW，但需先按天去重聚合且event_date为DATE类型；MySQL 8.0+需用LATERAL或自连接模拟。

如何在sql中使用窗口函数计算过去30天活跃用户数

窗口函数不能直接计算“过去30天”滚动范围

直接用 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW 算的是按行偏移的30行，不是按日期滚动的30天。如果某天没数据，就会漏掉；如果某天有多条记录，又会重复计数。真正要的是「截止当前行日期，往前推30个自然日」的去重用户数，这必须结合 RANGE 或自连接/子查询实现，而多数数据库（如 PostgreSQL、Snowflake）支持 RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW，但 MySQL 8.0+ 才开始有限支持，且要求排序列是 DATE 或 DATETIME 类型且无重复值（否则 RANGE 会合并相同值的行，导致统计不准）。

PostgreSQL 中用 RANGE + INTERVAL 最简洁

前提是你的活跃事件表有明确的 event_date（DATE 类型）和 user_id，且每天每个用户可能有多次行为。你需要先按天聚合去重用户，再开窗：

SELECT
  event_date,
  COUNT(DISTINCT user_id) AS daily_active_users,
  COUNT(DISTINCT user_id) OVER (
    ORDER BY event_date
    RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
  ) AS active_users_last_30d
FROM (
  SELECT DISTINCT event_date::DATE, user_id
  FROM user_events
  WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
) d
GROUP BY event_date
ORDER BY event_date;

注意三点：
• 必须先 DISTINCT 去重，否则同一天多个事件会让 COUNT(DISTINCT) 在窗口内失效；
• RANGE 要求 ORDER BY 列是单调递增且类型匹配（DATE 对 INTERVAL）；
• 如果 event_date 是 TIMESTAMP，得显式转成 DATE，否则 RANGE 可能不生效或报错。