7.8. Запросы WITH

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

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, но тогда потребуется два уровня вложенных под-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 до 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

При работе с рекурсивными запросами, важно убедиться, что рекурсивная часть запроса не будет возвращать строки в определённый момент времени, в противном случае запрос станет бесконечным циклом. Иногда, используя UNION вместо UNION ALL этого можно добиться отбрасывая строки, которые дублируют выведенные ранее. Однако, часто цикл не выдаёт строк, которые дублируются полностью: это может быть необходимо, чтобы проверить только одно или несколько полей, чтобы увидеть, когда та же самая точка была достигнута ранее. Стандартный метод управления такими ситуациями состоит в подсчёте массива уже обработаных значений. Например, рассмотрим следующий запрос, который просматривает таблицу graph, используя поле link:

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
)
SELECT * FROM search_graph;

Данный запрос зациклится, если отношения link содержат циклы. Поскольку мы требует вывода "depth", то простое изменение UNION ALL на UNION должно исключить зацикливание. Вместо этого, нам нужно определять достигли ли мы или нет той же строки снова, когда мы приходим к следующуму определённому пути поля link. Мы добавляем две колонки path и cycle в зацикливающийся запрос:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || g.id,
          g.id = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

Для предотвращения зацикливания, значение массива часто полезно для представления самого себя как "path", чтобы понять, что была достигнула любая отдельная строка.

В большинстве случаев, когда для определения зацикливания нужна проверка более чем одного поля, используйте массив строк. Например, если вам необходимо сравнить поля f1 и f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[ROW(g.f1, g.f2)],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || ROW(g.f1, g.f2),
          ROW(g.f1, g.f2) = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

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