CASE, даты, строки и NULL

CASE

CASE выбирает значение по условию: как несколько IF в одной колонке. Ветки смотрят сверху вниз, срабатывает первая истинная. Если ни одна не подошла — берут ELSE, а без него будет NULL.

Три товара с разными ценами: подушка 650, «Чистый код» 2100, ноутбук 89 000. Пороги как в задаче: меньше 1000 — бюджет, меньше 10 000 — средний, иначе премиум.

SELECT name, price
FROM products
WHERE id IN (2, 9, 11)
ORDER BY price;

SELECT name, price,
       CASE
         WHEN price < 1000 THEN 'бюджет'
         WHEN price < 10000 THEN 'средний'
         ELSE 'премиум'
       END AS price_group
FROM products
WHERE id IN (2, 9, 11)
ORDER BY price;

Если переставить ветки и сначала написать WHEN price < 10000, подушка тоже станет «средним»: 650 < 10000 истинно, до «бюджета» выполнение не дойдёт. Порядок веток — часть условия.

Когда сравнивают одно и то же поле с константами, пишут короче: CASE колонка WHEN значение THEN …. Это равенство, не «меньше» и не LIKE.

Три заказа Анны: paid, shipped, new.

SELECT id, order_date, status
FROM orders
WHERE customer_id = 1
ORDER BY id;

SELECT id, status,
       CASE status
         WHEN 'paid' THEN 'оплачен'
         WHEN 'shipped' THEN 'отправлен'
         WHEN 'cancelled' THEN 'отменён'
         ELSE 'новый'
       END AS status_label
FROM orders
WHERE customer_id = 1
ORDER BY id;

NULL

NULL — не число и не текст, а «значения нет». Сравнение = NULL никогда не истинно. Проверяют IS NULL и IS NOT NULL.

У Павла Морозова и Кирилла Белова заказов нет. LEFT JOIN оставляет клиента, а поля заказа будут пустыми:

SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (1, 8, 11)
ORDER BY c.name, o.id;

COUNT(*) считает строки, COUNT(колонка) — только непустые значения этой колонки. Москва: Анна (3 заказа) + Мария (2) + Елена (3) + Павел (строка без заказа). В соединении 9 строк, заполненных o.id — 8.

SELECT COUNT(*) AS rows_n,
       COUNT(o.id) AS orders_n
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Москва';

Здесь нет GROUP BY: агрегат считает по всей выборке после WHERE и возвращает одну строку. Это не группировка по клиенту, а итог по Москве целиком.

COALESCE

COALESCE(a, b) берёт первое значение, которое не NULL. Аргументов может быть несколько: COALESCE(a, b, c).

Тот же LEFT JOIN для Павла и Кирилла: вместо пустого статуса подставляем текст.

SELECT c.name,
       COALESCE(o.status, 'нет заказов') AS status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (8, 11)
ORDER BY c.name;

С суммой то же самое. Если к месяцу без заказов сделать LEFT JOIN, SUM(…) даст NULL, а в условии чаще ждут 0: COALESCE(SUM(quantity * unit_price), 0).

COALESCE — короткий CASE WHEN a IS NOT NULL THEN a ELSE b END. Для подстановки вместо пустого его пишут чаще, чем CASE.

Даты

Дата в PostgreSQL — тип date: год, месяц, день, без часов. Литерал пишут так: DATE '2024-06-01'. Вычитание двух дат даёт целое число дней.

Заказы Анны: 1 июня, 12 июля, 10 сентября.

SELECT id, order_date,
       EXTRACT(MONTH FROM order_date) AS month_n,
       EXTRACT(ISODOW FROM order_date) AS week_day,
       order_date - DATE '2024-06-01' AS days_from_june
FROM orders
WHERE customer_id = 1
ORDER BY order_date, id;

Фильтр «весь июль» безопаснее полуинтервалом, а не EXTRACT(MONTH) = 7 вслепую по разным годам: order_date >= DATE '2024-07-01' AND order_date < DATE '2024-08-01'.

date_trunc, TO_CHAR и INTERVAL

date_trunc('month', дата) обрезает дату до начала месяца: 12 июля становится 1 июля. Тип результата — timestamp, поэтому для сравнения с date часто пишут ::date.

