Руководство по предложению GROUP BY в SQL

Перевод статьи « SQL Group By Tutorial: Count, Sum, Average, and Having Clauses Explained».

Предложение GROUP BY это очень мощный параметр, но и непростой. Даже спустя восемь лет его использования я все еще каждый раз задумываюсь, что, собственно, я делаю.

В этой статье мы рассмотрим, как составляется предложение GROUP BY, какова роль этого параметра в вашем запросе и как его можно использовать для осуществления агрегаций и сбора информации о ваших данных.

Мы рассмотрим следующие темы:

Создание таблицы с данными
Как работает GROUP BY?
Написание предложений GROUP BY
Агрегатные функции (COUNT, SUM, AVG)
Работа с несколькими группами
Использование функций в GROUP BY
Фильтрация групп при помощи HAVING
Агрегации со скрытым группированием

Подготовительный этап с описанием программного обеспечения и созданием базы данных мы рассматривали в двух предыдущих статьях:

SQL-операторы: руководство с примерами запросов. (Раздел «Настройка базы данных»)
SQL JOIN: руководство по объединению таблиц. (Раздел «Настройка базы данных»)

Эти разделы совершенно идентичны, так что можете почитать в любой из двух статей.

Создание таблицы с данными

Для нашего примера мы создадим таблицу, в которой будут храниться записи о продажах различных продуктов в разных точках.

Таблицу мы назовем sales. Это будет простое представление продаж в магазинах: название локации, название продукта, цена и время продажи.

Если бы мы создавали такую таблицу для настоящего приложения, мы бы использовали внешние ключи к другим таблицам (например, locations или products). Но чтобы показать работу GROUP BY, мы создадим простые TEXT-столбцы.

Давайте создадим нашу таблицу и внесем в нее кое-какие данные о продажах:

CREATE TABLE sales(
  location TEXT,
  product TEXT,
  price DECIMAL,
  sold_at TIMESTAMP
);

INSERT INTO sales(location, product, price, sold_at) VALUES
('HQ', 'Coffee', 2, NOW()),
('HQ', 'Coffee', 2, NOW() - INTERVAL '1 hour'),
('Downtown', 'Bagel', 3, NOW() - INTERVAL '2 hour'),
('Downtown', 'Coffee', 2, NOW() - INTERVAL '1 day'),
('HQ', 'Bagel', 2, NOW() - INTERVAL '2 day'),
('1st Street', 'Bagel', 3, NOW() - INTERVAL '2 day' - INTERVAL '1 hour'),
('1st Street', 'Coffee', 2, NOW() - INTERVAL '3 day'),
('HQ', 'Bagel', 3, NOW() - INTERVAL '3 day' - INTERVAL '1 hour');

У нас есть три локации: HQ, Downtown, и 1st Street.

Также у нас есть два продукта: Coffee и Bagel (кофе и бублики). Продажи мы вносим с разными значениями sold_at, чтобы показать, сколько товаров было продано в разные дни и разное время.

У нас были продажи сегодня, вчера и позавчера.

Как работает GROUP BY?

Чтобы показать, как работает GROUP BY, давайте рассмотрим пример.

Представьте, что у нас есть комната, в которой находится много людей. Эти люди родились в разных странах.

Если мы хотим найти средний рост людей, находящихся в этой комнате, в разрезе по странам, мы сначала попросим их разделиться на группы по стране рождения.

Когда люди сгруппируются по месту рождения, мы сможем высчитать средний рост в каждой группе.

Вот так и работает предложение GROUP BY. Сначала мы определяем, как мы хотим сгруппировать строки, а затем осуществляем вычисления или агрегации в группах.

Множественные группы

Мы можем группировать данные в любое количество групп и подгрупп.

Например, когда люди разделились по странам, мы можем попросить их в каждой группе разделиться на подгруппы по цвету глаз.

Таким образом мы получим группы людей, родившихся в одной стране и имеющих одинаковый цвет глаз.

После этого мы можем высчитать средний рост в каждой такой маленькой группе и получить более специфический результат. Например, средний рост голубоглазых людей, родившихся во Франции.

Предложения GROUP BY часто используются в случаях, когда можно использовать обороты по чему-то или в каждом(ой):

средний рост по стране рождения,
общее число людей в каждой комбинации цвета глаз и волос,
общее число продаж по каждому продукту.

Написание предложений GROUP BY

Предложение GROUP BY пишется очень просто. Мы используем ключевые слова GROUP BY и указываем поля, по которым должна происходить группировка:

SELECT ...
FROM sales
GROUP BY location;

Этот простой запрос группирует данные таблицы sales по столбцу location.

