求过去 7 天每个视频的去重观看用户数（uv）和总播放次数（pv）。

考察要点

这道题面试官想看你是否懂：

1. 核心聚合能力：熟练运用 GROUP BY、COUNT(*) 和 COUNT(DISTINCT) 实现业务指标（PV/UV）的计算。
2. SQL 性能意识：是否知道在 WHERE 子句中对列使用函数（如 DATE_FORMAT）会导致索引失效，并能写出索引友好的时间范围查询。
3. 大数据思维：是否理解 COUNT(DISTINCT) 在海量数据下的性能瓶颈，并了解其优化思路（如预聚合或近似计算）。

核心概念

本题的核心是基于用户行为日志表，进行聚合统计。我们首先定义一个简化的日志表结构。

DDL 示例：

 1CREATE TABLE video_play_log (
 2    log_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 3    video_id INT UNSIGNED NOT NULL,
 4    user_id INT UNSIGNED NOT NULL,
 5    play_time DATETIME NOT NULL,
 6    INDEX idx_playtime_videoid (play_time, video_id) -- 关键索引
 7) ENGINE=InnoDB;

核心概念定义：

多种解法

假设我们需要查询从今天（含）起，过去 7 天的数据。

解法 1：标准 GROUP BY 聚合（通用解法）

这是最直接、最标准的解法，适用于绝大多数数据库系统和中小型数据量。

• 思路：通过 WHERE 子句筛选出最近 7 天的日志，然后按 video_id 分组，在组内分别使用 COUNT(DISTINCT user_id) 和 COUNT(*) 计算 UV 和 PV。
• 适用版本：✅ 所有 SQL 数据库通用。
• SQL 实现：
  sql

  ⧉⛶

   1SELECT
   2    video_id,
   3    COUNT(DISTINCT user_id) AS uv,
   4    COUNT(*) AS pv
   5FROM
   6    video_play_log
   7WHERE
   8    -- 假设 '过去7天' 包含今天。CURDATE() 返回 'YYYY-MM-DD'
   9    -- play_time >= '2023-10-27 00:00:00' (如果今天是 2023-11-02)
  10    play_time >= CURDATE() - INTERVAL 6 DAY
  11GROUP BY
  12    video_id;

• 复杂度：
  • 时间复杂度：$O(N)$，其中 N 是满足时间条件的行数。主要开销在 GROUP BY 和 COUNT(DISTINCT) 的哈希计算上。
  • 空间复杂度：$O(V + U_{max})$，其中 V 是时间范围内的视频数量， $U_{max}$ 是单个视频对应的最大独立用户数，用于 COUNT(DISTINCT) 的临时存储。
• 索引依赖：
  • 必须在 play_time 列上建立索引，否则 WHERE 子句将导致全表扫描。
  • 最佳索引是 (play_time, video_id, user_id) 复合索引。play_time 用于范围过滤，video_id 和 user_id 可用于覆盖索引（Covering Index），避免回表，MySQL 8.0+ 在特定情况下还能利用索引完成 GROUP BY 和 DISTINCT 操作（Extra: Using index for group-by）。

解法 2：近似计算（大数据场景解法）

当数据量达到数十亿甚至百亿级别时，精确的 COUNT(DISTINCT) 会成为性能噩梦。在很多业务场景下，一个误差率极低（如 1%-2%）的近似值是完全可以接受的。

• 思路：使用数据库或大数据系统提供的近似去重函数，如基于 HyperLogLog 算法的函数。
• 适用版本：❌ MySQL 原生不支持。✅ ClickHouse, Presto, Spark SQL, PostgreSQL (需 HLL 插件)。
• SQL 实现 (以 Presto/ClickHouse 语法为例)：
  sql

  ⧉⛶

   1-- Presto / Spark SQL 语法
   2SELECT
   3    video_id,
   4    approx_distinct(user_id) AS approx_uv, -- 近似去重
   5    COUNT(*) AS pv
   6FROM
   7    video_play_log
   8WHERE
   9    play_time >= CURRENT_DATE - INTERVAL '6' DAY
  10GROUP BY
  11    video_id;
  12
  13-- ClickHouse 语法
  14SELECT
  15    video_id,
  16    uniq(user_id) AS approx_uv, -- uniq 是 HLL 的实现
  17    count() AS pv
  18FROM
  19    video_play_log
  20WHERE
  21    play_time >= today() - 6
  22GROUP BY
  23    video_id;

