Последний урок про агрегацию объединяет две идеи. Первая: выбирать значение по условию прямо внутри запроса, как if в программе. Вторая: считать итоги по группе строк, не сворачивая сами строки. GROUP BY превращает пять кофейных напитков в одну строку, а иногда нужно видеть все пять и рядом с каждым среднюю цену кофе. Для этого есть оконные функции.
CASE: развилка в запросе
Бариста раскладывает стаканы по полкам: маленькие налево, средние посередине, большие направо. Он смотрит на стакан, проверяет условие за условием и кладёт на первую подходящую полку.
CASE делает то же со строками. Он проверяет условия сверху вниз и возвращает значение для первого истинного:
sqlite> SELECT name, price,
...> CASE
...> WHEN price IS NULL THEN 'нет цены'
...> WHEN price < 150 THEN 'дёшево'
...> WHEN price < 200 THEN 'средне'
...> ELSE 'дорого'
...> END AS tier
...> FROM drinks;
╭────────────────┬───────┬──────────╮
│ name │ price │ tier │
╞════════════════╪═══════╪══════════╡
│ Эспрессо │ 120 │ дёшево │
│ Американо │ 150 │ средне │
│ Капучино │ 190 │ средне │
│ Латте │ 210 │ дорого │
│ Раф │ 240 │ дорого │
│ Какао │ 180 │ средне │
│ Чай с чабрецом │ 130 │ дёшево │
│ Матча-латте │ NULL │ нет цены │
│ Тархун │ 200 │ дорого │
╰────────────────┴───────┴──────────╯Порядок условий важен. Американо стоит ровно 150, условие price < 150 для него ложно, и он попадает на следующую полку. Проверку на NULL мы поставили первой: иначе Матча-латте не прошла бы ни одно сравнение (вспомни урок про NULL) и попала бы в ELSE, то есть в «дорого».
Если все условия сравнивают один столбец с конкретными значениями, есть короткая форма:
sqlite> SELECT id, CASE paid WHEN 'карта' THEN 'безнал' ELSE 'касса' END AS how FROM orders WHERE id <= 3;
╭────┬────────╮
│ id │ how │
╞════╪════════╡
│ 1 │ безнал │
│ 2 │ касса │
│ 3 │ безнал │
╰────┴────────╯Для развилки на два исхода в SQLite есть функция iif(условие, если_да, если_нет):
sqlite> SELECT name, iif(price > 180, 'да', 'нет') AS pricey FROM drinks WHERE category = 'кофе';
╭───────────┬────────╮
│ name │ pricey │
╞═══════════╪════════╡
│ Эспрессо │ нет │
│ Американо │ нет │
│ Капучино │ да │
│ Латте │ да │
│ Раф │ да │
╰───────────┴────────╯CASE внутри агрегата
Самый полезный приём с CASE это условная агрегация. Сколько заказов оплачено картой, а сколько сделали гости без карты лояльности, одним запросом:
sqlite> SELECT count(*) AS total, sum(CASE WHEN paid = 'карта' THEN 1 ELSE 0 END) AS by_card, sum(customer_id IS NULL) AS guests FROM orders;
╭───────┬─────────┬────────╮
│ total │ by_card │ guests │
╞═══════╪═════════╪════════╡
│ 12 │ 9 │ 3 │
╰───────┴─────────┴────────╯CASE превращает каждую строку в 1 или 0, а sum складывает единицы. В третьем столбце трюк короче: в SQLite сравнение само возвращает 1 или 0, поэтому sum(customer_id IS NULL) считает гостей без карты. Больше примеров такой агрегации в уроке с задачами собеседований.
Окно: итог рядом со строкой
Теперь вторая идея. Представь, что ты листаешь меню и держишь перед ним рамку. Сквозь рамку видны несколько строк, и для каждой строки ты можешь посчитать что-то по всем строкам в рамке: среднее, максимум, номер по порядку. Сами строки при этом остаются на месте.
Эта рамка называется окном, а функции, которые считают по нему, оконными. Окно описывают после слова OVER. Пустые скобки значат «все строки результата»:
sqlite> SELECT name, price, avg(price) OVER () AS avg_all FROM drinks WHERE category = 'кофе';
╭───────────┬───────┬─────────╮
│ name │ price │ avg_all │
╞═══════════╪═══════╪═════════╡
│ Эспрессо │ 120 │ 182.0 │
│ Американо │ 150 │ 182.0 │
│ Капучино │ 190 │ 182.0 │
│ Латте │ 210 │ 182.0 │
│ Раф │ 240 │ 182.0 │
╰───────────┴───────┴─────────╯Пять строк остались пятью, а рядом с каждой средняя цена кофе. Обычный avg с GROUP BY дал бы одну строку.
PARTITION BY: окно на каждую группу
PARTITION BY режет строки на группы, и окно у каждой строки своё, только из её группы:
sqlite> SELECT category, name, price, max(price) OVER (PARTITION BY category) AS top_in_cat FROM drinks WHERE price IS NOT NULL;
╭──────────┬────────────────┬───────┬────────────╮
│ category │ name │ price │ top_in_cat │
╞══════════╪════════════════╪═══════╪════════════╡
│ другое │ Какао │ 180 │ 180 │
│ кофе │ Эспрессо │ 120 │ 240 │
│ кофе │ Американо │ 150 │ 240 │
│ кофе │ Капучино │ 190 │ 240 │
│ кофе │ Латте │ 210 │ 240 │
│ кофе │ Раф │ 240 │ 240 │
│ холодное │ Тархун │ 200 │ 200 │
│ чай │ Чай с чабрецом │ 130 │ 130 │
╰──────────┴────────────────┴───────┴────────────╯Рядом с каждым кофе максимум среди кофе, рядом с чаем максимум среди чая. Так легко ответить на вопрос «насколько этот напиток дешевле самого дорогого в своей категории»: top_in_cat - price.
Нумерация: row_number, rank, dense_rank
Если внутри OVER указать ORDER BY, строки в окне упорядочиваются, и появляются функции нумерации. Рейтинг напитков по проданным чашкам:
sqlite> SELECT d.name, sum(i.qty) AS cups,
...> row_number() OVER w AS rn, rank() OVER w AS rnk, dense_rank() OVER w AS dense
...> FROM drinks d JOIN order_items i ON i.drink_id = d.id
...> GROUP BY d.id
...> WINDOW w AS (ORDER BY sum(i.qty) DESC);
╭────────────────┬──────┬────┬─────┬───────╮
│ name │ cups │ rn │ rnk │ dense │
╞════════════════╪══════╪════╪═════╪═══════╡
│ Капучино │ 4 │ 1 │ 1 │ 1 │
│ Эспрессо │ 4 │ 2 │ 1 │ 1 │
│ Тархун │ 3 │ 3 │ 3 │ 2 │
│ Чай с чабрецом │ 3 │ 4 │ 3 │ 2 │
│ Какао │ 3 │ 5 │ 3 │ 2 │
│ Раф │ 3 │ 6 │ 3 │ 2 │
│ Американо │ 3 │ 7 │ 3 │ 2 │
│ Латте │ 2 │ 8 │ 8 │ 3 │
╰────────────────┴──────┴────┴─────┴───────╯Три функции по-разному ведут себя при ничьей:
row_number()раздаёт номера подряд, ничьи разбивает как придётся;rank()даёт равным одинаковое место, а следующее место пропускает: после двух первых идёт третье;dense_rank()тоже даёт равным одно место, но без пропусков.
WINDOW w AS (...) задаёт окно один раз под именем, чтобы не повторять его трижды. Окно здесь строится уже после GROUP BY, поэтому в нём можно сортировать по sum(i.qty). Задачи на рейтинги собраны в уроке про ранжирование.
Нарастающий итог и соседняя строка
sum с ORDER BY в окне считает нарастающий итог: сколько заказов набралось к концу каждого дня:
sqlite> SELECT day, n, sum(n) OVER (ORDER BY day) AS running FROM (SELECT date(ordered_at) AS day, count(*) AS n FROM orders GROUP BY day);
╭────────────┬───┬─────────╮
│ day │ n │ running │
╞════════════╪═══╪═════════╡
│ 2026-10-01 │ 3 │ 3 │
│ 2026-10-02 │ 2 │ 5 │
│ 2026-10-03 │ 2 │ 7 │
│ 2026-10-04 │ 2 │ 9 │
│ 2026-10-05 │ 2 │ 11 │
│ 2026-10-06 │ 1 │ 12 │
╰────────────┴───┴─────────╯Внутренний запрос в FROM это подзапрос: он считает заказы по дням, а внешний добавляет к ним окно. Функция lag(n) берёт значение из предыдущей строки окна, и с её помощью видно, как менялся спрос от дня к дню:
sqlite> SELECT day, n, lag(n) OVER (ORDER BY day) AS prev, n - lag(n) OVER (ORDER BY day) AS diff FROM (SELECT date(ordered_at) AS day, count(*) AS n FROM orders GROUP BY day);
╭────────────┬───┬──────┬──────╮
│ day │ n │ prev │ diff │
╞════════════╪═══╪══════╪══════╡
│ 2026-10-01 │ 3 │ NULL │ NULL │
│ 2026-10-02 │ 2 │ 3 │ -1 │
│ 2026-10-03 │ 2 │ 2 │ 0 │
│ 2026-10-04 │ 2 │ 2 │ 0 │
│ 2026-10-05 │ 2 │ 2 │ 0 │
│ 2026-10-06 │ 1 │ 2 │ -1 │
╰────────────┴───┴──────┴──────╯У первого дня предыдущего нет, отсюда NULL. Парная функция lead смотрит на следующую строку. Подробнее о них в уроках про lag и lead и про нарастающие итоги.
Попробуй сам
Пронумеруем заказы каждого бариста по времени: у каждого сотрудника своя нумерация с единицы.
Убери PARTITION BY o.staff_id и посмотри, как изменится нумерация. Потом добавь столбец count(*) OVER (PARTITION BY o.staff_id) AS total: сколько всего заказов у этого бариста.
Проверь себя
Задание
Дальше
Кластер про агрегацию закончен: ты умеешь сворачивать строки, группировать их, преобразовывать значения и считать итоги по окну. Начинается практика. В следующем уроке смотрим, как SQLite ищет строки, и ускоряем поиск индексами.
домашка