如何在Hive中用前序非空值填充NULL行直至下一个非空值？

需求说明 #

当md_flag字段为NULL时，用前一个最近的非空值填充，直至遇到下一个非空值为止，生成New md_flag字段。

输入示例数据 #

sellingseason|item|location|wk|md_flag|New md_flag
Q1-23|BP608WZ271812|LBDI|Mar W5-23|TRUE|TRUE
Q1-23|BP608WZ271812|LBDI|Apr W1-23|TRUE|TRUE
Q1-23|BP608WZ271812|LBDI|Apr W2-23|TRUE|TRUE
Q1-23|BP608WZ271812|LBDI|Apr W3-23|TRUE|TRUE
Q1-23|BP608WZ271812|LBDI|Apr W4-23|TRUE|TRUE
Q2-23|BP608WZ271812|LBDI|May W1-23|TRUE|TRUE
Q2-23|BP608WZ271812|LBDI|May W2-23|TRUE|TRUE
Q2-23|BP608WZ271812|LBDI|May W3-23|TRUE|TRUE
Q2-23|BP608WZ271812|LBDI|May W4-23|TRUE|TRUE
Q2-23|BP608WZ271812|LBDI|Jun W1-23|FALSE|FALSE
Q2-23|BP608WZ271812|LBDI|Jun W2-23|TRUE|TRUE
Q2-23|BP608WZ271812|LBDI|Jun W3-23|NULL|TRUE
Q2-23|BP608WZ271812|LBDI|Jun W4-23|TRUE|TRUE
Q2-23|BP608WZ271812|LBDI|Jun W5-23|NULL|TRUE
Q2-23|BP608WZ271812|LBDI|Jul W1-23|NULL|TRUE
Q2-23|BP608WZ271812|LBDI|Jul W2-23|FALSE|FALSE
Q2-23|BP608WZ271812|LBDI|Jul W3-23|NULL|FALSE
Q2-23|BP608WZ271812|LBDI|Jul W4-23|NULL|FALSE
Q3-23|BP608WZ271812|LBDI|Aug W1-23|NULL|FALSE
Q3-23|BP608WZ271812|LBDI|Aug W2-23|NULL|FALSE

Hive解决方案SQL #

SELECT 
    sellingseason,
    item,
    location,
    wk,
    md_flag,
    FIRST_VALUE(md_flag) OVER (
        PARTITION BY item, location, grp 
        ORDER BY wk 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS New_md_flag
FROM (
    SELECT 
        *,
        -- 为每个非空md_flag生成组ID，连续空值归属于前一个非空值的组
        SUM(CASE WHEN md_flag IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY item, location 
            ORDER BY wk 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS grp
    FROM sales_data
) t;

逻辑解释 #

1. 分组标记（grp字段）：

   • 按item和location分区，确保每个商品-地点的序列独立处理
   • 按wk排序，保证时间顺序正确
   • 用累计求和：每次遇到非空的md_flag就加1，这样连续的空值会和前面最近的非空值归为同一个组

2. 前向填充：

   • 在每个item, location, grp分组内，用FIRST_VALUE取组内第一个非空的md_flag值，填充组内所有空值
   • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW确保只取当前行及之前的第一个非空值，符合前向填充的逻辑

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