在BigQuery中排除单列去重，能否不使用row_number()实现？

当然可以不用row_number()实现你的需求，以下是几种实用方案，针对30列宽表中基于除a外29列去重、保留每组a值最小行的场景：

方案一：GROUP BY + 聚合函数 #

核心逻辑是把判定重复的列（除a外的29列）作为分组键，每组内取最小的a值，其他列因分组后本身一致，直接保留即可。

with
  t1 as (
    select 1 as a, 2 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 2 as a, 3 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 3 as a, 4 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 4 as a, 5 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 5 as a, 6 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 6 as a, 2 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i
  )
SELECT 
  MIN(a) AS a,
  b, c, d, e, f, g, h, i
FROM t1
GROUP BY b, c, d, e, f, g, h, i;

这个方案逻辑直观，大数据量下性能稳定，是BigQuery优化较好的操作类型。

方案二：DISTINCT ON（BigQuery标准SQL支持） #

DISTINCT ON会按指定列分组，保留每组中ORDER BY排序后的第一行。这里按去重列分组，再按a升序排列，就能自动保留每组a值最小的行。

with
  t1 as (
    select 1 as a, 2 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 2 as a, 3 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 3 as a, 4 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 4 as a, 5 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 5 as a, 6 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 6 as a, 2 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i
  )
SELECT DISTINCT ON (b, c, d, e, f, g, h, i)
  *
FROM t1
ORDER BY b, c, d, e, f, g, h, i, a ASC;

注意：DISTINCT ON后的列必须和ORDER BY的前几列完全匹配，这个方案代码更紧凑，适合需要保留所有列的场景。

方案三：EXISTS子查询 #

通过子查询找到每组（按去重列）的最小a值，再筛选原表中a等于该最小值的行。

with
  t1 as (
    select 1 as a, 2 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 2 as a, 3 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 3 as a, 4 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 4 as a, 5 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 5 as a, 6 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i union all
    select 6 as a, 2 as b, 3 as c, 4 as d, 5 as e, 6 as f, 7 as g, 8 as h, 9 as i
  )
SELECT t.*
FROM t1 t
WHERE a = (
  SELECT MIN(a)
  FROM t1
  WHERE b = t.b 
    AND c = t.c 
    AND d = t.d 
    AND e = t.e 
    AND f = t.f 
    AND g = t.g 
    AND h = t.h 
    AND i = t.i
);

这个方案适合不熟悉GROUP BY或DISTINCT ON的场景，但列数较多时，子查询里的匹配条件会比较冗长。

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