В SQL есть функции для подсчёта общего количества строк, суммы, среднего значения, максимума и минимума. Такие функции называют агрегирующими. Они собирают, или агрегируют все объекты группы, чтобы уже по ним вычислить нужные значения.
Основные агрегирующие функции в SQL:
- SUM(поле) возвращает сумму значений в поле;
- AVG(поле) находит среднее арифметическое для значений в поле;
- MIN(поле) возвращает минимальное значение в поле;
- МАХ(поле) возвращает максимальное значение в поле;
- COUNT(*) выводит количество записей в таблице, а COUNT(поле) — количество записей в поле.
Агрегирующие функции применяют после оператора SELECT.
Задача 1
Перед тем как применить агрегирующие функции, нужно получить срез данных. Выгрузите все поля из таблицы invoice, выберите записи о заказах, оформленных в сентябре. Информацию о дате заказа содержит поле invoice_date. Тип данных поля — varchar. Не забудьте про функции для работы с датой.
SELECT *
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS timestamp)) = 9;
Задача 2
Дополните запрос. Оставьте в таблице поля с идентификатором покупателя (поле customer_id), датой заказа без времени (поле invoice_date) и суммой заказа (поле total). Выберите покупателей с идентификаторами 11, 13, 44, 36, 48, 52, 54, 56.
SELECT CAST(invoice_date AS date), customer_id, total-- сюда запишите нужные поля
-- не забудьте преобразовать дату в нужный формат
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS timestamp)) = 9
AND customer_id in ('11', '13', '44', '36', '48', '52', '54', '56'); -- дополните условие;
Задача 3
Дополните запрос. Найдите минимальное и максимальное значения поля total. Условия для среза остаются прежними.
SELECT MIN(total),
MAX(total)-- добавьте расчёты с помощью агрегирующих функций
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS timestamp)) = 9
AND customer_id in (11, 13, 44, 36, 48, 52, 54, 56);
Задача 4
Добавьте к минимуму и максимуму несколько новых полей:
- среднее значение поля total, округлённое до ближайшего числа;
- количество уникальных идентификаторов покупателей из поля customer_id;
- суммарная выручка.
SELECT MIN(total),
MAX(total), ROUND(AVG(total)),
SUM(total),
COUNT(DISTINCT(customer_id))
-- добавьте нужные поля
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS timestamp)) = 9
AND customer_id in (11, 13, 44, 36, 48, 52, 54, 56);
Задача 5
Посчитайте, сколько пропусков содержит поле fax из таблицы client.
SELECT (COUNT(client)-COUNT(fax))
FROM client;
Задача 6
Посчитайте среднюю стоимость заказов, оформленных в понедельник.
SELECT AVG(total)
FROM invoice
WHERE EXTRACT(DOW FROM CAST(invoice_date AS timestamp)) = 1;