BigQuery如何生成日期数组并按客户维度向前填充缺失数据

问题核心原因 #

你之前的逻辑是先生成周维度数组，再左关联全量的用户分数数据，这就导致没有分数记录的周对应行的customer_id为空，无法按客户维度分区做向前填充。正确的做法是先生成每个客户对应目标区间所有周的全集，再关联原始分数数据，保证所有行都有确定的customer_id。

修正后的查询语句 #

WITH 
-- 生成目标区间的所有周
weeks AS (
    SELECT week
    FROM UNNEST(GENERATE_DATE_ARRAY('2019-10-20', '2019-11-10', INTERVAL 1 WEEK)) AS week
),
-- 提取所有唯一的客户ID
unique_customers AS (
    SELECT DISTINCT customer_id
    FROM score
),
-- 生成每个客户对应所有周的全集
customer_all_weeks AS (
    SELECT *
    FROM weeks
    CROSS JOIN unique_customers
),
-- 处理原始分数表，生成周维度字段
original_score AS (
    SELECT 
        *,
        DATE_TRUNC(EXTRACT(DATE FROM created_at), WEEK) AS week
    FROM score
)

SELECT 
    c.week,
    c.customer_id,
    -- 适配客户首次出现前保留空值的需求
    CASE 
        WHEN s.score IS NULL AND FIRST_VALUE(s.score IGNORE NULLS) OVER (PARTITION BY c.customer_id ORDER BY c.week) IS NULL 
        THEN NULL
        ELSE LAST_VALUE(s.score IGNORE NULLS) OVER (
            PARTITION BY c.customer_id 
            ORDER BY c.week
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) 
    END AS score
FROM customer_all_weeks c
LEFT JOIN original_score s
    ON c.week = s.week 
    AND c.customer_id = s.customer_id
ORDER BY c.customer_id, c.week

逻辑说明 #

• 用CROSS JOIN将唯一客户ID和周数组做笛卡尔积，确保每个客户在目标区间的每一周都有对应的行，不会出现customer_id为空的情况
• 左关联原始分数数据后，按customer_id分区做窗口计算，IGNORE NULLS参数会跳过空值，取当前行之前最近的非空分数填充，完全符合你需要的效果
• 额外加的CASE判断可以保留客户首次出现之前的行的空值，和你给出的预期输出完全匹配

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