Postgres SQL组不跳转行？

Question

假设我在表中有这些数据：

 id | thing | operation | timestamp
----+-------+-----------+-----------
  0 | foo   |       add |         0
  0 | bar   |       add |         1
  1 | baz   |    remove |         2
  1 | dim   |       add |         3
  0 | foo   |    remove |         4
  0 | dim   |       add |         5

是否有任何方法来构造Postgres SQL查询，该查询将按id和操作进行分组，但不对具有较低时间戳值的行进行分组？我想把它从查询中拿出来：

 id |  things  | operation
----+----------+-----------
  0 | foo, bar |       add
  1 |      baz |    remove
  1 |      dim |       add
  0 |      foo |    remove
  0 |      dim |       add

基本上按组，但只在相邻的行上按时间戳排序。

Answer 1

这是一个空隙和岛屿问题(尽管本文针对的是Server，它很好地描述了这个问题，因此仍然适用于Postgresql)，可以使用排序函数来解决：

SELECT  id,
        thing,
        operation,
        timestamp,
        ROW_NUMBER() OVER(ORDER BY timestamp) - 
                ROW_NUMBER() OVER(PARTITION BY id, operation ORDER BY Timestamp) AS groupingSet,
        ROW_NUMBER() OVER(ORDER BY timestamp) AS PositionInSet,
        ROW_NUMBER() OVER(PARTITION BY id, operation ORDER BY Timestamp) AS PositionInGroup
FROM    T
ORDER BY timestamp;

如您所见，通过获取集合中的整体位置，并扣除组中的位置，您可以识别岛屿，其中(id, operation, groupingset)的每个唯一组合代表一个岛：

id  thing   operation   timestamp   groupingSet PositionInSet   PositionInGroup
0   foo     add         0           0           1               1
0   bar     add         1           0           2               2           
1   baz     remove      2           2           3               1
1   dim     add         3           3           4               1
0   foo     remove      4           4           5               1
0   dim     add         5           3           6               3

然后，只需将其放入子查询中，并按相关字段进行分组，并使用string_agg连接您的内容：

SELECT  id, STRING_AGG(thing) AS things, operation
FROM    (   SELECT  id,
                    thing,
                    operation,
                    timestamp,
                    ROW_NUMBER() OVER(ORDER BY timestamp) - 
                            ROW_NUMBER() OVER(PARTITION BY id, operation ORDER BY Timestamp) AS groupingSet
            FROM    T
        ) AS t
GROUP BY id, operation, groupingset;

Answer 2

如果你的样本数据足够好的话，这也许是可行的：

select id, string_agg(thing,',') as things, operation
from tablename
group by id, operation

即使用id和运算来查找要连接的内容。

编辑，现在使用string_agg而不是group_concat。

Answer 3

您可以根据id结果对组中的不同操作进行计数，并将此计数器用于union 2选择表：

WITH cnt AS (
  SELECT id, operations_cnt FROM (
    SELECT id, array_length(array_agg(DISTINCT operation),1) AS operations_cnt
    FROM test GROUP BY id
  ) AS t
  WHERE operations_cnt=1
)
SELECT id, string_agg(things, ','), operation, MAX(timestamp) AS timestamp
FROM test
WHERE id IN (SELECT id FROM cnt) GROUP BY id, operation
UNION ALL
SELECT id, things, operation, timestamp
FROM test
WHERE id NOT IN (SELECT id FROM cnt)
ORDER BY timestamp;

结果：

 id | string_agg | operation | timestamp 
----+------------+-----------+-----------
  0 | foo,bar    | add       |         1
  1 | baz        | remove    |         2
  1 | dim        | add       |         3
  2 | foo        | remove    |         4
  2 | dim        | add       |         5
(5 rows)