如何在PostgreSQL中用GROUP BY实现日活周活指标统计？-SQL-PHP中文网

group by 必须配合 date_trunc() 截断时间粒度，如 date_trunc('day', login_time) 归到当日00:00，避免用 extract 或 to_char 导致跨年周错乱或性能低下；日活/周活须用 count(distinct user_id)，并注意时区转换（如 at time zone 'asia/shanghai'）确保业务口径一致。

如何在postgresql中用group by实现日活周活指标统计？

GROUP BY 配合日期截断函数是核心

直接用 GROUP BY login_time 肯定不行——时间戳精度到秒，根本聚不到“天”或“周”。必须先用日期截断函数把时间归到目标粒度，再分组。PostgreSQL 提供 date_trunc()，它比手动 CAST(login_time AS DATE) 更可靠，尤其处理周统计时能自动对齐周一（或周日，取决于 lc_time 设置）。

常见错误是写成 GROUP BY EXTRACT(YEAR FROM login_time), EXTRACT(WEEK FROM login_time)：这会导致跨年周错乱（比如 2024-12-30 属于 2025 年第 1 周），而 date_trunc('week', login_time) 返回的是该周周一的日期，天然避免这个问题。

date_trunc('day', login_time) → 归到当天 00:00:00
date_trunc('week', login_time) → 归到当周周一 00:00:00（默认，可配置）
别用 TO_CHAR(login_time, 'YYYY-WW') 做分组键，字符串比较慢，且无法直接参与日期运算

去重计数必须用 COUNT(DISTINCT user_id)

日活（DAU）、周活（WAU）本质是去重用户数，不是总登录次数。如果只写 COUNT(user_id)，一个用户一天登 5 次就记作 5，结果会严重高估。

注意 COUNT(DISTINCT user_id) 在大数据量下可能较慢，但这是语义正确性的底线。PostgreSQL 13+ 对此有优化，但如果表超大且无索引，考虑加部分索引：

CREATE INDEX idx_login_user_day ON login_log (date_trunc('day', login_time), user_id);

确保 user_id 列非空，否则 COUNT(DISTINCT) 会忽略 NULL 值，导致漏计
不要在 WHERE 中提前过滤掉重复 user_id（比如用子查询去重），那会破坏分组逻辑
若业务允许近似值，可用 APPROX_COUNT_DISTINCT(user_id)（需安装 hll 扩展），但生产环境慎用

单条 SQL 同时查 DAU 和 WAU 要用条件聚合

别写两个独立查询再 JOIN，效率低还难维护。用 CASE WHEN + 聚合函数，在一次扫描中完成多维度统计：

SELECT
  date_trunc('day', login_time) AS day,
  COUNT(DISTINCT user_id) AS dau,
  COUNT(DISTINCT CASE WHEN login_time >= CURRENT_DATE - INTERVAL '6 days' THEN user_id END) AS wau_7d
FROM login_log
WHERE login_time >= CURRENT_DATE - INTERVAL '6 days'
GROUP BY 1
ORDER BY 1;

关键点：

WHERE 先限定时间范围（比如最近 7 天），避免全表扫描；WAU 的计算窗口必须包含在该范围内
CASE WHEN 内部不加 ELSE NULL 是安全的，因为 COUNT(DISTINCT ...) 本就会跳过 NULL
别用 DATE_PART('week', login_time) 算 WAU，它返回的是纯数字（如 23），无法区分年份，跨年时会把 2024-W52 和 2025-W01 当作同一周