• 复杂度：
  • 时间复杂度：$O(N)$，但其常数因子远小于精确的 COUNT(DISTINCT)。
  • 空间复杂度：$O(V \cdot \log(\log U_{max}))$，HyperLogLog 的优势在于它使用固定且极小的内存（通常几 KB）来估计一个巨大集合的基数，空间复杂度与基数大小无关。
• 索引依赖：同解法 1，play_time 上的索引至关重要。

何时使用：当 UV 指标用于趋势观察、数据看板等对精度要求不极致的场景，且数据量巨大（TB 级别）时，这是生产环境中的首选方案。

常见低分回答

1. 在 WHERE 子句的索引列上使用函数：这是最常见且最致命的错误，直接导致索引失效，查询性能从毫秒级/秒级下降到分钟级甚至更长。
   sql

   ⧉⛶

    1-- 错误 ❌
    2SELECT video_id, COUNT(DISTINCT user_id)
    3FROM video_play_log
    4WHERE DATE_FORMAT(play_time, '%Y-%m-%d') >= '2023-10-27' -- 函数包裹，索引失效
    5GROUP BY video_id;

2. 对时间范围的理解和处理不精确：使用 BETWEEN 可能会包含边界外的意外数据，尤其是在 DATETIME 类型上。
   sql

   ⧉⛶

    1-- 不推荐 ⚠️
    2-- 如果 play_time 是 DATETIME, '2023-11-02' 实际是 '2023-11-02 00:00:00'
    3-- 这会丢失 11月2号这一整天的数据。
    4WHERE play_time BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY

   更稳健的写法是使用 >= 和 < 构成的左闭右开区间。
3. 混淆 COUNT(列) 和 COUNT(*)：虽然在本题中 COUNT(*) 和 COUNT(log_id) 结果一致，但面试者如果能清晰说出 COUNT(*) 的含义和优化，会是加分项。误用 COUNT(user_id) 计算 PV 是错误，因为它会忽略 user_id 为 NULL 的情况（尽管本场景下 user_id 通常不为 NULL）。
4. 不考虑 COUNT(DISTINCT) 的性能开销：只给出解法 1，当面试官追问数据量大的情况时，无法回答出其性能瓶颈和优化方案（如解法 2 或预聚合）。

工程实践与优化

• 预聚合 (Summary Table)：这是应对大数据量查询的“银弹”。与其每次查询都实时计算 7 天的数据，不如创建一个后台任务（如每日凌晨执行），计算出每个视频前一天的 PV 和 UV，并存入一张“视频日统计表” video_daily_stats。

  sql

  ⧉⛶

   1-- 每日统计表结构
   2CREATE TABLE video_daily_stats (
   3    stat_date DATE,
   4    video_id INT UNSIGNED,
   5    pv BIGINT,
   6    uv BIGINT,
   7    PRIMARY KEY (stat_date, video_id)
   8);

  查询时，只需对这张小得多的结果表进行简单聚合。

  sql

  ⧉⛶

   1-- 基于预聚合表的查询，性能极高
   2SELECT
   3    video_id,
   4    SUM(uv) AS total_uv, -- 注意：这里的 UV 求和是近似值，不完全精确
   5    SUM(pv) AS total_pv
   6FROM
   7    video_daily_stats
   8WHERE
   9    stat_date >= CURDATE() - INTERVAL 6 DAY
  10GROUP BY
  11    video_id;

  注意：7 天的 UV 不等于每天 UV 的简单加和（SUM(uv)），因为用户会跨天访问。要精确计算，日统计表需要存储用户ID的位图（Bitmap）或 HLL 结构，然后在查询时合并。

