SQL: JOIN, CTE, оконные функции, индексы

Работа с выборками: от базового JOIN до оконных функций и того, когда индекс молча перестаёт использоваться. Все примеры ниже — не абстрактные, а прогнаны на реальном Postgres на трёх маленьких таблицах, чтобы результат был не "примерно понятен", а виден построчно.

SELECT, GROUP BY/HAVING, все виды JOIN

Разница между WHERE и HAVING — это разница в том, ДО или ПОСЛЕ группировки применяется условие. WHERE отфильтровывает отдельные строки ещё до того, как они будут сгруппированы — поэтому в нём нельзя использовать агрегатные функции вроде SUM/COUNT, агрегатов на этом этапе просто ещё не существует. HAVING работает уже с готовыми группами, сформированными GROUP BY, — именно там законное место для условий на агрегатах, например HAVING SUM(amount) > 1000.

Для JOIN важнее всего разница в том, что происходит со строками БЕЗ пары. Возьмём три таблицы:

customers: (1, Аня), (2, Борис), (3, Вера)
orders:    (10, customer_id=1, 100), (11, customer_id=1, 200), (12, customer_id=2, 50)

У Ани два заказа, у Бориса один, у Веры — ни одного. Вот что реально возвращает LEFT JOIN на этих данных:

SELECT c.id, c.name, o.id AS order_id, o.amount
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id;

id | name  | order_id | amount
1  | Аня   | 10       | 100
1  | Аня   | 11       | 200
2  | Борис | 12       | 50
3  | Вера  | NULL     | NULL

Два важных факта видны прямо в этой таблице результата, не в теории. Во-первых, Аня физически размножилась на 2 строки — по одной на каждый её заказ: LEFT JOIN не "прикрепляет список заказов" к клиенту одной строкой, он честно создаёт отдельную строку результата на каждое совпадение справа. Во-вторых, Вера осталась в результате, хотя у неё нет ни одного заказа — вместо связанных полей она получила NULL.

INNER JOIN на тех же данных вернул бы только 3 строки (Аня×2, Борис×1) — Веру он бы просто выбросил, потому что для неё нет совпадения ни в одной из таблиц. RIGHT JOIN — то же самое, что LEFT JOIN, только с переставленными местами таблицами (сохраняются все строки ПРАВОЙ). FULL JOIN — объединение обоих сразу: сохраняются и клиенты без заказов, и (гипотетически) заказы без клиента.

Ровно то, что видно на NULL у Веры, даёт готовый рецепт классической практической задачи "найти клиентов без единого заказа" — LEFT JOIN плюс фильтр именно на отсутствие пары:

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

На данных выше это вернёт ровно одну строку — (3, Вера). Логика простая: LEFT JOIN сохраняет всех клиентов, у отсутствующих заказов подставляет NULL в o.id, а WHERE o.id IS NULL оставляет только тех, у кого этот NULL реально появился — то есть только тех, для кого справа не нашлось ни одной строки.

Подзапросы, CTE, оконные функции

CTE (WITH имя AS (...)) — просто именованный временный результат, который можно использовать в основном запросе как обычную таблицу; главная практическая ценность — читаемость и возможность сослаться на один и тот же промежуточный результат несколько раз, не повторяя один и тот же подзапрос текстом. WITH RECURSIVE — частный случай CTE, который ссылается сам на себя, что нужно для обхода иерархий (дерево категорий, организационная структура).

Оконная функция (OVER (PARTITION BY ... ORDER BY ...)) устроена принципиально иначе, чем GROUP BY: она НЕ схлопывает строки результата в одну на группу, а считает свою функцию (агрегат, ранжирование) поверх каждой строки, сохраняя все исходные строки на месте. Возьмём таблицу товаров:

products: (1,обувь,50) (2,обувь,80) (3,обувь,80) (4,обувь,10) (5,сумки,30) (6,сумки,60)

Реальный результат ROW_NUMBER() и RANK() над одними и теми же данными, отсортированными по продажам внутри категории:

SELECT id, category, sales,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn,
       RANK()       OVER (PARTITION BY category ORDER BY sales DESC) AS rnk
FROM products ORDER BY category, sales DESC;

