SQL 用户活跃时段分布：按小时聚合统计（小红书面试题）

一、题目

按小时将用户活跃日志映射到 6 个时段（凌晨/清晨/上午/下午/晚间/深夜），统计每个时段的活跃用户数（去重）和行为总次数，按活跃用户数降序输出。

假设有用户活跃日志表 t8_user_active_log：

+----------+----------+-------------------+
| user_id  |  action  |    action_time    |
+----------+----------+-------------------+
| u01      | browse   | 2025-06-01 08:30  |
| u02      | browse   | 2025-06-01 09:00  |
| u01      | like     | 2025-06-01 09:15  |
| u03      | browse   | 2025-06-01 12:00  |
| u01      | comment  | 2025-06-01 12:30  |
| u02      | post     | 2025-06-01 14:00  |
| u03      | like     | 2025-06-01 18:00  |
| u04      | browse   | 2025-06-01 19:30  |
| u04      | save     | 2025-06-01 20:00  |
| u01      | browse   | 2025-06-01 22:30  |
| u02      | browse   | 2025-06-01 23:00  |
| u03      | like     | 2025-06-01 23:15  |
+----------+----------+-------------------+

二、思路分析

1. 使用 substr(action_time, 12, 2) 提取小时，并用 cast(... as int) 转为整数；
2. 使用 case when 将小时映射为6个时段标签；
3. 按时段分组，统计去重用户数（count(distinct user_id)）和行为次数（count(1)）；
4. 按活跃用户数降序排列。

维度	评分
题目难度	⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

三、逐步推导

1.提取小时并映射时段标签

执行SQL

select user_id,
       action,
       cast(substr(action_time, 12, 2) as int) as action_hour,
       case when cast(substr(action_time, 12, 2) as int) between 0 and 3 then '凌晨(0-3)'
            when cast(substr(action_time, 12, 2) as int) between 4 and 7 then '清晨(4-7)'
            when cast(substr(action_time, 12, 2) as int) between 8 and 11 then '上午(8-11)'
            when cast(substr(action_time, 12, 2) as int) between 12 and 17 then '下午(12-17)'
            when cast(substr(action_time, 12, 2) as int) between 18 and 21 then '晚间(18-21)'
            when cast(substr(action_time, 12, 2) as int) between 22 and 23 then '深夜(22-23)'
            else '未知' end as time_period
from t8_user_active_log

查询结果

+----------+----------+--------------+--------------+
| user_id  |  action  | action_hour  | time_period  |
+----------+----------+--------------+--------------+
| u01      | browse   | 8            | 上午(8-11)     |
| u02      | browse   | 9            | 上午(8-11)     |
| u01      | like     | 9            | 上午(8-11)     |
| u03      | browse   | 12           | 下午(12-17)    |
| u01      | comment  | 12           | 下午(12-17)    |
| u02      | post     | 14           | 下午(12-17)    |
| u03      | like     | 18           | 晚间(18-21)    |
| u04      | browse   | 19           | 晚间(18-21)    |
| u04      | save     | 20           | 晚间(18-21)    |
| u01      | browse   | 22           | 深夜(22-23)    |
| u02      | browse   | 23           | 深夜(22-23)    |
| u03      | like     | 23           | 深夜(22-23)    |
+----------+----------+--------------+--------------+
12 rows selected (0.205 seconds)(https://www.dwsql.com)

2.按时段聚合统计

执行SQL

select time_period,
       count(distinct user_id) as active_uv,
       count(1) as action_cnt
from (
    select user_id,
           case when cast(substr(action_time, 12, 2) as int) between 0 and 3 then '凌晨(0-3)'
                when cast(substr(action_time, 12, 2) as int) between 4 and 7 then '清晨(4-7)'
                when cast(substr(action_time, 12, 2) as int) between 8 and 11 then '上午(8-11)'
                when cast(substr(action_time, 12, 2) as int) between 12 and 17 then '下午(12-17)'
                when cast(substr(action_time, 12, 2) as int) between 18 and 21 then '晚间(18-21)'
                when cast(substr(action_time, 12, 2) as int) between 22 and 23 then '深夜(22-23)'
                else '未知' end as time_period
    from t8_user_active_log
) t
group by time_period
order by active_uv desc

查询结果

+--------------+------------+-------------+
| time_period  | active_uv  | action_cnt  |
+--------------+------------+-------------+
| 深夜(22-23)    | 3          | 3           |
| 下午(12-17)    | 3          | 3           |
| 上午(8-11)     | 2          | 3           |
| 晚间(18-21)    | 2          | 3           |
+--------------+------------+-------------+
4 rows selected (0.729 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：substr 提取的是字符串，需 cast 为 int — substr(action_time, 12, 2) 返回字符串类型，不能直接用 between 做数值范围比较。需用 cast(substr(action_time, 12, 2) as int) 转为整数后再比较，否则可能因字符串排序规则导致判断错误。

坑2：between 边界需注意覆盖完整 — between 0 and 3 包含边界值0和3，between 4 and 7 包含4和7，以此类推。设计时段划分时需确保各区间边界不重叠、无遗漏，24小时完整覆盖。

坑3：case when 从上到下匹配，时段定义不重叠不遗漏 — case when 按书写顺序依次匹配，一旦命中即跳出。需确保时段条件互斥且覆盖所有可能值，末尾加 else '未知' 兜底异常数据（如NULL或超出0-23范围的小时值）。

五、知识点总结

考点	说明
substr + cast 类型转换	从时间字符串中提取小时并转为整数，是时间维度分析的基础操作
case when 分段映射	将连续数值（0-23小时）映射为离散的时段标签，实现业务维度的分组统计
count(distinct) 去重统计	统计独立用户数（UV），与 count(1) 统计总行为次数配合，多维度衡量活跃度
group by + order by	按时段分组聚合后，按活跃用户数降序排列，快速定位用户最活跃的时段

六、建表语句

点击展开 DDL & DML

-- 建表语句
create table t8_user_active_log (
    user_id     string comment '用户id',
    action      string comment '行为类型：browse,like,comment,post,save',
    action_time string comment '行为发生时间'
) comment '用户活跃日志表';

-- 插入数据
insert into t8_user_active_log(user_id, action, action_time) values
('u01','browse','2025-06-01 08:30'),
('u02','browse','2025-06-01 09:00'),
('u01','like','2025-06-01 09:15'),
('u03','browse','2025-06-01 12:00'),
('u01','comment','2025-06-01 12:30'),
('u02','post','2025-06-01 14:00'),
('u03','like','2025-06-01 18:00'),
('u04','browse','2025-06-01 19:30'),
('u04','save','2025-06-01 20:00'),
('u01','browse','2025-06-01 22:30'),
('u02','browse','2025-06-01 23:00'),
('u03','like','2025-06-01 23:15');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题