sql - 按重复属性分组

Question

基本上我有一个表 messages，其中包含 user_id 字段，用于标识创建消息的用户。

当我显示两个用户之间的对话(消息集)时，我希望能够按 user_id 对消息进行分组，但采用一种棘手的方式:

假设有一些消息(按 created_at desc 排序):

  id: 1, user_id: 1
  id: 2, user_id: 1
  id: 3, user_id: 2
  id: 4, user_id: 2
  id: 5, user_id: 1

我想按以下顺序获取 3 个消息组:[1,2], [3,4], [5]

它应该按 *user_id* 分组，直到它看到一个不同的，然后按那个分组。

我正在使用 PostgreSQL，并且很乐意使用特定于它的东西，无论什么都能提供最佳性能。

Answer 1

尝试这样的事情:

SELECT user_id, array_agg(id)
FROM (
SELECT id, 
       user_id, 
       row_number() OVER (ORDER BY created_at)-
       row_number() OVER (PARTITION BY user_id ORDER BY created_at) conv_id
FROM table1 ) t
GROUP BY user_id, conv_id;

表达式:

row_number() OVER (ORDER BY created_at)-
row_number() OVER (PARTITION BY user_id ORDER BY created_at) conv_id

会给每个消息组一个特殊的id你们所有不同的消息组)

我的 SQLFiddle举例说明。

详情:row_number() , OVER (PARTITION BY ... ORDER BY ...)