7.8. Запросы WITH
(Общие табличные выражения) #
WITH
предоставляет способ записывать дополнительные операторы для применения в больших запросах. Эти операторы, которые также называют общими табличными выражениями (Common Table Expressions, CTE), можно представить как определения временных таблиц, существующих только для одного запроса. Дополнительным оператором в предложении WITH
может быть SELECT
, INSERT
, UPDATE
, DELETE
или MERGE
, а само предложение WITH
присоединяется к основному оператору, которым также может быть SELECT
, INSERT
, UPDATE
, DELETE
или MERGE
.
7.8.1. SELECT
в WITH
#
Основное предназначение SELECT
в предложении WITH
заключается в разбиении сложных запросов на простые части. Например, запрос:
WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;
выводит итоги по продажам только для передовых регионов. Предложение WITH
определяет два дополнительных оператора regional_sales
и top_regions
так, что результат regional_sales
используется в top_regions
, а результат top_regions
используется в основном запросе SELECT
. Этот пример можно было бы переписать без WITH
, но тогда нам понадобятся два уровня вложенных подзапросов SELECT
. Показанным выше способом это можно сделать немного проще.
7.8.2. Рекурсивные запросы #
Необязательное указание RECURSIVE
превращает WITH
из просто удобной синтаксической конструкции в средство реализации того, что невозможно в стандартном SQL. Используя RECURSIVE
, запрос WITH
может обращаться к собственному результату. Очень простой пример, суммирующий числа от 1 до 100:
WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t;
В общем виде рекурсивный запрос WITH
всегда записывается как не рекурсивная часть, потом UNION
(или UNION ALL
), а затем рекурсивная часть, где только в рекурсивной части можно обратиться к результату запроса. Такой запрос выполняется следующим образом:
Вычисление рекурсивного запроса
Вычисляется не рекурсивная часть. Для
UNION
(но неUNION ALL
) отбрасываются дублирующиеся строки. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную рабочую таблицу.Пока рабочая таблица не пуста, повторяются следующие действия:
Вычисляется рекурсивная часть так, что рекурсивная ссылка на сам запрос обращается к текущему содержимому рабочей таблицы. Для
UNION
(но неUNION ALL
) отбрасываются дублирующиеся строки и строки, дублирующие ранее полученные. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную промежуточную таблицу.Содержимое рабочей таблицы заменяется содержимым промежуточной таблицы, а затем промежуточная таблица очищается.
Примечание
Хотя указание RECURSIVE
позволяет определять рекурсивные запросы, внутри такие запросы обрабатываются итерационно.
В показанном выше примере в рабочей таблице на каждом этапе содержится всего одна строка и в ней последовательно накапливаются значения от 1 до 100. На сотом шаге, благодаря условию WHERE
, не возвращается ничего, так что вычисление запроса завершается.
Рекурсивные запросы обычно применяются для работы с иерархическими или древовидными структурами данных. В качестве полезного примера можно привести запрос, находящий все непосредственные и косвенные составные части продукта, используя только таблицу с прямыми связями:
WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity * pr.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT sub_part, SUM(quantity) as total_quantity FROM included_parts GROUP BY sub_part
7.8.2.1. Порядок поиска #
Когда при выполнении рекурсивного запроса производится обход дерева, результаты можно получать в разном порядке: «сначала в глубину» или «сначала в ширину». Для этого можно в дополнение к другим столбцам вычислить упорядочивающий столбец и использовать его для сортировки результатов. Обратите внимание, что такой столбец не определяет порядок обхода строк запросом — этот порядок, как всегда, зависит от реализации SQL. Упорядочивающий столбец лишь позволяет удобным образом упорядочить полученные результаты.
Чтобы отсортировать результаты в порядке «сначала в глубину», для каждой строки результата вычисляется массив уже просмотренных строк. Например, рассмотрим следующий запрос, который выполняет поиск в таблице tree
по полю link
:
WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree;
Чтобы добавить информацию для упорядочивания «сначала в глубину», вы можете написать так:
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[t.id] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || t.id FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
В общем случае, когда для выявления строки нужно использовать несколько полей, следует использовать массив строк. Например, если нужно отследить поля f1
и f2
:
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2) FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
Подсказка
ROW()
можно опустить, когда нужно отслеживать только одно поле (как обычно и бывает). При этом будет использоваться не массив данных составного типа, а простой массив, что более эффективно.
Чтобы получить результаты, отсортированные «сначала в ширину», можно добавить столбец, отслеживающий глубину поиска, например:
WITH RECURSIVE search_tree(id, link, data, depth) AS ( SELECT t.id, t.link, t.data, 0 FROM tree t UNION ALL SELECT t.id, t.link, t.data, depth + 1 FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY depth;
Для обеспечения стабильности сортировки добавьте столбцы данных в качестве столбцов вторичной сортировки.
Подсказка
Этот алгоритм рекурсивного вычисления запроса выдаёт в результате узлы, упорядоченные «сначала в ширину». И всё же это зависит от реализации, поэтому полагаться на это поведение не следует. Порядок строк внутри каждого уровня, конечно, не определён, поэтому в любом случае может потребоваться явное упорядочивание.
Для определения столбца, который будет вычисляться для упорядочивания «сначала в глубину» или «сначала в ширину», есть встроенный синтаксис. Например:
WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SEARCH DEPTH FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol; WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SEARCH BREADTH FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol;
Внутри этот синтаксис преобразуется в формы, подобные вышеприведённым формам, составленным вручную. В предложении SEARCH
указывается, какой тип поиска необходим — «сначала в глубину» или «сначала в ширину», список отслеживаемых для сортировки столбцов и имя столбца, который будет содержать данные, используемые для упорядочивания. Этот столбец будет неявно добавлен в результирующие строки в CTE.
7.8.2.2. Выявление циклов #
Работая с рекурсивными запросами, важно обеспечить, чтобы рекурсивная часть запроса в конце концов не выдала никаких кортежей (строк), в противном случае цикл будет бесконечным. Иногда для этого достаточно применять UNION
вместо UNION ALL
, так как при этом будут отбрасываться строки, которые уже есть в результате. Однако часто в цикле выдаются строки, не совпадающие полностью с предыдущими: в таких случаях может иметь смысл проверить одно или несколько полей, чтобы определить, не была ли текущая точка достигнута раньше. Стандартный способ решения подобных задач — вычислить массив с уже обработанными значениями. Например, снова рассмотрите следующий запрос, просматривающий таблицу graph
по полю link
:
WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 0 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link ) SELECT * FROM search_graph;
Этот запрос зациклится, если связи link
содержат циклы. Так как нам нужно получать в результате «depth», одно лишь изменение UNION ALL
на UNION
не позволит избежать зацикливания. Вместо этого мы должны как-то определить, что уже достигали текущей строки, пройдя некоторый путь. Для этого, добавив два столбца is_cycle
и path
, мы получаем запрос, защищённый от зацикливания:
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[g.id] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, g.id = ANY(path), path || g.id FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
Помимо предотвращения циклов, значения массива часто бывают полезны сами по себе для представления «пути», приведшего к определённой строке.
В общем случае, когда для выявления цикла нужно проверять несколько полей, следует использовать массив строк. Например, если нужно сравнить поля f1
и f2
:
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[ROW(g.f1, g.f2)] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, ROW(g.f1, g.f2) = ANY(path), path || ROW(g.f1, g.f2) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
Подсказка
Часто для выявления цикла достаточно одного поля, и тогда ROW()
можно опустить. При этом будет использоваться не массив данных составного типа, а простой массив, что более эффективно.
Для упрощения выявления циклов есть встроенный синтаксис. Запрос выше можно записать так:
WITH RECURSIVE search_graph(id, link, data, depth) AS (
SELECT g.id, g.link, g.data, 1
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1
FROM graph g, search_graph sg
WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;
и внутри он будет приведён к вышеуказанной форме. В предложении CYCLE
сначала указывается список столбцов, отслеживаемых для выявления цикла, затем имя столбца, показывающего признак выявления цикла, и, наконец, имя ещё одного столбца, в котором будет отслеживаться путь. Столбцы, указывающие цикл и путь, будут неявно добавлены в результирующие строки в CTE.
Подсказка
Столбец с указанием пути цикла вычисляется так же, как и столбец для упорядочивания результатов «сначала в глубину», что показано в предыдущем подразделе. Запрос может содержать одновременно SEARCH
и CYCLE
, но в этом случае для упорядочивания «сначала в глубину» будут производиться лишние вычисления, поэтому эффективнее использовать только CYCLE
и сортировать результаты по столбцу пути. Однако использование в запросе и SEARCH
, и CYCLE
оправдано, когда нужен порядок «сначала в ширину».
Для тестирования запросов, которые могут зацикливаться, есть хороший приём — добавить LIMIT
в родительский запрос. Например, следующий запрос зациклится, если не добавить предложение LIMIT
:
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;
Но в данном случае этого не происходит, так как в PostgreSQL запрос WITH
выдаёт столько строк, сколько фактически принимает родительский запрос. В производственной среде использовать этот приём не рекомендуется, так как другие системы могут вести себя по-другому. Кроме того, это не будет работать, если внешний запрос сортирует результаты рекурсивного запроса или соединяет их с другой таблицей, так как в подобных случаях внешний запрос обычно всё равно выбирает результат запроса WITH
полностью.
7.8.3. Материализация общих табличных выражений #
Запросы WITH
имеют полезное свойство — обычно они вычисляются только раз для всего родительского запроса, даже если этот запрос или соседние запросы WITH
обращаются к ним неоднократно. Таким образом, сложные вычисления, результаты которых нужны в нескольких местах, можно выносить в запросы WITH
в целях оптимизации. Кроме того, такие запросы позволяют избежать нежелательных вычислений функций с побочными эффектами. Однако есть и обратная сторона — оптимизатор не может распространить ограничения родительского запроса на неоднократно задействуемый запрос WITH
, так как это может повлиять на использование результата WITH
во всех местах, тогда как должно повлиять только в одном. Многократно задействуемый запрос WITH
будет выполняться буквально и возвращать все строки, включая те, что потом может отбросить родительский запрос. (Но как было сказано выше, вычисление может остановиться раньше, если в ссылке на этот запрос затребуется только ограниченное число строк.)
Однако если запрос WITH
является нерекурсивным и свободным от побочных эффектов (то есть это SELECT
, не вызывающий изменчивых функций), он может быть свёрнут в родительский запрос, что позволит оптимизировать совместно два уровня запросов. По умолчанию это происходит, только если запрос WITH
задействуется в родительском запросе всего в одном месте, а не в нескольких. Это поведение можно переопределить, добавив указание MATERIALIZED
, чтобы выделить вычисление запроса WITH
, или указание NOT MATERIALIZED
, чтобы принудительно свернуть его в родительский запрос. В последнем случае возникает риск многократного вычисления запроса WITH
, но в итоге это может быть выгодно, если в каждом случае использования WITH
из всего результата запроса остаётся только небольшая часть.
Простой пример для демонстрации этих правил:
WITH w AS ( SELECT * FROM big_table ) SELECT * FROM w WHERE key = 123;
Этот запрос WITH
будет свёрнут в родительский и будет выполнен с тем же планом, что и запрос:
SELECT * FROM big_table WHERE key = 123;
В частности, если в таблице создан индекс по столбцу key
, он может использоваться для получения строк с key = 123
. В другом случае:
WITH w AS ( SELECT * FROM big_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref WHERE w2.key = 123;
запрос WITH
будет материализован, то есть создастся временная копия таблицы big_table
, которая будет соединена с собой же, без использования какого-либо индекса. Этот запрос будет выполняться гораздо эффективнее в таком виде:
WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref WHERE w2.key = 123;
В этом случае ограничения родительского запроса могут применяться непосредственно при сканировании big_table
.
Пример, в котором вариант NOT MATERIALIZED
может быть нежелательным:
WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
В данном случае благодаря материализации запроса WITH
ресурсоёмкая функция very_expensive_function
вычисляется только один раз для строки таблицы, а не дважды.
Примеры выше показывают только предложение WITH
с SELECT
, но таким же образом его можно использовать с командами INSERT
, UPDATE
, DELETE
и MERGE
. В каждом случае он по сути создаёт временную таблицу, к которой можно обратиться в основной команде.
7.8.4. Изменение данных в WITH
#
В предложении WITH
можно использовать операторы, изменяющие данные (INSERT
, UPDATE
, DELETE
или MERGE
). Это позволяет выполнять в одном запросе сразу несколько разных операций. Например:
WITH moved_rows AS ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;
Этот запрос фактически перемещает строки из products
в products_log
. Оператор DELETE
в WITH
удаляет указанные строки из products
и возвращает их содержимое в предложении RETURNING
; а затем главный запрос читает это содержимое и вставляет в таблицу products_log
.
Следует заметить, что предложение WITH
в данном случае присоединяется к оператору INSERT
, а не к SELECT
, вложенному в INSERT
. Это необходимо, так как WITH
может содержать операторы, изменяющие данные, только на верхнем уровне запроса. Однако при этом применяются обычные правила видимости WITH
, так что к результату WITH
можно обратиться и из вложенного оператора SELECT
.
Операторы, изменяющие данные, в WITH
обычно дополняются предложением RETURNING
(см. Раздел 6.4), как показано в этом примере. Важно понимать, что временная таблица, которую можно будет использовать в остальном запросе, создаётся из результата RETURNING
, а не целевой таблицы оператора. Если оператор, изменяющий данные, в WITH
не дополнен предложением RETURNING
, временная таблица не создаётся и обращаться к ней в остальном запросе нельзя. Однако такой запрос всё равно будет выполнен. Например, допустим следующий не очень практичный запрос:
WITH t AS ( DELETE FROM foo ) DELETE FROM bar;
Он удалит все строки из таблиц foo
и bar
. При этом число задействованных строк, которое получит клиент, будет подсчитываться только по строкам, удалённым из bar
.
Рекурсивные ссылки в операторах, изменяющих данные, не допускаются. В некоторых случаях это ограничение можно обойти, обратившись к конечному результату рекурсивного WITH
, например так:
WITH RECURSIVE included_parts(sub_part, part) AS ( SELECT sub_part, part FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) DELETE FROM parts WHERE part IN (SELECT part FROM included_parts);
Этот запрос удаляет все непосредственные и косвенные составные части продукта.
Операторы, изменяющие данные в WITH
, выполняются только один раз и всегда полностью, вне зависимости от того, принимает ли их результат основной запрос. Заметьте, что это отличается от поведения SELECT
в WITH
: как говорилось в предыдущем разделе, SELECT
выполняется только до тех пор, пока его результаты востребованы основным запросом.
Вложенные операторы в WITH
выполняются одновременно друг с другом и с основным запросом. Таким образом, порядок, в котором операторы в WITH
будут фактически изменять данные, непредсказуем. Все эти операторы выполняются с одним снимком данных (см. Главу 13), так что они не могут «видеть», как каждый из них меняет целевые таблицы. Это уменьшает эффект непредсказуемости фактического порядка изменения строк и означает, что RETURNING
— единственный вариант передачи изменений от вложенных операторов WITH
основному запросу. Например, в данном случае:
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM products;
внешний оператор SELECT
выдаст цены, которые были до действия UPDATE
, тогда как в запросе
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM t;
внешний SELECT
выдаст изменённые данные.
Неоднократное изменение одной и той же строки в рамках одного оператора не поддерживается. Иметь место будет только одно из нескольких изменений и надёжно определить, какое именно, часто довольно сложно (а иногда и вовсе невозможно). Это так же касается случая, когда строка удаляется и изменяется в том же операторе: в результате может быть выполнено только обновление. Поэтому в общем случае следует избегать подобного наложения операций. В частности, избегайте подзапросов WITH
, которые могут повлиять на строки, изменяемые основным оператором или операторами, вложенные в него. Результат действия таких запросов будет непредсказуемым.
В настоящее время, для оператора, изменяющего данные в WITH
, в качестве целевой нельзя использовать таблицу, для которой определено условное правило или правило ALSO
или INSTEAD
, если оно состоит из нескольких операторов.