PostgreSQL - Запросы WITH

WITHпредоставляет способ написания вспомогательных операторов для использования в более крупном запросе. Эти операторы, которые часто называют общими табличными выражениями или CTE , можно рассматривать как определяющие временные таблицы, которые существуют только для одного запроса. Каждое вспомогательное выражение в WITHпредложении может быть SELECT, INSERT, UPDATEили DELETE; а само WITHпредложение присоединено к первичному утверждению, которое также может быть SELECT, INSERT, UPDATEили DELETE.

SELECTвWITH

Основное значение SELECTin 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. Это немного легче следовать этому пути.

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

Необязательный 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) Пока рабочий стол не пуст, повторите эти шаги:

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

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

Порядок поиска

При вычислении обхода дерева с помощью рекурсивного запроса может потребоваться упорядочить результаты либо в глубину, либо в ширину. Это можно сделать, вычислив столбец упорядочивания вместе с другими столбцами данных и используя его для сортировки результатов в конце. Обратите внимание, что на самом деле это не влияет на то, в каком порядке оценка запроса посещает строки; это, как всегда, зависит от реализации 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;

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

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.

Обнаружение Цикла

При работе с рекурсивными запросами важно быть уверенным, что рекурсивная часть запроса в конечном итоге не вернет кортежи, иначе запрос будет бесконечно зацикливаться. Иногда с помощью 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отношения содержат циклы. Поскольку нам требуется вывод « deep » , простое изменение 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;

Имеется встроенный синтаксис для упрощения обнаружения циклов. Приведенный выше запрос также можно записать так:

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.

Полезный трюк для тестирования запросов, когда вы не уверены, могут ли они зацикливаться, — это поместить a 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выходные данные запроса.

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

Полезным свойством 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. В каждом случае он эффективно предоставляет временные таблицы, на которые можно ссылаться в основной команде.

Заявления об изменении данных вWITH

Вы можете использовать операторы изменения данных ( INSERT, UPDATE, или DELETE) в 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. DELETEin удаляет WITHуказанные строки из products, возвращая их содержимое с помощью своего RETURNINGпредложения; а затем основной запрос считывает этот вывод и вставляет его в products_log.

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

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

Подоператоры WITHвыполняются одновременно друг с другом и с основным запросом. Таким образом, при использовании операторов, изменяющих данные в WITH, порядок, в котором на самом деле происходят указанные обновления, непредсказуем. Все операторы выполняются с одним и тем же моментальным снимком, поэтому они не могут « видеть » влияние друг друга на целевые таблицы. Это смягчает последствия непредсказуемости фактического порядка обновления строк и означает, что 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правила, которое расширяется до нескольких операторов.