TO_CHAR форматирует дату в текст. В задачах месяц обычно просят как YYYY-MM.

SELECT id, order_date,
       date_trunc('month', order_date)::date AS month_start,
       TO_CHAR(order_date, 'YYYY-MM') AS month
FROM orders
WHERE customer_id = 1
ORDER BY order_date, id;

К дате прибавляют промежуток INTERVAL: DATE '2024-06-01' + INTERVAL '1 month' даёт 1 июля, + INTERVAL '7 days' — 8 июня.

SELECT DATE '2024-06-01' AS d,
       (DATE '2024-06-01' + INTERVAL '1 month')::date AS plus_month,
       (DATE '2024-06-01' + INTERVAL '7 days')::date AS plus_week;

«Следующий месяц» для полуинтервала: начало июля — это DATE '2024-06-01' + INTERVAL '1 month'. Заказы июня тогда: >= 1 июня и < 1 июля.

generate_series

generate_series строит ряд дат (или чисел). Оба конца входят в ряд. Шаг задаёт INTERVAL.

Сетка месяцев с июня по сентябрь 2024 — четыре первых числа:

SELECT gs::date AS month_start
FROM generate_series(
  DATE '2024-06-01',
  DATE '2024-09-01',
  INTERVAL '1 month'
) AS gs
ORDER BY 1;

У Анны заказы в июне, июле и сентябре. Августа нет. Если группировать только заказы, августа в результате не будет. Сетку оставляют слева и к ней делают LEFT JOIN:

SELECT
  TO_CHAR(g.month_start, 'YYYY-MM') AS month,
  COUNT(o.id) AS orders_n
FROM generate_series(
  DATE '2024-06-01',
  DATE '2024-09-01',
  INTERVAL '1 month'
) AS g(month_start)
LEFT JOIN orders o
  ON o.customer_id = 1
 AND date_trunc('month', o.order_date)::date = g.month_start::date
GROUP BY g.month_start
ORDER BY g.month_start;

COUNT(o.id) для пустого месяца даёт 0 сам: присоединять нечего, колонка пустая. Для SUM пустой месяц даст NULL — тогда пишут COALESCE(SUM(…), 0).

Ряд может быть дневным: INTERVAL '1 day'. Числовой ряд тоже бывает: generate_series(2019, 2022) — годы 2019, 2020, 2021, 2022.

Строки

Строки склеивают оператором ||. Если один из кусков NULL, весь результат NULL.

SELECT c.name,
       c.name || ' (' || c.city || ')' AS label
FROM customers c
WHERE c.city = 'Казань'
ORDER BY c.name;

У Павла заказа нет. name || ': ' || status даст NULL, потому что status пустой. Либо подставляют COALESCE внутрь склейки, либо берут CONCAT: в PostgreSQL он пропускает NULL и не обнуляет всю строку.

SELECT c.name,
       c.name || ': ' || o.status AS with_pipe,
       CONCAT(c.name, ': ', COALESCE(o.status, 'нет заказов')) AS with_concat
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id = 8;

LENGTH считает символы, не байты: у «Война и мир» длина 11, пробелы входят. LOWER / UPPER меняют регистр. ROUND(число, 2) округляет: среднее цен «Дома» (650 + 1900 + 2800 + 5400) / 4 = 2687.5 → 2687.50.

SELECT ROUND(AVG(price)::numeric, 2) AS avg_price
FROM products
WHERE category_id = 4;

Всё вместе

Заказы Анны: месяц текстом, русская подпись статуса, дни от первого заказа.

SELECT
  o.id,
  TO_CHAR(o.order_date, 'YYYY-MM') AS month,
  CASE o.status
    WHEN 'paid' THEN 'оплачен'
    WHEN 'shipped' THEN 'отправлен'
    ELSE 'новый'
  END AS status_label,
  o.order_date - DATE '2024-06-01' AS days_from_first
FROM orders o
WHERE o.customer_id = 1
ORDER BY o.order_date, o.id;

Коротко: CASE — ветки, COALESCE — вместо NULL, date_trunc и TO_CHAR — месяц, generate_series — календарная сетка, чтобы пустые периоды не пропадали.

Дальше — рекурсивный WITH.