PostgreSQL技术问题：重复分区序列统计及事件序列分析

嘿，我来帮你搞定这两个PostgreSQL的问题，都是窗口函数的经典应用场景，咱们一步步拆解：

问题1：统计重复分区内序列的连续运行次数 #

这个需求本质是把连续出现的相同值归为一组，然后统计每组的长度。核心思路是利用窗口函数生成的序号差值来标记连续组，具体步骤如下：

1. 先按你需要的分区键（比如用户ID）和排序字段（比如事件时间）生成全局序号；
2. 再按分区键+重复字段（比如事件类型）生成组内序号；
3. 用全局序号减去组内序号，得到的差值相同的行就是连续的相同值组；
4. 最后按分区键和差值分组，统计每组的行数就是连续运行次数。

举个实际的例子，假设你有一张user_events表，结构是user_id, event_type, event_time：

WITH event_groups AS (
    SELECT
        user_id,
        event_type,
        event_time,
        -- 全局序号（按用户+时间排序）
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY event_time) AS global_rn,
        -- 组内序号（按用户+事件类型+时间排序）
        ROW_NUMBER() OVER(PARTITION BY user_id, event_type ORDER BY event_time) AS group_rn
    FROM user_events
)
SELECT
    user_id,
    event_type,
    MIN(event_time) AS start_time,
    MAX(event_time) AS end_time,
    COUNT(*) AS consecutive_runs
FROM event_groups
-- 差值相同的就是连续的同事件组
GROUP BY user_id, event_type, (global_rn - group_rn)
ORDER BY user_id, start_time;

这个SQL会输出每个用户每个连续事件段的开始/结束时间，以及连续运行的次数。

问题2：统计每个事件的执行次数及顺序 #

你提到的rank()、dense_rank()、row_number()、lag()其实都能在这里派上用场，关键是明确需求：是要每个事件在全局的顺序/次数？还是按用户分区的顺序/次数？ 一般这类需求都是按用户分区的，我们以“每个用户的每个事件实例的执行顺序（第N次发生），以及该用户该事件的总执行次数”为例：

核心思路是：

• 用ROW_NUMBER()（或RANK()，如果有重复时间需要并列的话）按用户+事件类型+时间排序，得到每个事件实例的执行顺序；
• 用COUNT(*) OVER()窗口函数按用户+事件类型分区，得到该用户该事件的总执行次数；
• 如果需要对比上一次事件的情况，再用LAG()函数获取前一个事件的信息。

还是用user_events表举例，假设结构是user_id, event_id, event_type, event_time，SQL如下：

SELECT
    user_id,
    event_id,
    event_type,
    event_time,
    -- 该用户该事件的第几次执行（row_number会给每个实例唯一序号，rank会并列）
    ROW_NUMBER() OVER(PARTITION BY user_id, event_type ORDER BY event_time) AS execution_order,
    -- 该用户该事件的总执行次数
    COUNT(*) OVER(PARTITION BY user_id, event_type) AS total_executions,
    -- 可选：获取上一次同类型事件的时间（如果需要看间隔）
    LAG(event_time) OVER(PARTITION BY user_id, event_type ORDER BY event_time) AS prev_event_time
FROM user_events
ORDER BY user_id, event_time;

如果你的需求是全局的事件执行顺序（不按用户分区），只需要把PARTITION BY user_id去掉即可。另外，dense_rank()和rank()的区别在于：如果有相同的event_time，rank()会跳过序号（比如两个并列第1，下一个是第3），dense_rank()不会（下一个是第2），你可以根据需求选择。

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