BigQuery生成连续日期数组并用前值填充空缺的SQL查询问题求助

问题核心原因 #

你之前的查询使用CROSS JOIN会生成连续日期和所有日志行的笛卡尔积，导致每个日期重复匹配所有历史价格，同时窗口函数方向设置错误，没有向前查找最近的有效值。

修正后的查询语句（适配BigQuery语法） #

WITH log AS (
    -- 先过滤指定房源，同时对同天多条日志去重，取当天最新的价格
    SELECT 
        DATE(datetime_from) AS log_date, 
        listing_id, 
        price_cents_usd
    FROM `data_marts.pg_listings_log`
    WHERE listing_id = 769345 -- 提前过滤减少后续计算量
    QUALIFY ROW_NUMBER() OVER(PARTITION BY listing_id, DATE(datetime_from) ORDER BY datetime_from DESC) = 1
),
continuous_days AS (
    -- 生成2020-01-01到当前的连续日期序列
    SELECT date
    FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE(), INTERVAL 1 DAY)) date
)
SELECT 
    d.date,
    769345 AS listing_id,
    -- 向前查找最近的非空价格，若2020-01-01到第一条日志期间无值，默认填充1，可按需修改
    IFNULL(
        LAST_VALUE(l.price_cents_usd IGNORE NULLS) OVER(ORDER BY d.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        1
    ) AS val
FROM continuous_days d
-- 左连接仅匹配日期完全一致的日志行，无匹配则价格为null
LEFT JOIN log l ON d.date = l.log_date
ORDER BY d.date DESC

关键改动说明 #

1. 提前在日志表过滤目标房源，避免后续无效计算，同时新增同天日志去重逻辑，避免同一天多个价格导致结果异常
2. 把CROSS JOIN改为LEFT JOIN，仅匹配日期完全相同的日志行，从根源避免笛卡尔积带来的重复行
3. 窗口函数改用LAST_VALUE配合IGNORE NULLS，窗口范围设置为从最早行到当前行，实现向前查找最近的非空价格的逻辑
4. 移除多余的DISTINCT，左连接后每个日期天然只有一行结果

如果需要适配多个房源批量计算，只需把日志表中过滤listing_id的条件去掉，连续日期和所有去重后的listing_id做笛卡尔积后再左连接，窗口函数按listing_id分区即可。

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