Применение оператора 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

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

Задача 6 / 6

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

Подсказка

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

Подсказка

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

Подсказка

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

Подсказка

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

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

Подсказка

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

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

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

Подсказка

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

xxxxxxxxxx
 
SELECT
        CASE
            WHEN total < 1 THEN 'low cost'
            ELSE 'high cost'
        END,
        SUM(total)
FROM invoice
WHERE billing_postal_code IS NOT NULL
GROUP BY CASE
            WHEN total < 1 THEN 'low cost'
            ELSE 'high cost'
        END;

case	sum
low cost	50.49
high cost	2108.63