Применение оператора GROUP BY

Группировка помогает анализировать данные. Например, с помощью группировки можно сравнить выручку в разных городах. В SQL данные группируют оператором GROUP BY — его указывают после условного оператора WHERE. Если WHERE в запросе нет, оператор GROUP BY пишут после FROM.

SQL
-- нужно указать поля и необходимые вычисления
SELECT billing_city,
           ROUND(AVG(total))
-- не забыть таблицу
FROM invoice
-- здесь указывают поле, в котором группируют данные
GROUP BY billing_city
LIMIT 5; 

billing_city	round
Porto	5
Budapest	7
Reno	5
London	5
Paris	6

Не забудьте вывести поле, по которому группируете данные. Иначе таблица получится неинформативной.

SQL
SELECT ROUND(AVG(total))
FROM invoice
GROUP BY billing_city
LIMIT 5; 

round
5
7
5
5
6

Схема базы данных

Задача 5 / 6

Группировка помогает сравнивать данные. Но сначала нужно получить срез. Напишите запрос, который выгрузит общую выручку (поле total) в США (англ. USA). Информацию о стране хранит поле billing_country.

Подсказка

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

Теперь можно проверить, как отличаются данные по городам. Посчитайте общую выручку, количество заказов, среднюю выручку для каждого города США. Нужное поле — billing_city.

Подсказка

Добавьте ключевое слово billing_city после оператора SELECT и запишите все необходимые агрегирующие функции. Примените группировку и не забудьте вывести поле, по которому группируете данные.

Проверьте, какую выручку в среднем приносит каждый покупатель. Выгрузите общую сумму выручки, число уникальных покупателей (поле customer_id) и среднюю выручку на уникального пользователя для страны США.

Подсказка

Функция AVG тут не поможет, ведь нужно посчитать среднюю выручку на каждого уникального пользователя, а не на всех. Выход — поделить одну агрегацию на другую, вот так: SUM(...)/COUNT(...). Для подсчёта уникальных покупателей используйте DISTINCT.

Дополните предыдущий запрос. Сгруппируйте данные по неделе заказа. Для этого нужно усечь дату из поля invoice_date до недели. Обратите внимание, поле invoice_date хранит данные типа varchar.

Подсказка

Чтобы выделить неделю, используйте оператор DATE_TRUNC. Синтаксис оператора такой: DATE_TRUNC('отрезок времени', поле). Необходимое значение отрезка времени — 'week'. Не забудьте преобразовать поле invoice_date в формат timestamp. Сгруппируйте данные по неделе заказа.

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

идентификатор сотрудника,
количество пользователей.

Подсказка

Нужные электронные адреса содержат подстроки yahoo или gmail. Используйте оператор LIKE, чтобы найти записи по шаблону.

Создайте новое поле с категориями:

заказы на сумму меньше одного доллара получат категорию 'low cost';
заказы на сумму от одного доллара и выше получат категорию 'high cost'.

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

Подсказка

xxxxxxxxxx
 
SELECT support_rep_id,
        COUNT(customer_id)
FROM client
WHERE email LIKE '%yahoo%'
OR email LIKE '%gmail%'
GROUP BY support_rep_id;

support_rep_id	count
3	6
5	11
4	9