SQL CTE: как построить запрос по шагам, которые вы можете проверить

1. Что такое CTE в одном запросе

Перед объяснением: вы пишите WITH rated AS (...) в верхней части запроса. Является rated реальная таблица, которая сейчас существует в вашей базе данных, и сможет ли коллега запросить ее завтра?

CTE означает общее табличное выражение. Не обращайте внимания на имя. Это способ сказать: «Сначала разберись и назови это X», а затем использовать X в запросе ниже, как если бы это была таблица.

WITH rated AS (
    SELECT *
    FROM games
    WHERE TotalReviews >= 500
)
SELECT COUNT(*) FROM rated;

Это возвращает 10 507. WITH блок определяет шаг и называет его. SELECT под использует это имя. Ничего не было создано и ничего не сохранено, что является ответом на предварительный вопрос: rated существует только пока выполняется этот единственный запрос, и завтра коллега ничего не найдет.

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

Два правила синтаксиса охватывают практически все. Каждый именованный шаг заключен в скобки после AS. Несколько шагов разделяются запятыми, а слово WITH появляется только один раз, в самом верху.

2. Составление шагов и проверка каждого из них

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

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

WITH rated AS (
    SELECT * FROM games WHERE TotalReviews >= 500
),
well_liked AS (
    SELECT * FROM rated WHERE PctPositive >= 90
)
SELECT COUNT(*) FROM well_liked;

Обратите внимание, что well_liked читает из rated, а не из games. Каждый шаг сужает предыдущий. Счет возвращается к 3454.

Теперь о привычке, которая делает это стоящим. Чтобы протестировать любой шаг, ничего не меняйте, кроме последней строки.

-- Same CTEs above, then one of:
SELECT COUNT(*) FROM rated;       -- 10,507
SELECT COUNT(*) FROM well_liked;  -- 3,454
SELECT * FROM well_liked LIMIT 5; -- eyeball actual rows

Четыре отсчета по цепочке подскажут вам, где число ошиблось, и это займет секунды. Шаг, который падает с 10 507 до 12, — это сломанный фильтр, и вы обнаружите его на шаге, а не в конце.

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

3. Реальный пример четырех шагов

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

Четыре, и назвать их – это большая часть анализа. Вот собственно определение.

WITH rated AS (
    -- Step 1: enough reviews that the rating means something
    SELECT * FROM games WHERE TotalReviews >= 500
),
well_liked AS (
    -- Step 2: players actually rate it highly
    SELECT * FROM rated WHERE PctPositive >= 90
),
overlooked AS (
    -- Step 3: not many people own it
    SELECT * FROM well_liked WHERE EstOwnersMid <= 50000
),
genre_avg AS (
    -- Step 4: what "highly rated" means in its own genre
    SELECT PrimaryGenre, AVG(PctPositive) AS genre_rating
    FROM rated
    GROUP BY PrimaryGenre
    HAVING COUNT(*) >= 100
)
SELECT o.Name,
       o.PrimaryGenre,
       o.PctPositive,
       ROUND(g.genre_rating, 1) AS genre_avg
FROM overlooked AS o
JOIN genre_avg AS g ON o.PrimaryGenre = g.PrimaryGenre
ORDER BY o.PctPositive DESC;

Отсчет по цепочке: 82 956, затем 10 507, затем 3 454, затем 663. Каждая капля — это решение, с которым кто-то может поспорить, и это правильный аргумент, который можно иметь.

Четвертый шаг отличается от первых трех, и на это стоит обратить внимание. Это не сужает ряды. Он вычисляет среднее жанровое значение на основе rated шаг, а затем последний запрос снова присоединяется к нему. Используемый таким образом CTE представляет собой таблицу поиска, которую вы создаете на месте. Этот шаблон: вычисление сводки и последующее объединение ее с деталями — одна из самых полезных вещей, которые делают CTE.

Результаты говорят то, чего не может сказать исходный рейтинг. Кабуто Парк имеет положительный рейтинг 99,9% в жанре «Казуальный», средний показатель в жанре 84,6%. оборотень в действии имеет показатель 99,4%, тогда как средний показатель составляет 80,9%. Обе игры были невидимы в простом списке с самым высоким рейтингом, потому что простой список полон игр с сорока обзорами.