• 执行计划分析 (EXPLAIN)：对于解法 1，EXPLAIN 的输出至关重要。

  • type: 应该是 range，表示利用索引进行了范围扫描。如果是 ALL，说明索引失效，需要检查 WHERE 子句。
  • key: 应该是 idx_playtime_videoid，表示实际使用的索引。
  • Extra:
    • Using where: 表示 WHERE 子句被使用。
    • Using index for group-by: 这是一个很好的信号，表示 GROUP BY 操作也利用了索引，避免了额外的排序和临时表。
    • Using temporary; Using filesort: 坏信号，表示 MySQL 需要创建临时表并进行文件排序来完成 GROUP BY，通常是因为索引无法覆盖 GROUP BY 的列。

常见追问与延伸题目

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

   • 回答要点 1 (性能瓶颈)：直接查询性能会急剧恶化，甚至无法在有效时间内返回结果。主要瓶颈有两个：一是 WHERE 子句需要扫描的数据量剧增；二是 COUNT(DISTINCT) 对内存和 CPU 的消耗会成为主要矛盾，可能导致内存溢出或临时表写磁盘，IO 飙升。
   • 回答要点 2 (优化方案)：首选方案是预聚合。如上所述，建立日维度的统计表，将 T+1 的计算压力分摊到每日凌晨的批处理任务中。查询时只查这张小很多的统计表，响应速度可以保持在秒级。如果对 UV 的精度要求不高，可以引入近似计算（HyperLogLog），在预聚合阶段就将用户ID集合压缩成 HLL 结构存入日统计表，查询时合并 HLL 即可得到近乎实时的精确（近似）UV。

2. 追问：如果要看过去 7 天，每天的 Top 10 UV 的视频呢？

   • 回答要点：这需要使用窗口函数 ROW_NUMBER() 或 RANK()。首先按天和视频分组计算 UV，然后用窗口函数对每天的视频按 UV 进行排名，最后筛选出排名小于等于 10 的记录。
   • SQL 示例 (MySQL 8.0+)：
     sql

     ⧉⛶

      1WITH daily_uv AS (
      2    SELECT
      3        DATE(play_time) AS dt,
      4        video_id,
      5        COUNT(DISTINCT user_id) AS uv
      6    FROM video_play_log
      7    WHERE play_time >= CURDATE() - INTERVAL 6 DAY
      8    GROUP BY dt, video_id
      9),
     10ranked_videos AS (
     11    SELECT
     12        dt,
     13        video_id,
     14        uv,
     15        ROW_NUMBER() OVER(PARTITION BY dt ORDER BY uv DESC) as rn
     16    FROM daily_uv
     17)
     18SELECT dt, video_id, uv
     19FROM ranked_videos
     20WHERE rn <= 10;

3. 追问：如果换成 PostgreSQL 或 ClickHouse，答案有什么不同？

   • 回答要点 1 (PostgreSQL)：基本语法（解法 1）完全兼容。PG 的 COUNT(DISTINCT) 性能在某些版本和场景下可能优于 MySQL。PG 社区有成熟的 postgresql-hll 插件，可以轻松实现解法 2（近似计算）。此外，PG 的查询优化器和索引类型（如 BRIN 索引对时序数据友好）也可能提供不同的优化策略。
   • 回答要点 2 (ClickHouse)：ClickHouse 是为在线分析处理（OLAP）而生的列式数据库，是解决此类问题的“专业选手”。它的 COUNT(DISTINCT) 实现（uniq 函数，基于 HLL）性能极高，通常可以直接在海量数据上运行解法 1 的查询而无需预聚合，速度远超 MySQL。其数据压缩能力和列式存储特性，使得扫描和聚合操作非常快。因此，在 ClickHouse 中，直接查询原始日志是更常见的做法。