SQL ARRAY_AGG

В любом типичном языке программирования, таком как Python или Javascript, массивы обычно являются неотъемлемой частью и широко используются; когда вы обрабатываете данные в SQL, массивы встречаются немного реже, но являются удобным способом придать вашим данным больше структуры.

Чтобы создать массив из нескольких значений данных в SQL, вы, вероятно, воспользуетесь функцией ARRAY_AGG (сокращение от агрегация массива), которая помещает значения вашего входного столбца в массив.

Как использовать SQL ARRAY_AGG

Функция ARRAY_AGG имеет следующий синтаксис:

array_agg( [distinct] <field_name>) [within group (<order_by field>) over ([partition by <field>])

Несколько замечаний о функциональности этой функции:

• Большинство примеров синтаксиса выше являются необязательными, что означает, что функция ARRAY_AGG может быть такой простой, как array_agg(<field_name>), или использоваться как более сложная оконная функция
• DISTINCT — это необязательный аргумент, который можно передать, чтобы в возвращаемом массиве были только уникальные значения
• Если входной столбец пуст, возвращаемый массив также будет пустым
• Поскольку ARRAY_AGG является агрегатной функцией (о, ужас!), вам понадобится оператор GROUP BY в конце вашего запроса, если вы группируете по определенному полю
• ARRAY_AGG и аналогичные агрегатные функции могут стать неэффективными или дорогостоящими для вычисления на больших наборах данных, поэтому используйте ARRAY_AGG с умом и действительно понимайте ваши случаи использования массивов в ваших наборах данных

Давайте углубимся в практический пример использования функции ARRAY_AGG.

Пример SQL ARRAY_AGG

select
    date_trunc('month', order_date) as order_month,
    array_agg(distinct status) as status_array
from  {{ ref('orders') }}
group by 1
order by 1

Этот простой запрос, использующий пример набора данных таблицы orders из Jaffle Shop, возвращает новый столбец с уникальными статусами заказов по месяцам:

order_month	status_array
2018-01-01	[ "returned", "completed", "return_pending" ]
2018-02-01	[ "completed", "return_pending" ]
2018-03-01	[ "completed", "shipped", "placed" ]
2018-04-01	[ "placed" ]

Смотрим на результаты запроса — это имеет смысл! Мы ожидаем, что у новых заказов, вероятно, не будет возвратов, а у старых заказов возвраты будут завершены.

Синтаксис SQL ARRAY_AGG в Snowflake, Databricks, BigQuery и Redshift

Snowflake, Databricks и BigQuery поддерживают функцию ARRAY_AGG. Однако Redshift поддерживает встроенную функцию LISTAGG, которая может выполнять аналогичную функцию ARRAY_AGG. Основное отличие заключается в том, что LISTAGG позволяет явно выбрать разделитель для разделения списка, тогда как массивы по умолчанию разделяются запятыми.

Примеры использования ARRAY_AGG

Существует слишком много примеров использования функции ARRAY_AGG в ваших моделях dbt, чтобы перечислить их все, но очень вероятно, что ARRAY_AGG используется на более поздних этапах в вашем , так как вы, вероятно, не хотите, чтобы ваши данные были так сильно упакованы на более ранних этапах DAG для улучшения модульности и сухости. Несколько примеров использования ARRAY_AGG на более поздних этапах:

• В export_ моделях, которые используются для отправки данных на платформы с использованием инструмента , чтобы объединить несколько строк в одну строку. Некоторые платформы, например, требуют, чтобы определенные значения, которые мы обычно храним как отдельные строки, были одной строкой на клиента или пользователя. ARRAY_AGG удобен для объединения нескольких значений столбцов по одному идентификатору, например, создания массива всех товаров, которые пользователь когда-либо покупал, и отправки этого массива на платформу электронной почты для создания индивидуальной кампании.
• Аналогично экспортным моделям, вы можете увидеть использование ARRAY_AGG в mart таблицах для создания окончательных агрегированных массивов по одной измерению; проблемы с производительностью ARRAY_AGG в этих, вероятно, больших таблицах могут быть обойдены с использованием инкрементальных моделей в dbt.