如何统计每天每小时的活跃用户数？(date_format / hour() / 时间分桶)

考察要点

这道题看似简单，实则深入。面试官想通过这个问题，考察你对以下几点的掌握程度：

1. SQL 聚合能力：是否能熟练运用 GROUP BY 和 COUNT(DISTINCT ...) 来实现业务指标的精确统计。
2. 时间函数应用：是否熟悉 DATE_FORMAT、HOUR、DATE 等函数，并能灵活地将连续时间数据离散化（时间分桶）。
3. 性能优化意识：是否能预见到在大数据量下，直接查询的性能瓶颈，并提出索引优化、预计算等工程化解决方案。

简而言之，面试官想看你是否能写出正确、高效、且具备工程扩展性的 SQL。

核心概念

要解决这个问题，核心在于理解“时间分桶”和“精确去重统计”。

1. 时间分桶 (Time Bucketing) 时间分桶是指将连续的时间戳数据，按固定的时间间隔（如每分钟、每小时、每天）归入不同的“桶”中，以便进行聚合统计。本题要求“每天每小时”，即分桶的粒度是小时。

   示例数据：假设我们有一张用户登录日志表 user_logins。

   sql

   ⧉⛶

    1CREATE TABLE user_logins (
    2  id INT AUTO_INCREMENT PRIMARY KEY,
    3  user_id INT NOT NULL,
    4  login_time DATETIME NOT NULL,
    5  INDEX idx_login_time (login_time)
    6);

   id	user_id	login_time
   1	101	2023-10-26 08:05:00
   2	102	2023-10-26 08:15:00
   3	101	2023-10-26 08:30:00
   4	103	2023-10-26 09:02:00
   5	101	2023-10-26 09:10:00

   通过时间分桶，我们需要将 login_time 转换成统一的小时级别标识，如 2023-10-26 08:00:00。

2. 时间处理函数

   • DATE_FORMAT(date, format): 最通用、最灵活的函数。通过 format 参数可以自定义输出格式。
     • DATE_FORMAT(login_time, '%Y-%m-%d %H:00:00') 会将 2023-10-26 08:05:00 格式化为字符串 '2023-10-26 08:00:00'。
   • DATE(date) 和 HOUR(date): 特定用途的函数，分别提取日期和小时部分。
     • DATE('2023-10-26 08:05:00') -> '2023-10-26'
     • HOUR('2023-10-26 08:05:00') -> 8
   • FROM_UNIXTIME / UNIX_TIMESTAMP: 通过时间戳进行数学运算，实现分桶。
     • FLOOR(UNIX_TIMESTAMP(login_time) / 3600) * 3600 将时间戳对 3600（一小时的秒数）取整，实现小时级别的截断。

3. 精确去重聚合 COUNT(DISTINCT expression) “活跃用户数”指的是独立的用户数量，而不是登录次数。在 08:00-09:00 这个小时内，用户 101 登录了两次，但只能算作 1 个活跃用户。

   • COUNT(user_id) 或 COUNT(*): 统计的是行数，即登录次数。
   • COUNT(DISTINCT user_id): 统计的是 user_id 去重后的数量，这才是正确的“活跃用户数”。

多种解法

解法 1：使用 DATE_FORMAT (推荐，最通用)

这种方法通过 DATE_FORMAT 生成一个统一的时间桶标识，然后进行分组聚合。结果清晰，易于理解和在前端展示。

• 版本兼容性: ✅ 所有 MySQL/PostgreSQL/Oracle 版本均支持类似功能。
• SQL 实现:
  sql

  ⧉⛶

   1SELECT
   2    DATE_FORMAT(login_time, '%Y-%m-%d %H:00:00') AS hour_bucket,
   3    COUNT(DISTINCT user_id) AS active_users
   4FROM
   5    user_logins
   6-- 如果需要筛选特定时间段，在这里加 WHERE 条件效率最高
   7-- WHERE login_time >= '2023-10-26 00:00:00' AND login_time < '2023-10-27 00:00:00'
   8GROUP BY
   9    hour_bucket
  10ORDER BY
  11    hour_bucket;

• 复杂度:
  • 时间复杂度：$O(N \log N)$ 或 $O(N)$。取决于数据库 GROUP BY 的实现（基于排序或哈希）。主要开销在于扫描数据。
  • 空间复杂度：$O(M)$，其中 M 是分组后的桶数量。
