查找属于规模为N的时间跨度集群的事件SQL实现方案

现有event表结构 #

id | time  | location | user
1  | 08:55 | x        | A
2  | 08:57 | x        | B
3  | 08:58 | y        | C
4  | 08:59 | x        | A
5  | 09:01 | x        | C
6  | 09:01 | x        | D
7  | 09:04 | x        | B
8  | 09:08 | x        | C
9  | 09:12 | x        | D

表创建SQL #

CREATE TABLE event (
    id serial PRIMARY KEY,
    time time(0),
    location char,
    usr char
);
INSERT INTO event (time, location, usr) VALUES
    ('08:55', 'x', 'A'),
    ('08:57', 'x', 'B'),
    ('08:58', 'y', 'C'),
    ('08:59', 'x', 'A'),
    ('09:01', 'x', 'C'),
    ('09:01', 'x', 'D'),
    ('09:04', 'x', 'B'),
    ('09:08', 'x', 'C'),
    ('09:12', 'x', 'D');

需求说明 #

找出所有满足同一location下，5分钟内有N个或更多不同usr产生事件的事件ID：

• 当N=3时，结果为ID 2,4,5,6,7；
• 当N=4时，结果为ID 2,4,5,6。

现有查询的缺陷 #

当前实现的查询仅检查后续事件与当前事件用户不同，未验证用户间的唯一性，导致结果冗余，且无法准确统计不同用户数量：

现有查询语句 #

SELECT id, following_id
FROM  (
    SELECT
        event.id,
        following_event.id AS following_id,
        COUNT(*) OVER (PARTITION BY event.id) AS num_following
    FROM event
    INNER JOIN event AS following_event USING (location)
    WHERE
        following_event.usr != event.usr
        AND following_event.time >= event.time
        AND following_event.time < event.time + '00:05:00'
)
WHERE num_following >= 2;

现有查询结果 #

id | following_id
----+--------------
  2 |            6
  2 |            4
  2 |            5
  4 |            5
  4 |            6
  5 |            7
  5 |            6
  6 |            7
  6 |            5

优化后的SQL方案 #

核心思路是对每个事件，统计其所在location、时间窗口内的不同用户数量，再筛选出数量满足阈值N的事件ID。

方案1：窗口函数统计唯一用户数（高效适用于大数据集） #

WITH event_window_stats AS (
    SELECT
        e.id,
        -- 统计当前事件所在location、5分钟窗口内的唯一用户数
        COUNT(DISTINCT w.usr) OVER (
            PARTITION BY e.location
            ORDER BY e.time
            RANGE BETWEEN CURRENT ROW AND INTERVAL '5 minutes' FOLLOWING
        ) AS distinct_users
    FROM event e
    JOIN event w
        ON e.location = w.location
        AND w.time BETWEEN e.time AND e.time + INTERVAL '5 minutes'
)
-- 去重后筛选满足阈值的事件ID
SELECT DISTINCT id
FROM event_window_stats
WHERE distinct_users >= N; -- 将N替换为具体阈值，如3或4

方案2：关联子查询统计唯一用户数（逻辑简洁适用于小数据集） #

SELECT DISTINCT e.id
FROM event e
WHERE (
    SELECT COUNT(DISTINCT usr)
    FROM event
    WHERE location = e.location
      AND time BETWEEN e.time AND e.time + INTERVAL '5 minutes'
) >= N; -- 将N替换为具体阈值

验证结果 #

• 替换N为3时，返回ID：2,4,5,6,7；
• 替换N为4时，返回ID：2,4,5,6，完全符合需求。

内容的提问来源于stack exchange，提问作者Lamdba