如何用SQL窗口函数实现工单事件序列分组与ranking计算

我尝试使用dense_rank、LAG、LEAD等各类排序函数，但仍未解决当前的工单事件序列分组问题。以下是数据样本及期望生成的ranking列结果：

场景说明 #

• 常见场景：技术人员提交工单请求，由操作员处理解决
• 技术人员在操作员关闭工单后再次发起请求（result_tech='FWD'），属于同一工单内的新事件序列
• 操作员之间的FWD转发操作，属于同一事件序列

分组规则 #

单个PK_ID范围内，满足以下任一条件的记录为事件序列的起始：

• result_tech为空 且 source_id=1
• result_tech='FWD' 且 source_id=1

起始记录之后，按pk_id_row_num排序的所有后续记录都属于该序列，包括：

• 操作员的解决操作（result_tech='OK'）或转发操作（result_tech='FWD'且source_id=2）
• 技术人员的取消操作（result_tech='CANCEL'且source_id=1）

需要生成ranking列，对同一PK_ID下的每个事件序列进行分组标记。我尝试了多种窗口函数组合及分区设置，最近的尝试SQL如下，但仍需调整：

SELECT 
    pk_id
    ,pk_id_source_id
    ,reason_id
    ,reason_desc
    ,result_tech
    ,source_id
    ,source_descr
    ,CASE
        WHEN (result_tech IS NULL OR result_tech = 'FWD') AND source_id = 1 THEN 'START'
        WHEN Lead(source_id,1,1) Over(PARTITION BY pk_id ORDER BY pk_id_source_id) <=  source_id THEN 'NEXT'
    END sorting
FROM 
    My_example_table
WHERE 
    pk_id IN (5437376, 5647621, 5649364, 5649385, 5649387, 5649396, 5652537)
ORDER BY 
    pk_id_source_id;

解决方案 #

以下SQL可以实现符合需求的ranking分组：

WITH marked_starts AS (
    SELECT 
        pk_id,
        pk_id_row_num,
        result_Tech,
        source_id,
        source_descr,
        -- 标记每个序列的起始行
        CASE 
            WHEN (result_Tech IS NULL OR result_Tech = 'FWD') AND source_id = 1 THEN 1
            ELSE 0
        END AS is_start
    FROM My_example_table
    ORDER BY pk_id, pk_id_row_num
),
grouped_sequences AS (
    SELECT 
        *,
        -- 同PK_ID内累计求和，生成组内唯一标识
        SUM(is_start) OVER (PARTITION BY pk_id ORDER BY pk_id_row_num) AS group_seq
    FROM marked_starts
)
SELECT 
    pk_id,
    pk_id_row_num,
    result_Tech,
    source_id,
    source_descr,
    -- 全局生成连续的ranking值
    DENSE_RANK() OVER (ORDER BY pk_id, group_seq) AS ranking
FROM grouped_sequences
ORDER BY ranking, pk_id_row_num;

逻辑说明 #

1. 标记起始行：在marked_starts CTE中，将符合规则的序列起始行标记为1，其他行标记为0。
2. 生成组内标识：在grouped_sequences CTE中，对每个pk_id分区，按pk_id_row_num排序后累计求和is_start，同一序列的所有记录会得到相同的group_seq值。
3. 生成全局ranking：最后用DENSE_RANK()函数，按pk_id和group_seq全局排序，生成连续的ranking列，与期望结果完全匹配。

内容的提问来源于stack exchange，提问作者L.P.