Тема 03 — Выражения и CASE

Считать и решать внутри запроса: CASE — это if/else для SQL. Заодно закроем твой вопрос из агрегации: когда FILTER, а когда WHERE.

Что заберёшь с урока

Научишься писать CASE WHEN … THEN … ELSE … END (метки, ветвления, «корзины» значений), приводить типы через :: (и не попадаться на целочисленное деление), склеивать строки и работать с датами. И главное — увидишь, что FILTER — это просто аккуратный CASE внутри агрегата, и поймёшь правило «WHERE или FILTER».

Главный источник по теме PostgreSQL Docs — Conditional Expressions (CASE, COALESCE, NULLIF) и Type Conversion (первоисточник). Простыми словами — Mode SQL: CASE.

§1 CASE — это if/else в SQL

CASE проверяет условия сверху вниз и возвращает значение первой подошедшей ветки. Это выражение — его можно ставить в SELECT, WHERE, ORDER BY, внутрь агрегата. Основная форма:

CASE
  WHEN price < 20   THEN 'cheap'
  WHEN price <= 50  THEN 'medium'
  ELSE              'expensive'
END

Запусти на живых данных — метка на каждый товар:

Две важные детали:

Порядок веток важен. Postgres берёт первую истинную WHEN. Если поставить price <= 50 выше price < 20, дешёвые товары попадут в «medium».
Нет ELSE → получишь NULL (привет Теме 02). Если ни одна ветка не подошла и ELSE не написан, результат — NULL.

Есть и короткая форма для сравнения одного столбца с равенством: CASE status WHEN 'paid' THEN … END. Она удобна, но CASE WHEN … (с полным условием) мощнее — им и пользуйся по умолчанию.

Проверка: в CASE нет ветки ELSE, и ни одно WHEN не сработало. Что вернётся?

Верно: без ELSE непокрытые строки дают NULL. Это частый источник «внезапных» NULL — если нужен запасной вариант, пиши ELSE явно.

§2 Приведение типов через `::` и ловушка деления

Тип значения решает, как оно себя ведёт. Приведение (cast) меняет тип: value::type (это Postgres-синтаксис; стандартный — CAST(value AS type), то же самое).

Самая частая боль backend-разработчика — целочисленное деление. Если оба операнда целые, Postgres делит нацело и отбрасывает дробь:

Почему это важно для backend: если делишь два int-столбца (например, «сумма / количество»), ты молча получишь округлённое вниз число и «потеряешь копейки», без всякой ошибки. Лечится приведением одного операнда: col_a::numeric / col_b. Другие частые касты: '42'::int, now()::date, id::text.

§3 Строки и даты

Строки склеиваются оператором ||. Полезные функции: UPPER/LOWER, LENGTH, LEFT/RIGHT, TRIM.

Ловушка (снова Тема 02): || с NULL делает NULL всю строку. Защита — COALESCE(col, 'запасное').

Из даты можно достать часть через EXTRACT(part FROM date): EXTRACT(YEAR FROM created_at), MONTH, DOW (день недели). А разность двух дат (date - date) даёт число дней.

§4 Разбор твоего вопроса: `WHERE` vs `FILTER` vs `CASE`

Ты запнулся на этом в агрегации — разберём раз и навсегда. Все трое умеют «условие», но работают на разном уровне:

WHERE — фильтр строк для всего запроса. Он выкидывает строки до агрегации. Условие одно на все агрегаты.
FILTER (WHERE …) — фильтр для одного конкретного агрегата. Строки остаются в запросе, но этот COUNT/SUM считает только подходящие. Можно рядом поставить несколько агрегатов с разными условиями.
CASE внутри агрегата — то же самое, что FILTER, но «руками». FILTER — это синтаксический сахар над ним.

Эти три выражения дают одинаковый результат:

COUNT(*) FILTER (WHERE status = 'paid')          -- 1) чище всего
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)  -- 2) то же руками
COUNT(CASE WHEN status = 'paid' THEN 1 END)        -- 3) NULL не считается COUNT-ом

Правило выбора

Нужно одно условие, и «ненужные» строки тебе вообще не интересны → WHERE (короче и быстрее).
Нужно несколько разных подсчётов в одной строке результата (всего / оплачено / отменено) → FILTER (или CASE внутри агрегата). WHERE так не умеет: он один на весь запрос.

Проверка: нужно в одной строке вывести «всего заказов» и «сколько из них оплачено». Чем считать?

Верно: WHERE отфильтровал бы все строки до оплаченных, и «всего» посчиталось бы неправильно. Нужны два разных счётчика рядом → FILTER (или SUM(CASE …)).

§5 Практика · 12 упражнений

решено 0 / 12

Пиши запрос, жми «Проверить» (⌘/Ctrl+Enter). Сначала сам — усилие памяти строит навык. Подсказка и решение рядом, бери их только когда реально застрял. Держи схему под рукой. Внутри намеренно вплетены агрегация и NULL с прошлых тем — это повторение.

1. Разминка с CASE: для каждого товара выведи name и метку наличия — 'in stock', если stock > 0, иначе 'out of stock'.

2. Ценовые «корзины»: выведи name, price и метку tier: 'cheap' при price < 20, 'medium' при price <= 50, иначе 'expensive'. Следи за порядком веток!

3. CASE без ELSE: выведи order_id, status и столбец done — 'done', если статус paid или shipped. Для остальных статусов done должен быть NULL (не пиши ELSE).

4. Ловушка деления на живых данных. Для каждого товара выведи name, stock, целочисленное деление stock / 2 и точное stock::numeric / 2. Найди товары с нечётным stock — там видно, как первый столбец теряет дробь.

5. Склей строку: для каждого покупателя выведи name и столбец label вида name || ' — ' || country. Запусти и заметь: у кого country = NULL — весь label станет NULL.

6. Почини упражнение 5: тот же label, но вместо пустой страны подставь 'unknown', чтобы строка не обнулялась. (повтор Темы 02)

7. Даты + агрегация: сколько заказов создано в каждом месяце? Выведи номер месяца (из created_at) и количество, отсортируй по месяцу по возрастанию. (важен порядок)

8. Многоветочный CASE (так умеет только CASE — FILTER живёт лишь внутри агрегата). Для каждого заказа выведи order_id, status и столбец stage: 'done' для paid/shipped, 'active' для pending, 'lost' для cancelled.

9. Условный подсчёт: одной строкой по всем заказам посчитай paid, cancelled и shipped — три числа рядом. Одним WHERE так нельзя (он один на весь запрос) — нужен FILTER (или CASE внутри агрегата).

10. А здесь условие одно на всё → нужен WHERE, не FILTER. Только по paid-заказам посчитай, сколько их у каждого покупателя. Выведи customer_id и количество.

11. Тут WHERE не подойдёт — нужны два разных счётчика рядом. Для каждой категории выведи category_id, сколько всего товаров и сколько из них нет в наличии (stock = 0).

12. Капстоун. Только по paid-заказам: для каждого покупателя (name) посчитай суммарную выручку (сумма quantity * unit_price) и добавь метку tier через CASE: 'VIP', если сумма > 150, иначе 'regular'. Отсортируй по сумме по убыванию. (важен порядок)

Частые ловушки

1) Порядок веток в CASE: срабатывает первая истинная — узкие условия ставь выше. 2) CASE без ELSE молча даёт NULL. 3) int / int режет дробь — приводи один операнд к numeric. 4) || с NULL обнуляет всю строку — спасает COALESCE. 5) Одно условие на весь запрос → WHERE; несколько разных счётчиков рядом → FILTER.