BigQuery SQL：按country_code、rank分组将market_offers重复值置NULL

核心改造思路 #

在原有逻辑基础上，给country_code、rank分区内的行按业务规则打行号，通过判断行号是否为1决定保留原值还是置NULL即可，所有现有字段的计算逻辑完全无需改动。

具体改造代码 #

匹配需求的原有SQL参考 #

SELECT
  country_code,
  rank,
  store_id,
  SUM(offers) AS store_offers, -- 按country_code、rank、store_id等维度分组统计的offers
  SUM(SUM(offers)) OVER (PARTITION BY country_code, rank) AS market_offers -- 原重复取值的market_offers
FROM `bigquery-kpi-engine.cba.price_position`
GROUP BY country_code, rank, store_id

改造后简洁版SQL（无需子查询） #

SELECT
  country_code,
  rank,
  store_id,
  SUM(offers) AS store_offers,
  -- 改造后的market_offers逻辑
  CASE
    -- 分区内行号为1则保留原market_offers值，否则置NULL
    WHEN ROW_NUMBER() OVER (
      PARTITION BY country_code, rank 
      ORDER BY store_id ASC -- 【关键】按业务规则定义分组内的"第一条"，可替换为其他排序字段/规则
    ) = 1 THEN SUM(SUM(offers)) OVER (PARTITION BY country_code, rank)
    ELSE NULL
  END AS market_offers
FROM `bigquery-kpi-engine.cba.price_position`
GROUP BY country_code, rank, store_id

改造后易读版SQL（子查询拆分逻辑） #

如果原SQL逻辑更复杂，推荐用子查询拆分逻辑，避免嵌套窗口函数难以维护：

SELECT
  country_code,
  rank,
  store_id,
  store_offers,
  CASE WHEN rn = 1 THEN market_offers ELSE NULL END AS market_offers
FROM (
  SELECT
    country_code,
    rank,
    store_id,
    SUM(offers) AS store_offers,
    SUM(SUM(offers)) OVER (PARTITION BY country_code, rank) AS market_offers,
    ROW_NUMBER() OVER (
      PARTITION BY country_code, rank 
      ORDER BY store_id ASC -- 同上述，可调整排序规则
    ) AS rn
  FROM `bigquery-kpi-engine.cba.price_position`
  GROUP BY country_code, rank, store_id
)

效果验证示例 #

原有输出（同分区market_offers重复） #

预期输出（仅第一条保留market_offers原值） #

注意事项 #

• ROW_NUMBER()的PARTITION BY字段必须和原market_offers的分区完全一致，不能增减字段，否则会导致分组逻辑错误。
• ORDER BY后的排序规则是定义分组内"第一条"的核心，必须根据业务需求调整（比如按store_id DESC、按store_offers DESC等），BigQuery中如果省略ORDER BY，行号生成是随机的，会导致每次运行结果不一致。
• 所有其他字段的计算逻辑完全没有改动，符合"其余字段保持不变"的需求。

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