DAU、WAU、MAU 的查询和它们的关系？粘性 = DAU/MAU 的 SQL？

考察要点

这道题面试官想看你是否懂时间序列分析、聚合函数和SQL 编程能力。核心是考察三个方面：

1. 精准的业务指标翻译能力：能否将 DAU、MAU、粘性等业务术语准确无误地翻译成 SQL 中的 COUNT(DISTINCT ...) 和 GROUP BY 组合。
2. 熟练的日期函数运用：能否使用 DATE(), DATE_FORMAT(), DATE_SUB() 等函数对时间戳进行切割、格式化和范围筛选。
3. 复杂查询的构建能力：计算 DAU/MAU 这种复合指标，需要用到子查询或 CTE（公用表表达式），这能体现候选人的 SQL 逻辑组织能力。

核心概念

要准确计算这些指标，首先要理解其定义和在 SQL 中的实现。

假设我们有一张用户登录日志表 user_logs：

 1CREATE TABLE user_logs (
 2    log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
 3    user_id INT,
 4    login_ts DATETIME,
 5    INDEX idx_login_ts_userid (login_ts, user_id)
 6);

数据示例：

1. DAU (Daily Active Users)：每日的独立活跃用户数。

   • SQL 实现：COUNT(DISTINCT user_id) 按天 GROUP BY。
   • 示例：计算 2023-10-01 的 DAU。
     sql

     ⧉⛶

      1SELECT
      2    DATE(login_ts) AS dt,
      3    COUNT(DISTINCT user_id) AS dau
      4FROM user_logs
      5WHERE DATE(login_ts) = '2023-10-01'
      6GROUP BY dt;

   • 输出：

     dt	dau
     2023-10-01	2

2. WAU (Weekly Active Users)：每周的独立活跃用户数。

   • SQL 实现：COUNT(DISTINCT user_id) 按周 GROUP BY。注意周的定义（周一或周日开始）。YEARWEEK(date, mode) 是 MySQL 的常用函数。
   • 示例：计算 2023 年第 39 周（假设 10 月 1 日所在周）的 WAU。
     sql

     ⧉⛶

      1SELECT
      2    YEARWEEK(login_ts, 1) AS yw, -- mode=1 表示周一为一周的开始
      3    COUNT(DISTINCT user_id) AS wau
      4FROM user_logs
      5WHERE YEARWEEK(login_ts, 1) = '202339'
      6GROUP BY yw;

   • 输出：

     yw	wau
     202339	3

3. MAU (Monthly Active Users)：每月的独立活跃用户数。

   • SQL 实现：COUNT(DISTINCT user_id) 按月 GROUP BY。
   • 示例：计算 2023 年 10 月的 MAU。
     sql

     ⧉⛶

      1SELECT
      2    DATE_FORMAT(login_ts, '%Y-%m') AS ym,
      3    COUNT(DISTINCT user_id) AS mau
      4FROM user_logs
      5WHERE login_ts >= '2023-10-01' AND login_ts < '2023-11-01' -- 推荐的索引友好写法
      6GROUP BY ym;

   • 输出：

     ym	mau
     2023-10	3

4. 粘性 (Stickiness)：通常指 月平均 DAU / MAU。它衡量了用户在一个月内回访的频繁程度。

   • 公式：Stickiness = (SUM(DAU_day1, ..., DAU_dayN) / N) / MAU，其中 N 是当月天数。
   • 边界：计算时必须明确月份，例如计算“2023年10月”的粘性。

多种解法

以下解法均以计算 2023年10月 的粘性为例。

解法 1：使用 CTE (公用表表达式)

这是最清晰、可读性最好、最推荐的现代 SQL 写法。

• 版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server；❌ MySQL 5.7 不支持。

• 思路：

  1. dau_cte：计算出 10 月份每一天的 DAU。
  2. mau_cte：计算出 10 月份的总 MAU。
  3. 主查询：从 dau_cte 计算平均 DAU，从 mau_cte 获取 MAU，然后相除。

• SQL 代码：

  sql

  ⧉⛶

   1WITH
   2dau_cte AS (
   3    SELECT
   4        COUNT(DISTINCT user_id) AS dau
   5    FROM user_logs
   6    WHERE login_ts >= '2023-10-01' AND login_ts < '2023-11-01'
   7    GROUP BY DATE(login_ts)
   8),
   9mau_cte AS (
  10    SELECT
  11        COUNT(DISTINCT user_id) AS mau
  12    FROM user_logs
  13    WHERE login_ts >= '2023-10-01' AND login_ts < '2023-11-01'
  14)
  15SELECT
  16    AVG(dau_cte.dau) AS avg_dau,
  17    mau_cte.mau,
  18    AVG(dau_cte.dau) / mau_cte.mau AS stickiness
  19FROM dau_cte, mau_cte;

