如何在BigQuery中处理重复行：仅保留首行重复字段值

针对你需要的同一ID+People分组下，仅保留每组第一行的Total Price、Total Revenue、Percentage，其余行对应字段置空的需求，推荐使用**窗口函数ROW_NUMBER()**实现，避开string_agg处理空值时的报错问题。

实现代码 #

WITH original_data AS (
  SELECT 2345 AS ID, 'Anna' AS People, 'Eyeliner' AS Product, 200 AS Total_Price, 1500 AS Total_Revenue, '30%' AS Percentage UNION ALL
  SELECT 2345, 'Anna', 'Pencil eyeliner', 200, 1500, '30%' UNION ALL
  SELECT 2345, 'Anna', 'Liquid eyeliner', 200, 1500, '30%' UNION ALL
  SELECT 2345, 'Anna', 'Pen eyeliner', 200, 1500, '30%' UNION ALL
  SELECT 2345, 'Anna', 'Gel eyeliner', 200, 1500, '30%' UNION ALL
  SELECT 5423, 'Arnold', 'Lipstick', 300, 2000, '35%' UNION ALL
  SELECT 5423, 'Arnold', 'Lipgloss', 300, 2000, '35%' UNION ALL
  SELECT 5423, 'Arnold', 'Liptint', 300, 2000, '35%'
)
SELECT
  ID,
  People,
  Product,
  CASE WHEN row_num = 1 THEN Total_Price ELSE NULL END AS Total_Price,
  CASE WHEN row_num = 1 THEN Total_Revenue ELSE NULL END AS Total_Revenue,
  CASE WHEN row_num = 1 THEN Percentage ELSE NULL END AS Percentage
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY ID, People ORDER BY Product) AS row_num
  FROM original_data
)
ORDER BY ID, People, row_num;

代码说明 #

1. 窗口函数分组排序：ROW_NUMBER() OVER(PARTITION BY ID, People ORDER BY Product) 会为每个ID+People分组内的行按Product排序并分配序号，每组第一行序号为1。
2. 条件判断置空：通过CASE语句判断行序号，仅保留序号为1的行的目标字段值，其余行对应字段设为NULL（BigQuery中显示为空）。
3. 兼容空值场景：该方法直接处理原始字段值，无需聚合操作，完全兼容Total Revenue存在空值的情况，不会出现string_agg的报错问题。

执行结果 #

注：如果需要严格匹配你示例中的行顺序（比如Anna组首行是Eyeliner，后续按原顺序），可以调整ORDER BY的字段——如果有原始数据的排序依据（如插入时间戳），替换Product为对应字段即可；若无特殊排序需求，按Product排序的结果已满足核心需求。

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