7 способов сделать генератор чисел в SQL

Рассказали о 7 способах генерации последовательности чисел в заданном диапазоне в SQL и сравнили их производительность.

2К открытий4К показов

Расскажем о способах генерирования последовательности чисел в заданном диапазоне в SQL. И сравним их производительность.

Задача

Требуется сгенерировать последовательность чисел в заданном диапазоне.

Для всех скриптов будем использовать следующий набор переменных:

			declare @startnum bigint = 1
declare @endnum bigint = 1000000
declare @step bigint = 1

Можно найти много решений данной задачи, но многие из них можно свести к 7 способам. Сначала опишем каждое из решений, а затем сравним их производительность.

Решение 1: выводим цифры от 0 до 9

В данном решении используется обобщенное табличное выражение, в котором выводятся цифры от 0 до 9.

Перемножая эти списки необходимое число раз, можно получить набор записей, состоящий из требуемого количества элементов. Пронумеровав эти элементы по порядку оператором ROW_NUMBER, получаем список чисел.

			;with x as (
	select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
)
select top (round((@endnum-@startnum) / @step, 0) + 1)
	@startnum - @step + row_number() over(order by (select null)) * @step as n
from
	x as x1, --1 - 10
	x as x2, --11 - 100
	x as x3, --101 - 1000
	x as x4, --1001 - 10 000
	x as x5, --10 001 - 100 000
	x as x6, --100 001 - 1 000 000
	x as x7, --1 000 001 - 10 000 000
	x as x8, --10 000 001 - 100 000 000
	x as x9, --100 000 001 - 1 000 000 000
	x as x10, --1 000 000 001 - 10 000 000 000
	x as x11, --10 000 000 001 - 100 000 000 000
	x as x12, --100 000 000 001 - 1 000 000 000 000
	x as x13, --1 000 000 000 001 - 10 000 000 000 000
	x as x14, --10 000 000 000 001 - 100 000 000 000 000
	x as x15 --100 000 000 000 001 - 1 000 000 000 000 000

Важно отметить, что добавление в запрос 15 соединений запроса x не сильно влияет на итоговую производительность. Это происходит из-за того, что оптимизатор определяет требуемое количество подзапросов, а из остальных берёт только по одной строке. Это можно увидеть в плане запроса.

7 способов сделать генератор чисел в SQL 1

Вырезка из плана запроса решения №1

Решение 2: используем функцию replicate

Второе решение основано на двух функциях:

replicate – для генерации строки, длина которой равна количеству элементов в списке, который требуется получить в результате

string_split – для разбиения строки на список

			select top (round((@endnum-@startnum) / @step, 0) + 1)
	@startnum - @step + row_number() over(order by (select null)) * @step as n
from string_split(replicate(convert(varchar(max),' '), @endnum - @startnum), ' ')

Решение 3: replicate + cross

Следующее решение является смесью из первых двух решений.

Сначала в x генерируется список, состоящий из 10 строк. Затем полученный список перемножается необходимое число раз, чтобы получился список из нужного количества записей.

			;with x as (
	select null as n from string_split(replicate(' ', 10), ' ')
)
select top (round((@endnum-@startnum) / @step, 0) + 1)
	@startnum - @step + row_number() over(order by (select null)) * @step as n
from
	x as x1,  --1 - 10
	x as x2,  --11 - 100
	x as x3,  --101 - 1000
	x as x4,  --1001 - 10 000
	x as x5,  --10 001 - 100 000
	x as x6,  --100 001 - 1 000 000
	x as x7,  --1 000 001 - 10 000 000
	x as x8,  --10 000 001 - 100 000 000
	x as x9,  --100 000 001 - 1 000 000 000
	x as x10, --1 000 000 001 - 10 000 000 000
	x as x11, --10 000 000 001 - 100 000 000 000
	x as x12, --100 000 000 001 - 1 000 000 000 000
	x as x13, --1 000 000 000 001 - 10 000 000 000 000
	x as x14, --10 000 000 000 001 - 100 000 000 000 000
	x as x15  --100 000 000 000 001 - 1 000 000 000 000 000

Решение 4: recursive

Четвертый скрипт использует рекурсивный запрос для генерации последовательности чисел.

Рекурсивный запрос начинается с начального значения последовательности (@startnum) и использует оператор UNION ALL для добавления следующего значения в последовательность, пока не будет достигнуто конечное значение (@endnum).

Этот метод является простым в использовании, но может быть не так быстр, как другие методы, особенно для больших наборов данных.

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

			;with gen as (
    select @startnum as num
    union all
    select num + @step from gen where num + @step <= @endnum
)
select * from gen
option (maxrecursion 0)

Решение 5: while

Пятое решение может быть самое неоптимальное, но обойти его вниманием нельзя, потому что оно тоже решает задачу. Основано оно на использовании цикла WHILE, выполняемого до тех пор, пока временная таблица #numbers не заполнится.

			drop table if exists #numbers
create table #numbers (n bigint primary key);

