SQL GROUP BY
GROUP BY... это немного сложно определить так, чтобы это действительно имело смысл, но он неизбежно будет появляться бесчисленное количество раз в аналитической работе, и вам он понадобится часто.
Проще говоря, оператор GROUP BY позволяет группировать результаты запроса по указанным столбцам и используется вместе с агрегатными функциями, такими как AVG и SUM, для вычисления этих значений по определённым строкам.
Как использовать оператор SQL GROUP BY
Оператор GROUP BY появляется в конце запроса, после применения любых соединений и фильтров WHERE:
select
my_first_field,
count(id) as cnt --или любая другая агрегатная функция (sum, avg и т.д.)
from my_table
where my_first_field is not null
group by 1 --группировка по my_first_field
order by 1 desc
Несколько моментов, которые стоит отметить о реализации GROUP BY:
- Обычно он указывается как одна из последних строк в запросе, после любых соединений или операторов where; обычно после него в запросе вы увидите только операторы HAVING, ORDER BY или LIMIT
- Вы можете группировать по нескольким полям (например,
group by 1,2,3), если это необходимо; в общем, мы рекомендуем выполнять агрегации и соединения в отдельных CTE, чтобы избежать необходимости группировки по слишком многим полям в одном запросе или CTE - Вы также можете группировать по явному имени столбца (например,
group by my_first_field) или даже по изменённому имени столбца, который есть в запросе (например,group by date_trunc('month', order_date))
Группировка по явному имени столбца (в отличие от номера столбца в запросе) может иметь две стороны: с одной стороны, это потенциально более читаемо для конечных бизнес-пользователей; с другой стороны, если имя сгруппированного столбца изменится, это изменение должно быть отражено в операторе group by. Используйте соглашение о группировке, которое работает для вас и ваших данных, но старайтесь придерживаться одного стандартного стиля.
Пример SQL GROUP BY
select
customer_id,
count(order_id) as num_orders
from {{ ref('orders') }}
group by 1
order by 1
limit 5
Этот простой запрос, использующий пример набора данных таблицы order из Jaffle Shop, вернёт клиентов и количество заказов, которые они сделали:
| customer_id | num_orders |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 3 |
| 6 | 1 |
| 7 | 1 |
Обратите внимание, что операторы order by и limit находятся после group by в запросе.
Синтаксис SQL GROUP BY в Snowflake, Databricks, BigQuery и Redshift
Snowflake, Databricks, BigQuery и Redshift поддерживают возможность группировки по столбцам и следуют одному и тому же синтаксису.
Примеры использования GROUP BY
Агрегаты, агрегаты, и, упоминали ли мы, агрегаты? Операторы GROUP BY необходимы, когда вы вычисляете агрегаты (средние значения, суммы, подсчёты и т.д.) по определённым столбцам; ваш запрос не будет успешно выполнен без них, если вы пытаетесь использовать агрегатные функции в вашем запросе. Вы также можете увидеть операторы GROUP BY, используемые для удаления дубликатов строк или присоединения агрегатов к другим таблицам с помощью CTE; эта статья предоставляет отличное описание конкретных областей, где вы можете увидеть использование GROUP BY в ваших проектах dbt и работе с моделированием данных.
В некоторых сложных сценариях моделирования данных вы можете обнаружить, что вам нужно группировать по многим столбцам, чтобы сократить таблицу до меньшего количества строк или удалить дубликаты строк. В этом случае вы можете написать group by 1, 2, 3,.....,n, что может стать утомительным, запутанным и трудным для устранения неполадок. Вместо этого вы можете использовать макрос dbt, который избавит вас от написания group by 1,2,....,46, заменив его на простой {{ dbt_utils.group_by(46) }}...позже вы нас поблагодарите 😉