sql - 基于两列的 GCP Big Query (SQL) 中的重复行

Question

我正在尝试输出所有列，同时对某些行进行重复数据删除。我尝试过的一切似乎都没有接近。

SELECT * FROM `project.dataset.table`
??

• 基于名称去重
• 删除重复 Column1 时要保留的所选行使用 LastUpdateDate 中的最新日期
• 要保留的行将始终是最后一行（在 Name 处具有重复值），因为它稍后会附加到表中

标签： sqlgoogle-bigquery

Answer 1

考虑使用ARRAY_AGG：

with TestData as (
  select 'Tom' as Name, '1' as Phone, timestamp('2020-01-01 00:00:00') as LastUpdateDate 
  union all
  select 'Tom' as Name, '2' as Phone, timestamp('2020-01-02 00:00:00') as LastUpdateDate
  union all
  select 'Eva' as Name, '3' as Phone, timestamp('2020-01-03 00:00:00') as LastUpdateDate
  union all
  select 'Eva' as Name, '4' as Phone, timestamp('2020-01-04 00:00:00') as LastUpdateDate
)
SELECT deduplicated.* FROM (
  SELECT ARRAY_AGG(t ORDER BY t.LastUpdateDate DESC LIMIT 1)[OFFSET(0)] as deduplicated
  FROM TestData as t 
  GROUP BY Name
)