Подзапрос против CTE в SQL: та же логика, которую вы можете проверить

1. Что такое подзапрос и четыре места, в которых он находится

Подзапрос – это SELECT написано внутри другого оператора, заключенного в скобки. Что это такое для зависит от того, куда вы его поставите, а мест всего четыре.

Все остальное на этой странице — одно из этих четырех с вариациями. Зная, какой из четырех вы пишете, вы сразу же поймете, какую форму должен вернуть подзапрос, а большинство ошибок подзапроса — это несоответствие формы: список, в котором ожидалось одно значение, или один столбец, в котором была таблица.

2. Скалярный подзапрос: одно значение

Прежде чем объяснить: вы хотите, чтобы заказы стоили больше среднего заказа. Скажи, почему ты не можешь писать WHERE revenue > AVG(revenue).

Потому что WHERE выполняется перед любым агрегированием, поэтому на момент его оценки еще нет среднего значения. Подзапрос решает эту проблему, сначала вычисляя среднее значение в виде отдельного полного запроса.

SELECT COUNT(*) AS orders, SUM(units*unit_price) AS revenue
FROM orders
WHERE units*unit_price > (SELECT AVG(units*unit_price) FROM orders);

8 | 6475

Восемь из шестнадцати заказов общей стоимостью 6475 долларов. Само среднее составляет 9890 ÷ 16, что составляет 618,125, а восьмёрки — те, что выше него.

Подзапрос, который возвращает ровно одно значение, называется скаляр, и его можно использовать везде, где может находиться число. Сюда входит список выбора, в котором рядом с каждой строкой помещается цифра всей таблицы:

SELECT order_id,
       units*unit_price AS revenue,
       ROUND(100.0*(units*unit_price)
             / (SELECT SUM(units*unit_price) FROM orders), 1) AS pct
FROM orders ORDER BY revenue DESC LIMIT 3;

1010 | 1100 | 11.1
1001 |  880 |  8.9
1015 |  880 |  8.9

Самый крупный заказ составляет 11,1% от всего. Проверьте это: 1100 ÷ 9890 равно 0,1112. Подзапрос выполнялся один раз, а не один раз для каждой строки, поскольку он не зависит от строки, рядом с которой он находится. Это различие составляет весь четвертый раздел.

3. IN и NOT IN, которые ничего не возвращают.

IN принимает список, а не значение, поэтому подзапрос возвращает один столбец и столько строк, сколько ему нужно.

SELECT COUNT(*) FROM orders
WHERE order_id IN (SELECT order_id FROM cancelled);

2

Два из шестнадцати заказов находятся в списке отмененных. Теперь очевидный следующий шаг, и именно ради него нужно притормозить.

Перед пояснением: отмененная таблица содержит три строки, 1004, 1009 и один NULL. Предскажите, сколько из шестнадцати заказов вернутся не отмененными.

SELECT COUNT(*) FROM orders
WHERE order_id NOT IN (SELECT order_id FROM cancelled);

0

Ноль. Не четырнадцать. Каждая строка исчезла, и ничего не произошло.

Причина в том, что x NOT IN (a, b, NULL) расширяется до x <> a AND x <> b AND x <> NULLи сравнение с NULL никогда не является правдой, это неизвестно. Ан AND цепочка, содержащая неизвестное, никогда не может оказаться истинной, поэтому ни одна строка не подходит. Полный отчет о том, почему неизвестный ведет себя таким образом, находится в NULL в SQL, и это самое дорогое его последствие.

Два исправления, оба проверены на одних и тех же данных:

-- exclude the NULLs from the list
WHERE order_id NOT IN (SELECT order_id FROM cancelled WHERE order_id IS NOT NULL)
14

-- or use NOT EXISTS, which is immune by construction
WHERE NOT EXISTS (SELECT 1 FROM cancelled c WHERE c.order_id = o.order_id)
14

