В этой статье мы рассмотрим, как составить выражение GROUP BY, как будет выглядеть ваш запрос и как с его помощью выполнять группировку.
Настройка базы данных
Прежде чем писать запросы, нам нужно настроить базу данных.
В этих примерах мы будем использовать PostgreSQL, но приведенные здесь запросы и концепции легко применимы к любой другой современной системе баз данных (например, MySQL, SQL Server и т. д.).
Для работы с базой данных PostgreSQL мы можем использовать psql - интерактивную программу командной строки PostgreSQL. Если у вас есть другой клиент базы данных, с которым вам нравится работать, это тоже хорошо.
Для начала давайте создадим нашу базу данных. Если PostgreSQL уже установлен, мы можем выполнить команду createdb <database-name> в терминале, чтобы создать новую базу данных. Я назвал свою базу данных fcc:
|
Далее запустим интерактивную консоль с помощью команды psql и подключимся к базе данных, которую мы только что создали, используя \c <database-name>:
|
Примечание: Я очистил вывод psql в этих примерах, чтобы его было легче читать, поэтому не беспокойтесь, если вывод, показанный здесь, не совсем такой, как в вашем терминале.
Я рекомендую вам последовать за этими примерами и выполнить эти запросы самостоятельно. Вы узнаете и запомните гораздо больше, если будете работать с этими примерами, а не просто читать их.
Настройка данных (создание продаж)
В наших примерах мы будем использовать таблицу, в которой хранятся данные о продажах различных товаров в разных магазинах.
Мы назовем эту таблицу sales, и она будет представлять собой простое представление продаж в магазине: название места, название продукта, цена и время, когда он был продан.
Если бы мы создавали эту таблицу в реальном приложении, мы бы установили внешние ключи к другим таблицам (например, местоположениям или продуктам). Но для иллюстрации концепции GROUP BY мы будем использовать простые столбцы TEXT.
Давайте создадим таблицу и вставим в нее данные о продажах:
|
У нас есть три места расположения: HQ, Downtown и 1st Street.
У нас есть два продукта, Coffee и Bagel, и мы вставляем эти продажи с разными значениями sold_at, чтобы представить товары, проданные в разные дни и время.
Есть продажи сегодня, есть вчера, а есть позавчера.
Как работает GROUP BY?
Чтобы проиллюстрировать, как работает предложение GROUP BY, давайте сначала рассмотрим пример.
Представьте, что у нас есть комната, полная людей, родившихся в разных странах.
Если бы мы хотели найти средний рост людей в комнате по странам, мы бы сначала попросили этих людей разделиться на группы по странам рождения.
После того как они будут разделены на группы, мы сможем вычислить средний рост в этой группе.
Вот как работает предложение GROUP BY. Сначала мы определяем, как мы хотим сгруппировать строки, а затем мы можем выполнить вычисления или агрегирование для групп.
Множественные группы
Мы можем разделить данные на столько групп или подгрупп, сколько захотим.
Например, попросив людей разделиться на группы по странам рождения, мы можем попросить каждую из этих групп разделиться на группы по цвету глаз.
Таким образом, мы получим группы людей, основанные на сочетании их страны рождения и цвета глаз.
Теперь мы можем найти средний рост в каждой из этих небольших групп, и получим более конкретный результат: средний рост по стране и цвету глаз.
Предложения GROUP BY часто используются в ситуациях, когда можно использовать фразу per something или for each something:
- Средний рост по стране рождения
- Общее количество людей для каждой комбинации цвета глаз и волос
- Общее количество продаж по каждому продукту
Написание предложений GROUP BY
Предложение GROUP BY очень просто написать - мы просто используем ключевые слова GROUP BY, а затем указываем поле (поля), по которому хотим сгруппировать данные:
|
Этот простой запрос группирует наши данные о продажах по столбцу местоположения.
Мы выполнили группировку, но что мы должны вставить в SELECT?
Очевидно, что нужно выбрать местоположение - мы группируем по нему, поэтому хотим видеть хотя бы название групп, которые мы создали:
|
В результате у нас появилось три места:
|
Если мы посмотрим на данные нашей исходной таблицы (SELECT * FROM sales;), то увидим, что у нас есть четыре строки с местоположением HQ, две строки с местоположением Downtown и две строки с местоположением 1st Street:
|
Группируя по столбцу «местоположение», наша база данных берет эти строки и определяет среди них уникальные местоположения - эти уникальные местоположения и служат нашими «группами».
Но как быть с другими столбцами в нашей таблице?
Если мы попытаемся выбрать такой столбец, как товар, который мы не группировали по...
|
...мы столкнулись с этой ошибкой:
|
Проблема в том, что мы взяли восемь строк и сжали или сократили их до трех.
Мы не можем просто вернуть остальные столбцы, как обычно - у нас было восемь строк, а теперь осталось три.
Что делать с оставшимися пятью строками данных? Какие данные из восьми строк должны отображаться в этих трех разных строках расположения?
Здесь нет четкого и однозначного ответа.
Чтобы использовать оставшиеся данные таблицы, нам также нужно свести данные из этих оставшихся столбцов к трем группам местоположений.
Это означает, что нам нужно произвести агрегирование или вычисления, чтобы получить какую-то сводную информацию об оставшихся данных.
Агрегаторы (COUNT, SUM, AVG)
После того как мы решили, как сгруппировать данные, мы можем выполнить агрегацию оставшихся столбцов.
Это такие действия, как подсчет количества строк в группе, суммирование определенного значения по группе или усреднение информации в группе.
Для начала давайте найдем количество продаж на одно место.
Поскольку каждая запись в нашей таблице продаж - это одна продажа, количество продаж в каждой локации будет равно количеству строк в каждой группе локаций.
Для этого мы воспользуемся агрегатной функцией COUNT(), чтобы подсчитать количество строк в каждой группе:
|
Мы используем COUNT(*), который подсчитывает все входные строки для группы.
(COUNT() также работает с выражениями, но у него немного другое поведение).
Вот как база данных выполняет этот запрос:
FROM sales — сначала извлекаем все записи из таблицы sales.
GROUP BY location — далее определяем уникальные группы местоположений
SELECT ... — наконец, выберите название местоположения и подсчет количества строк в этой группе.
Мы также дадим этому количеству строк псевдоним AS number_of_sales, чтобы сделать вывод более читабельным. Это выглядит следующим образом:
|
Здесь мы видим, как мы взяли оставшиеся данные столбцов из наших восьми независимых строк и преобразовали их в полезную итоговую информацию для каждого местоположения: количество продаж.
SUM
Аналогичным образом, вместо подсчета количества строк в группе, мы можем суммировать информацию внутри группы - например, общее количество денег, заработанных в этих местах.
Для этого мы воспользуемся функцией SUM():
|
Вместо подсчета количества строк в каждой группе мы суммируем долларовую сумму каждой продажи, и это показывает нам общий доход для каждого местоположения:
|
Среднее значение (AVG)
Найти среднюю цену продажи для каждого места можно, просто заменив функцию SUM() на функцию AVG():
|
Работа с несколькими группами
До сих пор мы работали только с одной группой: местоположение.
А что, если мы захотим разделить эту группу еще больше?
По аналогии со сценарием «страны рождения и цвет глаз», с которого мы начали, что если мы захотим найти количество продаж одного товара в зависимости от местоположения?
Для этого нам нужно всего лишь добавить второе условие группировки в оператор GROUP BY:
|
Добавив второй столбец в нашу GROUP BY, мы еще больше разделим наши группы местоположений на группы местоположений по каждому продукту.
Поскольку теперь мы группируем и по столбцу продукта, мы можем вернуть его в наш SELECT!
(Я собираюсь добавить несколько предложений ORDER BY к этим запросам, чтобы сделать вывод более удобным для чтения).
|
Глядя на результат нашей новой группировки, мы видим уникальные сочетания местоположения и продукта:
|
Теперь, когда у нас есть группы, что мы хотим сделать с остальными данными столбцов?
Ну, мы можем найти количество продаж одного продукта в каждом месте, используя те же агрегатные функции, что и раньше:
|
|
Использование функций в GROUP BY
Далее попробуем найти общее количество продаж за день.
Если мы будем действовать по той же схеме, что и в случае с местоположениями, и сгруппируемся по столбцу sold_at.
|
Мы могли бы ожидать, что каждая группа будет представлять собой каждый уникальный день, но вместо этого мы видим следующее:
|
Кажется, что наши данные вообще не сгруппированы - мы получаем каждую строку по отдельности.
Но на самом деле наши данные сгруппированы! Проблема в том, что sold_at каждого ряда - это уникальное значение, поэтому каждый ряд получает свою собственную группу!
GROUP BY работает правильно, но это не тот результат, который мы хотим получить.
Виной тому уникальная информация о часах/минутах/секундах в метке времени.
Каждая из этих временных меток отличается на часы, минуты или секунды, поэтому они помещаются в свою группу.
Нам нужно преобразовать каждое из этих значений даты и времени в просто дату:
2020-09-01 08:42:33.085995 => 2020-09-01
2020-09-01 09:42:33.085995 => 2020-09-01
Преобразованные в дату, все временные метки одного и того же дня вернут одно и то же значение даты и, следовательно, будут помещены в одну группу.
Для этого мы приведем значение временной метки sold_at к дате:
|
В предложении GROUP BY мы используем ::DATE для усечения временной метки до «дня». Это эффективно отсекает часы/минуты/секунды временной метки и возвращает только день.
В нашем SELECT мы также возвращаем это же выражение и даем ему псевдоним, чтобы украсить вывод.
По той же причине, по которой мы не можем вернуть product без группировки по нему или выполнения какого-либо агрегата, база данных не позволит нам вернуть просто sold_at - все в SELECT должно быть либо в GROUP BY, либо в каком-то агрегате для результирующих групп.
В результате мы получаем данные о продажах за день, которые мы изначально хотели увидеть:
|
Фильтрация групп с помощью HAVING
Далее рассмотрим, как отфильтровать сгруппированные строки.
Для этого попробуем найти дни, когда у нас было более одной продажи.
Без группировки мы обычно фильтруем наши строки с помощью предложения WHERE. Например:
|
В наших группах мы можем захотеть сделать что-то вроде этого, чтобы фильтровать наши группы на основе количества строк
|
К сожалению, это не работает, и мы получаем эту ошибку:
ERROR: aggregate functions are not allowed in WHERE
Агрегатные функции не допускаются в предложении WHERE, потому что предложение WHERE оценивается перед предложением GROUP BY - в нем еще нет групп для выполнения вычислений.
Но есть тип предложения, который позволяет нам фильтровать, выполнять агрегацию, и он оценивается после предложения GROUP BY: предложение HAVING.
Предложение HAVING - это как предложение WHERE для ваших групп.
Чтобы найти дни, когда у нас было больше одной продажи, мы можем добавить предложение HAVING, которое проверяет количество строк в группе:
|
Это предложение HAVING отфильтровывает все строки, в которых количество строк в этой группе не больше единицы, и мы видим это в нашем наборе результатов:
|
Для полноты картины приведем порядок выполнения всех частей SQL-оператора:
- FROM — Извлечь все строки из таблицы FROM
- JOIN — Выполнить любые соединения
- WHERE — Отфильтровать строки
- GROUP BY — Формировать группы
- HAVING — Фильтровать группы
- SELECT — Выбрать возвращаемые данные
- ORDER BY — Упорядочить выводимые строки
- LIMIT — Возврат определенного количества строк
Агрегаты с неявной группировкой
Последняя тема, которую мы рассмотрим, — это агрегаты, которые можно выполнять без GROUP BY - или, лучше сказать, они имеют неявную группировку.
Такие агрегации полезны в сценариях, когда вы хотите найти один конкретный агрегат из таблицы - например, общую сумму доходов или наибольшее или наименьшее значение столбца.
Например, мы можем найти общую выручку по всем отделениям, просто выбрав сумму по всей таблице:
|
|
На данный момент мы сделали 19 продаж во всех местах (ура!).
Еще одна полезная вещь, которую мы можем запросить, - это дата первой или последней продажи.
Например, какова дата нашей первой продажи?
Чтобы узнать это, мы просто используем функцию MIN():
|
|
(Чтобы найти дату последней продажи, просто замените MAX()на MIN()).
Использование MIN / MAX
Хотя эти простые запросы могут быть полезны как самостоятельные, они часто являются частью фильтров для более крупных запросов.
Например, давайте попробуем найти общее количество продаж за последний день, когда у нас были продажи.
Этот запрос можно написать следующим образом:
|
Этот запрос работает, но мы, очевидно, жестко задали дату 2020-09-01.
09/01/2020 может быть последней датой, когда у нас была распродажа, но это не всегда будет именно эта дата. Нам нужно динамическое решение.
Этого можно добиться, объединив данный запрос с функцией MAX() в подзапросе:
|
В нашем предложении WHERE мы находим самую большую дату в нашей таблице с помощью подзапроса:
SELECT MAX(sold_at::DATE) FROM sales.
Затем мы используем эту максимальную дату в качестве значения, по которому мы фильтруем таблицу, и суммируем цену каждой продажи.
Неявная группировка
Это неявная группировка, потому что если мы попытаемся выбрать агрегированное значение с неагрегированным столбцом, как здесь.
|
мы получаем знакомую ошибку:
|
GROUP BY - это инструмент
Как и многие другие темы в разработке программного обеспечения, GROUP BY - это инструмент.
Существует множество способов написать и переписать эти запросы, используя комбинации GROUP BY, агрегатных функций или других инструментов, таких как DISTINCT, ORDER BY и LIMIT.
Понимание и работа с GROUP BY потребуют немного практики, но как только вы освоите их, вы обнаружите, что теперь перед вами открылась совершенно новая группа проблем!
