组通过重复属性(Group by repeating attribute)

基本上我有一个表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 ANALYZE与60K行（执行几次，挑最快的结果排除兑现效应）类似现实生活中的表：

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

还要考虑这些密切相关的问题：

GROUP BY和聚合连续的数字值
GROUP BY由间隙分隔连续日期
连续重复/重复项的有序计数

Answer 2:

尝试是这样的：

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（这conv_id可以重复其他user_id ，但user_id, conv_id会给你所有不同的消息组）

我SQLFiddle与实例。

详细说明： row_number() OVER (PARTITION BY ... ORDER BY ...)

Answer 3:

在GROUP BY子句将崩溃在2个记录的响应-一个与user_id 1和一个与user_id 2没有的事ORDER BY子句，所以我建议你只发送了ORDER BY created_at

prev_id = -1
messages.each do |m|
 if ! m.user_id == prev_id do 
    prev_id = m.user_id
    #do whatever you want with a new message group
 end
end

Answer 4:

您可以使用块：

Message = Struct.new :id, :user_id

messages = []
messages << Message.new(1, 1)
messages << Message.new(2, 1)
messages << Message.new(3, 2)
messages << Message.new(4, 2)
messages << Message.new(5, 1)

messages.chunk(&:user_id).each do |user_id, records| 
  p "#{user_id} - #{records.inspect}" 
end

输出：

"1 - [#<struct Message id=1, user_id=1>, #<struct Message id=2, user_id=1>]"
"2 - [#<struct Message id=3, user_id=2>, #<struct Message id=4, user_id=2>]"
"1 - [#<struct Message id=5, user_id=1>]"

文章来源: Group by repeating attribute