Hive中如何在非连续记录处将行号重置为1

要实现给用户的连续日期数据分配递增行号、断档时重置为1的需求，你可以通过嵌套窗口函数的方式完成，核心是先识别每个用户的连续日期区间，再在区间内分配行号，具体实现如下：

完整SQL代码 #

WITH get_prev_date AS (
    -- 按用户分组、日期排序，获取上一条记录的日期
    SELECT 
        Date,
        User,
        LAG(to_date(Date), 1) OVER (PARTITION BY User ORDER BY to_date(Date)) AS prev_date
    FROM your_table_name
),
mark_break_point AS (
    -- 判断当前日期与上一条是否连续，标记断档点
    SELECT 
        Date,
        User,
        CASE 
            WHEN datediff(to_date(Date), prev_date) = 1 THEN 0
            ELSE 1
        END AS is_break
    FROM get_prev_date
),
generate_group_id AS (
    -- 累加断档标识，生成连续区间的分组ID
    SELECT 
        Date,
        User,
        SUM(is_break) OVER (PARTITION BY User ORDER BY to_date(Date)) AS group_id
    FROM mark_break_point
)
-- 按用户和分组ID分配行号
SELECT 
    Date,
    User,
    ROW_NUMBER() OVER (PARTITION BY User, group_id ORDER BY to_date(Date)) AS Rank
FROM generate_group_id
ORDER BY User, Date;

关键逻辑说明 #

1. 获取上一条日期：用LAG()窗口函数按用户分组、日期排序，取出当前行的上一条记录日期，为判断连续性做准备。
2. 标记断档点：通过datediff()计算当前日期与上一条日期的差值，差值不为1则标记为断档点（值为1），连续则标记为0。
3. 生成区间分组ID：对断档点标识做累加求和，同一个连续日期区间的分组ID会保持一致，断档时分组ID自动递增，实现区间划分。
4. 分配行号：在每个(User, group_id)分组内，用ROW_NUMBER()生成从1开始的递增行号，断档时因分组ID变化，行号自动重置为1。

注意：将代码中的your_table_name替换为你的实际表名；如果Date字段已是Hive日期类型，可以去掉to_date()转换函数。

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