sql - Postgresql收件箱查询

Question

我有一个如下所示的消息表:

                    Messages
+-----+------------+-------------+--------------+
|  id |  sender_id | receiver_id |  created_at  |
+-----------------------------------------------+
|  1  |      1     |      2      |   1/1/2013   |
|  2  |      1     |      2      |   1/1/2013   |
|  3  |      2     |      1      |   1/2/2013   |
|  4  |      3     |      2      |   1/2/2013   |
|  5  |      3     |      2      |   1/3/2013   |
|  6  |      5     |      4      |   1/4/2013   |
+-----------------------------------------------+

“线程”是给定的 sender_id 和 receiver_id 之间的一组消息 我希望查询返回 最近 10 个线程的最近 10 条消息 其中 sender_id 或 receiver_id 是给定的 ID。

给定 user_id 为 5 时的预期输出:

+-----+------------+-------------+--------------+
|  id |  sender_id | receiver_id |  created_at  |
+-----------------------------------------------+
|  1  |      5     |      2      |   1/4/2013   |
|  2  |      5     |      2      |   1/4/2013   |
|  3  |      2     |      5      |   1/4/2013   |
|  4  |      3     |      5      |   1/4/2013   |
|  5  |      5     |      2      |   1/3/2013   |
|  6  |      5     |      4      |   1/3/2013   |
+-----------------------------------------------+

例如，用户 5 和 2 之间最多 10 条消息(上面有 4 个)和最多 10 个线程(上面有 3 个)。

我一直在尝试使用子查询进行此类查询，但未能获得不同线程数量的第二个限制。

SELECT * FROM (SELECT DISTINCT ON (sender_id, receiver_id) messages.* 
FROM messages 
WHERE (receiver_id = 5 OR sender_id = 5) ORDER BY sender_id, receiver_id, 
created_at DESC)   
q ORDER BY created_at DESC 
LIMIT 10 OFFSET 0;

我正在考虑创建一个新的 Thread 表，其中包含一个 thread_id 字段，该字段是 sender_id + receiver_id 的串联，然后只是加入 Messages，但我偷偷怀疑它应该只用一个表就可以实现。

Answer 1

我能想到在一个查询中解决您的问题的最简洁的查询是以下查询:

select * from (
  select row_number() 
    over (partition by sender_id, receiver_id order by created_at desc) as rn, m.*
  from Messages m
  where (m.sender_id, m.receiver_id) in (
    select sender_id, receiver_id
    from Messages
    where sender_id = <id> or receiver_id = <id>
    group by sender_id, receiver_id
    order by max(created_at) desc
    limit 10 offset 0
  )
) res where res.rn <= 10

row_number() over (partition by sender_id, receiver_id order by created_at desc) 列将包含每个线程中每条消息的行号(如果您运行单独查询以仅查询一个线程)。除了这个行号之外，如果消息包含在最顶层的 10 个线程中(由 ...query... 中的 (m.sender_id, m.receiver_id) 创建)，您还可以查询消息本身。最后，由于您只需要 10 条最上面的消息，因此您将行号限制为小于或等于 10。