Средства формулировки аналитических и рекурсивных запросов

Рекурсивные запросы с разделом WITH

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

with_clause ::= WITH [ RECURSIVE ]
with_element_comma_list
with_element ::= query_name [ (column_name_list) ]
     AS ( query_expression ) [ search_or_cycle_clause ]
search_or_cycle_clause ::= search_clause
    | cycle_clause
    | search_clause cycle_clause
search_clause ::= SEARCH recursive_search_order SET
      sequence_column_name
recursive_search_order ::= DEPTH FIRST BY
      order_item_commalist
    | BREADTH FIRST BY order_item_commalist
cycle_clause ::= CYCLE cycle_column_name_comma_list
    SET cycle_mark_column_name TO value_expression
    DEFAULT value_expression
    USING path_column_name

Для иллюстрации возможностей рекурсивных запросов с разделом WITH и пояснения смысла конструкций SEARCH и CYCLE воспользуемся классическим примером "разборки деталей" (в данном случае мы будем разбирать автомобиль). Предположим, что данные о конструктивных элементах автомобиля хранятся в таблице CAR, определенной следующим образом:

CREATE TABLE CAR (CONTAINING_PART VARCHAR (10),
                  CONTAINED_PART VARCHAR (10),
                  NUMBER_OF_PARTS INTEGER,
                  PART_COST DECIMAL (6,2));

У автомобиля имеется один конструктивный элемент верхнего уровня - полностью собранный автомобиль. Этот элемент не является составной частью какого-либо другого элемента, и для его строки значением столбца CONTAINING_PART является текстовая строка длины 0. В любой другой строке таблицы CAR, соответствующей некоторому неатомарному конструктивному элементу e, столбец CONTAINING_PART содержит идентификационный номер элемента e1, в который входит элемент e, столбец NUMBER_OF_PARTS - число экземпляров элемента e, входящих в e1, а столбец CONTAINED_PART - идентификационный номер самого элемента e. В любой строке таблицы CAR, соответствующей некоторому атомарному конструктивному элементу, значением столбца CONTAINED_PART является строка длины 0, а в столбце PART_COST сохраняется цена атомарного конструктивного элемента (для неатомарных элементов значение этого столбца равно нулю).

Предположим, что нам требуется разобрать автомобиль, начиная с элемента самого верхнего уровня, и для каждого конструктивного элемента получить его номер, общее число используемых экземпляров этого элемента, а также, если элемент является атомарным, общую стоимость используемых экземпляров. Вот возможная формулировка запроса (пример 16.3):

WITH RECURSIVE PARTS (PART_NUMBER, 
        NUMBER_OF_PARTS, COST) AS
   (SELECT CONTAINED_PART, 1, 0.00 (a)
   FROM CAR
   WHERE CONTAINING_PART = ''
 UNION ALL
   SELECT CAR.CONTAINED_PART, CAR.NUMBER_OF_PARTS,
      CAR.NUMBER_OF_PARTS * CAR.PART_COST
   FROM CAR, PARTS
   WHERE PARTS.PART_NUMBER = CAR.CONTAINING_PART)
SELECT PART_NUMBER, SUM(NUMBER_OF PARTS), 
   SUM(COST) (b)
FROM PARTS
GROUP BY PART_NUMBER;

Этот запрос будет выполняться следующим образом. При вычислении раздела FROM основного запроса ( b ) начнется выполнение рекурсивного выражения запросов ( a ), определенного в разделе WITH . На первом шаге рекурсии будет выполнена часть данного выражения, предшествующая операции UNION ALL и образующая начальный источник рекурсии. В результате будет произведено исходное состояние виртуальной таблицы PARTS, в котором, в нашем случае, появится единственная строка, соответствующая автомобилю целиком. На следующем шаге к таблице PARTS будут добавлены строки, соответствующие конструктивным элементам второго уровня (для автомобиля это, по-видимому, двигатель, колеса, шасси и т.д.). Этот процесс будет продолжаться до тех пор, пока мы не дойдем до атомарных конструктивных элементов и не достигнем, тем самым, фиксированной точки. Поскольку в рекурсивном запросе содержится операция UNION ALL, в результирующей таблице могут появляться строки-дубликаты. Наличие строки-дубликата вида <part_no, number, cost> означает, что элемент с номером part_no входит в одном и том же числе экземпляров в несколько конструктивных элементов более высокого уровня.

Раздел SEARCH

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

WITH RECURSIVE PARTS (ASSEMBLY, PART_NUMBER,
        NUMBER_OF_PARTS, COST) AS
     (SELECT CONTAINING_PART, CONTAINED_PART, 1, 0.00
     FROM CAR
     WHERE CONTAINING_PART = ''
  UNION ALL
     SELECT CAR.CONTAINING_PART, CAR.CONTAINED_PART,
            CAR.NUMBER_OF_PARTS, CAR.NUMBER_OF_PARTS *
            CAR.PART_COST
     FROM CAR, PARTS
     WHERE PARTS.PART_NUMBER = CAR.CONTAINING_PART)
     SEARCH BREADTH FIRST 
     BY CONTAINING_PART, CONTAINED_PART
     SET ORDER_COLUMN
SELECT PART_NUMBER, NUMBER_OF PARTS, COST
FROM PARTS
ORDER BY ORDER_COLUMN;

В списке столбцов сортировки раздела SEARCH должны указываться имена столбцов виртуальной таблицы, определенной в разделе WITH. Поскольку в данном случае мы хотим, чтобы в результате сначала появлялись все конструктивные элементы одного уровня ( CONTAINING_PART ), а затем все их подэлементы ( CONTAINED_PART ), в список выборки рекурсивного запроса PARTS добавлен столбец CONTAINING_PART, который не используется нигде, кроме раздела SEARCH . В разделе SET к результирующей таблице рекурсивного запроса добавлен столбец, который мы назвали ORDER_COLUMN. Название соответствует природе столбца, потому что при выполнении рекурсивного запроса в этот столбец автоматически заносятся значения, характеризующие порядок генерируемых строк в соответствии с выбранным способом обхода иерархии. Чтобы строки результата основного запроса появлялись в должном порядке, в этом запросе требуется наличие раздела ORDER BY с указанием столбца, определенного в разделе SET.