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

Откуда:
Сообщений: 633
Добрый день.
Суть вопроса в следующем:
есть некая последовательность целых чисел, нужно непрерывные её куски перевести в интервалы.
например 1, 2, 3, 4, 5, 7, 8, 9 10, 12, 14, 15, 16.
перевести в 1 - 5; 7 - 10; 12 - 12; 14 - 14, 15 - 16.

Вот данные для примера
--- Исходные данные
declare @__TEMP table(NN int)
insert @__TEMP(NN)
          select 03
union all select 07
union all select 08
union all select 09
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 16
union all select 17
union all select 19
union all select 21
union all select 23
union all select 24
union all select 25
union all select 26
union all select 27
union all select 30
union all select 31

--- какой д.б. результат
          select  3 from_,  3 to_
union all select  7, 14
union all select  16, 17
union all select  19, 19
union all select  21, 21
union all select  23, 27
union all select  30, 31 



нужно сделать без CTE и циклов.
Если где-то такой ответ уже есть - прошу тыкать носом.



больше всего в mssql я люблю cross apply.
30 сен 14, 08:09    [16636763]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
aleks2
Guest
declare @T table(NN int)
insert @T(NN)
          select 03
union all select 07
union all select 08
union all select 09
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 16
union all select 17
union all select 19
union all select 21
union all select 23
union all select 24
union all select 25
union all select 26
union all select 27
union all select 30
union all select 31

declare @b table(nn int, id int identity primary key clustered);
declare @e table(nn int, id int identity primary key clustered);

insert @b
 select t.nn from @T t where not exists( select * from @t where nn = t.nn - 1 )
 order by t.nn;

insert @e
 select t.nn from @T t where not exists( select * from @t where nn = t.nn + 1 )
 order by t.nn;

select b.nn as beg, e.nn as [end] from @b b inner join @e e on b.id = e.id
30 сен 14, 08:37    [16636807]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
aleks2,
НЕ, у меня интереснее получилось, без доп. объектов. пряма не отходя от кассы
select IsNULL(tp1.From_, t.To_) From_, t.To_
from (select  --- сначала выбираем все конечные точки
	  t.NN To_,
	  LAG(t.NN) over(order by t.NN asc) PrevTo
	  from @__TEMP t 
	  where
	  		not exists(select 1 from @__TEMP tn where tn.NN = t.NN+1) /**/ ) t
outer apply(select min(t2.NN) From_ --- потом для них находим начальные
			from @__TEMP t2
			where
					t2.NN < t.To_
			and		t2.NN > t.PrevTo /**/) tp1
order by 1 asc
30 сен 14, 08:48    [16636826]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
aleks2,

но за помощь все равно спасибо.
30 сен 14, 08:48    [16636829]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
aleks2
Guest
Jaffar
aleks2,
НЕ, у меня интереснее получилось, без доп. объектов. пряма не отходя от кассы
select IsNULL(tp1.From_, t.To_) From_, t.To_
from (select  --- сначала выбираем все конечные точки
	  t.NN To_,
	  LAG(t.NN) over(order by t.NN asc) PrevTo
	  from @__TEMP t 
	  where
	  		not exists(select 1 from @__TEMP tn where tn.NN = t.NN+1) /**/ ) t
outer apply(select min(t2.NN) From_ --- потом для них находим начальные
			from @__TEMP t2
			where
					t2.NN < t.To_
			and		t2.NN > t.PrevTo /**/) tp1
order by 1 asc


Курам на смех.

ЗЫ. Доп. объекты заменяются банальным row_number(). Но с доп. объектами - быстрее.
30 сен 14, 09:15    [16636937]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
aleks2
Guest
Если уж тяга к "прогрессу" неискоренима - хоть грамотно "прогресс" надо использовать


;with
ll as (select isnull(LEAD(NN) over(order by NN asc), NN - 2 ) [lead]
            , *
            , isnull(LAG(NN) over(order by NN asc), NN + 2 )  [lag],  
         from @T
      )
, b as (select *, row_number() over(order by NN) as n from ll where NN - 1 <> [lead])
, e as (select *, row_number() over(order by NN) as n from ll where NN + 1 <> [lag])
select b.NN as beg, e.NN as [end] from b inner join e on b.n = e.n;
30 сен 14, 09:32    [16636993]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Добрый Э - Эх
Guest
Jaffar,

так-то тут вообще банальная группировка по разности поля-"последовательности" и row_number-а.
30 сен 14, 09:56    [16637066]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх,

ну или в коде:

--
-- Тестовые данные:
with
  t (nn) as (
            select 03
union all select 07
union all select 08
union all select 09
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 16
union all select 17
union all select 19
union all select 21
union all select 23
union all select 24
union all select 25
union all select 26
union all select 27
union all select 30
union all select 31
  )
