unique - bigquery - 仅过滤掉唯一结果

Question

我的数据库如下所示:

Entry-Key     Name     Surname     Age
10a           Smith    Alex        35
11b           Finn     John        41
10a           Smith    Al          35
10c           Finn     Berta       28
11b           Fin      John        41

我需要从中获取唯一的行。 Group by 无法正常工作，因为有时 Name/Surname 列中会出现错误。

我想只按输入键分组，然后在表中找到第一次出现的键并只取这一行。我知道如何在 Excel 中执行此操作，但由于数据库有大约 100,000 行，Excel 不是一个真正的选择。

想法是最终得到这张表:

10a           Smith    Alex        35
11b           Finn     John        41
12c           Finn     Berta       28

求助!

Answer 1

对于您的逻辑，您可以执行以下查询:

select key, first(name), first(surname), first(age) from 
(select '10a' as key,           'Smith' as name,    'Alex' as surname,        35 as age),
(select '11b' as key,           'Finn' as name,     'John' as surname,        41 as age),
(select '10a' as key,           'Smith' as name,    'Al' as surname,          35 as age),
(select '10c' as key,           'Finn' as name,     'Berta' as surname,       28 as age),
(select '11b' as key,           'Fin' as name,      'John' as surname,        41 as age),
group by key

返回:

+-----+-----+-------+-------+-----+---+
| Row | key |  f0_  |  f1_  | f2_ |   |
+-----+-----+-------+-------+-----+---+
|   1 | 10a | Smith | Alex  |  35 |   |
|   2 | 11b | Finn  | John  |  41 |   |
|   3 | 10c | Finn  | Berta |  28 |   |
+-----+-----+-------+-------+-----+---+