sql - 在 PostgreSQL 中将相似的连续事件分组在一起

Question

我是 SQL 新手。我目前正在学习使用按时间排序的用户事件的数据集。例如，示例子集如下 -

事件ID	用户身份	事件名称	时间戳
1	1	搜索事件	2020-01-20 09:42:52
2	1	搜索事件	2020-01-20 09:42:58
3	2	搜索事件	2020-01-20 09:43:27
4	1	checkout_event	2020-01-20 09:43:49
5	2	checkout_event	2020-01-20 09:43:54
6	2	搜索事件	2020-01-20 09:44:12
7	1	搜索事件	2020-01-20 09:54:21
8	1	搜索事件	2020-01-20 12:45:10
9	1	搜索事件	2020-01-20 12:45:32
10	2	预订事件	2020-01-20 12:46:52

我想在第一次事件发生后每隔 10 分钟计算一次不同事件的总数。

1. 用户 1 在 09:42:52 进行了 search_event。在接下来的 10 分钟内，即直到 09:52:52 之前，用户 1 的每个搜索事件都不计算在内。（即在计数中省略了 09:42:58 的搜索事件）
2. 用户 2 在 09:43:27 进行了 search_event。因此，直到 9:53:27，用户 2 的任何 search_events 都不会被计算在内。
3. 用户 1 在 09:43:49 进行了 checkout_event。省略用户 1 到 09:53:49 之前对所有结帐事件的计数
4. 用户 2 在 09:43:54 进行了 checkout_event。忽略用户 2 到 09:53:54 之前对所有结帐事件的计数

基本上计算以下事件 - （由 event_ids 通知）

Search - 1,3,7,8
Checkout - 4,5
Booking - 10

输出为计数 -

Search 4
Checkout 2
Booking 1

由于时间重叠，以下事件被省略。

Event ID 2 - Occurs within 10mins of Event ID 1
Event ID 6 - Occurs within 10mins of Event ID 3
Event ID 9 - Occurs within 10mins of Event ID 8

谢谢

标签： sqlpostgresql

Answer 1

您可以在 10 分钟内使用简单not exists的检查是否存在相同的记录：

with a(EventID, UserID, EventName, ts) as (
  select 1, 1, 'search_event', timestamp '2020-01-20 09:42:52' union all
  select 2, 1, 'search_event', timestamp '2020-01-20 09:42:58' union all
  select 3, 2, 'search_event', timestamp '2020-01-20 09:43:27' union all
  select 4, 1, 'checkout_event', timestamp '2020-01-20 09:43:49' union all
  select 5, 2, 'checkout_event', timestamp '2020-01-20 09:43:54' union all
  select 6, 2, 'search_event', timestamp '2020-01-20 09:44:12' union all
  select 7, 1, 'search_event', timestamp '2020-01-20 09:54:21' union all
  select 8, 1, 'search_event', timestamp '2020-01-20 12:45:10' union all
  select 9, 1, 'search_event', timestamp '2020-01-20 12:45:32' union all
  select 10, 2, 'booking_event', timestamp '2020-01-20 12:46:52'
)
select *
from a
where not exists (
  select null
  from a as f
  where f.userid = a.userid
    and f.eventname = a.eventname
    and f.ts > a.ts - interval '10' minute
    and f.ts < a.ts 
)
order by eventid

事件ID | 用户名 | 事件名称 | ts                 
------: | -----: | :------------- | :-----------------
      1 | 1 | 搜索事件 | 2020-01-20 09:42:52
      3 | 2 | 搜索事件 | 2020-01-20 09:43:27
      4 | 1 | 结帐事件 | 2020-01-20 09:43:49
      5 | 2 | 结帐事件 | 2020-01-20 09:43:54
      7 | 1 | 搜索事件 | 2020-01-20 09:54:21
      8 | 1 | 搜索事件 | 2020-01-20 12:45:10
     10 | 2 | 预订活动 | 2020-01-20 12:46:52

db<>在这里摆弄