--
-- Основной запрос:
select min(nn) as from_, max(nn) as to_
  from (
         select nn, nn - row_number() over(order by nn) as grp_id
          from t
       ) v
 group by grp_id
 order by 1
on-line проверка на sqlfiddle.com
30 сен 14, 10:02    [16637084]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Добрый Э - Эх
Guest
aleks2
Если уж тяга к "прогрессу" неискоренима - хоть грамотно "прогресс" надо использовать


;with
ll as (select isnull(LEAD(NN) over(order by NN asc), NN - 2 ) [lead]
            , *
            , isnull(LAG(NN) over(order by NN asc), NN + 2 )  [lag],  
         from @T
      )
, b as (select *, row_number() over(order by NN) as n from ll where NN - 1 <> [lead])
, e as (select *, row_number() over(order by NN) as n from ll where NN + 1 <> [lag])
select b.NN as beg, e.NN as [end] from b inner join e on b.n = e.n;
aleks2
Курам на смех.


Лучше и не скажешь. А главное - так самокритично ;)
30 сен 14, 10:15    [16637152]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
aleks2
Guest
Добрый Э - Эх
aleks2
Если уж тяга к "прогрессу" неискоренима - хоть грамотно "прогресс" надо использовать


;with
ll as (select isnull(LEAD(NN) over(order by NN asc), NN - 2 ) [lead]
            , *
            , isnull(LAG(NN) over(order by NN asc), NN + 2 )  [lag],  
         from @T
      )
, b as (select *, row_number() over(order by NN) as n from ll where NN - 1 <> [lead])
, e as (select *, row_number() over(order by NN) as n from ll where NN + 1 <> [lag])
select b.NN as beg, e.NN as [end] from b inner join e on b.n = e.n;
aleks2
Курам на смех.


Лучше и не скажешь. А главное - так самокритично ;)

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

Тут даже куры плачут.
30 сен 14, 12:09    [16637853]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
aleks2, эх

Господа подскажите вот еще что.
Есть, кажись, в 2012 ms-sql`е вьюха системная которая типа возвращает пронумерованные строки типа
1
2
3
4
5
и т.д.

как она называется? а то я запарился ее искать
или такого нету?
Я что-то такое видел, возможно это функция была.
30 сен 14, 12:47    [16638073]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
aleks2
Но с доп. объектами - быстрее
Чушь.
aleks2
Ээээ, дарагуля, группировки - зло.
Ты пихаешь самый тормознутый вариант.
Опять чушь.

+ Простенький тест
if object_id('tempdb..#t', 'U') is not null
 drop table #t;

create table #t (nn int primary key);
insert into #t select top (130000) row_number() over (order by char('a')) from master.dbo.spt_values a cross join master.dbo.spt_values b;
with x as (select top (30) percent nn from #t order by checksum(newid())) delete from x;

declare @r table (Вариант varchar(100), [Затрачено мс] int);
declare @dt datetime2, @bn int, @en int;

/*---------------------------------------------------*/
select @dt = sysdatetime();

declare @b table(nn int, id int identity primary key clustered);
declare @e table(nn int, id int identity primary key clustered);

insert @b
 select t.nn from #t t where not exists( select * from #t where nn = t.nn - 1 )
 order by t.nn;

insert @e
 select t.nn from #t t where not exists( select * from #t where nn = t.nn + 1 )
 order by t.nn;

select @bn = b.nn, @en = e.nn from @b b inner join @e e on b.id = e.id

insert into @r values ('aleks2 + табличные переменные', datediff(ms, @dt, sysdatetime()));
/*---------------------------------------------------*/

/*---------------------------------------------------*/
select @dt = sysdatetime();

select
 @bn = a.nn, @en = b.nn
from
 (select t.nn, row_number() over (order by t.nn) as rn from #t t where not exists( select * from #t where nn = t.nn - 1)) a join
 (select t.nn, row_number() over (order by t.nn) as rn from #t t where not exists( select * from #t where nn = t.nn + 1)) b on b.rn = a.rn;

insert into @r values ('Банальный row_number', datediff(ms, @dt, sysdatetime()));
/*---------------------------------------------------*/

/*---------------------------------------------------*/
select @dt = sysdatetime();

select @bn = min(nn), @en = max(nn)
  from (
         select nn, nn - row_number() over(order by nn) as grp_id
          from #t
       ) v
 group by grp_id
 order by 1;
 
insert into @r values ('Добрый Э - Эх', datediff(ms, @dt, sysdatetime()));
/*---------------------------------------------------*/

/*---------------------------------------------------*/
select @dt = sysdatetime();
--/*
 select @bn = IsNULL(tp1.From_, t.To_), @en = t.To_
from (select  --- сначала выбираем все конечные точки
	  t.NN To_,
	  LAG(t.NN) over(order by t.NN asc) PrevTo
	  from #t t 
	  where
	  		not exists(select 1 from #t tn where tn.NN = t.NN+1) /**/ ) t
outer apply(select min(t2.NN) From_ --- потом для них находим начальные
			from #t t2
			where
					t2.NN < t.To_
			and		t2.NN > t.PrevTo /**/) tp1
order by 1 asc
--*/ 
insert into @r values ('jaffar', datediff(ms, @dt, sysdatetime()));
/*---------------------------------------------------*/

/*---------------------------------------------------*/
select @dt = sysdatetime();

with
ll as (select isnull(LEAD(NN) over(order by NN asc), NN - 2 ) [lead]
            , *
            , isnull(LAG(NN) over(order by NN asc), NN + 2 )  [lag]  
         from #t
      )
, b as (select *, row_number() over(order by NN) as n from ll where NN - 1 <> [lead])
, e as (select *, row_number() over(order by NN) as n from ll where NN + 1 <> [lag])
select @bn = b.NN, @en = e.NN from b inner join e on b.n = e.n;

insert into @r values ('jaffar + модификация от aleks2', datediff(ms, @dt, sysdatetime()));
/*---------------------------------------------------*/

select * from @r order by [Затрачено мс];
Результат:
ВариантЗатрачено мс
Банальный row_number75
Добрый Э - Эх123
jaffar154
aleks2 + табличные переменные188
jaffar + модификация от aleks2276


Jaffar
Есть, кажись, в 2012 ms-sql`е вьюха системная которая типа возвращает пронумерованные строки типа
Заведите себе постоянную таблицу натуральных чисел и не парьтесь.
30 сен 14, 13:00    [16638142]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
invm,


