如何在whatsapp_messages表中识别唯一对话（双向视为同一对话）

要识别唯一对话，核心是把双向的对话（比如1→2和2→1）标准化为同一个标识。具体可以通过将每个对话的两个用户ID按大小排序，生成统一的对话键，之后去重即可。

步骤说明 #

• 先过滤掉自己给自己发消息的记录（如果不需要这类对话可省略）
• 对每条记录的sender_id和receiver_id，生成标准化字段：user_a取较小的ID，user_b取较大的ID
• 对标准化后的字段去重，得到所有唯一对话

修正后的代码 #

WITH standardized_convos AS (
    SELECT
        -- 生成标准化的对话双方：小ID在前，大ID在后
        LEAST(sender_id, receiver_id) AS user_a,
        GREATEST(sender_id, receiver_id) AS user_b
    FROM whatsapp_messages
    -- 排除自己给自己发消息的情况（不需要可删除此行）
    WHERE sender_id != receiver_id
)
-- 去重得到唯一对话
SELECT DISTINCT user_a, user_b
FROM standardized_convos;

拓展：统计对话消息数量 #

如果需要统计每个对话的消息总数，可以在CTE中加入计数逻辑后分组：

WITH standardized_convos AS (
    SELECT
        LEAST(sender_id, receiver_id) AS user_a,
        GREATEST(sender_id, receiver_id) AS user_b,
        COUNT(*) AS message_count
    FROM whatsapp_messages
    WHERE sender_id != receiver_id
    GROUP BY user_a, user_b
)
SELECT user_a, user_b, message_count
FROM standardized_convos;

内容的提问来源于stack exchange，提问作者vinsy2774