Представьте себе, что эта цепочка работает с таблицей, с которой вы на самом деле работаете. Какими бы были ваши четыре шага и о каком из них, по вашему мнению, кто-то будет спорить?

4. CTE для подзапроса, тот же ответ

Перед объяснением: обе версии ниже возвращают одинаковые строки. Если база данных в любом случае работает одинаково, имеет ли вообще значение выбор?

Вот вложенная версия первых двух шагов.

SELECT COUNT(*)
FROM (
    SELECT * FROM (
        SELECT * FROM games WHERE TotalReviews >= 500
    ) WHERE PctPositive >= 90
);

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

Выбор имеет значение для человека, который будет читать его через три месяца, а это обычно вы. Вот и весь аргумент, и этого достаточно.

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

5. Полное до и после

Тот же вопрос: у каких игр с высоким рейтингом очень мало владельцев по сравнению с их жанром?

До

SELECT Name, PrimaryGenre, PctPositive
FROM games
WHERE TotalReviews >= 500
  AND PctPositive >= 90
  AND EstOwnersMid <= 50000
ORDER BY PctPositive DESC;

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

после

-- ============================================================
-- STEP 5: Which well-rated games are overlooked in their genre?
-- WHY: A plain top-rated list is dominated by games with forty
--      reviews. Each step below removes one specific problem,
--      and each has a row count you can check on its own.
-- COUNTS: 82,956 all games, 10,507 rated, 3,454 well liked,
--      663 overlooked. Change a threshold and rerun the counts.
-- ============================================================
WITH rated AS (
    --Games with enough reviews for the rating to be stable
    SELECT * FROM games WHERE TotalReviews >= 500
),
well_liked AS (
    --Of those, the ones players rate 90% positive or better
    SELECT * FROM rated WHERE PctPositive >= 90
),
overlooked AS (
    --Of those, the ones hardly anyone owns
    SELECT * FROM well_liked WHERE EstOwnersMid <= 50000
),
genre_avg AS (
    --Separately: the average rating within each genre,
    --  built from rated so it is not skewed by tiny samples
    SELECT PrimaryGenre, AVG(PctPositive) AS genre_rating
    FROM rated
    GROUP BY PrimaryGenre
    HAVING COUNT(*) >= 100
)
--Finally: the overlooked games, each shown next to its genre average
SELECT o.Name,
       o.PrimaryGenre,
       o.PctPositive,
       ROUND(g.genre_rating, 1) AS genre_avg
FROM overlooked AS o
JOIN genre_avg AS g ON o.PrimaryGenre = g.PrimaryGenre
ORDER BY o.PctPositive DESC;

Каждый порог теперь стоит рядом с его причиной. Каждый шаг можно посчитать. Сравнение жанров, о котором на самом деле задавался вопрос, присутствует. Формат комментариев тот же, что и у Как комментировать SQL, чтобы он обучал.

6. Крайние случаи и честные ограничения

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

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

Некоторые базы данных рассматривают CTE как ограждение. Это ответ на предварительный вопрос. PostgreSQL до версии 12 материализовал каждый CTE, то есть рассчитывал каждый из них полностью, а не сворачивал его в более широкий план, поэтому фильтр в конечном запросе не мог перейти на предыдущий шаг. Современный PostgreSQL встраивает их по умолчанию, и вы можете принудительно настроить любое поведение с помощью AS MATERIALIZED или AS NOT MATERIALIZED. Если перезапись стала медленнее, это первое, на что следует обратить внимание.

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

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

Запятая перед финалом SELECT ломает его. Последний CTE не имеет запятой после закрывающей скобки. Это кусается каждый раз, когда вы меняете порядок шагов.

Название шага в честь реальной таблицы затеняет его. Если ты напишешь WITH games AS (...), каждое упоминание о games ниже теперь означает ваш шаг, а не стол. Иногда преднамеренно. Чаще полдень терял.

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