sql - 如何在 SQL 中使用 Mode 删除重复值

Question

我正在使用 GCP BigQuery 和我的表。

我想删除 Col1 中的重复值并在 Col2 中获取模式（最出现的值）。

【源表】

Col1	Col2
1	BB
2	BB
3	抄送
1	AA
1	AA
1	DD
2	BB
2	EE

[预期结果]

Col1	Col2
1	AA
2	BB
3	抄送

我可以只使用一个查询得到预期的结果吗？或者我需要什么？（临时表，程序或类似的东西）

标签： sqlgoogle-bigquery

Answer 1

一种方法使用两个级别的聚合：

select col1,
       (array_agg(col2 order by cnt desc limit 1))[ordinal(1)] as mode
from (select col1, col2, count(*) as cnt
      from t
      group by col1, col2
     ) cc
group by col1;

上面使用了 BigQuery 特有的数组，另一种方法使用窗口函数和条件聚合：

select col1,
       max(case when seqnum = 1 then col2 end) as mode
from (select col1, col2, count(*) as cnt,
             row_number() over (partition by col1 order by count(*) desc) as seqnum
      from t
      group by col1, col2
     ) cc
group by col1;