Окна, UNION, INTERSECT и EXCEPT

Окно

GROUP BY схлопывает строки в одну на группу. Оконная функция считает по группе, но строки не трогает: у каждой остаётся своя цена, имя, дата.

Окно пишут в SELECT (и в ORDER BY): функция, затем OVER (…). Пустые скобки — считает по всему результату.

PostgreSQL считает окно вместе с SELECT, после WHERE, GROUP BY и HAVING. В WHERE псевдоним колонки окна поставить нельзя.

PARTITION BY

PARTITION BY режет строки на группы внутри окна, как ключ GROUP BY, но снова без схлопывания. COUNT(*) OVER (PARTITION BY category_id) пишет размер категории в каждую её строку.

SELECT p.name, p.price,
       COUNT(*) OVER (PARTITION BY p.category_id) AS products_n
FROM products p
WHERE p.category_id = 3
ORDER BY p.name;

Так же в OVER ставят AVG, SUM, MIN, MAX. Если добавить ORDER BY внутри OVER, агрегат становится нарастающим: каждая строка видит себя и всех, кто раньше по этому порядку.

ROW_NUMBER

ROW_NUMBER() нумерует строки с единицы. Порядок задаёт ORDER BY внутри OVER. Ничьей нет: при равной цене решает следующий ключ, иначе номер всё равно разный.

SELECT p.name, p.price,
       ROW_NUMBER() OVER (ORDER BY p.price DESC, p.name) AS rn
FROM products p
WHERE p.category_id = 3
ORDER BY rn;

С PARTITION BY нумерация начинается заново в каждой группе: «первый по цене в категории», «первый заказ клиента».

RANK и DENSE_RANK

RANK и DENSE_RANK тоже нумеруют, но одинаковые значения получают одно место. Разница — что идёт дальше.

Два клиента с тремя заказами: оба на первом месте. RANK следующее место делает третьим (единица и двойка заняты). DENSE_RANK — вторым, дырки нет.

SELECT c.name,
       COUNT(o.id) AS orders_n,
       RANK() OVER (ORDER BY COUNT(o.id) DESC) AS place,
       DENSE_RANK() OVER (ORDER BY COUNT(o.id) DESC) AS dense_place
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Москва'
GROUP BY c.name
ORDER BY orders_n DESC, c.name;

COUNT(o.id), не COUNT(*): у Павла нет заказов, после LEFT JOIN строка клиента есть, а o.id пустой.

LAG и LEAD

LAG берёт значение из предыдущей строки окна, LEAD — из следующей. Порядок снова задаёт ORDER BY внутри OVER. У первой строки LAG будет NULL, у последней — LEAD.

SELECT o.id, o.order_date,
       LAG(o.order_date) OVER (
         PARTITION BY o.customer_id
         ORDER BY o.order_date, o.id
       ) AS prev_date
FROM orders o
WHERE o.customer_id = 1
ORDER BY o.order_date, o.id;

Разницу дат считают как order_date - prev_date. Строку с пустым prev_date обычно отбрасывают снаружи — в том же WHERE её ещё нет.

Фильтр по окну

Чтобы оставить «первую строку группы», номер считают в CTE, а фильтр пишут снаружи.

WITH ranked AS (
  SELECT p.name, p.price,
         ROW_NUMBER() OVER (ORDER BY p.price DESC, p.name) AS rn
  FROM products p
  WHERE p.category_id = 3
)
SELECT name, price
FROM ranked
WHERE rn = 1;

Внутри одного SELECT написать WHERE rn = 1 нельзя: колонка rn появляется вместе со списком колонок, а WHERE уже прошёл.

UNION и UNION ALL

UNION ставит друг на друга результаты двух запросов. Число колонок и типы должны совпадать. Имена колонок берёт верхний запрос.

UNION убирает повторы, UNION ALL оставляет все строки.

SELECT name
FROM products
WHERE category_id = 4
UNION
SELECT name
FROM products
WHERE price < 800
ORDER BY name;

Подушка и дом, и дешевле 800. UNION ALL даст её дважды, UNION — один раз.

SELECT name
FROM products
WHERE category_id = 4
UNION ALL
SELECT name
FROM products
WHERE price < 800
ORDER BY name;

ORDER BY пишут один раз, в конце, ко всему результату.

INTERSECT

INTERSECT оставляет строки, которые есть в обоих запросах. Повторы убирает.

SELECT name
FROM products
WHERE category_id = 4
INTERSECT
SELECT name
FROM products
WHERE price < 800;

EXCEPT

EXCEPT берёт строки первого запроса, которых нет во втором. Порядок запросов важен: это не симметрия.

SELECT name
FROM products
WHERE category_id = 4
EXCEPT
SELECT name
FROM products
WHERE price < 800
ORDER BY name;

Тот же смысл часто пишут через NOT EXISTS или LEFT JOIN … IS NULL. EXCEPT короче, когда сравнивают два готовых множества строк.

Всё вместе

Самый дорогой товар каждой категории: нумерация внутри категории, затем оставить место 1.

WITH ranked AS (
  SELECT
    c.name AS category,
    p.name AS product,
    p.price,
    ROW_NUMBER() OVER (
      PARTITION BY p.category_id
      ORDER BY p.price DESC, p.name
    ) AS rn
  FROM products p
  JOIN categories c ON c.id = p.category_id
)
SELECT category, product, price
FROM ranked
WHERE rn = 1
ORDER BY category;

Если вместо ROW_NUMBER взять RANK, две одинаковые максимальные цены обе получат место 1 — в категории останется две строки.

Дальше в справочнике — функции дат, строк и COALESCE.