Я знаю что можно самому - но хочется вспомнить.
для изучения.
30 сен 14, 13:02    [16638148]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Jaffar,
master..spt_values
30 сен 14, 13:04    [16638162]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
invm,

что вы как дети носитесь с производительностью.
в данном случае это не главное.
забейте пусть все тормозит.
30 сен 14, 13:04    [16638163]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Jaffar
что вы как дети носитесь с производительностью.
в данном случае это не главное.

ну вот здесь Вы,увы,не правы в корне
30 сен 14, 13:06    [16638167]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Jaffar
что вы как дети носитесь с производительностью.
в данном случае это не главное.

Ну так делайте курсором тогда. Милое дело.
30 сен 14, 13:08    [16638176]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Maxx,

не это не то.
там вьюха была друга ятам кажись было поле типа N и значения 1, 2, 3
а состояла она из какой-то таблички или CTE`шки за_кросс_ждойненной на себя 7 или 8 раз.
как-то так
30 сен 14, 13:13    [16638200]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Glory,


здесь главное удобство чтения и применения в осн. задаче.
т.к. понятно что сортировать просто числа никому не нужно.
30 сен 14, 13:14    [16638207]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Jaffar
здесь главное удобство чтения

Ну так в курсоре прозрачнее некуда
если текущее значение больше предыдущего на 1 - это продолжение диапазона
если текущее значение больше предыдущего больше, чем 1 - это начало нового диапазона
30 сен 14, 13:16    [16638219]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Jaffar
Maxx,

не это не то.
там вьюха была друга ятам кажись было поле типа N и значения 1, 2, 3
а состояла она из какой-то таблички или CTE`шки за_кросс_ждойненной на себя 7 или 8 раз.
как-то так
WITH CTE(N) AS(SELECT 1 UNION ALL SELECT N+1 FROM CTE WHERE N<1000)
SELECT N FROM CTE ORDER BY N OPTION(MAXRECURSION 0);
30 сен 14, 13:17    [16638224]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Jaffar
invm,

что вы как дети носитесь с производительностью.
в данном случае это не главное.
забейте пусть все тормозит.
Форум не только вы читаете. Для других производительность очень даже важна.
Jaffar
там вьюха была друга ятам кажись было поле типа N и значения 1, 2, 3
а состояла она из какой-то таблички или CTE`шки за_кросс_ждойненной на себя 7 или 8 раз.
как-то так
Нет такой штатной. Самописная, да, всплывает на форуме регулярно.
30 сен 14, 13:22    [16638262]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
iap,

да как-то так,
но там уже была готовая вьюха.
мне нужно её название.
30 сен 14, 13:23    [16638276]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Jaffar
мне нужно её название.

как вы лодку назовтете так она и поплывет
30 сен 14, 13:25    [16638290]     Ответить | Цитировать Сообщить модератору
 Re: Задачка про пеервод последовательности в интервалы.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Jaffar
iap,

да как-то так,
но там уже была готовая вьюха.
мне нужно её название.
Написать VIEW из предложенного SELECTа никак невозможно?
А! Ну да - она же ещё не готовая!
30 сен 14, 13:26    [16638302]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить