PostgreSQL按对话对去重：取日期最新的单行数据

我有一张包含senderId、receiverId、date字段的表，原始数据如下：

当前使用的查询语句：

SELECT DISTINCT ON ("senderId", "receiverId") "sender"."id" AS "senderId", "receiver"."id" AS "receiverId", cm."createdAt" AS "createdAt" 
FROM "chat_message" "cm" 
LEFT JOIN "user" "sender" ON "sender"."id"="cm"."senderId"  
LEFT JOIN "user" "receiver" ON "receiver"."id"="cm"."receiverId" 
WHERE "senderId" = 2 OR "receiverId" = 2 
ORDER BY "senderId", "receiverId" ASC, cm."createdAt" DESC

执行后得到的结果：

但期望仅保留对话对中date最大的那一行，结果如下：

即需要将(1,2)和(2,1)视为同一个对话对，仅保留该对话中最新的一条消息。

原查询的DISTINCT ON ("senderId", "receiverId")会把(1,2)和(2,1)当成两个独立分组，因此返回两条结果。要实现需求，需将对话双方的ID统一为固定顺序作为分组依据，再取每组最新记录。

修改后的查询语句：

SELECT "sender"."id" AS "senderId", "receiver"."id" AS "receiverId", cm."createdAt" AS "createdAt"
FROM (
    SELECT 
        cm.*,
        -- 统一对话组的ID顺序，确保(1,2)和(2,1)归为同一组
        LEAST(cm."senderId", cm."receiverId") AS chat_partner_min,
        GREATEST(cm."senderId", cm."receiverId") AS chat_partner_max,
        -- 给每个对话组的消息按时间降序排名
        ROW_NUMBER() OVER (
            PARTITION BY LEAST(cm."senderId", cm."receiverId"), GREATEST(cm."senderId", cm."receiverId")
            ORDER BY cm."createdAt" DESC
        ) AS rn
    FROM "chat_message" cm
    WHERE cm."senderId" = 2 OR cm."receiverId" = 2
) cm
LEFT JOIN "user" "sender" ON "sender"."id" = cm."senderId"
LEFT JOIN "user" "receiver" ON "receiver"."id" = cm."receiverId"
WHERE rn = 1;

关键说明 #

1. 子查询中用LEAST和GREATEST函数，将对话双方ID转换为固定顺序的分组键，消除发送方和接收方顺序对分组的影响；
2. ROW_NUMBER()窗口函数给每个对话组内的消息按时间倒序排名，最新消息的排名为1；
3. 最后筛选排名为1的记录，即可得到目标对话组的最新消息。

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