BigQuery中替换Type列0值为前序非零值的SQL实现求助

我们需要处理BigQuery中的数据表，按date列升序排序后，将所有Type=0的数值替换为该行之前最近的非零Type值。

原始数据 #

期望输出 #

实现SQL语句 #

1. 查询验证语句 #

先通过查询确认处理结果是否符合预期：

WITH ranked_data AS (
  SELECT
    行号,
    date,
    Type,
    -- 给每个非零Type分组，后续用分组填充0值
    SUM(CASE WHEN Type != 0 THEN 1 ELSE 0 END) OVER (ORDER BY date ASC) AS group_id
  FROM
    `your-project.your-dataset.your-table`
)
SELECT
  行号,
  date,
  -- 提取分组内第一个非零Type值
  FIRST_VALUE(Type) OVER (PARTITION BY group_id ORDER BY date ASC) AS Type
FROM
  ranked_data
ORDER BY
  date ASC;

2. 表更新语句 #

如果需要直接更新原表，可使用MERGE语句（操作前建议备份数据，确保表支持DML操作）：

MERGE INTO `your-project.your-dataset.your-table` AS target
USING (
  WITH ranked_data AS (
    SELECT
      行号,
      date,
      Type,
      SUM(CASE WHEN Type != 0 THEN 1 ELSE 0 END) OVER (ORDER BY date ASC) AS group_id
    FROM
      `your-project.your-dataset.your-table`
  ),
  processed_data AS (
    SELECT
      行号,
      FIRST_VALUE(Type) OVER (PARTITION BY group_id ORDER BY date ASC) AS new_Type
    FROM
      ranked_data
  )
  SELECT * FROM processed_data
) AS source
ON target.行号 = source.行号
WHEN MATCHED THEN
  UPDATE SET target.Type = source.new_Type;

核心逻辑说明 #

• 用SUM(CASE...) OVER (ORDER BY date)创建分组：每遇到非零Type，分组ID递增，连续的0值会和之前最近的非零Type归为同一分组。
• FIRST_VALUE(Type)提取分组内首个非零值，自动填充该分组内所有行的Type字段。
• 替换语句中的your-project.your-dataset.your-table为实际的项目、数据集和表名。

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