Hive SQL如何按人员分组获取每行对应之前最近一次到岗日期

实现思路 #

采用纯窗口函数实现，仅对原表做一次全量扫描，时间复杂度为O(n)，无自连接带来的额外开销，完全适配千万级数据量的运算需求。
核心逻辑：按人员ID分组，按日期升序排序，仅保留到岗（In-office=1）记录的日期值，其余记录置为null，取当前行之前所有记录中最近的非空日期值即可。

实现代码 #

select 
    Person,
    Date,
    `In-office`,
    last_value(if(`In-office` = 1, Date, null), true) over (
        partition by Person 
        order by to_date(Date, 'MM-dd-yyyy') 
        rows between unbounded preceding and 1 preceding
    ) as `Most recent in-office`
from 你的表名;

参数说明：

• last_value的第二个参数设为true代表忽略null值，仅取非空的到岗日期
• 窗口范围rows between unbounded preceding and 1 preceding限定仅计算当前行之前的所有记录，符合需求规则
• 调用to_date转换日期类型避免字符串排序异常，可根据实际存储的日期格式调整格式串

兼容性写法 #

如果使用的Hive版本较低不支持last_value的忽略null参数，可替换为以下写法，效果完全一致：

select 
    Person,
    Date,
    `In-office`,
    max(if(`In-office` = 1, Date, null)) over (
        partition by Person 
        order by to_date(Date, 'MM-dd-yyyy') 
        rows between unbounded preceding and 1 preceding
    ) as `Most recent in-office`
from 你的表名;

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