• 时间/空间复杂度：$O(M \log U)$，其中 $M$ 是当月日志条数，$U$ 是当月独立用户数。GROUP BY 和 DISTINCT 通常使用 Hash 或排序实现。

• 索引依赖：idx_login_ts_userid (login_ts, user_id) 索引是理想的。WHERE 条件利用 login_ts 进行范围扫描（range scan），后续的 COUNT(DISTINCT user_id) 可以直接在索引上完成，实现覆盖索引，避免回表。

解法 2：使用子查询

对于不支持 CTE 的老版本数据库（如 MySQL 5.7），子查询是标准解法。

• 版本：✅ 所有 SQL 版本。

• 思路：将 CTE 的逻辑改写为 FROM 子句中的派生表或 SELECT 子句中的标量子查询。

• SQL 代码：

  sql

  ⧉⛶

   1SELECT
   2    (SELECT AVG(dau) FROM (
   3        SELECT COUNT(DISTINCT user_id) AS dau
   4        FROM user_logs
   5        WHERE login_ts >= '2023-10-01' AND login_ts < '2023-11-01'
   6        GROUP BY DATE(login_ts)
   7    ) AS daily_counts) AS avg_dau,
   8
   9    (SELECT COUNT(DISTINCT user_id)
  10     FROM user_logs
  11     WHERE login_ts >= '2023-10-01' AND login_ts < '2023-11-01'
  12    ) AS mau,
  13
  14    (SELECT AVG(dau) FROM (
  15        SELECT COUNT(DISTINCT user_id) AS dau
  16        FROM user_logs
  17        WHERE login_ts >= '2023-10-01' AND login_ts < '2023-11-01'
  18        GROUP BY DATE(login_ts)
  19    ) AS daily_counts)
  20    /
  21    (SELECT COUNT(DISTINCT user_id)
  22     FROM user_logs
  23     WHERE login_ts >= '2023-10-01' AND login_ts < '2023-11-01'
  24    ) AS stickiness;

• 时间/空间复杂度：与 CTE 版本类似。现代数据库的查询优化器通常能将多个相同的子查询优化为一次执行。

• 索引依赖：同上，idx_login_ts_userid (login_ts, user_id) 索引至关重要。

常见低分回答

1. WHERE 条件中使用函数：WHERE DATE_FORMAT(login_ts, '%Y-%m') = '2023-10'。这会导致 login_ts 列上的索引失效，数据库被迫进行全表扫描，性能极差。正确做法是使用范围查询 login_ts >= '2023-10-01' AND login_ts < '2023-11-01'。
2. 误用 COUNT：使用 COUNT(user_id) 或 COUNT(*) 代替 COUNT(DISTINCT user_id)。这计算的是登录总次数（PV），而不是用户数（UV），完全曲解了指标的含义。
3. 错误的粘性计算逻辑：SELECT COUNT(DISTINCT user_id) / COUNT(DISTINCT DATE(login_ts)) ...。这种写法毫无意义，分子分母的聚合粒度不同，无法得出正确结果。
4. 将 DAU 求和：SUM(DAU) / MAU。粘性的分子是平均 DAU，而不是 DAU 的总和。DAU 的总和在业务上没有明确意义。
5. 忽略时区问题：如果 login_ts 是 UTC 时间，而业务要求按特定时区（如 Asia/Shanghai）计算，需要使用 CONVERT_TZ() 函数进行转换，否则日期切分会出错。

工程实践与优化

• 数据量级与预计算：在生产环境中，user_logs 表通常是百亿甚至千亿级别。直接在原始日志表上进行 COUNT(DISTINCT) 是灾难性的。最佳实践是进行预计算（Pre-aggregation）。
  • ETL 过程：每天凌晨通过 Spark、Flink 或定时 SQL 任务，计算好前一天的 DAU，并生成一张用户粒度的日活表 dws_user_active_daily(user_id, dt)。
  • 查询汇总表：所有 DAU/WAU/MAU 的查询都基于这张轻量的汇总表进行，性能提升百倍以上。粘性计算也从这张表出发，查询压力大大减小。
