如何在Hive的Lead函数中实现忽略空值的功能

报错原因 #

Hive 原生窗口函数暂不支持 IGNORE NULLS 语法，这是 Presto 和 Hive 的语法差异导致的，你遇到的语法报错就是因为使用了 Hive 不识别的 ignore nulls 参数。

适配Hive的解决方案 #

我们可以通过分组标记填充法实现和 LEAD(...) IGNORE NULLS 完全一致的效果，同时保留你原有的业务判断逻辑，修改后的完整代码如下：

WITH cte_v1 AS (
    -- 此处保留你原来cte_v1的所有逻辑不变
),
-- 新增中间层：给同分区内的行打分组标记，同组对应正序下同一个非空坐席ID
cte_agent_group AS (
    SELECT
        *,
        SUM(CASE WHEN id_agent IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY id_ticket, interaction_channel
            ORDER BY interaction_start_time DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS agent_group
    FROM cte_v1
),
-- 计算每个分组对应的非空坐席ID，以及下一行的渠道值
cte_fill AS (
    SELECT
        *,
        MAX(id_agent) OVER (PARTITION BY id_ticket, interaction_channel, agent_group) AS next_non_null_agent,
        LEAD(interaction_channel, 1) OVER (
            PARTITION BY id_ticket, interaction_channel
            ORDER BY interaction_start_time
        ) AS next_interaction_channel
    FROM cte_agent_group
)
-- 最终查询，逻辑和你原有的CASE完全对齐
SELECT
    *,
    CASE
        WHEN id_agent IS NOT NULL THEN id_agent
        WHEN id_agent IS NULL AND next_interaction_channel = 'messaging' THEN next_non_null_agent
    END AS agent
FROM cte_fill;

逻辑说明 #

1. 先按id_ticket、interaction_channel分区，倒序按交互时间排序，遇到非空的id_agent就给分组号加1，这样正序里连续的空值行都会被划分到离它最近的下一个非空id_agent的分组里
2. 按分组取最大值就能拿到每个空值行对应的下一个非空坐席ID，等价于原Presto语法里lead(id_agent,1) ignore nulls的效果
3. 单独用普通LEAD函数取相邻下一行的渠道值，匹配你原有的判断条件，保证业务逻辑完全不变

该方案兼容所有Hive版本，运行后输出结果和你提供的Presto运行示例完全一致。

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