从BigQuery中的表中删除重复的ID

Question

我在BigQuery中有一个具有以下结构的表：

CREATE TABLE if not EXISTS tbl (
id_1 int,
id_2 int,
value int
);
INSERT INTO tbl values
(1, 1, 0),
(1, 1, 0.1),
(1, 2, 0),
(1, 2, 0),
(2, 1, 0),
(2, 2, 0.1),
(2, 2, 0.5),
(2, 2, 0.9);

我想使用以下逻辑删除重复的行：

• 我希望id_1、id_2对只具有1行
• 我想保留具有最高值的行

在以下条件下：

• 可能会有平局，但我只想保留其中的一场
• 对于给定的id对，该id对可能有1、2或更多行

在这种特定情况下，输出表应该是：

id_1,id_2,value
1, 1, 0.1
1, 2, 0
2, 1, 0
2, 2, 0.9

在BigQuery中执行delete的最简单方法是什么？

Answer 1

使用以下方法

create or replace table `project.dataset.table` as 
select array_agg(t order by value desc limit 1)[offset(0)].*
from `project.dataset.table` t
group by t.id_1, t.id_2;

Answer 2

如果您想要返回结果的查询，请使用：

select t.*
from t
where 1=1
qualify row_number() over (partition by id_1, id_2 order by value desc);

不幸的是，如果要删除表中的行，则完全重复会导致问题。相反，我建议您将上述查询的结果保存到一个表中。