SQL 消息会话统计：发送+接收合并统计（腾讯面试题）

数据仓库晨曦

发布于 2026-07-21 09:33:53

210

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

这道题来自腾讯微信/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  |
+---------+---------+------------+----------------------+
10 rows selected (0.281 seconds)(https://www.dwsql.com)

2. 按会话分组统计消息总数和最新消息时间

执行SQL

select user_a,
       user_b,
       count(1)         as msg_cnt,        -- 消息总数
       max(send_time)   as last_msg_time   -- 最新消息时间
from (
    selectleast(send_id, recv_id)   as user_a,
           greatest(send_id, recv_id) as user_b,
           content,
           send_time
    from t12_message
) t
groupby user_a, user_b
orderby 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  |
+---------+---------+----------+----------------------+
4 rows selected (1.441 seconds)(https://www.dwsql.com)

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 (
        selectleast(send_id, recv_id)   as user_a,
               greatest(send_id, recv_id) as user_b,
               content,
               send_time
        from t12_message
    ) t
    groupby user_a, user_b

    unionall

    -- 用户作为 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 (
        selectleast(send_id, recv_id)   as user_a,
               greatest(send_id, recv_id) as user_b,
               content,
               send_time
        from t12_message
    ) t
    groupby user_a, user_b
) tt
orderby 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  |
+----------+----------------+----------+----------------------+
8 rows selected (0.784 seconds)(https://www.dwsql.com)

五、常见坑点

坑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在前，大的在后

八、建表语句和数据插入

CREATE TABLE t12_message (
    send_id   stringCOMMENT'发送用户ID',
    recv_id   stringCOMMENT'接收用户ID',
    content   stringCOMMENT'消息内容',
    send_time stringCOMMENT'发送时间'
) COMMENT'消息表';

-- 数据插入
INSERTINTO 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');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-20，如有侵权请联系 cloudcommunity@tencent.com 删除

统计