sql - Postgres 限制来自另一个表的 WHERE IN id 中每个行的行数

Question

我有一个消息传递应用程序，我需要在其中返回用户参与的所有对话以及与每个对话相关的消息。我想限制每次对话的消息数量。

表结构如下:

用户

| id   | name | email    | created_at |
|------|------|----------|------------|
| 1    | Bob  | a@b.com  | timestamp  |
| 2    | Tom  | b@b.com  | timestamp  |
| 3    | Mary | c@b.com  | timestamp  |

消息

| id   | sender_id | conversation_id  | message | created_at |
|------|-----------|------------------|---------|------------|
| 1    | 1         | 1                | text    | timestamp  |
| 2    | 2         | 2                | text    | timestamp  |
| 3    | 2         | 1                | text    | timestamp  |
| 4    | 3         | 3                | text    | timestamp  |

对话

| id | created_at |
|----|------------|
| 1  | timestamp  |
| 2  | timestamp  |
| 3  | timestamp  |

Conversations_Users

| id | user_id | conversation_id |
|----|---------|-----------------|
| 1  | 1       | 1               |
| 2  | 2       | 1               |
| 3  | 2       | 2               |
| 3  | 3       | 2               |
| 4  | 3       | 3               |
| 5  | 1       | 3               |

我想加载用户 (id 1) 所在的所有对话(在示例中 - 对话 1 和 3)。对于每个对话，我需要与其关联的消息，按 conversation_id 分组，按 created_at ASC 排序。我当前的查询处理这个:

SELECT
    *
FROM
    messages
WHERE
    conversation_id IN (
        SELECT
            conversation_id
        FROM
            conversations_users
        WHERE
            user_id = 1
    )
ORDER BY
    conversation_id, created_at ASC;

但是，这会将大量数据存入内存。因此，我想限制每次对话的消息数量。

我查看了 rank() 和 ROW_NUMBER() 但不确定如何实现它们/它们是否是需要的。

Answer 1

您确实可以使用 row_number()。以下查询将为您提供给定用户每次对话的最后 10 条消息:

select *
from (
    select 
        m.*, 
        row_number() over(
            partition by cu.user_id, m.conversation_id 
            order by m.created_at desc
        ) rn
    from messages m
    inner join conversations_users cu 
        on  cu.conversation_id  = m.conversation_id 
        and cu.user_id = 1
) t
where rn <= 10
order by conversation_id, created_at desc

注意事项:

• 我将带有 in 的子查询转换为常规 join，因为我相信这是表达您的需求的更简洁的方式

  <

• 我在分区子句中添加了用户 ID；因此，如果您删除过滤用户的 where 子句，您将获得每个用户对话的最后 10 条消息