Агрегатные функции

До сих пор каждый запрос возвращал строки таблицы: напитки, заказы, пары сотрудник и руководитель из прошлого урока. Но управляющей «Пенки» часто нужна одна цифра, а не список. Сколько напитков в меню? Какой самый дорогой? Сколько в среднем стоит чашка? Для таких вопросов в SQL есть агрегатные функции: они берут много строк и возвращают одно значение.

Кассир в конце смены

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

Агрегатная функция делает то же самое с таблицей. Обычная функция вроде length(name) работает с каждой строкой отдельно и возвращает столько же строк. Агрегатная сворачивает все строки в одну.

count: сколько строк

Самая частая агрегатная функция это count. У неё две формы, и разница между ними важна:

sqlite> SELECT count(*), count(price) FROM drinks;
╭──────────┬──────────────╮
│ count(*) │ count(price) │
╞══════════╪══════════════╡
│        9 │            8 │
╰──────────┴──────────────╯

count(*) считает строки: в меню девять напитков. count(price) считает только строки, где в price не NULL. Матча-латте добавили в меню, а цену ещё не назначили, поэтому восемь.

Это не особенность одной функции, а общее правило: агрегатные функции пропускают NULL. Вспомни урок про NULL: NULL значит «неизвестно», и складывать или сравнивать неизвестное бессмысленно. Все варианты count с примерами из собеседований разобраны в теоретическом уроке.

Чтобы посчитать разные значения, внутрь кладут DISTINCT:

sqlite> SELECT count(category) AS n, count(DISTINCT category) AS kinds FROM drinks;
╭───┬───────╮
│ n │ kinds │
╞═══╪═══════╡
│ 9 │     4 │
╰───┴───────╯

Девять напитков, но всего четыре категории: кофе, чай, холодное и другое.

sum, avg, min, max

Остальные функции из кассового отчёта:

sqlite> SELECT sum(price), avg(price), min(price), max(price) FROM drinks;
╭────────────┬────────────┬────────────┬────────────╮
│ sum(price) │ avg(price) │ min(price) │ max(price) │
╞════════════╪════════════╪════════════╪════════════╡
│       1420 │      177.5 │        120 │        240 │
╰────────────┴────────────┴────────────┴────────────╯

• sum складывает значения;
• avg считает среднее;
• min и max находят самое маленькое и самое большое.

Посмотри на среднее: 177.5. Сумма 1420 разделена на восемь, а не на девять. Матча-латте без цены в расчёт не попала совсем, ни в сумму, ни в количество. Это ровно то, что нужно: неизвестная цена не должна тянуть среднее вниз, как если бы напиток был бесплатным.

min и max работают не только с числами. Для текста они сравнивают строки, для дат в формате 2026-10-01 находят самую раннюю и самую позднюю. Попробуй max(ordered_at) в песочнице ниже.

Пустой набор: sum против total

Что вернёт сумма, если строк нет совсем? В меню нет категории «сок»:

sqlite> SELECT sum(price), total(price), count(*) FROM drinks WHERE category = 'сок';
╭────────────┬──────────────┬──────────╮
│ sum(price) │ total(price) │ count(*) │
╞════════════╪══════════════╪══════════╡
│ NULL       │          0.0 │        0 │
╰────────────┴──────────────┴──────────╯

count(*) честно отвечает «ноль строк». А sum возвращает NULL: по стандарту SQL сумма пустого набора неизвестна. Для отчёта это неудобно, «выручка: NULL» выглядит как ошибка. Поэтому в SQLite есть своя функция total: она возвращает 0.0 там, где sum вернула бы NULL. Других баз с total нет, в PostgreSQL то же самое пишут как coalesce(sum(price), 0).

Склеить строки: group_concat

Иногда итог это не число, а список. group_concat склеивает значения всех строк в одну строку через разделитель:

sqlite> SELECT group_concat(name, ', ') AS teas FROM drinks WHERE category = 'чай';
╭─────────────────────────────╮
│            teas             │
╞═════════════════════════════╡
│ Чай с чабрецом, Матча-латте │
╰─────────────────────────────╯

Порядок склейки не гарантирован: SQLite берёт строки в том порядке, в каком их читает. С версии 3.44 у функции есть второе имя string_agg, как в PostgreSQL.

Агрегаты и динамическая типизация

Помнишь столбец volume из урока про типы? У него нет объявленного типа, и в нём лежит что попало:

sqlite> SELECT name, volume, typeof(volume) FROM drinks WHERE id IN (5, 6, 9);
╭────────┬────────┬────────────────╮
│  name  │ volume │ typeof(volume) │
╞════════╪════════╪════════════════╡
│ Раф    │    350 │ integer        │
│ Какао  │ 300 мл │ text           │
│ Тархун │    0.5 │ real           │
╰────────┴────────┴────────────────╯
sqlite> SELECT sum(volume) AS total_ml, avg(volume) AS avg_ml FROM drinks;
╭──────────┬────────────────────╮
│ total_ml │       avg_ml       │
╞══════════╪════════════════════╡
│   2490.5 │ 276.72222222222223 │
╰──────────┴────────────────────╯

Ни ошибки, ни предупреждения. SQLite превратил строку 300 мл в число по началу строки, то есть в 300, а пол-литра Тархуна сложил как 0.5. Среднее посчитано по девяти «объёмам», где литры смешаны с миллилитрами. PostgreSQL на такой столбец даже не дал бы создать таблицу без типа. SQLite посчитает, но за смысл отвечаешь ты: агрегат над грязным столбцом выдаёт правдоподобное и неверное число.

Агрегат и обычный столбец

Одна оговорка на будущее. Запрос SELECT name, max(price) FROM drinks в SQLite сработает и покажет Раф. В большинстве других баз он упадёт с ошибкой: агрегат даёт одну строку, а названий девять, какое из них показать? SQLite здесь делает исключение, и в следующем уроке мы разберём, когда на него можно полагаться, а когда нет.

Попробуй сам

В песочнице кассовый отчёт «Пенки»: сколько чашек продано, когда был первый и последний заказ и какая средняя цена кофе.

Последний запрос показывает, сколько заказов сделали гости с картой. Подумай, почему цифры разные, а потом проверь догадку запросом с WHERE customer_id IS NULL.

Проверь себя

Задание

Дальше

Сейчас итог считается по всей таблице сразу. А управляющей хочется видеть цифры по каждой категории или по каждому дню отдельно. В следующем уроке делим строки на группы через GROUP BY и фильтруем группы через HAVING.