Ну хорошо, мы их сгруппировали, но что нам поместить в наш SELECT?

Очевидно, что нам нужно сделать выборку локации. Мы группируем данные по этому столбцу и как минимум хотим увидеть имена созданных групп:

SELECT location
FROM sales
GROUP BY location;

Результатом будут три наши локации:

  location
------------
 1st Street
 HQ
 Downtown
(3 rows)

Если мы посмотрим на необработанные данные нашей таблицы (SELECT * FROM sales;), мы увидим, что у нас есть четыре строки с локацией HQ, две строки с локацией Downtown и еще две — с локацией 1st Street:

 product |  location  | price |          sold_at
---------+------------+-------+----------------------------
 Coffee  | HQ         |     2 | 2020-09-01 09:42:33.085995
 Coffee  | HQ         |     2 | 2020-09-01 08:42:33.085995
 Bagel   | Downtown   |     3 | 2020-09-01 07:42:33.085995
 Coffee  | Downtown   |     2 | 2020-08-31 09:42:33.085995
 Bagel   | HQ         |     2 | 2020-08-30 09:42:33.085995
 Bagel   | 1st Street |     3 | 2020-08-30 08:42:33.085995
 Coffee  | 1st Street |     2 | 2020-08-29 09:42:33.085995
 Bagel   | HQ         |     3 | 2020-08-29 08:42:33.085995
(8 rows)

Группируя данные по столбцу location, наша база данных берет эти входные строки и определяет среди них уникальные локации. Эти уникальные локации служат в качестве «групп».

А как насчет остальных столбцов таблицы?

Если мы попробуем выбрать столбец product, по которому мы не делали группировку,

SELECT
  location,
  product
FROM sales
GROUP BY location;

мы получим вот такую ошибку:

ERROR:  column "sales.product" must appear in the GROUP BY clause or be used in an aggregate function

Проблема в том, что мы взяли восемь строк и попытались втиснуть их в три.

Мы не можем просто возвращать оставшиеся столбцы, как обычно, потому что раньше у нас было восемь строк, а теперь их только три.

Что делать с оставшимися пятью строками данных? Какие данные из восьми строк должны быть отображены в трех строках?

На эти вопросы нет четкого и ясного ответа.

Чтобы использовать остальные данные таблицы, мы должны выделить данные из оставшихся столбцов в наши три локационные группы.

Это означает, что мы должны агрегировать эти данные или осуществить какие-то вычисления, чтобы получить некую итоговую информацию об оставшихся данных.

Агрегатные функции (COUNT, SUM, AVG)

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

Для начала давайте найдем количество продаж по каждой локации.

Поскольку каждая запись в таблице sales это запись об одной продаже, число продаж по локации будет равно числу строк в каждой группе (при группировке по локациям).

Чтобы получить нужный результат, нам нужно применить агрегатную функцию COUNT() — так мы вычислим количество строк в каждой группе.

SELECT
  location,
  COUNT(*) AS number_of_sales
FROM sales
GROUP BY location;

Мы используем COUNT(*), чтобы считать все входящие строки в группе.

(COUNT() также работает с выражениями, но при этом имеет несколько другое поведение).

Вот как база данный выполняет наш запрос:

FROM sales — Сначала получи все записи из таблицы sales.
GROUP BY location — Затем определи уникальные группы при группировке по локации (т. е. уникальные локации).
SELECT … — Наконец, выбери имя локации и посчитай число строк в этой группе.

Чтобы сделать вывод более читабельным, мы даем числу строк псевдоним — при помощи AS number_of_sales. Выглядит это так:

  location  | number_of_sales
------------+-----------------
 1st Street |               2
 HQ         |               4
 Downtown   |               2
(3 rows)

Локация 1st Street имеет две продажи, HQ — четыре, а Downtown — две.

Как видно, здесь мы взяли данные столбца, по которому не делали группировку, и из восьми отдельных строк вычленили полезную итоговую информацию по каждой локации, а именно — число продаж.

SUM

Вместо подсчета числа строк в группе мы могли бы суммировать информацию по группе. Например, получить общее количество вырученных денег по каждой локации.

Для этого мы будем использовать функцию SUM():

SELECT
  location,
  SUM(price) AS total_revenue
FROM sales
GROUP BY location;

Вместо подсчета числа строк в каждой группе мы сложили количество долларов, полученных в результате каждой продажи, и вывели общий доход по локациям:

  location  | total_revenue
------------+---------------
 1st Street |             5
 HQ         |             9
 Downtown   |             5
(3 rows)

AVG

Функция AVG() позволяет находить среднее значение (AVG от Average — среднее). Давайте найдем среднюю сумму выручки по локациям. Для этого просто заменим функцию SUM() на функцию AVG():

