Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6782 / 673 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 8:

Соединения таблиц в предложении SELECT

Формулирование рекурсивных запросов

С версии 11.2 фраза WITH может использоваться для формулирования рекурсивных запросов, в соответствии (неполном) со стандартом SQL:1999. В этом качестве она способна решать ту же задачу, что и CONNECT BY, однако (а) делает это похожим с СУБД других типов образом, (б) обладает более широкими возможностями, (в) применима не только к запросам по иерархии и (г) записывается значительно более замысловато.

Общий алгоритм вычисления фразой WITH таков:

Результат := пусто;
Добавок := исходный SELECT ...;
Пока Добавок не пуст выполнять:
    Результат  :=     Результат  
            {UNION ALL | UNION | INTERSECT | EXCEPT}
Добавок;
    Добавок := рекурсивный SELECT ... FROM Добавок …;
конец цикла;

Предложение SELECT для исходного множества строк Oracle называет опорным (anchor) членом фразы WITH. Предложение SELECT для получения добавочного множества строк Oracle называют рекурсивным членом. Обратите внимание, что для вычитания множеств строк Oracle использует здесь не собственное обозначение MINUS, а стандартное EXCEPT.

Простой пример

Простой пример употребления фразы WITH для построения рекурсивного запроса:

WITH
numbers ( n ) AS (
   SELECT 1 AS n FROM dual -- исходное множество -- одна строка
      UNION ALL           -- символическое "объединение" строк 
   SELECT n + 1 AS n       -- рекурсия: добавок к предыдущему результату
   FROM   numbers           -- предыдущий результат в качестве источника данных
   WHERE  n < 5           -- если не ограничить, будет бесконечная рекурсия
)
SELECT n FROM numbers       -- основной запрос
;

Операция UNION ALL здесь используется символически, в рамках определенного контекста, для указания способа рекурсивного накопления результата.

Ответ:

         N
----------
         1
         2
         3
         4
         5

Строка с n = 1 получена из опорного запроса, а остальные строки — из рекурсивного. Из примера видна оборотная сторона рекурсивных формулировок: при неаккуратном планировании они допускают "бесконечное" выполнение (на деле — пока хватит ресурсов СУБД для сеанса или же пока администратор не прервет запрос или сеанс). С фразой CONNECT BY "бесконечное" выполнение в принципе невозможно. Программист обязан отнестись к построению рекурсивного запроса ответственно.

Еще один вывод из этого примера: подобно случаю с CONNECT BY, вынесенный рекурсивный подзапрос применим вовсе не обязательно только к иерархически организованным данным.

Пример с дополнительным разъяснением способа выполнения:

SQL> WITH
  2    anchor1234 ( n ) AS (            -- обычный
  3       SELECT 1 FROM dual UNION ALL
  4       SELECT 2 FROM dual UNION ALL
  5       SELECT 3 FROM dual UNION ALL
  6       SELECT 4 FROM dual
  7    )
  8  , numbers ( n ) AS (            -- рекурсивный
  9       SELECT n FROM anchor1234
 10          UNION ALL
 11       SELECT n + 1 AS n
 12       FROM   numbers
 13       WHERE  n < 5
 14    )
 15  SELECT n FROM numbers
 16  ;
         N
----------
         1  ← опорный запрос
         2  ← опорный запрос
         3  ← опорный запрос
         4  ← опорный запрос
         2  ← рекурсия 1
         3  ← рекурсия 1
         4  ← рекурсия 1
         5  ← рекурсия 1
         3  ← рекурсия 2
         4  ← рекурсия 2
         5  ← рекурсия 2
         4  ← рекурсия 3
         5  ← рекурсия 3
         5  ← рекурсия 4

Приведенный пример рекурсивного запроса позволяет перестроить один из приводившихся ранее "отчетных" запросов без прибегания к служебной таблице (названной выше PIVOT_YEARS) или же к табличной функции:

WITH     period ( year ) AS (
            SELECT 1980 AS year FROM dual
               UNION ALL
            SELECT year + 1 AS year
            FROM   period
            WHERE  year < 1990
         )
SELECT   p.year, COUNT ( e.empno )
FROM     emp e RIGHT OUTER JOIN period p
         ON p.year = EXTRACT ( YEAR FROM e.hiredate )
GROUP BY p.year
ORDER BY p.year
;

Запрос в приведенной формулировке самодостаточен. Желание его параметризировать, если оно возникнет, осуществимо с помощью "контекста сеанса" Oracle, но уже потребует соблюдения определенной технологии программирования при обращении к запросу.

Использование предыдущих значений при рекурсивном вычислении

Рекурсивные запросы с фразой WITH позволяют программисту больше, нежели запросы с CONNECT BY (тоже рекурсивные). Например, они позволяют накапливать изменения и не испытывают необходимости в функциях LEVEL или SYS_CONNECT_BY_PATH, имея возможность легко их моделировать.

Пример запроса по маршрутам из Москвы с подсчетом километража:

WITH stepbystep ( node, route, distance ) AS (
  SELECT node, parent || '-' || node, distance 
  FROM   way 
  WHERE  parent = 'Москва'
     UNION ALL
  SELECT w.node
       , s.route || '-' || w.node
       , w.distance + s.distance
  FROM way w
       INNER JOIN
       stepbystep s
       ON ( s.node = w.parent )
  )
SELECT route, distance FROM stepbystep
/

Ответ:

ROUTE                                      DISTANCE
---------------------------------------- ----------
Москва-Ленинград                                696
Москва-Новгород                                 538
Москва-Новгород-Ленинград                       717
Москва-Ленинград-Выборг                         831
Москва-Новгород-Ленинград-Выборг                852

Запрос по маршрутам из Выборга аналогичен, но с поправкой на симметрию, вызванной движением по иерархии снизу вверх, а не сверху вниз:

WITH stepbystep ( parent, route, distance ) AS (
  SELECT parent, node || '-' || parent, distance 
  FROM   way 
  WHERE  node = 'Выборг'
     UNION ALL
  SELECT w.parent
       , s.route || '-' || w.parent
       , w.distance + s.distance
  FROM   way w
         INNER JOIN
         stepbystep s
         ON ( s.parent = w.node )
  )
SELECT route, distance FROM stepbystep
/

Ответ:

ROUTE                                      DISTANCE
---------------------------------------- ----------
Выборг-Ленинград                                135
Выборг-Ленинград-Москва                         831
Выборг-Ленинград-Новгород                       314
Выборг-Ленинград-Новгород-Москва                852
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Ольга Угарова
Ольга Угарова
Россия, Москва, НИЯУ МИФИ
Никита  Ефименко
Никита Ефименко
Казахстан, Алматы, АУЭС