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

В прошлом уроке мы сворачивали строки в итоги. Но у магазина есть вопросы, на которые одним GROUP BY не ответить. «Какие книги дороже средней?» требует сначала посчитать среднее, а потом сравнить с ним. «Самая дорогая книга в каждом жанре» требует итог по жанру, но не теряя сами книги. Сегодня три инструмента для таких вопросов: подзапросы, WITH и оконные функции. Примеры работают на учебной базе магазина.

Запрос внутри запроса

Какие книги дороже средней? Средняя цена это отдельный вопрос к базе. Можно спросить в два приёма: узнать среднее, записать на бумажку, вставить число во второй запрос. А можно вставить первый вопрос прямо во второй, в скобках:

SELECT title, price
FROM books
WHERE price > (SELECT avg(price) FROM books)
ORDER BY price DESC;

       title       |  price
-------------------+---------
 Совершенный код   | 2490.00
 Грокаем алгоритмы | 1290.00
(2 rows)

Запрос в скобках называют подзапросом. PostgreSQL сначала считает среднее, 836.25, а потом подставляет его в условие.

Подзапрос в сравнении обязан вернуть одно значение. Если строк окажется больше, база откажет:

SELECT title FROM books
WHERE price > (SELECT price FROM books WHERE genre = 'роман');

ERROR:  more than one row returned by a subquery used as an expression

Романов два, и с какой из двух цен сравнивать, непонятно.

Подзапрос как список

Когда подзапрос возвращает столбец значений, его ставят после IN. Покупатели, у которых есть доставленный заказ:

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'delivered');

 name
-------
 Ольга
(1 row)

EXISTS: есть ли хоть одна строка

EXISTS спрашивает не «что вернул подзапрос», а «вернул ли он хоть что-то». Покупатели без заказов:

SELECT name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

   name
----------
 Светлана
(1 row)

Подзапрос здесь ссылается на c.id из внешнего запроса, поэтому выполняется для каждого покупателя заново. Такой подзапрос называют коррелированным. В уроке про JOIN ту же задачу решал LEFT JOIN ... IS NULL. Чем эти способы отличаются и почему NOT IN здесь опасен, разобрано в уроке про анти-соединения.

Подзапрос как таблица

Подзапрос можно поставить и в FROM, тогда его результат читается как временная таблица. Ей обязательно дают имя:

SELECT genre, round(avg(pages)) AS avg_pages
FROM (SELECT genre, pages FROM books WHERE year > 1900) AS modern
GROUP BY genre
ORDER BY genre;

Больше примеров подзапросов во всех трёх местах есть в разборе на собеседовании.

WITH: черновик по шагам

Подзапросы вкладываются друг в друга, и читать их приходится изнутри наружу. На второй-третьей вложенности это мучение. Решение похоже на черновик по математике: сначала «пусть A равно…», потом ответ через A.

Задача: заказы, сумма которых больше средней суммы заказа. Шаг первый, суммы заказов. Шаг второй, сравнение со средним по этим суммам:

WITH order_totals AS (
  SELECT order_id, sum(qty * price) AS total
  FROM order_items
  GROUP BY order_id
)
SELECT o.id, o.status, t.total
FROM orders o
JOIN order_totals t ON t.order_id = o.id
WHERE t.total > (SELECT avg(total) FROM order_totals)
ORDER BY t.total DESC;

 id |  status   |  total
----+-----------+---------
  3 | shipped   | 2740.00
  1 | delivered | 1980.00
(2 rows)

WITH имя AS (запрос) объявляет CTE, именованный подзапрос. Дальше в запросе order_totals ведёт себя как таблица, и обратиться к ней можно дважды: в JOIN и в подзапросе со средним. CTE живёт только внутри своего запроса. Несколько CTE подряд и рекурсивный WITH RECURSIVE для иерархий разобраны в уроке для собеседования про CTE.