SELECT
  location,
  AVG(price) AS average_revenue_per_sale
FROM sales
GROUP BY location;

Работа с несколькими группами

Пока что мы работали с одной группировкой — по локациям. Что, если нам нужно разбить полученные группы на подгруппы?

Вспомните пример сценария, приведенный в начале статьи, с группировкой людей по цвету глаз и стране происхождения. Давайте попробуем найти число продаж каждого продукта в каждой отдельной локации (Например, сколько было продаж кофе, а сколько — бубликов на 1st Street, HQ и Downtown).

Для этого нам нужно добавить к нашему предложению GROUP BY второе группирующее условие:

SELECT ...
FROM sales
GROUP BY location, product;

Добавив название еще одного столбца в наше предложение GROUP BY, мы разделили наши локационные группы на подгруппы по продуктам.

Поскольку теперь мы группируем также по столбцу product, мы можем вернуть результат при помощи нашего SELECT!

(Для облегчения чтения я добавил в запрос также предложения ORDER BY).

SELECT
  location,
  product
FROM sales
GROUP BY location, product
ORDER BY location, product;

В результатах нашего нового группирования мы видим уникальные комбинации локаций и продуктов:

  location  | product
------------+---------
 1st Street | Bagel
 1st Street | Coffee
 Downtown   | Bagel
 Downtown   | Coffee
 HQ         | Bagel
 HQ         | Coffee
(6 rows)

Ну хорошо, у нас есть наши группы, а что мы будем делать с данными остальных столбцов?

Мы можем найти число продаж определенного продукта в каждой локации, используя все те же агрегатные функции:

SELECT
  location,
  product,
  COUNT(*) AS number_of_sales
FROM sales
GROUP BY location, product
ORDER BY location, product;

  location  | product | number_of_sales
------------+---------+-----------------
 1st Street | Bagel   |               1
 1st Street | Coffee  |               1
 Downtown   | Bagel   |               1
 Downtown   | Coffee  |               1
 HQ         | Bagel   |               2
 HQ         | Coffee  |               2
(6 rows)

(Задание «со звездочкой»: найдите общую выручку (сумму) за каждый продукт в каждой локации).

Использование функций в GROUP BY

Давайте попытаемся найти общее число продаж в день.

Следуя тому же шаблону, который применяли со столбцом sold_at,

SELECT
  sold_at,
  COUNT(*) AS sales_per_day
FROM sales
GROUP BY sold_at
ORDER BY sold_at;

мы можем ожидать, что каждая группа будет уникальным днем, но вместо этого видим следующее:

          sold_at           | sales_per_day
----------------------------+---------------
 2020-08-29 08:42:33.085995 |             1
 2020-08-29 09:42:33.085995 |             1
 2020-08-30 08:42:33.085995 |             1
 2020-08-30 09:42:33.085995 |             1
 2020-08-31 09:42:33.085995 |             1
 2020-09-01 07:42:33.085995 |             1
 2020-09-01 08:42:33.085995 |             1
 2020-09-01 09:42:33.085995 |             1
(8 rows)

Похоже, наши данные вообще не сгруппировались: мы получили каждую строку отдельно.

Но на самом деле наши данные сгруппированы! Проблема в том, что 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 к дате:

SELECT
  sold_at::DATE AS date,
  COUNT(*) AS sales_per_day
FROM sales
GROUP BY sold_at::DATE
ORDER BY sold_at::DATE;

В нашем предложении GROUP BY мы используем ::DATE, чтобы обрезать временную метку до «дня». Таким образом мы эффективно отрезаем часы/минуты/секунды и возвращаем просто день.

В запросе SELECT мы возвращаем то же выражение и даем ему псевдоним для более красивого вывода.

По той же причине, по которой нельзя вернуть product, не сгруппировав данные по этому столбцу или не совершив каких-то вычислений, база данных не позволит нам и вернуть просто sold_at. В SELECT все должно быть или в GROUP BY, или как-то агрегировано по группам.

Вот результат числа продаж за день, который мы хотели увидеть:

    date    | sales_per_day
------------+---------------
 2020-08-29 |             2
 2020-08-30 |             2
 2020-08-31 |             1
 2020-09-01 |             3
(4 rows)

Фильтрация групп при помощи HAVING

Давайте теперь разберем, как можно фильтровать наши сгруппированные строки. Например, попробуем найти дни, в которые у нас было больше одной продажи.

Не применяя группирование, мы могли бы отфильтровать наши строки, используя предложение WHERE. Например:

SELECT *
FROM sales
WHERE product = 'Coffee';

Имея группы, мы можем попробовать отфильтровать наши группы по числу строк…

