BigQuery SQL补全连续日期 用前序价格填充缺失日期记录

问题说明 #

• 商品价格表仅在价格发生变动时写入新记录，价格无变动的中间日期不入库，缺失日期的价格与前一有记录日期的价格保持一致
• 输入表字段：date（日期）、id（商品ID）、price（价格）
• 示例：id=1的商品仅存在3条记录：2022-01-01（price=5）、2022-01-03（price=6）、2022-01-05（price=7），需要补全2022-01-02（price=5）、2022-01-04（price=6）两条缺失记录，最终得到每个商品对应全连续日期、价格无缺失的结果集
• 原有实现尝试：先构建连续日期表date_table，关联原价格表后用lag函数取前值填充，编写的SQL如下，但无法得到正确结果

select
date,id,
case
when price is null then nullPrice 
else price
end as price
from(
select *,
Lag(price, 1) OVER(.
       ORDER BY date,id ASC) AS nullPrice
from price_table
join date_table using(date)
)

原有代码问题 #

1. 关联逻辑错误：使用内连接join只会保留原价格表和日期表都能匹配上日期的记录，缺失日期的行根本不会出现在关联结果中，无法做填充
2. 窗口函数逻辑错误：lag仅取当前行的上1行值，如果出现连续多日价格缺失的情况，上一行的price也是null，无法取到最近一次的有效价格；且窗口没有按商品ID分区，会跨商品混算价格
3. 排序逻辑错误：窗口排序没有先按商品ID分区隔离，不同商品的价格序列会被打乱

正确实现代码 #

首先确认提前构建的date_table已经覆盖了需要统计的完整日期范围，比如要统计2022年1月全月数据，这个表就要包含1月1日到1月31日的所有连续日期。

版本1：支持IGNORE NULLS语法的引擎（BigQuery、Spark SQL、PostgreSQL 11+等） #

WITH all_date_product AS (
    -- 生成所有商品+所有连续日期的全量基础数据集
    SELECT DISTINCT d.date, p.id
    FROM date_table d
    CROSS JOIN (SELECT DISTINCT id FROM price_table) p
),
joined_price AS (
    -- 左连接原价格表，有效价格记录保留price，缺失日期price为null
    SELECT
        adp.date,
        adp.id,
        pt.price
    FROM all_date_product adp
    LEFT JOIN price_table pt
        ON adp.id = pt.id AND adp.date = pt.date
)
-- 取每个商品按日期排序后，到当前行为止最近的非空价格
SELECT
    date,
    id,
    LAST_VALUE(price IGNORE NULLS) OVER (
        PARTITION BY id
        ORDER BY date ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS price
FROM joined_price
ORDER BY id, date;

版本2：不支持IGNORE NULLS语法的引擎（旧版MySQL、SQL Server等） #

通过分组标记的方式实现同样的逻辑，兼容性更强：

WITH all_date_product AS (
    SELECT DISTINCT d.date, p.id
    FROM date_table d
    CROSS JOIN (SELECT DISTINCT id FROM price_table) p
),
joined_price AS (
    SELECT
        adp.date,
        adp.id,
        pt.price
    FROM all_date_product adp
    LEFT JOIN price_table pt
        ON adp.id = pt.id AND adp.date = pt.date
),
price_segment AS (
    -- 给每个价格段打标记，同一价格有效期内的所有记录属于同一个分组
    SELECT
        *,
        COUNT(price) OVER (PARTITION BY id ORDER BY date ASC) AS seg_id
    FROM joined_price
)
-- 同一个价格分组内的所有日期，价格取该组的有效价格值
SELECT
    date,
    id,
    MAX(price) OVER (PARTITION BY id, seg_id) AS price
FROM price_segment
ORDER BY id, date;

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