• 索引依赖:
  • 如果没有 WHERE 条件，通常会进行全表扫描（Full Table Scan）。
  • 如果有 WHERE login_time BETWEEN ... 条件，login_time 列上的索引将极大提升性能，实现范围扫描（Range Scan）。

解法 2：使用 DATE() 和 HOUR() 函数

这种方法分别提取日期和小时进行分组，功能上等价，但在结果呈现上是两列。

• 版本兼容性: ✅ MySQL/MariaDB 支持。其他数据库有类似函数。
• SQL 实现:
  sql

  ⧉⛶

   1SELECT
   2    DATE(login_time) AS login_date,
   3    HOUR(login_time) AS login_hour,
   4    COUNT(DISTINCT user_id) AS active_users
   5FROM
   6    user_logins
   7GROUP BY
   8    login_date,
   9    login_hour
  10ORDER BY
  11    login_date,
  12    login_hour;

• 复杂度: 与解法 1 相同。
• 索引依赖: 与解法 1 相同。
• 适用场景: 当你明确需要日期和小时作为两个独立的列进行后续分析时，这种写法更直观。但通常 DATE_FORMAT 生成的单一时间列更方便。

解法 3：使用 UNIX_TIMESTAMP 进行数学运算

这是一种更底层、更偏向数学运算的思路，不依赖于特定的日期格式化函数，执行效率可能略高，因为主要是整数运算。

• 版本兼容性: ✅ MySQL/MariaDB 支持。
• SQL 实现:
  sql

  ⧉⛶

   1SELECT
   2    -- 将秒级时间戳向下取整到最近的小时，再转换回 DATETIME 格式
   3    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(login_time) / 3600) * 3600) AS hour_bucket,
   4    COUNT(DISTINCT user_id) AS active_users
   5FROM
   6    user_logins
   7GROUP BY
   8    hour_bucket
   9ORDER BY
  10    hour_bucket;

• 复杂度: 与解法 1 相同。
• 索引依赖: 与解法 1 相同。
• 适用场景: 在需要进行跨数据库平台兼容或追求极致性能的场景下可以考虑。可读性相比 DATE_FORMAT 稍差。

常见低分回答

1. 误用 COUNT(*)：SELECT DATE_FORMAT(login_time, '%Y-%m-%d %H'), COUNT(*) FROM ...。这是最严重的错误，计算的是“每小时登录次数”，而不是“每小时活跃用户数”。
2. 在 WHERE 子句中对列使用函数：WHERE DATE_FORMAT(login_time, '%Y-%m-%d') = '2023-10-26'。这会导致 login_time 列上的索引失效，数据库不得不进行全表扫描，然后对每一行计算函数值再进行比较，性能极差。
   • 正确做法：WHERE login_time >= '2023-10-26 00:00:00' AND login_time < '2023-10-27 00:00:00'。这样可以利用索引进行范围查找。
3. GROUP BY 列不一致：SELECT DATE(login_time), HOUR(login_time), ... GROUP BY DATE(login_time)。这在大多数数据库中会直接报错，因为 SELECT 列表中的非聚合列（HOUR(login_time)）没有出现在 GROUP BY 子句中。
4. 忽略时区问题：如果表中存储的是 TIMESTAMP 类型或涉及多个时区的业务，直接使用上述函数可能会因数据库服务器的默认时区而导致统计错误。高分回答应提及使用 CONVERT_TZ 函数进行时区转换。

工程实践与优化

在生产环境中，user_logins 表可能有数十亿甚至上万亿行，直接查询是不可接受的。

• 大数据量下的挑战：

  • I/O 瓶颈：全表扫描耗时过长。
  • CPU/内存瓶颈：GROUP BY 和 COUNT(DISTINCT) 对海量数据进行哈希或排序聚合，会消耗大量 CPU 和内存。