SELECT
  sold_at::DATE AS date,
  COUNT(*) AS sales_per_day
FROM sales
WHERE COUNT(*) > 1      -- filter the groups?
GROUP BY sold_at::DATE;

К сожалению, это не сработало и мы получили ошибку:

ERROR: aggregate functions are not allowed in WHERE

В предложениях WHERE агрегатные функции недопустимы, потому что WHERE вычисляется до предложения GROUP BY, то есть в момент, когда еще никаких групп нет.

Но есть одно предложение, позволяющее нам фильтровать записи и осуществлять агрегации, и оно срабатывает после предложения GROUP BY. Это предложение HAVING.

Предложение HAVING это как предложение WHERE, только для групп.

Чтобы найти дни, в которые было больше одной продажи. мы можем добавить предложение HAVING, которое проверит число строк в группе:

SELECT
  sold_at::DATE AS date,
  COUNT(*) AS sales_per_day
FROM sales
GROUP BY sold_at::DATE
HAVING COUNT(*) > 1;

Это предложение HAVING отфильтровывает все строки, если число строк в группе не больше одной. Вот результат:

    date    | sales_per_day
------------+---------------
 2020-09-01 |             3
 2020-08-29 |             2
 2020-08-30 |             2
(3 rows)

Чисто для полноты картины вот вам порядок выполнения всех предложений SQL:

FROM — получает все записи из указанной таблицы
JOIN — осуществляет соединения
WHERE — фильтрует строки
GROUP BY — формирует группы
HAVING — фильтрует группы
SELECT — выбирает данные для возврата
ORDER BY — упорядочивает строки вывода
LIMIT — возвращает определенное число строк.

Агрегации со скрытым группированием

Последняя тема, которую мы затронем, это агрегации без GROUP BY или, если выражаться более точно, агрегации со скрытым группированием.

Эти агрегации полезны в сценариях, где вы хотите найти одну конкретную агрегацию из таблицы. Например, общую выручку или наибольшее/наименьшее значение столбца.

Мы могли бы найти общую выручку по всем локациям, просто выбрав сумму по всей таблице:

SELECT SUM(price)
FROM sales;

 sum
-----
  19
(1 row)

На данный момент в результате всех продаж по всем локациям мы получили $19 (ура!).

Еще один полезный сценарий — запросить первое или последнее что-нибудь.

Например, дату самой первой продажи.

Чтобы ее найти, мы моем применить функцию MIN():

SELECT MIN(sold_at)::DATE AS first_sale
FROM sales;

 first_sale
------------
 2020-08-29
(1 row)

(Для поиска даты последней продажи нужно всего лишь заменить MIN() на MAX()).

Использование MIN / MAX

Хотя эти простые запросы могут быть полезны сами по себе, они часто являются составляющими более длинных запросов.

Например, давайте попробуем найти общую выручку в последний день, когда у нас вообще были продажи.

Мы можем написать запрос так:

SELECT
  SUM(price)
FROM sales
WHERE sold_at::DATE = '2020-09-01';

Этот запрос сработает, но мы захардкодили дату 2020-09-01. А ведь дата последней продажи будет постоянно меняться. Нам нужно динамическое решение.

Для этого нам нужно скомбинировать этот запрос с функцией MAX() в подзапросе:

SELECT
  SUM(price)
FROM sales
WHERE sold_at::DATE = (
  SELECT MAX(sold_at::DATE)
  FROM sales
);

В нашем предложении WHERE, используя подзапрос, мы нашли самую последнюю дату в таблице: SELECT MAX(sold_at::DATE) FROM sales.

Затем мы использовали эту «максимальную» дату в качестве значения, по которому фильтруется таблица, и суммировали выручку по каждой продаже.

Скрытое группирование

Я назвал это скрытым группированием, потому что если мы попытаемся вот так выбрать агрегированное значение с не-агрегированным столбцом —

SELECT
  SUM(price),
  location
FROM sales;

мы получим уже знакомую ошибку:

ERROR:  column "sales.location" must appear in the GROUP BY clause or be used in an aggregate function

GROUP BY — это инструмент

Как и многие другие вещи в сфере разработки, GROUP BY — это инструмент.

Есть много способов писать и переписывать эти запросы, используя комбинации GROUP BY, агрегатных функций и других инструментов, таких как DISTINCT, ORDER BY и LIMIT.

Чтобы понять, как работает GROUP BY, нужно немного попрактиковаться. Но когда вы разберетесь, то поймете, что теперь можете куда легче решать многие задачи!

[customscript]techrocks_custom_after_post_html[/customscript]

[customscript]techrocks_custom_script[/customscript]