基于未来变更为BigQuery表中每个key_1填充首个有效key_2值

我有一张包含date、key_1、key_2字段的BigQuery表，每个key_1对应的key_2在未分配有效取值前均为-1。需要实现：获取每个key_1的首个非空且非-1的key_2值，将其填充到该值变更前的所有日期行中；后续key_2发生变更时，从变更日期起保留新值。

举个例子：3月1日至3日key_2为-1，3月4日变为XYZ123，则3月1-3日的key_2需填充为XYZ123；3月7日key_2变为PGQ123，则从3月7日起保留该值。

输入数据 #

期望输出数据 #

解决方案 #

通过窗口函数结合条件判断可实现需求，核心思路是先标记每个有效key_2的起始区间，再向前填充该区间内的所有-1值。

SQL 代码 #

WITH labeled_data AS (
  SELECT
    date,
    key_1,
    key_2,
    -- 标记每个有效key_2的分组：当key_2不为-1时，生成新的分组ID
    SUM(CASE WHEN key_2 != '-1' THEN 1 ELSE 0 END) OVER (PARTITION BY key_1 ORDER BY date) AS group_id
  FROM
    `your_project.your_dataset.your_table`
),
grouped_values AS (
  SELECT
    key_1,
    group_id,
    -- 提取每个分组的有效key_2值
    MAX(CASE WHEN key_2 != '-1' THEN key_2 END) AS valid_key_2
  FROM
    labeled_data
  GROUP BY
    key_1, group_id
)
SELECT
  ld.date,
  ld.key_1,
  gv.valid_key_2 AS key_2
FROM
  labeled_data ld
JOIN
  grouped_values gv
ON
  ld.key_1 = gv.key_1 AND ld.group_id = gv.group_id
ORDER BY
  ld.key_1, ld.date;

代码说明 #

1. labeled_data CTE：通过窗口函数SUM() OVER (...)为每个key_1下的行分组。每当遇到非-1的key_2时，分组ID加1，确保第一个有效key_2之前的所有-1行与该有效key_2的连续行同属一个分组。
2. grouped_values CTE：按key_1和group_id分组，提取每个组的有效key_2值（即组内的非-1值）。
3. 最终查询：关联标记表与分组值表，用有效key_2替换原表中的-1值，得到符合要求的结果。

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