• 优化方案：

  1. 索引优化：为 login_time 列创建索引。如果查询总是带时间范围，这是最基础的优化。
  2. 预聚合/物化视图：这是解决此类问题的终极方案。
     • 创建一个小时级汇总表 summary_hourly_active_users。
     • sql

       ⧉⛶

        1CREATE TABLE summary_hourly_active_users (
        2  hour_bucket DATETIME PRIMARY KEY,
        3  active_users INT
        4);

     • 使用定时任务（如 Airflow、cron job），每小时或每天凌晨，将前一个周期的原始日志 user_logins 聚合计算后，写入或更新到这个汇总表。
     • 所有前端报表和数据分析请求都查询这个轻量的汇总表，响应时间可以做到毫秒级。
  3. 分区表 (Partitioning)：在 MySQL 中，可以按 login_time 对 user_logins 表进行范围分区（如按月分区）。当查询带有时间范围时，数据库优化器可以只扫描相关的分区（分区裁剪），而不是整张表。
  4. 执行计划分析：使用 EXPLAIN 命令分析查询计划。
     • 关注 type 列：ALL (全表扫描) 是灾难，range (范围扫描) 较好，ref (索引查找) 更佳。
     • 关注 Extra 列：出现 Using temporary; Using filesort 意味着需要磁盘临时表和文件排序，这是 GROUP BY 在无法利用索引时的性能瓶颈，需要警惕。

常见追问与延伸题目

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

   • 回答要点 1 (问题分析)：在线直接查询已经完全不可行。千亿级别的数据扫描、去重、聚合的计算成本是巨大的，单机 MySQL 无法在合理时间内完成。
   • 回答要点 2 (架构演进)：必须转向大数据处理和数据仓库架构。
     • 离线数仓：将原始日志通过 ETL 工具（如 DataX, Flink CDC）导入到分布式存储（如 HDFS），然后使用 Spark 或 Hive 在数据仓库（如 Hive, ClickHouse）中进行离线计算，按小时/天生成汇总报表。这是业界最成熟、最主流的方案。
     • 实时数仓：如果要求近实时的活跃用户统计，可以使用 Flink 或 Spark Streaming 消费 Kafka 中的实时日志流，进行窗口计算，并将结果实时写入到像 ClickHouse, Doris, StarRocks 这样的 OLAP 数据库或 Redis 中，供前端查询。

2. 追问：如何统计“连续 3 天活跃”的用户？

   • 回答要点: 这是典型的“用户留存”或“连续行为”分析，需要用到窗口函数。
     • 思路: 先按用户 user_id 分组，按日期 login_date 排序，然后使用 LAG() 或 DATEDIFF() 函数计算连续天数。
     • SQL 示例 (MySQL 8.0+):
       sql

       ⧉⛶

        1WITH daily_active AS (
        2    SELECT DISTINCT user_id, DATE(login_time) AS login_date
        3    FROM user_logins
        4),
        5with_lag AS (
        6    SELECT
        7        user_id,
        8        login_date,
        9        LAG(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_2_day
       10    FROM daily_active
       11)
       12SELECT DISTINCT user_id
       13FROM with_lag
       14WHERE DATEDIFF(login_date, prev_2_day) = 2;

     • 这展示了你对窗口函数和复杂查询的掌握，是重要的加分项。

3. 追问：如果我想看“滚动 24 小时”的活跃用户数，而不是固定的整点小时，该怎么实现？

   • 回答要点: 这个问题考察的是滑动窗口的概念，通常在实时计算中更常见。
     • 思路: 对于每一条登录记录，都需要计算从它发生时刻起，回溯 24 小时内的独立用户数。
     • SQL 实现 (PostgreSQL/Oracle/SQL Server): 这些数据库对窗口函数的 RANGE 子句支持更好。
       sql

       ⧉⛶

        1-- MySQL 8.0+ 的窗口函数对 COUNT(DISTINCT) 支持有限，此语法在 PostgreSQL 中更常见
        2SELECT
        3    login_time,
        4    user_id,
        5    COUNT(DISTINCT user_id) OVER (
        6        ORDER BY UNIX_TIMESTAMP(login_time)
        7        RANGE BETWEEN 86400 PRECEDING AND CURRENT ROW
        8    ) AS rolling_24h_active_users
        9FROM user_logins;

     • MySQL 替代方案: 在 MySQL 中实现这个功能非常困难且低效，通常会采用自连接或在应用层处理。更好的回答是指出这个问题不适合在 MySQL 中进行大规模在线查询，而应该在 Flink/Spark Streaming 等流处理引擎中使用滑动窗口（Sliding Window）来高效实现。这体现了你对不同技术选型边界的理解。