为BigQuery中各ID补全每日记录并延续状态值至CURRENT_DATE()

原表数据 #

期望输出 #

实现SQL #

WITH date_range AS (
    -- 生成从所有ID最早日期到当前日期的每日序列
    SELECT date
    FROM UNNEST(GENERATE_DATE_ARRAY(
        (SELECT MIN(Date) FROM `your-project.your-dataset.your-table`),
        CURRENT_DATE(),
        INTERVAL 1 DAY
    )) AS date
),
distinct_ids AS (
    -- 获取所有唯一ID及对应的姓名信息
    SELECT DISTINCT ID, First_name, Last_name
    FROM `your-project.your-dataset.your-table`
),
all_dates_per_id AS (
    -- 关联ID和日期序列，得到每个ID的完整日期范围
    SELECT di.ID, di.First_name, di.Last_name, dr.date
    FROM distinct_ids di
    CROSS JOIN date_range dr
),
original_data_with_gaps AS (
    -- 关联原表数据，标记有值的记录
    SELECT adpi.ID, adpi.First_name, adpi.Last_name, adpi.date, t.Value
    FROM all_dates_per_id adpi
    LEFT JOIN `your-project.your-dataset.your-table` t
        ON adpi.ID = t.ID AND adpi.date = t.Date
)
-- 使用LAST_VALUE填充缺失的Value，忽略NULL值
SELECT 
    ID,
    First_name,
    Last_name,
    date AS Date,
    LAST_VALUE(Value IGNORE NULLS) OVER (
        PARTITION BY ID 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Value
FROM original_data_with_gaps
ORDER BY ID, date;

代码说明 #

• date_range：生成从表中最早日期到当前日期的每日日期序列，确保覆盖所有需要补全的日期。
• distinct_ids：提取所有唯一ID及其对应的姓名信息，避免重复关联。
• all_dates_per_id：通过交叉连接，为每个ID生成完整的日期范围，补全缺失的日期行。
• original_data_with_gaps：左连接原表，保留所有日期行，原表中无数据的行Value为NULL。
• LAST_VALUE窗口函数：按ID分组、日期排序，向前取最近的非NULL值填充当前行的Value，实现状态沿用。

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