Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Генерация последовательности чисел  [new]
Shrek2
Member

Откуда: Питер
Сообщений: 55
Всем привет.

Есть, например, такая табличка.

if object_id('dbo.TEST_NUMBERS') is not null
begin
drop table dbo.TEST_NUMBERS
end

CREATE TABLE TEST_NUMBERS
(
start int,
finish int
)

INSERT INTO TEST_NUMBERS
VALUES (5, 10),
(12, 15),
(17, 17)

Хочется получить из этого последовательность: 5, 6, 7, 8, 9, 10, 12, 13, 14, 15, 17
поместить это в другую таблицу.

Как такое попроще сделать? Пока только курсоры и циклы на уме.

Спасибо
19 июн 15, 18:44    [17794601]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
Кролик-зануда
Guest
Shrek2,
создайте таблицу, содержащую последовательность необходимой длины.
и джойните ее по условию between start and finish
19 июн 15, 18:56    [17794641]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
_djХомяГ
Guest
Сджойнть с таблицей натуральных чисел
упрошенно
declare  @tmp table (fld1 int,fld2 int)
insert into @tmp 
select 1,12
union all
select 13,13
select number from master..spt_values spt join @tmp t
on number between fld1 and fld2
 where spt.type='P' 
19 июн 15, 18:59    [17794651]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4831
SQL2005: популярные задачи форума и CTE. Задача №1. Генерирование последовательности чисел, дат и т.п.
19 июн 15, 19:28    [17794760]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
Shrek2
Member

Откуда: Питер
Сообщений: 55
Кролик-зануда
Shrek2,
создайте таблицу, содержащую последовательность необходимой длины.
и джойните ее по условию between start and finish


Не понятно, как узнать "необходимую длину".

Вообще-то, у меня есть такой джойн с таблицей по первичному ключу, типа ID BETWEEN START AND FINISH
- в таблице 50 млн записей, медленно работает.
Может по этому случаю дадите рекомендацию?
19 июн 15, 19:47    [17794830]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
Shrek2
Member

Откуда: Питер
Сообщений: 55
_djХомяГ
Сджойнть с таблицей натуральных чисел
упрошенно
declare  @tmp table (fld1 int,fld2 int)
insert into @tmp 
select 1,12
union all
select 13,13
select number from master..spt_values spt join @tmp t
on number between fld1 and fld2
 where spt.type='P' 

жаль, но в spt_values вроде только 2048 значений
19 июн 15, 19:48    [17794840]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
Shrek2
Member

Откуда: Питер
Сообщений: 55
a_voronin
SQL2005: популярные задачи форума и CTE. Задача №1. Генерирование последовательности чисел, дат и т.п.


Я это видел, но пока не понимаю, как составить запрос применительно к моей задаче
19 июн 15, 19:53    [17794857]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
Кролик-зануда
Guest
Shrek2,

ну start и finish у вас int - несложно догадаться о необходимом диапазоне :)

по поводу "медленно работает"
+

create table numbers(n int primary key clustered)
insert numbers
select top 50000000 row_number()over(order by isnull(1,1))n
 from master..spt_values v,master..spt_values v2,master..spt_values v3


declare @t table(start int,finish int)
insert @t values(1,15),(100,10000),(21000,22000)

set statistics time,io on

select *
  from @t
  join numbers
    on n between start and finish

set statistics time,io off
--drop table numbers


(строк обработано: 10917)
Таблица "numbers". Число просмотров 3, логических чтений 30, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "#A80E3A35". Число просмотров 1, логических чтений 1, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 70 мс.
19 июн 15, 22:09    [17795277]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Shrek2
a_voronin
SQL2005: популярные задачи форума и CTE. Задача №1. Генерирование последовательности чисел, дат и т.п.


Я это видел, но пока не понимаю, как составить запрос применительно к моей задаче

Странно, потому что в вашем варианте составить запрос еще проще:

if object_id('tempdb..#TEST_NUMBERS') is not null
	drop table #TEST_NUMBERS


create table #TEST_NUMBERS (
	start int,
	finish int
);

insert into #TEST_NUMBERS
values (5, 10), (12, 15), (17, 17);

with cte as
(
	select	start as [current], finish
	from	#TEST_NUMBERS
	union all
	select	[current] + 1, finish
	from	cte
	where	[current] < finish
)
select	[current] 
from	cte
order	by [current]


Правда, тут есть один нюанс - если разница будет больше 100, то напоретесь на ограничение рекурсии, обойти его можно так:

if object_id('tempdb..#TEST_NUMBERS') is not null
	drop table #TEST_NUMBERS


create table #TEST_NUMBERS (
	start int,
	finish int
);

insert into #TEST_NUMBERS
values (5, 10), (12, 15), (17, 17), (18, 120);

declare @maxrec int, @sql nvarchar(max);


select @maxrec = max(finish - start)
from	#TEST_NUMBERS;

set @sql = N'with cte as
(
	select	start as [current], finish
	from	#TEST_NUMBERS
	union all
	select	[current] + 1, finish
	from	cte
	where	[current] < finish
)
select	[current] 
from	cte
order	by [current]
option (maxrecursion ' + cast(@maxrec as nvarchar(max)) + ')';

exec(@sql);
22 июн 15, 12:23    [17801515]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
_djХомяГ
Guest
Minamoto
Правда, тут есть один нюанс - если разница будет больше 100, то напоретесь на ограничение рекурсии, обойти его option (maxrecursion ' + cast(@maxrec as nvarchar(max)) + ')';

Зачем динамика - можно поставить option maxrecursion 0
MAXRECURSION number
Указывает максимально допустимое количество рекурсий для запроса. Число number — неотрицательное целое значение, находящееся в диапазоне между 0 и 32 767. Если указано значение 0, ограничения не применяются. Если этот параметр не указан, ограничение по умолчанию равно 100.


22 июн 15, 12:56    [17801690]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
Shrek2,
CREATE FUNCTION [dbo].[fn_num] (@min int, @max int)
RETURNS table AS

return
WITH NUM (N) as
(
  select cast(number as bigint) from master.dbo.spt_values where type = 'P' and number between 0 and 255
)

select
N1.N + isnull(N2.N,0) * 256 + isnull(N3.N,0) * 256 * 256 + isnull(N4.N,0) * 256 * 256 * 256 + @min N
from                      NUM N1 
          left outer join NUM N2 on N2.N<=(@max-@min)/256      
          left outer join NUM N3 on N3.N<=(@max-@min)/(256 * 256)
          left outer join NUM N4 on N4.N<=(@max-@min)/(256 * 256 * 256)
WHERE
N1.N + isnull(N2.N,0) * 256 + isnull(N3.N,0) * 256*256 + isnull(N4.N,0) * 256*256*256 >= 0 AND
N1.N + isnull(N2.N,0) * 256 + isnull(N3.N,0) * 256*256 + isnull(N4.N,0) * 256*256*256 <= @max-@min

GO
22 июн 15, 14:16    [17802061]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
Shrek2
Member

Откуда: Питер
Сообщений: 55
Всем ответившим - огромное спасибо!
Первый раз столкнулся с рекурсией - восхищает :)
22 июн 15, 16:59    [17802847]     Ответить | Цитировать Сообщить модератору
 Re: Генерация последовательности чисел  [new]
Кролик-зануда
Guest
Shrek2,

Вы только учтите, что при больших диапазонах вариант с рекурсией будет в разы медленнее, чем вариант с джоином таблицы чисел.
в предложенном мной наборе данных на моем сервере рекурсия раза в 3 проигрывает.
22 июн 15, 17:19    [17802921]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить