按重复属性分组

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

正确的SQL

@Igor提供了一种很好的带有窗口函数的纯SQL技术。
然而：

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

要获取请求的订单，请添加ORDER BY min(id)：

SELECT array_agg(id) AS ids
FROM (
   SELECT id
         ,user_id
         ,row_number() OVER (ORDER BY id) -
          row_number() OVER (PARTITION BY user_id ORDER BY id) AS grp
   FROM   messages
   ORDER  BY id) t   -- for ordered arrays in result
GROUP  BY grp, user_id
ORDER  BY min(id);

SQL提琴。

增加几乎不能保证另一个答案。更重要的问题是：

使用PL / pgSQL更快

我使用的是PostgreSQL，并且很乐意使用特定于它的东西，无论哪种都能提供 最佳性能 。

纯SQL都很好，而且很闪亮，但是过程 服务器端函数 对于此任务来说 要 快得多。尽管从程序上处理行 通常较慢 ，但 plpgsql
赢得了这场竞争的 青睐 ，因为它可以通过 单个 表扫描和 单个 ORDER BY操作来完成：

CREATE OR REPLACE FUNCTION f_msg_groups()
  RETURNS TABLE (ids int[]) AS
$func$
DECLARE
   _id    int;
   _uid   int;
   _id0   int;                         -- id of last row
   _uid0  int;                         -- user_id of last row
BEGIN
   FOR _id, _uid IN
       SELECT id, user_id FROM messages ORDER BY id
   LOOP
       IF _uid <> _uid0 THEN
          RETURN QUERY VALUES (ids);   -- output row (never happens after 1 row)
          ids := ARRAY[_id];           -- start new array
       ELSE
          ids := ids || _id;           -- add to array
       END IF;

       _id0  := _id;
       _uid0 := _uid;                  -- remember last row
   END LOOP;

   RETURN QUERY VALUES (ids);          -- output last iteration
END
$func$ LANGUAGE plpgsql;

称呼：

SELECT * FROM f_msg_groups();

基准和链接

我在具有EXPLAIN ANALYZE6万行的类似现实生活中的表上进行了快速测试（执行几次，选择最快的结果以排除兑现影响）：

SQL：
总运行时间：1009.549毫秒
Pl / pgSQL：
总运行时间： 336.971毫秒

还请考虑以下密切相关的问题：

• GROUP BY和汇总顺序数值
• GROUP BY连续日期，以空格分隔
• 连续重复/重复的有序计数