javascript - BigQuery 对两列使用 Group By 函数，顺序并不重要

Question

我正在尝试对此表进行分组，使其如下所示。

之前

 Student  Fruit 1  Fruit 2
 Tom      Apple    Banana
 Tom      Banana   Apple
 Gary     Apple    Banana

之后

 Student  Fruit 1  Fruit 2  Count  Repeated Condition 
 Tom      Apple    Banana   2      True
 Gary     Apple    Banana   1      False

有人知道如何在 Google BigQuery 上轻松实现这一点吗？顺序并不重要。我尝试过 String_AGG 和 Concat 函数，但顺序很重要。

尝试创建一个函数来尝试创建第二个表。

Answer 1

您可以使用LEAST和 GREATEST将两列中的水果按字母顺序排序，然后对这些排序值进行分组:

SELECT Student,
       LEAST(Fruit1, Fruit2) AS Fruit1,
       GREATEST(Fruit1, Fruit2) AS Fruit2,
       COUNT(*) AS Count,
       CASE WHEN COUNT(*) > 1 THEN 'True' ELSE 'False' END AS "Repeated Condition"
FROM fruits
GROUP BY Student, LEAST(Fruit1, Fruit2), GREATEST(Fruit1, Fruit2)

输出:

student     fruit1  fruit2  count   Repeated Condition
Tom         Apple   Banana  2       True
Gary        Apple   Banana  1       False