Агрегаты SQL. 18 задач на COUNT, SUM и AVG

Почему агрегаты опаснее, чем выглядят

COUNT, SUM и AVG выглядят простыми, пока отчёт не начинает считать не тех клиентов, не те строки или не тот уровень детализации.

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

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

Как проверять себя

Перед запуском запроса посчитайте руками две-три контрольные строки. Если итог неожиданен, проблема обычно не в SQL-движке, а в выбранном зерне отчёта.

Отдельно проверяйте NULL. COUNT(*), COUNT(column), SUM и AVG относятся к отсутствующим значениям по-разному, и это часто меняет выводы.

Все запросы рассчитаны на SQLite. Базовые агрегаты SQL переносимы почти в любую СУБД, поэтому задачи полезны и за пределами SQLite.

Задача 1. Посчитать все заказы

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

CREATE TABLE orders(id INTEGER, status TEXT, total INTEGER);

INSERT INTO orders VALUES (1,'paid',500),(2,'new',700),(3,'paid',300),(4,'cancelled',900);

SELECT COUNT(*) AS orders_count

FROM orders;

[[4]]

COUNT(*) считает строки результата.

В таблице orders четыре строки.

Статус и сумма не влияют на число строк без WHERE.

COUNT(*) не означает число оплаченных заказов или клиентов. Это только число строк после всех фильтров.

Как изменится результат, если добавить пятую строку со status = test?

Ответ: COUNT(*) станет 5, потому что без WHERE считаются все строки таблицы.

Задача 2. Посчитать выручку оплаченных заказов

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

CREATE TABLE orders(id INTEGER, status TEXT, total INTEGER);

INSERT INTO orders VALUES (1,'paid',500),(2,'new',700),(3,'paid',300),(4,'cancelled',900);

SELECT SUM(total) AS paid_revenue

FROM orders

WHERE status = 'paid';

[[800]]

WHERE оставляет только строки paid.

SUM(total) складывает 500 и 300.

new и cancelled не участвуют в расчёте выручки.

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

Добавьте paid-заказ на 200. Каким станет paid_revenue?

Ответ: paid_revenue станет 1000, потому что к 800 добавится 200.

Задача 3. Найти средний оплаченный чек

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

CREATE TABLE orders(id INTEGER, status TEXT, total INTEGER);

INSERT INTO orders VALUES (1,'paid',500),(2,'new',900),(3,'paid',700),(4,'cancelled',300);

SELECT ROUND(AVG(total), 1) AS avg_paid_total

FROM orders

WHERE status = 'paid';

[[600.0]]