腾讯大数据面试SQL-消息发送和接收的会话统计
⚠️ 待修正
一、题目背景
这道题来自腾讯微信/QQ的社交数据数据分析岗面试。微信和QQ每天产生数千亿条消息，统计用户之间的会话活跃度是社交分析的基础——谁跟谁聊天最频繁？哪些关系最近在降温？会话统计是所有社交关系链分析的第一步。
业务场景：微信的聊天列表排序算法会参考"最近聊天频次"和"最后消息时间"，高频且最近有消息的会话排在前面。这道题的SQL就是会话列表排序所需的基础数据查询。
二、题目
现有一张消息表 t12_message，记录了用户之间发送的消息。请统计任意两个用户之间的会话消息总数和最新消息发送时间（无论谁发谁收，都算同一个会话）。
消息表 t12_message：
+----------+----------+------------------------+---------------------+
| send_id | recv_id | content | send_time |
+----------+----------+------------------------+---------------------+
| u01 | u02 | 你好！ | 2023-03-01 10:00:00 |
| u02 | u01 | 你好，有什么事吗？ | 2023-03-01 10:05:00 |
| u01 | u02 | 周末一起吃饭吧 | 2023-03-01 10:10:00 |
| u02 | u01 | 好的呀，去哪里吃？ | 2023-03-01 10:15:00 |
| u01 | u03 | 在吗？ | 2023-03-01 11:00:00 |
| u03 | u01 | 在的，怎么了？ | 2023-03-01 11:05:00 |
| u02 | u03 | 推荐一个电影给你 | 2023-03-02 09:00:00 |
| u03 | u02 | 什么电影呀？ | 2023-03-02 09:30:00 |
| u01 | u02 | 我订好位置了 | 2023-03-02 12:00:00 |
| u04 | u01 | 大佬请教个问题 | 2023-03-02 14:00:00 |
+----------+----------+------------------------+---------------------+
三、思路分析
本题的关键在于将"谁发给谁"归一化为"两个用户之间的会话"。例如 u01发给u02 和 u02发给u01 应该属于同一个会话。
核心思路：
- 使用
LEAST(send_id, recv_id)和GREATEST(send_id, recv_id)将消息方向归一化——较小的user_id在前，较大的在后，形成唯一的会话标识； - 按归一化后的会话标识分组统计消息数和最新消息时间；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 将消息方向归一化，构建会话ID
执行SQL
select least(send_id, recv_id) as user_a,
greatest(send_id, recv_id) as user_b,
content,
send_time
from t12_message
执行结果
+--------+--------+------------------------+---------------------+
| user_a | user_b | content | send_time |
+--------+--------+------------------------+---------------------+
| u01 | u02 | 你好！ | 2023-03-01 10:00:00 |
| u01 | u02 | 你好，有什么事吗？ | 2023-03-01 10:05:00 |
| u01 | u02 | 周末一起吃饭吧 | 2023-03-01 10:10:00 |
| u01 | u02 | 好的呀，去哪里吃？ | 2023-03-01 10:15:00 |
| u01 | u03 | 在吗？ | 2023-03-01 11:00:00 |
| u01 | u03 | 在的，怎么了？ | 2023-03-01 11:05:00 |
| u02 | u03 | 推荐一个电影给你 | 2023-03-02 09:00:00 |
| u02 | u03 | 什么电影呀？ | 2023-03-02 09:30:00 |
| u01 | u02 | 我订好位置了 | 2023-03-02 12:00:00 |
| u01 | u04 | 大佬请教个问题 | 2023-03-02 14:00:00 |
+--------+--------+------------------------+---------------------+
2. 按会话分组统计消息总数和最新消息时间
执行SQL
select user_a,
user_b,
count(1) as msg_cnt, -- 消息总数
max(send_time) as last_msg_time -- 最新消息时间
from (
select least(send_id, recv_id) as user_a,
greatest(send_id, recv_id) as user_b,
content,
send_time
from t12_message
) t
group by user_a, user_b
order by msg_cnt desc
执行结果
+--------+--------+---------+---------------------+
| user_a | user_b | msg_cnt | last_msg_time |
+--------+--------+---------+---------------------+
| u01 | u02 | 5 | 2023-03-02 12:00:00 |
| u01 | u03 | 2 | 2023-03-01 11:05:00 |
| u02 | u03 | 2 | 2023-03-02 09:30:00 |
| u01 | u04 | 1 | 2023-03-02 14:00:00 |
+--------+--------+---------+---------------------+
3. 补充：查询每个用户参与的所有会话
执行SQL
select user_id,
other_user_id,
msg_cnt,
last_msg_time
from (
-- 用户作为 user_a 的会话
select user_a as user_id,
user_b as other_user_id,
count(1) as msg_cnt,
max(send_time) as last_msg_time
from (
select least(send_id, recv_id) as user_a,
greatest(send_id, recv_id) as user_b,
content,
send_time
from t12_message
) t
group by user_a, user_b
union all
-- 用户作为 user_b 的会话
select user_b as user_id,
user_a as other_user_id,
count(1) as msg_cnt,
max(send_time) as last_msg_time
from (
select least(send_id, recv_id) as user_a,
greatest(send_id, recv_id) as user_b,
content,
send_time
from t12_message
) t
group by user_a, user_b
) tt
order by user_id, msg_cnt desc
执行结果
+----------+----------------+---------+---------------------+
| user_id | other_user_id | msg_cnt | last_msg_time |
+----------+----------------+---------+---------------------+
| u01 | u02 | 5 | 2023-03-02 12:00:00 |
| u01 | u03 | 2 | 2023-03-01 11:05:00 |
| u01 | u04 | 1 | 2023-03-02 14:00:00 |
| u02 | u01 | 5 | 2023-03-02 12:00:00 |
| u02 | u03 | 2 | 2023-03-02 09:30:00 |
| u03 | u01 | 2 | 2023-03-01 11:05:00 |
| u03 | u02 | 2 | 2023-03-02 09:30:00 |
| u04 | u01 | 1 | 2023-03-02 14:00:00 |
+----------+----------------+---------+---------------------+
五、常见坑点
坑1：LEAST/GREATEST 处理 NULL 的行为
如果 send_id 或 recv_id 有 NULL，LEAST(NULL, 'u01') 返回 NULL。需要在 WHERE 中提前过滤 NULL 或用 COALESCE 处理。
坑2：UNION ALL 方案的双倍计算
如果同时对 user_a 和 user_b 做 UNION ALL，每条消息会被统计两次（分别在双方的视角下），msg_cnt 会翻倍。用作"每个用户参与的所有会话"时这没问题，但用作"会话级别统计"时不能 UNION ALL。
坑3：消息方向归一化后丢失"谁先发的"信息
LEAST/GREATEST 让 u01→u02 和 u02→u01 合并为一组，但也丢失了"谁是发起方"的信息。如果需要保留，可以加一列 CASE WHEN send_id < recv_id THEN '正向' ELSE '反向' END。
六、举一反三
- 每日会话数趋势：GROUP BY 加上日期维度，看每天有多少活跃会话
- 单向沟通检测：如果所有消息都是 A→B 而没有 B→A，可能A在发广告/骚扰
- 会话时长统计：第一条和最后一条消息的时间差 = 会话持续时长
- 亲密度评分：加权公式 = 消息数 × 0.5 + 最近7天消息数 × 0.3 + 会话持续天数 × 0.2
七、知识点总结
| 考点 | 说明 |
|---|---|
| LEAST / GREATEST | 将双向发送归一化为无向会话ID |
| GROUP BY + COUNT / MAX | 统计会话消息数和最新消息时间 |
| UNION ALL 展开方向 | 还原为每个用户的视角，展示其所有会话 |
| 会话归一化 | 经典社交SQL技巧：小的ID在前，大的在后 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t12_message (
send_id string COMMENT '发送用户ID',
recv_id string COMMENT '接收用户ID',
content string COMMENT '消息内容',
send_time string COMMENT '发送时间'
) COMMENT '消息表';
-- 数据插入
INSERT INTO t12_message VALUES
('u01', 'u02', '你好！', '2023-03-01 10:00:00'),
('u02', 'u01', '你好，有什么事吗？', '2023-03-01 10:05:00'),
('u01', 'u02', '周末一起吃饭吧', '2023-03-01 10:10:00'),
('u02', 'u01', '好的呀，去哪里吃？', '2023-03-01 10:15:00'),
('u01', 'u03', '在吗？', '2023-03-01 11:00:00'),
('u03', 'u01', '在的，怎么了？', '2023-03-01 11:05:00'),
('u02', 'u03', '推荐一个电影给你', '2023-03-02 09:00:00'),
('u03', 'u02', '什么电影呀？', '2023-03-02 09:30:00'),
('u01', 'u02', '我订好位置了', '2023-03-02 12:00:00'),
('u04', 'u01', '大佬请教个问题', '2023-03-02 14:00:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