Предпочитать NOT EXISTS. Его нельзя нарушить, если в следующем месяце в списке появится нуль, обычно он также планирует, по крайней мере, и читается так, как вы имеете в виду: нет соответствующей строки. Держать IN для положительного случая, когда ноль в списке безвреден.

4. Коррелирующие подзапросы: один запуск на строку.

Подзапрос, который ссылается на столбец из внешнего запроса, не может быть вычислен один раз, поскольку его ответ различен для каждой внешней строки. Это коррелированный подзапрос.

Вопрос: какие заказы превосходят средние для своего региона? Вот средние показатели по региону, так что есть с чем сравнить.

East  | 4 orders | 3040 | 760.00
North | 5 orders | 2495 | 499.00
South | 4 orders | 2670 | 667.50
West  | 3 orders | 1685 | 561.67

SELECT o.order_id, o.region, o.units*o.unit_price AS revenue
FROM orders o
WHERE o.units*o.unit_price > (
  SELECT AVG(i.units*i.unit_price)
  FROM orders i
  WHERE i.region = o.region)
ORDER BY o.region, revenue DESC;

1010 | East  | 1100
1001 | North |  880
1014 | North |  510
1015 | South |  880
1002 | South |  850
1012 | West  |  765

Шесть заказов. Проверьте первый: среднее значение Норта равно 499, а заказы Норта равны 880, 510, 440, 425 и 240, поэтому ровно два его очищают. Внутренний запрос выполнялся шестнадцать раз, по одному разу для каждой внешней строки, каждый раз фильтруясь по региону этой строки.

Далее следуют две вещи. Читается хорошо, потому что WHERE i.region = o.region линия говорит вслух, что означает «свой регион». И это самая медленная фигура на этой странице, потому что это петля: на шестнадцати строках никто не замечает, на шестнадцати миллионах это разница между секундой и перерывом на кофе. В восьмом разделе есть версия, выполняющая ту же работу за один проход.

5. Производная таблица и места, где она перестает читаться

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

SELECT region, revenue FROM (
  SELECT region, SUM(units*unit_price) AS revenue
  FROM orders GROUP BY 1
) t
WHERE revenue > 2500
ORDER BY revenue DESC;

East  | 3040
South | 2670

Это работает, и это нормально на одном уровне. Проблемы начинаются с двух, потому что порядок чтения инвертируется: запрос выполняется изнутри наружу и записывается снаружи внутрь, поэтому вы прокручиваете вниз, чтобы найти начало. На трёх уровнях, с объединением посередине, его вообще никто не читает, включая того, кто его написал.

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

6. CTE: тот же запрос с именем

Общее табличное выражение — это тот же подзапрос, перемещенный наверх и получивший имя с помощью WITH. Вот производная таблица из пятого раздела, не изменившаяся по смыслу:

WITH by_region AS (
  SELECT region, SUM(units*unit_price) AS revenue
  FROM orders GROUP BY 1
)
SELECT region, revenue
FROM by_region
WHERE revenue > 2500
ORDER BY revenue DESC;

East  | 3040
South | 2670

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

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

WITH big AS (
  SELECT * FROM orders WHERE units*unit_price > 600
), by_rep AS (
  SELECT rep, COUNT(*) AS orders, SUM(units*unit_price) AS revenue
  FROM big GROUP BY 1
)
SELECT rep, orders, revenue FROM by_rep ORDER BY revenue DESC;

Priya Shah | 3 | 2440
Owen Park  | 3 | 2390
Dana Reyes | 1 |  880
Sam Okafor | 1 |  765

Восемь крупных заказов, распределенных в четырех направлениях. При чтении сверху вниз анализ описывается в том порядке, в котором кто-то объяснил бы его вслух: сначала возьмите большие заказы, затем сгруппируйте их по представителям, затем отсортируйте. Вложенная версия того же самого говорит, что сначала последний шаг.

Именованный шаг также можно использовать несколько раз, чего не может сделать производная таблица. Руководство по CTE идет дальше в синтаксис и строит запрос на проверяемых этапах; на этой странице рассказывается о том, когда использовать его вместо подзапроса.