declare @counter bigint = @startnum

while @counter <= @endnum
begin
    insert into #numbers (n)
	select @counter

    set @counter = @counter + @step
end;

select * from #numbers

Решение 6: spt_values

Следующее решение использует системную таблицу master.dbo.spt_values, соединяя её самой с собой нужное число раз.

Количество записей в этой таблице в разных базах различное, но, зная его, можно вычислить, сколько раз требуется выполнить cross join.

В моей таблице в этой таблице 2574 записи, поэтому соединив её саму с собой один раз, уже можно получить числа до 6 625 476. Соединив ещё раз, записей уже будет больше 17 миллиардов.

Конечно, вместо таблицы master.dbo.spt_values можно использовать и другие таблицы. Например, sys.all_objects. А можно использовать даже не системные таблицы, но при этом нужно быть уверенным, что количество записей в них не станет внезапно слишком маленьким.

			select top (round((@endnum-@startnum) / @step, 0) + 1)
	@startnum - @step + row_number() over(order by (select null)) * @step as n
from
	master.dbo.spt_values t1,
	master.dbo.spt_values t2,
	master.dbo.spt_values t3,
	master.dbo.spt_values t4,
	master.dbo.spt_values t5,
	master.dbo.spt_values t6

Так же, как и в первом решении, заметим, что добавление в запрос 6 соединений таблицы master.dbo.spt_values тоже не сильно влияет на итоговую производительность. Покажем это вырезкой из плана запроса.

7 способов сделать генератор чисел в SQL 2

Вырезка из плана запроса решения №6

Решение 7: функция generate_series

И есть ещё один способ, о котором нужно сказать. В SQL Server версии от 2022 имеется встроенная функция для решения данной задачи – GENERATE_SERIES.

			select value from generate_series(@startnum, @endnum, @step)

Сравнение производительности

Для сравнения скорости был использован скрипт, в котором 10 раз по очереди запускается каждый из тестов.

В результате получились следующие итоги в миллисекундах.

7 способов сделать генератор чисел в SQL 3

Результаты запуска каждого из способов

Расположив тесты в порядке увеличения скорости работы, получаем следующую диаграмму.

7 способов сделать генератор чисел в SQL 4

Сравнительная диаграмма

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

function dbo.generate_range

			create function dbo.generate_range(
	@startnum bigint, --начало диапазона
	@endnum bigint, --конец диапазона
	@step bigint --шаг
)
returns @generate table (
	n bigint
)
as begin

	;with x as (
		select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
	)
	insert into @generate (n)
	select top (round((@endnum-@startnum) / @step, 0) + 1)
		@startnum - @step + row_number() over(order by (select null)) * @step as n
	from
		x as x1, --1 - 10
		x as x2, --11 - 100
		x as x3, --101 - 1000
		x as x4, --1001 - 10 000
		x as x5, --10 001 - 100 000
		x as x6, --100 001 - 1 000 000
		x as x7, --1 000 001 - 10 000 000
		x as x8, --10 000 001 - 100 000 000
		x as x9, --100 000 001 - 1 000 000 000
		x as x10, --1 000 000 001 - 10 000 000 000
		x as x11, --10 000 000 001 - 100 000 000 000
		x as x12, --100 000 000 001 - 1 000 000 000 000
		x as x13, --1 000 000 000 001 - 10 000 000 000 000
		x as x14, --10 000 000 000 001 - 100 000 000 000 000
		x as x15 --100 000 000 000 001 - 1 000 000 000 000 000
	option(recompile)

return
end

procedure dbo.get_range

			create procedure dbo.get_range (
	@startnum bigint = 1, --начало диапазона
	@endnum bigint, --конец диапазона
	@step bigint = 1 --шаг
)
as
;with x as (
	select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
)
select top (round((@endnum-@startnum) / @step, 0) + 1)
	@startnum - @step + row_number() over(order by (select null)) * @step as n
from
	x as x1, --1 - 10
	x as x2, --11 - 100
	x as x3, --101 - 1000
	x as x4, --1001 - 10 000
	x as x5, --10 001 - 100 000
	x as x6, --100 001 - 1 000 000
	x as x7, --1 000 001 - 10 000 000
	x as x8, --10 000 001 - 100 000 000
	x as x9, --100 000 001 - 1 000 000 000
	x as x10, --1 000 000 001 - 10 000 000 000
	x as x11, --10 000 000 001 - 100 000 000 000
	x as x12, --100 000 000 001 - 1 000 000 000 000
	x as x13, --1 000 000 000 001 - 10 000 000 000 000
	x as x14, --10 000 000 000 001 - 100 000 000 000 000
	x as x15 --100 000 000 000 001 - 1 000 000 000 000 000
option(recompile)

Вывод

Таким образом, если у вас используется MS SQL SERVER версии от 2022, используйте для генерации встроенную функцию GENERATE_SERIES.

Для более старых версий выбирайте либо 1, либо 6 способы.

2К открытий4К показов