id | category | sales | rn | rnk
2  | обувь    | 80    | 1  | 1
3  | обувь    | 80    | 2  | 1    <- та же цена 80, но RANK повторил 1, а ROW_NUMBER пошёл дальше
1  | обувь    | 50    | 3  | 3    <- RANK перескочил через 2 (пропуск из-за дубля выше)
4  | обувь    | 10    | 4  | 4
6  | сумки    | 60    | 1  | 1
5  | сумки    | 30    | 2  | 2

Разница видна ровно на строках с одинаковыми продажами (два товара по 80): ROW_NUMBER() даёт им РАЗНЫЕ номера подряд (1 и 2), потому что просто нумерует строки без оглядки на равенство значений. RANK() даёт им ОДИНАКОВЫЙ ранг (1 и 1) — потому что для него это formally равные по рангу строки, — но следующий за ними товар получает ранг 3, а не 2: RANK() "тратит" номера мест на дубли, ROW_NUMBER() — никогда.

Почему "топ-3 товара в каждой категории" нельзя решить через GROUP BY ... LIMIT 3. LIMIT ограничивает ВЕСЬ результат запроса целиком, а не результат внутри каждой группы отдельно — GROUP BY category схлопнёт данные до одной строки на категорию (для этого и нужен агрегат типа MAX/COUNT), а LIMIT 3 после этого просто обрежет список категорий до трёх, что вообще не то же самое, что "топ-3 внутри категории". Правильное решение — сначала пронумеровать строки оконной функцией внутри каждой группы, потом отфильтровать по номеру:

WITH ranked AS (
  SELECT p.*, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products p
)
SELECT * FROM ranked WHERE rn <= 2;

На тех же 6 товарах (2 категории) этот запрос реально возвращает ровно 4 строки — топ-2 обуви (id 2 и 3, оба с sales=80) и топ-2 сумок (id 6 и 5) — то есть именно "по N лучших на каждую группу", а не "N лучших групп" и не "N лучших строк вообще".

Индексы: B-tree, когда не используются

Индекс — это отдельная структура данных (в Postgres по умолчанию B-tree), которая хранит отсортированные значения колонки вместе со ссылками на реальные строки — это превращает поиск из линейного перебора всей таблицы в логарифмический поиск по дереву.

Составной индекс на (A, B) ускоряет запросы по A или по паре A и B, но НЕ по одному B без A — та же логика, что у бумажной телефонной книги, отсортированной по фамилии, а внутри фамилии по имени: искать по одной фамилии легко (это первый уровень сортировки), а искать только по имени без фамилии всё ещё требует перебора всей книги, потому что имена внутри разных фамилий не отсортированы между собой.

Индексы — это не "чем больше, тем лучше": каждый индекс замедляет запись, потому что обновляется при КАЖДОМ INSERT/UPDATE/DELETE затронутой строки, и занимает дополнительное место на диске — это прямой компромисс между скоростью чтения и скоростью записи, а не бесплатное ускорение.

Индекс существует, но реально не используется в двух хорошо проверяемых на практике случаях — оба подтверждены прогоном EXPLAIN на реальной таблице из 1000 строк с обычным индексом CREATE INDEX idx_email ON users(email):

EXPLAIN SELECT * FROM users WHERE LOWER(email) = 'user1@mail.com';
-- Seq Scan on users  (полное сканирование таблицы, индекс НЕ использован)
--   Filter: (lower(email) = 'user1@mail.com'::text)

EXPLAIN SELECT * FROM users WHERE email = 'user1@mail.com';
-- Bitmap Heap Scan on users  (индекс использован)
--   ->  Bitmap Index Scan on idx_email
--         Index Cond: (email = 'user1@mail.com'::text)

Причина первого случая: обычный индекс хранит отсортированные значения САМОЙ колонки email, а не результата функции LOWER(email) — Postgres физически не может использовать это дерево для поиска по значению функции, которое в индексе нигде не хранится. Решение — либо функциональный индекс CREATE INDEX ON users(LOWER(email)), либо сравнение без функции над колонкой.

Второй классический случай (не показан выше, но по той же причине) — LIKE '%текст' с ведущим %: B-tree отсортирован по началу строки, а не по произвольной подстроке где-то внутри, поэтому неизвестно, с какого места дерева начинать поиск, и Postgres снова уходит в полное сканирование.