Postgres按连续事件分组，当有超过10分钟的间隔时打破

Question

假设我有一个表，其中包含：

id | type | timemstamp
1  | 'dog' | '2019-01-01T12:00:00Z'
2  | 'cat' | '2019-01-01T12:01:00Z'
3  | 'dog' | '2019-01-01T12:02:00Z'
4  | 'dog' | '2019-01-01T12:03:00Z'
5  | 'cat' | '2019-01-01T12:03:00Z'
6  | 'dog' | '2019-01-01T12:15:00Z'

我想看看：

starttime | endtime | count | type
'2019-01-01T12:00:00Z', '2019-01-01T12:03:00Z', 3, 'dog'
'2019-01-01T12:01:00Z', '2019-01-01T12:03:00Z', 2, 'cat'
'2019-01-01T12:14:00Z', '2019-01-01T12:14:00Z', 1, 'dog'

编辑：

为了澄清，我基本上是在集群中按活动分组，其中集群被定义为10分钟内的连续活动。

因此，在上面的例子中，第一个dog集群的计数为3，因为它在10分钟的时间段内有3行，而每行之间没有超过10分钟。

Answer 1

使用lag()获取上一个时间戳。然后对之前间隔超过10分钟的地方进行累积计数。最后，聚合：

select min(timestamp), max(timestamp), count(*), type
from (select t.*,
count(*) filter (where prev_ts < timestamp - interval '10 minute') over (partition by type order by timestamp) as grp
from (select t.*,
lag(timestamp) over (partition by type order by timestamp) as prev_ts
from t
) t
) t
group by type, grp
order by type, min(timestamp)

这并不能产生你在问题中所得到的确切结果，但这可能是你真正想要的。

这里有一个db<gt；不停摆弄

Answer 2

这是一个挑战：(

with recursive c as (
(
select
type,
min(timestamp) as timestamp,
'0'::interval as dt,
1 as cl
from t group by type) union all
(
select distinct on (t.type)
t.type,
t.timestamp,
case when dt + (t.timestamp - c.timestamp) > '10 min'::interval then '0'::interval else dt + (t.timestamp - c.timestamp) end,
case when dt + (t.timestamp - c.timestamp) > '10 min'::interval then cl + 1 else cl end
from t join c on (t.type = c.type and t.timestamp > c.timestamp)
order by t.type, t.timestamp))
select
min(timestamp) as starttime,
max(timestamp) as endtyime,
count(*) as "count",
"type"
from c
group by "type", cl
order by 1;
┌─────────────────────┬─────────────────────┬───────┬──────┐
│      starttime      │      endtyime       │ count │ type │
├─────────────────────┼─────────────────────┼───────┼──────┤
│ 2019-01-01 12:00:00 │ 2019-01-01 12:03:00 │     3 │ dog  │
│ 2019-01-01 12:01:00 │ 2019-01-01 12:03:00 │     2 │ cat  │
│ 2019-01-01 12:15:00 │ 2019-01-01 12:15:00 │     1 │ dog  │
└─────────────────────┴─────────────────────┴───────┴──────┘

希望您熟悉递归CTE。

简要说明：

CCD_ 3列保持从最后一个"0"开始的间隔；"检查点"；到当前时间。如果大于10分钟，则重置为0。

CCD_ 4列保持"；簇号"；。当dt大于10分钟时，它增加。

最后，我们找到作为开始和结束时间的最小和最大时间戳，以及每种类型的行数和"0"；集群"；。

演示