Группировка помогает анализировать данные. Например, с помощью группировки можно сравнить выручку в разных городах. В SQL данные группируют оператором GROUP BY — его указывают после условного оператора WHERE. Если WHERE в запросе нет, оператор GROUP BY пишут после FROM.
Задача 1
Напишите запрос, который выгрузит общую выручку (поле total) в США (англ. USA). Информацию о стране хранит поле billing_country.
SELECT SUM(total)
FROM invoice
WHERE billing_country = 'USA';
Задача 2
Посчитайте общую выручку, количество заказов, среднюю выручку для каждого города США. Нужное поле — billing_city.
SELECT SUM(total), COUNT(total), AVG(total), billing_city
FROM invoice
WHERE billing_country = 'USA'
GROUP BY billing_city
Задача 3
Проверьте, какую выручку в среднем приносит каждый покупатель. Выгрузите общую сумму выручки, число уникальных покупателей (поле customer_id) и среднюю выручку на уникального пользователя для страны США.
SELECT SUM(total), COUNT(DISTINCT(customer_id)), SUM(total)/COUNT(DISTINCT(customer_id))
FROM invoice
WHERE billing_country = 'USA';
Задача 4
Дополните предыдущий запрос. Сгруппируйте данные по неделе заказа. Для этого нужно усечь дату из поля invoice_date, округлив её до первого дня недели. Обратите внимание, поле invoice_date хранит данные типа varchar.
SELECT DATE_TRUNC('week', CAST(invoice_date AS timestamp)),
SUM(total),
COUNT(DISTINCT customer_id),
SUM(total)/COUNT(DISTINCT customer_id)
FROM invoice
WHERE billing_country = 'USA'
GROUP BY DATE_TRUNC('week', CAST(invoice_date AS timestamp));
Задача 5
SELECT support_rep_id,
COUNT(customer_id)
FROM client
WHERE email LIKE '%yahoo%'
OR email LIKE '%gmail%'
GROUP BY support_rep_id;
Задача 6
Создайте новое поле с категориями:
- заказы на сумму меньше одного доллара получат категорию 'low cost';
- заказы на сумму от одного доллара и выше получат категорию 'high cost'.
Для каждой категории посчитайте сумму значений в поле total, но только для тех заказов, при оформлении которых указан почтовый код. В итоговую таблицу должны войти только два поля.
SELECT
CASE
WHEN total <1 THEN 'low cost'
WHEN total >=1 THEN 'high cost'
END,
SUM(total)
FROM invoice
WHERE billing_postal_code IS NOT NULL
GROUP BY
CASE
WHEN total <1 THEN 'low cost'
WHEN total >=1 THEN 'high cost'
END;
Данные проще анализировать отсортированными. Сортировка позволит выгрузить «топовые» значения от большего к меньшему или наоборот, например топ-10 самых популярных треков или топ-5 самых невовлечённых пользователей.
В SQL сортируют значения в полях с любым типом данных. Поля с числовым типом данных можно отсортировать по возрастанию или убыванию. Так же можно сортировать дату и время. Поля с символьными данными сортируют в лексикографическом порядке и наоборот.
Для сортировки данных в SQL используют оператор ORDER BY. Его пишут в самом конце запроса, после него можно указать только оператор LIMIT.
По умолчанию оператор ORDER BY сортирует данные от меньшего к большему. Чтобы изменить порядок сортировки вручную, после названия поля указывают ключевое слово DESC: тогда данные будут отсортированы по убыванию. Если указать ASC, данные будут отсортированы по возрастанию — так же, как и по умолчанию.
Задача 1
Отберите пять самых крупных заказов из таблицы invoice.
SELECT *
FROM invoice
ORDER BY total DESC-- впишите условие для сортировки
LIMIT 5-- добавьте ограничение;
Задача 2
Отберите пятерых самых активных клиентов в США с 25 мая 2011 по 25 сентября 2011. Дату хранит поле invoice_date, тип данных поля — varchar. Выведите два поля: идентификатор клиента и количество заказов. Расположите записи по убыванию количества заказов.
В выдаче встретятся записи с одинаковым числом заказов. Их нужно отсортировать по возрастанию идентификаторов клиентов. Таким образом, получится двойная сортировка. Для этого указывают нужные поля после оператора для сортировки через запятую: ОПЕРАТОР поле_1, поле_2.
SELECT customer_id,
COUNT(customer_id)
FROM invoice
WHERE billing_country = 'USA' AND
CAST(invoice_date AS date)
BETWEEN '2011-05-25'
AND '2011-09-25'-- сюда впишите условия
GROUP BY customer_id
ORDER BY COUNT(customer_id) DESC, customer_id ASC-- добавьте данные для сортировки
LIMIT 5;
Задача 3
Нужно посмотреть продажи по годам. Выгрузите таблицу, в которую войдут:
- год покупки;
- минимальная сумма заказа;
- максимальная сумма заказа;
- общая сумма выручки;
- количество заказов;
- средняя выручка на уникального покупателя, округлённая до ближайшего целого числа.
Отсортируйте таблицу по году от большего к меньшему. Отберите только те записи, в которых в поле billing_country указаны страны: США (англ. USA), Великобритания (англ. United Kingdom) и Германия (англ. Germany).
SELECT EXTRACT(YEAR FROM CAST(invoice_date AS timestamp)),
MAX (total),
MIN(total),
SUM(total),
COUNT(total),
ROUND(SUM(total)/COUNT(DISTINCT customer_id))
FROM invoice
WHERE billing_country IN ('USA','United Kingdom','Germany')
GROUP BY EXTRACT(YEAR FROM CAST(invoice_date AS timestamp))
ORDER BY EXTRACT(YEAR FROM CAST(invoice_date AS timestamp)) DESC;
SQL позволяет группировать и сортировать данные не только по одному полю, но и по нескольким сразу.
При сортировке важен порядок полей, которые указывают после оператора ORDER BY. В случае с группировкой порядок полей влияет только на расположение полей в таблице.
Оператор HAVING используют для того, чтобы получить срез данных после группировки. У этого оператора есть несколько особенностей:
- HAVING нельзя применять без оператора GROUP BY;
- Использовать HAVING без агрегирующих функций нет смысла: в таких случаях больше подойдёт WHERE.
Оператор HAVING всегда указывают после GROUP BY.
Задача 1
Сравните фильмы разных возрастных рейтингов. Найдите среднее значение цены аренды фильма в поле rental_rate для каждого рейтинга (поле rating). Оставьте в таблице только те записи, в которых среднее значение rental_rate больше 3.
SELECT rating, AVG(rental_rate)
FROM movie
GROUP BY rating
HAVING AVG(rental_rate)>3;
Задача 2
Изучите заказы, которые оформили в сентябре 2011 года. Сравните общую сумму выручки (поле total) за каждый день этого месяца: выведите день в формате '2011-09-01' и сумму. Информацию о дате заказа хранит поле invoice_date. Не забудьте изменить тип данных в этом поле, чтобы использовать операторы для работы с датой. Оставьте в таблице только те значения суммы, которые больше 1 и меньше 10.
SELECT CAST(invoice_date AS date), SUM(total)
FROM invoice
WHERE CAST(invoice_date AS date)
BETWEEN '2011-09-01'
AND '2011-09-30'
GROUP BY CAST(invoice_date AS date)
HAVING SUM(total) BETWEEN 1 AND 10;
Задача 3
Посчитайте пропуски в поле с почтовым индексом billing_postal_code для каждой страны (поле billing_country). Получите срез: в таблицу должны войти только те записи, в которых поле billing_address не содержит слов Street, Way, Road или Drive. Отобразите в таблице страну и число пропусков, если их больше 10.
SELECT billing_country,
(COUNT(invoice) - COUNT(billing_postal_code))
FROM invoice
WHERE billing_address NOT LIKE '%Street%'
AND billing_address NOT LIKE '%Way%'
AND billing_address NOT LIKE '%Road%'
AND billing_address NOT LIKE '%Drive%'
GROUP BY billing_country
HAVING (COUNT(invoice) - COUNT(billing_postal_code))>10;