Блог

У меня есть таблица с 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_AGG или ARRAY_CONCAT_AGG, но мои формулы всегда были неправильными, поэтому я не знаю, как использовать is.

У меня есть таблица с 30 столбцами.

Ввод 30 (или во многих практических случаях даже больше) имен столбцов в списке ВЫБОРА и предложении GROUP BY — это всегда проблема и источник опечаток и т. Д.

Все значения, кроме двух столбцов (CropVariety, Date), в одних и тех же группах строк одинаковы.

Очевидно, было бы здорово использовать только эти два имени столбцов в запросе

Итак, ниже приведено общее решение для таких случаев (стандартный SQL BigQuery)

 #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  
  

Обновление: проверьте ответ Михаила на синтаксис для указания столбцов в пакете.

======= Вы сказали 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 |
 --------- ------------ ------ -------------- ------------------------