将同一行组聚合为一行

Question

我有一张30栏的桌子。除了两个列(CropVariety，Date)外，同一行组中的所有值都是相同的。我希望将具有相同列值( CropVariety、Date)的行聚合组聚合到一行，将不同的列值叠加到一个单元格中。

Exapmple:

ID      Field      Year IDFarm Farm   Type         CropVariety  Date
1222980 MU-9 - EKO 2020 219255  MU    Cover Crop   Rice         2020-04-03
1222980 MU-9 - EKO 2020 219255  MU    Cover Crop   Soy          2020-04-07
3333333 AB-9 - EKO 2020 444444  AB    Cover Crop   Lentil       2020-05-03
3333333 AB-9 - EKO 2020 444444  AB    Cover Crop   Hemp         2020-05-07


Desired output:

ID      Field      Year IDFarm Farm   Type         CropVariety  Date
1222980 MU-9 - EKO 2020 219255  MU    Cover Crop   Rice, Soy    2020-04-03, 2020-04-07
3333333 AB-9 - EKO 2020 444444  AB    Cover Crop   Lentil, Hemp 2020-05-03, 2020-05-07

我想我应该使用ARRAY_CONCAT_AGG函数或ARRAY_AGG，但是我的公式总是错误的，所以我不知道如何使用is。

Answer 1

我有一个30列的表。

在SELECT list和GROUP BY子句中键入30 (甚至更多)列名总是很痛苦的，而且是排字的来源，等等。

除两个列(CropVariety、Date)外，同一行组中的所有值都是相同的。

显然，在查询中只使用这两个列名是很好的。

因此，下面是这类情况的通用解决方案(BigQuery标准SQL)

#standardSQL
SELECT ANY_VALUE(t).* EXCEPT(CropVariety, Date), 
  STRING_AGG(CropVariety, ', ') AS CropVariety, 
  STRING_AGG(CAST(Date AS STRING), ', ') AS Date
FROM `project.dataset.table` t
GROUP BY TO_JSON_STRING((SELECT AS STRUCT * EXCEPT(CropVariety, Date) FROM UNNEST([t])))

正如您在这里所看到的，只有CropVariety和Date列名被显式使用，其余的则由代码来处理。

如果要应用于问题输出中的样本数据，请参见

Row ID          Field           Year    CropVariety     Date     
1   1222980     MU-9 - EKO      2020    Rice, Soy       2020-04-03, 2020-04-07   
2   3333333     AB-9 - EKO      2020    Lentil, Hemp    2020-05-03, 2020-05-07  

Answer 2

更新:检查Mikhail的答案以获得指定批列的语法。

=======，您说的是ARRAY_AGG，但是您的预期结果看起来像一个字符串。您可以使用下面的查询(如果需要数组，可以将STRING_AGG()替换为ARRAY_AGG() )

WITH data AS (
    SELECT 1222980 ID, 'MU-9 - EKO' Field, 2020 Year, "Rice" CropVariety, DATE('2020-04-03') Date
    UNION ALL
    SELECT 1222980 ID, 'MU-9 - EKO' Field, 2020 Year, "Soy" CropVariety, DATE('2020-04-07') Date
    UNION ALL
    SELECT 3333333 ID, 'AB-9 - EKO' Field, 2020 Year, "Lentil" CropVariety, DATE('2020-05-03') Date
    UNION ALL
    SELECT 3333333 ID, 'AB-9 - EKO' Field, 2020 Year, "Hemp" CropVariety, DATE('2020-05-07') Date
)
SELECT ID, Field, Year, string_agg(CropVariety, ', '), string_agg(CAST(Date AS STRING), ', ')
FROM data
GROUP BY 1,2,3

输出：

+---------+------------+------+--------------+------------------------+
|   ID    |   Field    | Year |     f0_      |          f1_           |
+---------+------------+------+--------------+------------------------+
| 1222980 | MU-9 - EKO | 2020 | Rice, Soy    | 2020-04-03, 2020-04-07 |
| 3333333 | AB-9 - EKO | 2020 | Lentil, Hemp | 2020-05-03, 2020-05-07 |
+---------+------------+------+--------------+------------------------+