Общие табличные выражения (CTE)

Рекурсивные СТЕ

В очередной реализации MS SQL Server 2005 появилась возможность использования рекурсивных CTE   конструкций, позволяющих реализовывать циклы для генерации числовых последовательностей и итерационных вычислений.

Введение в рекурсии MS SQL Server можно найти в соответствующих руководствах Microsoft, учебнике  и Интернете. В данном параграфе мы рассмотрим только новые примеры, помогающие в ее практическом освоении.  Самым простым примером использования рекурсивного CTE является генерация ограниченной числовой последовательности натурального ряда: 1,2,3, ... N.

Консоль

Выполнить

WITH Series(a) AS
(
 SELECT 1
 UNION ALL
 SELECT a+1 FROM Series WHERE a < 100
)
SELECT * FROM Series;

WITH Series(a) AS (  SELECT 1  UNION ALL  SELECT a+1 FROM Series WHERE a < 100 ) SELECT * FROM Series;

    Схема

Нет схемы

Эта конструкция предназначена для генерации последовательности целых чисел (в виде одноколоночной таблицы) от 1 до 100.

А. Итерационные вычисления

В элементарной и высшей математике есть много интересных последовательностей, обладающих замечательными свойствами. Некоторые последовательности сходятся, и их можно использовать для реализации вычислительных алгоритмов или для вычисления алгебраических и трансцендентных чисел, значений тригонометрических функций, для нахождения корней уравнений, решения систем линейных алгебраических уравнений и пр. Другие последовательности, такие как факториал n!, коэффициенты бинома Ньютона, числа Фибоначчи представляют собой расходящиеся последовательности, которые имеют широкое использование в теории вероятности и математической статистике.

Эти последовательности получаются с помощью итераций (рекурсий SQL Server), например:
A1, A2, A3, A4 = fun(A1, A2, A3).

Здесь A1, A2, A3 - начальные значения для итерационного процесса, fun - функция для вычисления 4-го, пятого и т.д. чисел, которая всегда задействует предыдущие 3 числа. Предположим, что процесс стартует с трех одинаковых чисел A1 = A2 = A3 = 1. Тогда схема реализации с использованием рекурсивных CTE будет иметь следующий вид:

WITH TABLE(iter, A1, A2, A3, A4) AS
(
 SELECT iter = 1, A1 = 1, A2 = 1, A3 = 1, A4 = fun(1, 1, 1)
 UNION ALL
 SELECT iter + 1, A1 = A2, A2 = A3, A3 = A4, A4=fun(A2, A3, A4)
 FROM TABLE WHERE iter < 50
)
SELECT * FROM TABLE;

Здесь колонка iter введена для вывода номера итерации, колонка [A1] содержит пятьдесят первых членов последовательности, [A2], [A3] и [A4] – вспомогательные колонки для хранения необходимых промежуточных результатов.

Обобщением такого подхода является следующий пример. Пусть n >= 1, m >= 1. Тогда последовательные вычисления

A[n+1] = fun(A[1], A[2], …, A[n])

A[n+2] = fun(A[2], A[3], …, A[n+1])

…

A[n+m] = fun(A[m], A[m+1], …, A[m+n-1])

приводят к генерации m (m >= 1) новых членов последовательности A[n+1],…, A[n+m].

Мы не будем приводить примера использования рекурсии в общем случае, так как это возможно разве что в виде некоторого псевдокода. Читатели могут попытаться построить конкретные примеры самостоятельно. Отметим, что результирующая таблица выглядит следующим образом:

A[1]    A[2]    …    A[n]      A[n+1]
A[2]    A[3]    …    A[n+1]    A[n+2]
…
A[m+1]  A[m+2]  …    A[m+n-1]  A[m+n]