SQL 如何计算日活、周活、月活指标？

日活（DAU）按天去重用户数，即用date()截日期后对user_id做count(DISTINCT)；周活（WAU）分自然周（如YEARWEEK(event_time,1)）和滚动7天（如event_time≥’2024-01-04’）。

日活（DAU）怎么算：按天去重用户数

日活本质是统计某一天内有行为的独立用户数量，关键在「当天」和「去重」。用 DATE() 或数据库特有日期截断函数把时间字段转成日期，再对用户 ID 做 COUNT(DISTINCT user_id)。

常见错误是直接 COUNT(user_id) —— 这会把同一用户多次访问算成多条，结果虚高；或者用 GROUP BY event_time（带时分秒），导致同一天多个时间点被拆成多组。

• mysql 示例：

  SELECT DATE(event_time) AS dt, COUNT(DISTINCT user_id) AS dau FROM events WHERE event_time >= '2024-01-01' GROUP BY DATE(event_time);

• postgresql 要用 event_time::date，而不是 DATE(event_time)
• 注意时区：如果 event_time 是 UTC，而业务按北京时间算日活，得先 AT TIME ZONE 'Asia/Shanghai' 再截日期

周活（WAU）怎么算：按自然周或滚动 7 天？

周活有两种主流口径：一种是自然周（周一到周日），一种是滚动 7 天（比如今天往前推 7 天）。自然周适合看趋势对比，滚动周更反映近期活跃水位。

自然周计算依赖数据库的周起始设定（MySQL 默认周日为一周开始，PostgreSQL 默认周一），容易出错。建议显式控制起始日。

• MySQL 自然周（周一为起点）：

  SELECT YEARWEEK(event_time, 1) AS week_id, COUNT(DISTINCT user_id) AS wau FROM events GROUP BY week_id;

  其中 1 表示周一为每周第一天

• 滚动 7 天 WAU（以 2024-01-10 为例）：

  SELECT COUNT(DISTINCT user_id) AS wau FROM events WHERE event_time >= '2024-01-04' AND event_time < '2024-01-11';

• 别用 BETWEEN 包含右边界，避免跨天数据重复计入多个滚动窗口

月活（MAU）要注意时间边界和性能

月活通常是自然月（1 号到月末最后一天），但要注意不同月份天数不同，不能简单写死日期范围。另外，大表上 COUNT(DISTINCT) 容易慢，尤其用户量超千万时。

• 安全写法（MySQL）：

  SELECT YEAR(event_time) AS y, MONTH(event_time) AS m, COUNT(DISTINCT user_id) AS mau FROM events GROUP BY y, m;

  比拼接 CONCAT(YEAR(), '-', MONTH()) 更利于索引下推

• 如果表有分区（按月分区），确保 WHERE 条件能命中分区，否则全表扫描
• 用户 ID 是字符串且很长（如 UUID），COUNT(DISTINCT) 内存开销大，可考虑采样估算（如 HyperLogLog，PostgreSQL 的 HLL 扩展或 clickhouse 的 uniq()）

一个 SQL 同时查 DAU/WAU/MAU：小心逻辑陷阱

想在一个查询里输出某天的 DAU、对应周的 WAU、对应月的 MAU，不能简单用多个 COUNT(DISTINCT) 套在同一层 GROUP BY DATE() 下——因为 WAU 和 MAU 的时间范围远大于当天，会导致聚合逻辑混乱。

正确做法是用子查询或 CTE 分别计算，再通过日期字段关联。否则会出现 WAU/MAU 数值异常偏小（只统计了当天用户的周/月行为）或偏大（漏了 JOIN 条件）。

• 推荐结构（以 PostgreSQL 为例）：

  WITH dau AS (SELECT DATE(event_time) AS dt, COUNT(DISTINCT user_id) AS val FROM events GROUP BY 1),
  wau AS (SELECT DATE(event_time) AS dt, COUNT(DISTINCT user_id) OVER (PARTITION BY DATE_TRUNC('week', event_time)) AS val FROM events),
  mau AS (SELECT DATE(event_time) AS dt, COUNT(DISTINCT user_id) OVER (PARTITION BY DATE_TRUNC('month', event_time)) AS val FROM events)
  SELECT d.dt, d.val AS dau, w.val AS wau, m.val AS mau FROM dau d JOIN wau w ON d.dt = w.dt JOIN mau m ON d.dt = m.dt;

• 注意：窗口函数版 WAU/MAU 在大数据量下可能内存暴涨，生产环境优先用预聚合表或物化视图
• 别忽略 NULL 用户 ID：加 WHERE user_id IS NOT NULL，否则 COUNT(DISTINCT) 会把 NULL 当作一个有效值

实际跑这些指标时，最常被跳过的其实是数据清洗环节：登录态不一致、游客 ID 泄露、埋点重复上报，都会让去重结果失真。指标数字本身不难算，难的是它到底代表什么。