Подзапросы и EXISTS

Мира · маркетолог магазина настолок «Кубик»

Хочу значок «премиум» в каталоге. Только честно: варгейм за пять тысяч это норма, а карточная игра за тысячу уже роскошь. И пришли список всех, кто хоть раз что-то возвращал, хочу спросить у них, что пошло не так.

Чтобы отметить «премиум», нужно сначала узнать среднюю цену, а потом сравнить с ней каждую игру. Два шага, и второй зависит от первого. Можно выполнить первый запрос, переписать число на бумажку и вставить во второй. А можно поручить это базе: вложить один запрос в другой.

Запрос в скобках

Подзапрос это SELECT в скобках, который стоит внутри другого запроса. База сначала выполняет его, а потом подставляет результат туда, где стоят скобки.

Средняя цена игры в каталоге:

SELECT round(avg(price), 2) FROM products;

 round
---------
 2283.44

Теперь вместо числа подставим сам запрос:

SELECT title, genre, price
FROM products
WHERE price > (SELECT avg(price) FROM products);

Подзапрос вернул одно значение, одну строку и один столбец. Такой подзапрос называют скалярным, и его можно ставить везде, где ждут обычное число или строку. Дороже средней по каталогу 51 игра. Но Мира просила честно: сравнивать варгейм за 4671 ₽ в среднем с карточной игрой за 636 ₽ бессмысленно.

Сравнение внутри жанра

Нужна средняя не по всему каталогу, а по жанру той игры, которую мы сейчас проверяем. Значит, подзапрос должен знать, какая строка внешнего запроса сейчас на очереди:

SELECT p.title, p.genre, p.price
FROM products p
WHERE p.price > (
  SELECT avg(q.price)
  FROM products q
  WHERE q.genre = p.genre
);

Внутри подзапроса стоит q.genre = p.genre, где p это строка снаружи. Такой подзапрос называют коррелированным: он пересчитывается для каждой игры, как вопрос, который продавец задаёт себе у каждой полки: «а сколько в среднем стоит этот жанр?».

Премиумом стали 53 игры. Среди карточных, например, «Крепость Степи» за 1140 ₽: по меркам каталога дёшево, по меркам карточных игр почти вдвое дороже средней.