sql - 按多列分组并限制每组 - Postgres

Question

我正在创建一个消息传递应用程序作为一个副项目，我正在尝试有效地查询用户的对话。

messages 表结构现在是基本的，带有一些虚拟数据:

| id   | sender_id | receiver_id | message | created_at |
|------|-----------|-------------|---------|------------|
| 1    | 1         | 2           | text    | time       |
| 2    | 2         | 1           | text    | time       |
| 3    | 1         | 2           | text    | time       |
| 4    | 1         | 3           | text    | time       |
| 5    | 3         | 2           | text    | time       |
| 6    | 3         | 1           | text    | time       |
| 7    | 2         | 1           | text    | time       |

我希望能够查询数据库并按“对话”进行分组 - A.K.A 在任一列中具有相同 sender_id 或 receiver_id 的任何行 - 行(1, 2, 3, 7), (4, 6), (5)。我希望能够将每个“组”限制为 n 行，并按 created_at 列对它们进行排序。理想情况下它看起来像(created_at 值是显示降序值的任意数字):

| id   | sender_id | receiver_id | message | created_at |
|------|-----------|-------------|---------|------------|
| 1    | 1         | 2           | text    | 400        |
| 2    | 2         | 1           | text    | 300        |
| 3    | 1         | 2           | text    | 200        |
| 7    | 2         | 1           | text    | 100        |
| 4    | 1         | 3           | text    | 700        |
| 6    | 3         | 1           | text    | 500        |
| 5    | 3         | 2           | text    | 300        |

理想情况下，会添加一个额外的列来为每个组编号(易于创建多维数组。

到目前为止，我已经能够按发送者/接收者 ID 进行“分组”，按 created_at 进行排序，并限制每组的数量。然而，这并不完全正确。这是查询:

SELECT
    filter.id, filter.sender_id, filter.receiver_id, filter.message, filter.created_at
FROM (
    SELECT messages.*,
        rank() OVER (
            PARTITION BY sender_id
            ORDER BY created_at DESC
        )
    FROM messages
    WHERE messages.sender_id = 1 or messages.receiver_id = 1
) filter WHERE rank <= 50;

我的结果集是这样的:

| id   | sender_id | receiver_id | message | created_at |
|------|-----------|-------------|---------|------------|
| 1    | 1         | 2           | text    | 400        |
| 3    | 1         | 2           | text    | 300        |
| 4    | 1         | 3           | text    | 700        |
| 2    | 2         | 1           | text    | 300        |
| 7    | 2         | 1           | text    | 100        |
| 6    | 3         | 1           | text    | 500        |
| 5    | 3         | 2           | text    | 300        |

您可以看到第 3 行和第 6 行应该分组但没有分组。

Answer 1

您可以使用 rank()。要限制每个 session 的记录数(即发送者/接收者 或 接收者/发送者元组)，您可以使用像 least(sender_id, receiver_id), greatest(sender_id, receiver_id):

select filter.id, filter.sender_id, filter.receiver_id, filter.message, filter.created_at
from (
    select
        t.*,
        rank() over(
            partition by least(sender_id, receiver_id), greatest(sender_id, receiver_id)
            order by created_at desc
        ) rn
    from mytable t
) t
where rn <= 50
order by least(sender_id, receiver_id), greatest(sender_id, receiver_id), rn