7. Считаем каждый шаг

Это практическая причина предпочитать CTE, и она важнее, чем просто читаемость.

Когда вложенный запрос возвращает неправильный ответ, у вас есть одно число, последнее, и нет возможности определить, какой слой пошёл не так. С помощью именованных шагов вы можете указать SELECT COUNT(*) в любом из них, изменив последнюю строку:

WITH big AS (
  SELECT * FROM orders WHERE units*unit_price > 600
), by_rep AS (
  SELECT rep, COUNT(*) AS orders, SUM(units*unit_price) AS revenue
  FROM big GROUP BY 1
)
SELECT COUNT(*) FROM big;      -- 8
-- SELECT COUNT(*) FROM by_rep;   -- 4

Пришло шестнадцать заказов, восемь прошли фильтр, четыре строки вышли из группировки. Запишите эти три числа, прежде чем запускать реальный запрос, а затем сравните. Фильтр, который должен был удалить несколько и большую часть таблицы, сразу же объявляет о себе, а группировка, которая создает больше строк, чем категорий, означает, что соединение что-то умножило.

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

8. Когда оконная функция превосходит обе

Связанный подзапрос из четвертого раздела спрашивал: превышает ли этот порядок средний показатель по региону? Вот тот же вопрос без подзапроса.

SELECT order_id, region, revenue FROM (
  SELECT order_id, region,
         units*unit_price AS revenue,
         AVG(units*unit_price) OVER (PARTITION BY region) AS region_avg
  FROM orders)
WHERE revenue > region_avg
ORDER BY region, revenue DESC;

1010 | East  | 1100
1001 | North |  880
1014 | North |  510
1015 | South |  880
1002 | South |  850
1012 | West  |  765

Те же шесть заказов. AVG(...) OVER (PARTITION BY region) вычисляет среднее значение для каждого региона один раз и присоединяет его к каждой строке этого региона за один проход вместо выполнения отдельного запроса для каждой строки.

Итак, практическое правило: всякий раз, когда коррелированный подзапрос вычисляет агрегат по группе, к которой принадлежит внешняя строка, оконная функция делает это быстрее и обычно более четко. Оконные функции охватывает все остальное, что OVER могу сделать. Связанные подзапросы сохраняют свое место для проверок существования и логики, которую окно не может выразить.

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

Краевые случаи, которые нарушают подзапрос

Шесть, которые стоит знать.

Скалярный подзапрос, возвращающий более одной строки. Хорошо, пока данные не изменятся, а затем ошибки запроса. Если вы ожидаете, что он вернет одну строку, скажите об этом с помощью LIMIT 1 и ORDER BYили агрегировать его.

Скалярный подзапрос, который не возвращает строк. Это дает NULL, это не ошибка, а NULL при сравнении все условие становится неизвестным, поэтому строки незаметно исчезают, а не появляются сообщения.

NOT IN против всего, что можно обнулить. Раздел третий. Предположим, что каждый столбец имеет значение NULL, если в схеме не указано иное, и используйте NOT EXISTS.

Связанный подзапрос к большой таблице. Это петля. Если запрос стал медленным после того, как кто-то добавил фильтр, ссылающийся на внешнюю таблицу, вот почему.

Один и тот же CTE использовался дважды. Некоторые движки пересчитывают его каждый раз, когда к нему обращаются, а некоторые материализуют его один раз, и то, какой из них делает ваш, влияет как на скорость, так и, если CTE содержит что-то недетерминированное, на ответ. Проверьте поведение вашего двигателя, прежде чем полагаться на него.

Псевдоним, который затеняет таблицу. Если в коррелированном подзапросе внутренние и внешние таблицы не имеют явного псевдонима, WHERE region = region сравнивает столбец сам с собой и всегда имеет значение Верно, поэтому фильтр ничего не делает. Всегда используйте псевдонимы обоих, т.к. o и i выше.