PostgreSQL:如何按 'nearby' 时间戳对行进行分组

Question

考虑以下简化情况:

create table trans
(
  id integer not null
, tm timestamp without time zone not null
, val integer not null
, cus_id integer not null
);

insert into trans 
  (id, tm, val, cus_id)
values
  (1, '2017-12-12 16:42:00', 2, 500) --
 ,(2, '2017-12-12 16:42:02', 4, 501) -- <--+---------+
 ,(3, '2017-12-12 16:42:05', 7, 502) --    |dt=54s   |
 ,(4, '2017-12-12 16:42:56', 3, 501) -- <--+         |dt=59s
 ,(5, '2017-12-12 16:43:00', 2, 503) --              |
 ,(6, '2017-12-12 16:43:01', 5, 501) -- <------------+
 ,(7, '2017-12-12 16:43:15', 6, 502) --
 ,(8, '2017-12-12 16:44:50', 4, 501) --
;

我想按 cus_id 对行进行分组，而且同一 cus_id 的连续行的时间戳之间的间隔小于 1 分钟。

在上面的示例中，这适用于 ID 为 2、4 和 6 的行。这些行具有相同的 cus_id (501) 并且间隔小于 1 分钟。间隔 id{2,4} 是 54s，id{2,6} 是 59s。间隔 ID {4,6} 也低于 1 分钟，但它被更大的间隔 ID {2,6} 覆盖。

我需要一个能给我输出的查询:

 cus_id |         tm          | val 
--------+---------------------+-----
    501 | 2017-12-12 16:42:02 |  12
(1 row)

tm 值将是第一行的 tm，即具有最低的 tm。 val 将是分组行的总和 (val)。

在示例中，3 行被分组，但也可以是 2、4、5，...为简单起见，我只让 cus_id 501 的行具有附近的时间戳，但在我的真实表中，它们会更多。它包含超过 2000 万行。

这可能吗？

Answer 1

使用 CTE 的原始(次优)解决方案(更快的方法会避免 CTE，用连接的子查询代替它，甚至可能使用窗口函数):

---

        -- Step one: find the start of a cluster
        -- (the start is everything after a 60 second silence)
WITH starters AS (
        SELECT * FROM trans tr
        WHERE NOT EXISTS (
                SELECT * FROM trans nx
                WHERE nx.cus_id = tr.cus_id
                AND nx.tm < tr.tm
                AND nx.tm >= tr.tm -'60sec'::interval
                )
        )
                -- SELECT * FROM starters ; \q
        -- Step two: join everything within 60sec to the starter
        -- and aggregate the clusters
SELECT st.cus_id
        , st.id AS id
        , MAX(tr.id) AS max_id
        , MIN(tr.tm) AS first_tm
        , MAX(tr.tm) AS last_tm
        , SUM(tr.val) AS val
FROM trans tr
JOIN starters st ON st.cus_id = tr.cus_id
        AND st.tm <= tr.tm AND st.tm > tr.tm -'60sec'::interval
GROUP BY 1,2
ORDER BY 1,2
    ;