Вспомогательные виртуальные таблицы чисел в T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Вспомогательные виртуальные таблицы чисел

Ранее мы уже подробно рассказали об оконных функциях, в том числе логике их работы и оптимизации. Далее я расскажу, как решать широкий диапазон задач с помощью оконных функций. Это может показаться странным, но в большинстве решений используется функция ROW_NUMBER — она оказалась самой популярной из всех оконных функций.

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

Вспомогательные таблицы номеров содержат последовательности номеров, которые нужны для работы запросов. У таких таблиц чисел есть много применений, например генерация последовательности значений дат и времени и разбиение раздельных списков значений. Обычно рекомендуется хранить такую таблицу в базе данных постоянно, заполнив ее нужными числами и используя их по мере необходимости. Однако в некоторых средах невозможно создавать и наполнять новые таблицы, поэтому приходится их создавать в логике запроса.

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

SELECT c FROM (VALUES(1),(1)) AS D(c);

Этот запрос дает следующий результат:

Далее на основе этого запроса надо определить обобщенное табличное выражение (CTE) — назовем его L0 для уровня «0», после чего выполнить перекрестное соединение двух экземпляров CTE, чтобы получить четыре строки:

WITH
  L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c))
SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B;

Аналогично можно определить CTE (назовем его L1 для уровня «1»), после чего выполнить перекрестное соединение двух экземпляров CTE, чтобы получить на этот раз 16 строк:

WITH
  L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B)
SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B;

Теперь можно добавлять CTE, в каждом из которых выполняется перекрестное соединение двух последних CTE и увеличение по экспоненте числа строк. При наличии L уровней (начиная с нуля) общее число полученных строк равно 2^2^L (то есть 2 в степени 2 в степени L). Например, для пяти уровней число строк равно 4 294 967 296. Поэтому при пяти уровнях CTE помимо нулевого уровня, этот метод дает более 5 млрд строк. Вам вряд ли потребуется столько строк в таблице чисел, но использование параметра OFFSET/FETCH в Microsoft SQL Server 2012 или TOP в предыдущих версиях SQL Server позволяет ограничить сверху число строк на основе вводимой пользователем информации. Применяя ROW_NUMBER с ORDER BY (SELECT NULL) можно генерировать конкретные числа, не беспокоясь о затратах на сортировку. Суммируя все сказанное: чтобы сгенерировать последовательность чисел в диапазоне от @low до @high, в SQL Server 2012 можно использовать следующий код:

WITH
    L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L5)
  SELECT @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum
  OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;

Красота такого подхода в том, что оптимизатор SQL Server «догадывается», что не нужно генерировать больше строк, чем @high - @low + 1, поэтому обработчик запросов прекращает работу при достижении этого числа. Поэтому если вам нужна последовательность только 10 чисел, после генерации десяти штук процесс будет останавливаться. Если вы не хотите повторять этот код каждый раз, когда нужна последовательность чисел, можно инкапсулировать его в функцию, возвращающую табличное значение:

USE TSQL2012;
IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;

GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
    L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L5)
  SELECT @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum
  OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;

Параметр OFFSET/FETCH появился SQL Server 2012. Если нужно определить такую функцию в более ранних версиях SQL Server, нужно использовать параметр TOP, например так:

IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;

GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) 
    RETURNS TABLE
AS
RETURN
  WITH
    L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L5)
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum;

Эти две функции оптимизируются одинаково, поэтому ориентируясь на производительность, нельзя выбрать, какая из них лучше. Если вам важна совместимость с версиями, предшествующими SQL Server 2012, следует выбрать версию с TOP. Надо еще понимать, что в отличие от OFFSET/FETCH параметр TOP не является стандартным, поэтому если важно соответствие кода стандартам, возможно потребуется использовать TOP в системах с SQL Server 2012.

В качестве примера использования функции GetNums приведем код, генерирующий последовательность чисел из диапазона от 11 до 20:

SELECT n FROM dbo.GetNums(11, 20);

Чтобы понять, как быстро работает этот метод, я протестировал его на среднем оснащенности ноутбуке, выбрав в диалоговом окне Query Options параметр Discard Results After Execution (Отбросить результаты после выполнения). Следующему запросу понадобилось всего шесть секунд, чтобы сгенерировать последовательность 10 млн символов:

SELECT n FROM dbo.GetNums(1, 10000000);

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

Далее вы увидите еще не одно решение, в котором используется функция GetNums.