• 近似计算 HyperLogLog：对于允许一定误差的场景（如内部看板），COUNT(DISTINCT) 的成本太高。可以使用 HyperLogLog 算法进行近似去重计数。MySQL 8.0+ 虽然没有原生 HLL，但可以通过用户自定义函数（UDF）实现。ClickHouse、PostgreSQL（带扩展）、Redis 等数据库原生支持 HLL，性能极高，内存消耗极小。
• 执行计划分析：使用 EXPLAIN 分析查询。
  • type：理想情况是 range（使用了索引范围扫描）。最差是 ALL（全表扫描）。
  • key：确认是否用上了我们期望的 idx_login_ts_userid 索引。
  • Extra：Using index 是最佳状态，表示实现了覆盖索引。Using where 表示在存储引擎层过滤。Using temporary; Using filesort 是性能噩梦，表示需要创建临时表并进行文件排序，必须优化。
• 分区表：对 user_logs 这种时序数据表，按时间（如月或日）进行分区是标准操作。PARTITION BY RANGE (TO_DAYS(login_ts))。这样查询时，优化器可以自动进行分区裁剪（Partition Pruning），只扫描相关的分区，极大减少 I/O。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，这条 SQL 性能怎样？怎么优化？

   • 回答要点 1 (性能瓶颈)：直接查询原始表会变得不可行。COUNT(DISTINCT) 操作对 CPU 和内存消耗巨大，全表扫描或大范围索引扫描的 I/O 会成为瓶颈，查询可能需要数小时甚至超时失败。
   • 回答要点 2 (核心优化思路)：核心思路是“不要算，要查”。
     • 预计算：建立日/周/月粒度的用户活跃事实表（dws_user_active_daily）。将计算压力分摊到每日凌晨的 ETL 任务中，线上查询只对结果表进行简单聚合。
     • 物化视图：如果数据库支持（如 PostgreSQL, Oracle, ClickHouse），可以创建物化视图来自动缓存聚合结果。
     • 近似算法：引入 HyperLogLog，将精确计算转换为近似计算，用极小的误差换取数量级的性能提升。

2. 追问：如何计算“次日留存率”？请写出 SQL。

   • 回答要点：次日留存率 = (第一天活跃且第二天也活跃的用户数) / (第一天的总活跃用户数)。这考察的是 LEFT JOIN 或 INNER JOIN 的巧妙运用。
   • SQL 示例 (以 2023-10-01 为例)：
     sql

     ⧉⛶

      1-- 使用 LEFT JOIN
      2SELECT
      3    COUNT(t2.user_id) * 1.0 / COUNT(t1.user_id) AS retention_rate
      4FROM
      5    (SELECT DISTINCT user_id FROM user_logs WHERE DATE(login_ts) = '2023-10-01') AS t1
      6LEFT JOIN
      7    (SELECT DISTINCT user_id FROM user_logs WHERE DATE(login_ts) = '2023-10-02') AS t2
      8ON t1.user_id = t2.user_id;
      9
     10-- 使用 INNER JOIN (分子) 和独立子查询 (分母)
     11SELECT
     12    (SELECT COUNT(*) FROM
     13        (SELECT DISTINCT user_id FROM user_logs WHERE DATE(login_ts) = '2023-10-01') a
     14        INNER JOIN
     15        (SELECT DISTINCT user_id FROM user_logs WHERE DATE(login_ts) = '2023-10-02') b
     16        ON a.user_id = b.user_id
     17    ) * 1.0 /
     18    (SELECT COUNT(DISTINCT user_id) FROM user_logs WHERE DATE(login_ts) = '2023-10-01');

3. 追问：如果换成 ClickHouse 或 PostgreSQL，这个问题的解法有什么不同？

   • 回答要点 (ClickHouse)：
     • 性能：ClickHouse 是列式存储，为 OLAP 而生，即使在原始表上进行聚合查询，性能也远超 MySQL。
     • 函数：可以使用 toYYYYMM()、toStartOfDay() 等更丰富的日期函数。
     • 近似计算：原生支持 uniqCombined 函数（HyperLogLog 的实现）进行高效近似去重，是计算大规模 UV 的首选。SELECT uniqCombined(user_id) FROM ...。
     • 物化视图：强大的物化视图功能可以实时地将插入 user_logs 的数据聚合到一张汇总表中，实现实时看板。
   • 回答要点 (PostgreSQL)：
     • CTE 支持：和 MySQL 8.0 一样，支持 CTE，解法 1 同样适用且推荐。
     • 窗口函数：PG 的窗口函数功能比 MySQL 更强大，可以用来解决更复杂的用户行为路径分析问题。
     • 扩展：可以安装 postgresql-hll 扩展来支持 HyperLogLog，实现近似计算。
     • 时间函数：date_trunc('day', login_ts) 是一个非常强大和常用的截断时间的函数，比 DATE() 更具通用性。