sql - 根据时间列选择连续事件的计数

Question

这是我的数据:

app_id  event_type  event_time
1       event1      2020-22-03 04:05:03
1       event3      2020-22-03 04:05:04
1       event2      2020-22-03 04:05:05
1       event3      2020-22-03 04:05:10
1       event1      2020-22-03 04:05:11
1       event2      2020-22-03 04:05:12
2       event3      2020-22-03 04:05:04

我想为每个 app_id 计算两个事件的总和:

event3数量

event1数量紧随其后(基于 event_time) event2

有了上面的数据，输出将是:

app_id  count
1       3 <--- (2 * event3 + event1 followed by event2)
2       1

数量 event3可以计算为:

SELECT app_id,
    count(event_type = 'event3')
    FROM test_table
    GROUP BY app_id;

对于第二次计数，我猜数据需要是 GROUP BY app_id结合 ORDERBY event_time
我还看到了检查连续值的其他答案( Count Number of Consecutive Occurrence of values in Table )，但我无法将其适应我的用例

Answer 1

使用 CTE在那里您将 2 个条件转换为整数 0 或 1，然后将聚合:

WITH cte AS (
  SELECT *, 
    (event_type = 'event3')::int is_event3,
    (event_type = 'event1' AND LEAD(event_type) OVER (PARTITION BY app_id ORDER BY event_time) = 'event2')::int is_event1_event2
  FROM test_table   
)  
SELECT app_id, SUM(is_event3 + is_event1_event2) "count"
FROM cte
GROUP BY app_id 

见 demo .
结果:

| app_id | count |
| ------ | ----- |
| 1      | 3     |
| 2      | 1     |