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

Откуда:
Сообщений: 181
Добрый день!
В Оракле есть замечательные аналитические функции: lag(), lead() - в MSSQL2005 их нет.
Иногда встает такая задача:
есть:
a
b
c
нужно для каждой записи получить следующую:
а b
b c
c null
1) В mssql2000 это делается, как правило, подзапросом с обращением к той же самой таблице.
select * from(select a = 'a' union all select 'b' union all select 'c')a

select *, next_a = ( select top 1 a from(
   select * from(select a = 'a' union all select 'b' union all select 'c')a
                                        )b where b.a > a.a order by b.a)
from(select a = 'a' union all select 'b' union all select 'c')a
2) В mssql2005 появились аналитические функции, но lag(), lead() по-прежнему нету.
Поэтому пришлось изобретать велосипед:

select * from(select a = 'a' union all select 'b' union all select 'c')a

select *, npp = row_number() over(order by a) 
  from(select a = 'a' union all select 'b' union all select 'c')a
order by npp


select *, npp1 = npp+k
from(
select *, npp = row_number() over(order by a) 
  from(select a = 'a' union all select 'b' union all select 'c')a
)a
, (select k = 0 union all select 1)k
order by npp1


select npp1 = npp+k, a = max(case when k = 1 then a end), lead_a = max(case when k = 0 then a end)
from(
select *, npp = row_number() over(order by a) 
  from(select a = 'a' union all select 'b' union all select 'c')a
)a
, (select k = 0 union all select 1)k
--where not(npp=1 and k = 0)
--where a is not null 
group by npp+k
28 фев 07, 06:48    [3840451]     Ответить | Цитировать Сообщить модератору
 Re: lag, lead - велосипед для mssql2005  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31328
DamirS
В mssql2005 появились аналитические функции, но lag(), lead() по-прежнему нету.
Поэтому пришлось изобретать велосипед:
Это вы изобрели не велосипед, а самолёт :-)

Зачем так сложно? Разве в mssql2005 нельзя это сделать подзапросом с обращением к той же самой таблице, как и в 2000-ом, и в 7-м, и в других версиях?

DamirS
В Оракле есть замечательные аналитические функции: lag(), lead() - в MSSQL2005 их нет.
В фокспро тоже, наверное, есть. Но я не понимаю, зачем они нужны для обработки множеств, для реляционной СУБД.

Не знаю, что такое ф-ции lag() и lead(), но если это просто записи следующая или предыдущая в наборе данных... Зачем нужны ф-ции для их определения? Ведь в клиенте есть все записи из возвращаемого набора, он и так сможет определить следующую или предыдущую запись для текущей.
28 фев 07, 09:27    [3840742]     Ответить | Цитировать Сообщить модератору
 Re: lag, lead - велосипед для mssql2005  [new]
Бабичев Сергей
Member

Откуда: Красноярск
Сообщений: 2497
2 Автор:

Вот так попробуй:
with 
  tst (my_str) as
    (
      select 'a' as my_str union all
      select 'b' as my_str union all
      select 'c' as my_str
    )
select t1.my_str, 
       min(t2.my_str) as lead_my_str, 
       max(t3.my_str) as lag_my_str2
  from tst t1
  left join
       tst t2
    on t1.my_str < t2.my_str
  left join
       tst t3
    on t1.my_str > t3.my_str
group by t1.my_str
28 фев 07, 09:58    [3840893]     Ответить | Цитировать Сообщить модератору
 Re: lag, lead - велосипед для mssql2005  [new]
Nick Anikin
Member

Откуда: Москва
Сообщений: 2174
а через курсор сделать вам не подойдет?
28 фев 07, 10:26    [3841081]     Ответить | Цитировать Сообщить модератору
 Re: lag, lead - велосипед для mssql2005  [new]
Strong
Member

Откуда: Россия
Сообщений: 277
заполнял "дырки" между периодами:
делал так :
		declare @CurMod table 
		(
			[ccode] [char](5) COLLATE Cyrillic_General_CI_AS NOT NULL,
			[cname] [char](150) COLLATE Cyrillic_General_CI_AS NOT NULL,
			[dfrom] [datetime] NULL,
			[dto] [datetime] NULL,
			[Id] [int]  NULL
		)
	
	declare @temp table 
		(
			[ccode] [char](5) COLLATE Cyrillic_General_CI_AS NULL,
			[dfrom] [datetime] NULL,
			[ID] [int] NULL,
			PeriodRank int null ,
			[rank] [int] NULL
		)


