postgresql - 将时间差较小的交错记录分组

Question

这个问题很难回答，但我正在尝试复制社交媒体或通知提要在批量最近事件时所做的事情，以便它们可以显示操作的“序列”。例如，如果这些是“类似”记录，按时间倒序排列:

like_id | user_id |   like_timestamp
--------------------------------
1       | bob     | 12:30:00
2       | bob     | 12:29:00
3       | jane    | 12:27:00
4       | bob     | 12:26:00
5       | jane    | 12:24:00
6       | jane    | 12:23:00
7       | scott   | 12:22:00
8       | bob     | 12:20:00
9       | alice   | 12:19:00
10      | scott   | 12:18:00

我想对它们进行分组，以便获得用户喜欢的最后 3 个“突发”，按用户分组(分区？)。如果“连发”规则是相隔小于 5 分钟的点赞属于同一个连发，那么我们会得到:

user_id | num_likes | burst_start | burst_end
----------------------------------------------
bob     | 3         | 12:26:00    | 12:30:00
jane    | 3         | 12:23:00    | 12:27:00
scott   | 2         | 12:18:00    | 12:22:00

alice 的点赞没有被计算在内，因为它是第 4 个最近批处理的一部分，而点赞 8 没有被添加到 bob 的计数中，因为它是下一个之前 6 分钟。

我已经尝试使用 postgres 的 lag 函数来跟踪突发事件，它可以让我标记开始和结束事件，但是由于类似事件可以错开，所以我无法将类似事件联系起来到它的“发起者”(例如，将 id 4 绑定(bind)回 2)。

这样分组可行吗？如果是这样，是否可以跟踪每个突发的开始和结束时间戳？

Answer 1

step-by-step demo:db<>fiddle

WITH group_ids AS (   -- 1
    SELECT DISTINCT
        user_id,
        first_value(like_id) OVER (PARTITION BY user_id ORDER BY like_id) AS group_id
    FROM
        likes
    LIMIT 3
)
SELECT 
    user_id,
    COUNT(*) AS num_likes,
    burst_start,
    burst_end
FROM (
    SELECT
        user_id,
        -- 4
        first_value(like_timestamp) OVER (PARTITION BY group_id ORDER BY like_id) AS burst_end,
        first_value(like_timestamp) OVER (PARTITION BY group_id ORDER BY like_id DESC) AS burst_start
    FROM (
        SELECT
            l.*, gi.group_id,
            -- 2
            lag(like_timestamp) OVER (PARTITION BY group_id ORDER BY like_id) - like_timestamp AS diff
        FROM
            likes l 
        JOIN
            group_ids gi ON l.user_id = gi.user_id
    ) s
    WHERE diff IS NULL OR diff <= '00:05:00'  -- 3
) s
GROUP BY user_id, burst_start, burst_end  -- 5

1. CTE 用于为每个 user_id 创建一个有序的组 ID。因此，第一个用户(这里是最近的用户)获得最低的 group_id(即 bob)。第二个用户第二高(jane)等等。这用于能够在一个分区内与某个用户的所有喜欢一起工作。这一步是必要的，因为您不能简单地按 user_id 排序，这会使 alice 到达顶部。 LIMIT 3 将整个查询限制为前三个用户。
2. 加入计算出的用户的group_id 后，使用lag() window function 计算时间差。这使您可以获得以前的值。所以它可以用来轻松计算当前时间戳与前一个时间戳之间的差异。这仅发生在用户组内。
3. 之后，可以通过计算的 diff
移除距离太远的点赞(距离上一个点赞超过 5 分钟)
4. 然后可以使用first_value() 窗口函数(升序和降序)计算最高和最低时间戳。这些标记您的 burst_start 和 burst_end
5. 最后，您可以对所有用户进行分组并计算他们的记录。