7.8. Запросы WITH (Общие табличные выражения)#

7.8. Запросы WITH (Общие табличные выражения)
7.8. Запросы WITH (Общие табличные выражения)
Назад НаверхГлава 7. ЗапросыНачало Далее

7.8. Запросы WITH (Общие табличные выражения)

7.8.1. SELECT в WITH
7.8.2. Рекурсивные запросы
7.8.3. Материализация общего выражения таблицы
7.8.4. Изменение данных в WITH

WITH предоставляет способ написания вспомогательных операторов для использования в более крупном запросе. Эти операторы, которые часто называются «общими табличными выражениями» или CTE, можно рассматривать как определение временных таблиц, которые существуют только для одного запроса. Каждый вспомогательный оператор в предложении WITH может быть SELECT, INSERT, UPDATE или DELETE; и само предложение 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), затем рекурсивного термина, где только рекурсивный термин может содержать ссылку на собственный вывод запроса. Такой запрос выполняется следующим образом:

Рекурсивная оценка запроса

  1. Оцените нерекурсивный термин. Для UNION (но не для UNION ALL) отбросьте дублирующиеся строки. Включите все оставшиеся строки в результат рекурсивного запроса и также поместите их во временную рабочую таблицу.

  2. Пока рабочая таблица не пуста, повторяйте следующие шаги:

    1. Оцените рекурсивное выражение, заменяя текущее содержимое рабочей таблицы на рекурсивную ссылку на саму себя. Для UNION (но не для UNION ALL) отбросьте дублирующиеся строки и строки, которые дублируют любую предыдущую строку результата. Включите все оставшиеся строки в результат рекурсивного запроса и также поместите их во временную промежуточную таблицу.

    2. Замените содержимое рабочей таблицы содержимым промежуточной таблицы, а затем очистите промежуточную таблицу.

Примечание

В то время как RECURSIVE позволяет задавать запросы рекурсивно, внутренне такие запросы вычисляются итеративно.

В приведенном выше примере рабочая таблица имеет только одну строку на каждом шаге и принимает значения от 1 до 100 на последующих шагах. На 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.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.

Подсказка

Столбец cycle path вычисляется так же, как и столбец упорядочения в глубину, показанный в предыдущем разделе. Запрос может иметь как редложение 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;

Это работает, потому что реализация Tantor SE вычисляет только столько строк запроса WITH, сколько фактически выбирается родительским запросом. Использование этого трюка в производственной среде не рекомендуется, потому что другие системы могут работать по-другому. Кроме того, это обычно не будет работать, если внешний запрос сортирует результаты рекурсивного запроса или объединяет их с другой таблицей, потому что в таких случаях внешний запрос обычно попытается выбрать все результаты запроса WITH в любом случае.

7.8.3. Материализация общего выражения таблицы

Важным свойством запросов WITH является то, что они обычно вычисляются только один раз при выполнении родительского запроса, даже если на них ссылается родительский запрос или соседние запросы WITH. Таким образом, дорогостоящие вычисления, которые необходимы в нескольких местах, могут быть помещены в запрос WITH, чтобы избежать избыточной работы. Еще одно возможное применение - предотвращение нежелательного многократного выполнения функций с побочными эффектами. Однако, другая сторона этой медали заключается в том, что оптимизатор не может передать ограничения из родительского запроса в многократно ссылаемый запрос WITH, так как это может повлиять на все использования вывода запроса WITH, когда оно должно влиять только на одно. Многократно ссылаемый запрос WITH будет вычислен так, как написано, без подавления строк, которые родительский запрос может отбросить после этого. (Однако, как уже упоминалось выше, вычисление может остановиться раньше, если ссылки на запрос требуют только ограниченное количество строк).

Однако, если запрос WITH не рекурсивный и не имеет побочных эффектов (то есть, это SELECT, не содержащий изменчивых функций), то его можно объединить с родительским запросом, позволяя совместную оптимизацию двух уровней запроса. По умолчанию это происходит, если родительский запрос ссылается на запрос WITH только один раз, но не происходит, если он ссылается на запрос WITH более одного раза. Вы можете изменить это решение, указав MATERIALIZED, чтобы принудительно выполнить отдельное вычисление запроса WITH, или указав NOT MATERIALIZED, чтобы объединить его с родительским запросом. Последний вариант может привести к дублированию вычислений запроса WITH, но он все равно может привести к сокращению, если каждое использование запроса 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

Вы можете использовать большинство операторов изменения данных (INSERT, UPDATE или DELETE, но не MERGE) в WITH. Это позволяет вам выполнить несколько разных операций в одном запросе. Пример: 

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, которые раскрываются в несколько операторов.

Назад Наверх Далее
7.7. Списки VALUES Начало Глава 8. Типы данных