Impala中使用LAG函数填充NULL值失败，求错误原因排查

问题场景 #

我有一张汇率表，数据如下：

exchange_rate_date  from_currency   to_currency
12/18/23            CAD             USD
12/17/23            NULL            NULL
12/16/23            NULL            NULL
12/15/23            CAD             USD

需要用列中的非NULL历史值填充from_currency和to_currency的空值，但使用LAG函数后仍得到NULL，语句如下：

LAG(a.from_currency,1) OVER(PARTITION BY a.from_currency,a.to_currency ORDER BY a.from_currency DESC)

错误原因 #

你的LAG函数写法有两个核心问题：

• 分区逻辑错误：用from_currency和to_currency作为分区键，会把NULL值单独分到一个独立分区（NULL与任何值都不相等），而有值的行（CAD/USD）在另一个分区。LAG只能在同一分区内取前一行，所以NULL分区的行根本取不到有值的行数据。
• 排序逻辑错误：按from_currency降序排序完全不合理，这个列本身就是要填充的字段，应该按exchange_rate_date（日期）排序，才能按时间顺序取历史非NULL值。

正确解决方案 #

方法1：LAST_VALUE + IGNORE NULLS（支持PostgreSQL 11+、SQL Server 2022+、Oracle等） #

直接取窗口内最近的非NULL值，语法简洁：

SELECT 
    exchange_rate_date,
    LAST_VALUE(from_currency) OVER (ORDER BY exchange_rate_date DESC IGNORE NULLS) AS from_currency,
    LAST_VALUE(to_currency) OVER (ORDER BY exchange_rate_date DESC IGNORE NULLS) AS to_currency
FROM your_table;

说明：按日期倒序排列，IGNORE NULLS让LAST_VALUE跳过空值，自动取最近的非NULL值填充。

方法2：分组累积填充（兼容更多数据库） #

如果你的数据库不支持IGNORE NULLS，可以用分组的方式实现：

WITH ranked_data AS (
    SELECT 
        *,
        -- 每遇到非NULL值就生成新分组，连续NULL会和最近的非NULL值同组
        SUM(CASE WHEN from_currency IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY exchange_rate_date DESC) AS currency_group
    FROM your_table
)
SELECT 
    exchange_rate_date,
    MAX(from_currency) OVER (PARTITION BY currency_group) AS from_currency,
    MAX(to_currency) OVER (PARTITION BY currency_group) AS to_currency
FROM ranked_data
ORDER BY exchange_rate_date DESC;

说明：通过SUM生成分组标识，同一组内的NULL会被组内的非NULL值（MAX取到）填充。

方法3：递归CTE（通用写法） #

适合所有支持递归的数据库，比如MySQL 8+、PostgreSQL等：

WITH recursive_fill AS (
    -- 取最新日期的行作为初始数据
    SELECT 
        exchange_rate_date,
        from_currency,
        to_currency
    FROM your_table
    WHERE exchange_rate_date = (SELECT MAX(exchange_rate_date) FROM your_table)
    
    UNION ALL
    
    -- 递归关联前一天的行，用已有值填充空值
    SELECT 
        t.exchange_rate_date,
        COALESCE(t.from_currency, rf.from_currency),
        COALESCE(t.to_currency, rf.to_currency)
    FROM your_table t
    JOIN recursive_fill rf ON t.exchange_rate_date = DATE_SUB(rf.exchange_rate_date, INTERVAL 1 DAY)
)
SELECT * FROM recursive_fill ORDER BY exchange_rate_date DESC;

注意：如果日期不连续，需要用ROW_NUMBER替代日期加减来关联前后行。

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