INSERT INTO  @CurMod  -- периоды были
           (
			   [ccode]
			   ,[cname]
			   ,[dfrom]
			   ,[dto]
			   ,[ID]
           )
           
	select
           '10405'
           ,'--00--'
           , '20070101'
           , '20070130'
           , 52525
	union all
           select
           '10405'
           ,'--01--'
           , '20070103'
           , '20070110'
           , 52535
	union all
           select
           '10406'
           ,'--02--'
           , '20070101'
           , '20070130'
           , 52545
    union all
           select
           '10406'
           ,'--04--'
           , '20070104'
           , '20070114'
           , 52575
           
INSERT INTO  @CurMod -- периоды добавил пользователь
           (
			   [ccode]
			   ,[cname]
			   ,[dfrom]
			   ,[dto]
			   ,[ID]
           )
     select
           '10405'
           ,'--03--'
           , '20070104'
           , '20070106'
           , 52565
	union all
           select
           '10406'
           ,'--05--'
           , '20070105'
           , '20070107'
           , 52595
          

select * from @curmod order by ccode, dfrom
	
insert into @temp
select 
	   T01.[ccode]
	  , T01.[dfrom]
	  , T01.[ID]
	  , ABS( cast ((T01.[dto] -T01.[dfrom]) as int ) ) as PeriodRank
	  , Row_number() OVER (PARTITION BY T01.ccode order by T01.ccode ,T01.dfrom ) as rank
	from 
		(
			select 
				   T01.[ccode] 
				  , T01.[dfrom] 
				  , T01.[dto]
				  , T01.[ID]
			from @CurMod as t01
			union all
			select 
				   T01.[ccode]
				  , T01.[dto] -- поменяем поля dto  dfrom местами  для сортировки дат
				  , T01.[dfrom]
				  , T01.[ID]
			from @CurMod as t01
		) as T01


		
select distinct
	   C.[ccode]
	  , D.[cname]
	  , C.[dfrom]
	  , C.[dto]
	  , C.[ID]
	from 
		(
			select 
			   A.[ccode]
			  , case
					when B.PeriodRank > A.PeriodRank
						then dateadd( day , +1, A.[dfrom])
					else A.[dfrom]
				  end
				as dfrom
			  
			  , case
					when B.PeriodRank < A.PeriodRank
						then dateadd( day , -1, B.dfrom)
					else B.dfrom
				  end
				as dto
			  ,    case 
						when B.PeriodRank > A.PeriodRank
							then B.ID
						else A.ID
					end
				 as ID
			from 
				@temp
				 as A
			inner join 
				@temp
				 as B
				on B.rank = A.rank +1 and B.ccode = A.ccode
		) as C
	left join @CurMod as D
		on D.ID = C.ID
	
	order by 1,3
			
28 фев 07, 10:30    [3841111]     Ответить | Цитировать Сообщить модератору
 Re: lag, lead - велосипед для mssql2005  [new]
Даутов
Member

Откуда: Казань
Сообщений: 502
Вот здесь http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc
подробно объяснена вся неполнота текущей реализации конструкции OVER в SQL Server 2005 (по сравнению со ANSI SQL:1999 OLAP Extensions/ANSI SQL:2003),
включая отсутствие аналитических функций LAG и LEAD.
Предлагается проголосовать за более полную реализацию в следующих версиях
https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=over+clause+enhancement

Рекомендую поддержать
28 фев 07, 11:01    [3841373]     Ответить | Цитировать Сообщить модератору
 Re: lag, lead - велосипед для mssql2005  [new]
DamirS
Member

Откуда:
Сообщений: 181
2 Бабичев Сергей
Ваш метод плохо переносится на случай более сложной таблицы.
Т.е. если полей штук 5 и нужно их перечислить все.

2 Даутов Всё понятно, не доделали еще... Надеюсь, с каким-нить сервис-паком появится.

2alexeyvg Всё дело в стоимости запроса. Аналитические функции сильно выигрывают в производительности.
28 фев 07, 14:12    [3843007]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить