求Databricks中t1表按规则生成Result列的SQL查询语句

假设你的表t1包含row_num和Event Label字段，以下是满足需求的SQL查询：

WITH sorted_data AS (
    SELECT 
        row_num,
        `Event Label`,
        -- 标记分组起始点：Hire和Rehire都是新分组的开始
        CASE WHEN `Event Label` IN ('Hire', 'Rehire') THEN 1 ELSE 0 END AS group_flag
    FROM t1
    ORDER BY row_num DESC
),
grouped_data AS (
    SELECT 
        row_num,
        `Event Label`,
        -- 累计分组起始点，生成分组ID
        SUM(group_flag) OVER (ORDER BY row_num DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM sorted_data
),
result_calculation AS (
    SELECT 
        row_num,
        `Event Label`,
        -- 以Hire所在分组为基准，计算Result值
        (group_id - MIN(CASE WHEN `Event Label` = 'Hire' THEN group_id ELSE NULL END) OVER ()) AS Result
    FROM grouped_data
)
SELECT row_num, `Event Label`, Result
FROM result_calculation
ORDER BY row_num DESC;

逻辑说明 #

1. 排序数据：先按row_num降序排列，确保后续处理顺序符合要求。
2. 标记分组起始点：将Hire和Rehire标记为分组起始标志，其他事件无标志。
3. 生成分组ID：通过累计分组起始标志，为每个行分配分组ID，每个Hire或Rehire会开启一个新的分组。
4. 计算Result值：找到Hire所在的分组ID，用当前行的分组ID减去该基准值，得到最终的Result：
   • Hire所在行的Result为0
   • 第一个Rehire及后续行（直到下一个Rehire）Result为1
   • 第二个Rehire及后续行Result为2，以此类推

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