编写Hive SQL实现连续相同item_id同排名的单调递增排名

需求描述 #

按event_ts排序后添加rank列：

• 连续行的item_id相同时，这些行需获得相同的排名
• 排名需单调递增，不能按item_id分区（否则每个分区排名从1开始），dense_rank()无法直接满足该需求

输入数据 #

期望输出 #

解决方案SQL #

WITH ranked_data AS (
    SELECT 
        event_ts,
        item_id,
        -- 判断当前行与上一行item_id是否不同，首次出现或不同则标记为1
        CASE 
            WHEN LAG(item_id) OVER (ORDER BY event_ts) != item_id 
                 OR LAG(item_id) OVER (ORDER BY event_ts) IS NULL
            THEN 1 
            ELSE 0 
        END AS rank_flag
    FROM your_table_name
)
SELECT 
    event_ts,
    item_id,
    -- 对标记累计求和，得到连续分组的单调递增排名
    SUM(rank_flag) OVER (ORDER BY event_ts) AS rank
FROM ranked_data
ORDER BY event_ts;

逻辑说明 #

1. 生成分组标记：用LAG()窗口函数获取上一行的item_id，对比后生成rank_flag——首次出现或与上一行item_id不同时标记为1，否则为0。
2. 计算累计排名：对rank_flag按event_ts排序做累计求和，连续相同item_id的行因rank_flag为0，累计值保持不变，从而获得统一排名；每次出现新item_id时，累计值加1，保证排名单调递增。

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