如何用PostgreSQL SQL查询计算记录数据中活动直接跟随(DF)的频次

原SQL问题说明 #

• 未覆盖所有活动对组合，仅返回有出现记录的跟随关系，缺少频次为0的条目
• 排序逻辑冗余且不稳定：partition by后的排序规则包含分组字段ci无意义，同完成时间的事件缺少次排序键可能导致顺序错乱
• 自连接实现相邻匹配效率较低，可直接用窗口函数简化

正确SQL实现 #

WITH all_activities AS (
    -- 提取所有唯一活动值，用于生成全量活动对
    SELECT DISTINCT Act AS activity
    FROM table_name
),
all_activity_pairs AS (
    -- 生成所有可能的From->To活动对，覆盖频次为0的组合
    SELECT 
        a1.activity AS "From",
        a2.activity AS "To"
    FROM all_activities a1
    CROSS JOIN all_activities a2
),
follow_relations AS (
    -- 计算每个案例下的实际直接跟随关系
    SELECT 
        Act AS from_act,
        -- 取同案例下按时间排序的下一个活动
        LEAD(Act) OVER (
            PARTITION BY ev_ID 
            ORDER BY Complete ASC, ID ASC -- 同时间按ID排序保证顺序稳定
        ) AS to_act
    FROM table_name
)
-- 关联全量对和实际统计结果，无匹配则频次填0
SELECT 
    ap."From",
    ap."To",
    COALESCE(COUNT(fr.from_act), 0) AS "Frequency"
FROM all_activity_pairs ap
LEFT JOIN follow_relations fr 
    ON ap."From" = fr.from_act 
    AND ap."To" = fr.to_act
WHERE fr.to_act IS NOT NULL -- 过滤掉每个案例最后一个活动的无效LEAD结果
GROUP BY ap."From", ap."To"
ORDER BY ap."From", ap."To";

逻辑说明 #

1. all_activities 提取数据中所有出现过的唯一活动值，为生成全量活动对做准备
2. all_activity_pairs 通过笛卡尔积生成所有可能的「From-To」活动组合，确保最终输出包含频次为0的条目，符合要求的输出格式
3. follow_relations 使用PostgreSQL原生LEAD窗口函数，按ev_ID分组后按完成时间升序排序，直接获取当前活动的下一个相邻活动，排序规则增加ID作为次键，避免同完成时间的事件顺序错乱
4. 最后左关联全量活动对和实际跟随关系统计结果，用COALESCE将无匹配的频次设为0，按要求排序输出

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