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

Откуда:
Сообщений: 8
Добрый день, коллеги, окажите, пожалуйста, содействие.
Суть в том, что есть табличка в которой id и 3 даты. Надо схлопнуть таким образом, чтобы если дата 3 пустая, то берем дату 3 из следующей строки и т.д. последняя строка должна содержать дату 2 в качестве закрывающей.
С помощью аналитических функций не удалось добиться результата, решил попробовать с помощью курсора. Но ранее не пользовался и как-то не так работает. Подскажите, возможно ли обойтись без курсора, также необходимо учитывать, что записей в таблице много в рамках каждого ID, как это возможно учесть?
т.е. по приведенному ниже примеру должны получится следующие строки:
1 2013-02-22 00:00:00.000 2013-03-01 00:00:00.000
1 2013-03-22 00:00:00.000 2013-04-30 00:00:00.000
1 2013-06-22 00:00:00.000 2013-07-12 00:00:00.000
1 2013-07-22 00:00:00.000 2013-08-14 00:00:00.000
1 2013-09-22 00:00:00.000 2013-10-28 00:00:00.000
1 2013-12-22 00:00:00.000 2014-02-13 00:00:00.000

и т.д. ........
И послед строка:
1 2015-03-22 00:00:00.000 2015-08-21 00:00:00.000


create table dbo.test (
		id int,
		df datetime, dt datetime);

declare some_cursor cursor

for
  (select 1 as id, '2013-02-22 00:00:00.000' as df, '2013-03-21 00:00:00.000' as dt, '2013-03-01 00:00:00.000' as fd union 
select 1 as id, '2013-03-22 00:00:00.000' as df, '2013-04-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2013-04-22 00:00:00.000' as df, '2013-05-21 00:00:00.000' as dt, '2013-04-30 00:00:00.000' as fd union all 
select 1 as id, '2013-06-22 00:00:00.000' as df, '2013-07-21 00:00:00.000' as dt, '2013-07-12 00:00:00.000' as fd union all 
select 1 as id, '2013-07-22 00:00:00.000' as df, '2013-08-21 00:00:00.000' as dt, '2013-08-14 00:00:00.000' as fd union all 
select 1 as id, '2013-09-22 00:00:00.000' as df, '2013-10-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2013-10-22 00:00:00.000' as df, '2013-11-21 00:00:00.000' as dt, '2013-10-28 00:00:00.000' as fd union all 
select 1 as id, '2013-12-22 00:00:00.000' as df, '2014-01-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2014-01-22 00:00:00.000' as df, '2014-02-21 00:00:00.000' as dt, '2014-02-13 00:00:00.000' as fd union all 
select 1 as id, '2014-03-22 00:00:00.000' as df, '2014-04-21 00:00:00.000' as dt, '2014-04-18 00:00:00.000' as fd union all 
select 1 as id, '2014-05-22 00:00:00.000' as df, '2014-06-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2014-06-22 00:00:00.000' as df, '2014-07-21 00:00:00.000' as dt, '2014-06-26 00:00:00.000' as fd union all 
select 1 as id, '2014-07-22 00:00:00.000' as df, '2014-08-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2014-08-22 00:00:00.000' as df, '2014-09-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2014-09-22 00:00:00.000' as df, '2014-10-21 00:00:00.000' as dt, '2014-09-26 00:00:00.000' as fd union all 
select 1 as id, '2014-10-22 00:00:00.000' as df, '2014-11-21 00:00:00.000' as dt, '2014-11-21 00:00:00.000' as fd union all 
select 1 as id, '2014-11-22 00:00:00.000' as df, '2014-12-21 00:00:00.000' as dt, '2014-12-20 00:00:00.000' as fd union all 
select 1 as id, '2014-12-22 00:00:00.000' as df, '2015-01-21 00:00:00.000' as dt, '2015-01-20 00:00:00.000' as fd union all 
select 1 as id, '2015-01-22 00:00:00.000' as df, '2015-02-21 00:00:00.000' as dt, '2015-02-21 00:00:00.000' as fd union all 
select 1 as id, '2015-02-22 00:00:00.000' as df, '2015-03-21 00:00:00.000' as dt, '2015-03-21 00:00:00.000' as fd union all 
select 1 as id, '2015-03-22 00:00:00.000' as df, '2015-04-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2015-04-22 00:00:00.000' as df, '2015-05-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2015-05-22 00:00:00.000' as df, '2015-06-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2015-06-22 00:00:00.000' as df, '2015-07-21 00:00:00.000' as dt, 'NULL' as fd union all 
select 1 as id, '2015-07-22 00:00:00.000' as df, '2015-08-21 00:00:00.000' as dt, 'NULL' as fd 
) 

open some_cursor


declare  @int_var int, @df datetime, @dt datetime, @fd datetime,  @dff datetime

fetch next from some_cursor INTO  @int_var,  @df, @dt , @fd

while @@FETCH_STATUS = 0
begin

 if @fd is not null  
SELECT @int_var, @df, @fd
INSERT INTO dbo.test (id,df, dt) VALUES (@int_var,  @df, @fd)
 set @dff = @df

fetch next from some_cursor INTO @int_var,  @df, @dt , @fd
SELECT @int_var, @dff, @fd
INSERT INTO dbo.test (id,df, dt) VALUES (@int_var, @dff, @fd)

end

close some_cursor
deallocate some_cursor


Сообщение было отредактировано: 13 авг 18, 10:10
13 авг 18, 10:05    [21639971]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
Щукина Анна
Member

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

то есть, другими словами - вам нужна "протяжка" последнего непустого значения на последующие строки? для этого идеально подошел бы last_value() over( order by ... ignore nulls), но в MS его пока нет. Но его можно заменить аналитическим MAX-ом с небольшим допилом напильником...
13 авг 18, 10:20    [21639982]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Щукина Анна
BSCHECK,

то есть, другими словами - вам нужна "протяжка" последнего непустого значения на последующие строки? для этого идеально подошел бы last_value() over( order by ... ignore nulls), но в MS его пока нет. Но его можно заменить аналитическим MAX-ом с небольшим допилом напильником...

но есть first_value
13 авг 18, 10:22    [21639984]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
Щукина Анна
Member

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

сказал - "а", говори - "б"(с)

показывайте, как вы его тут будете применять без ignore nulls.

но даже если и примените... дальнейшая логика не совсем понятна. что будет считаться непрерывной группой строк, в конце которой должно быть "последняя строка должна содержать дату 2 в качестве закрывающей"(с) но это не к вам вопрос, а ТС...
13 авг 18, 10:29    [21639994]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
BSCHECK
Member

Откуда:
Сообщений: 8
Анна, Тарак, так как порекомендуете?
Или поможете допилить курсор?
MS SQL 2016
13 авг 18, 10:34    [21640003]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
BSCHECK
Member

Откуда:
Сообщений: 8
Щукина Анна
BSCHECK,

то есть, другими словами - вам нужна "протяжка" последнего непустого значения на последующие строки? для этого идеально подошел бы last_value() over( order by ... ignore nulls), но в MS его пока нет. Но его можно заменить аналитическим MAX-ом с небольшим допилом напильником...

Все -таки назвал бы скорее схлопыванием строк, видно по примеру, что 2 строка схлопывается с 3 и из 2 строки берется df, а из 3 - fd
13 авг 18, 10:38    [21640011]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2344
BSCHECK,

но при этом почему-то совсем исчезают даты dt из 3 и 5 строк (дальше не смотрел)
критерий не точный совсем.
13 авг 18, 10:47    [21640028]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
BSCHECK
Member

Откуда:
Сообщений: 8
ShIgor
BSCHECK,

но при этом почему-то совсем исчезают даты dt из 3 и 5 строк (дальше не смотрел)
критерий не точный совсем.


Не знаю как еще точнее описать. Фактически dt нужна только для последней строки.
Берем строку, если fd заполнена записываем id, df, fd, если - нет переходим к следующей строке , если опять не заполнена , то опять к следующей. когда встречаем заполненную, то записываем id, df - из первой строки и fd из той где заполнена. Ну к примеру по этим 7 строкам:
select 1 as id, '2013-02-22 00:00:00.000' as df, '2013-03-21 00:00:00.000' as dt, '2013-03-01 00:00:00.000' as fd union
select 1 as id, '2013-03-22 00:00:00.000' as df, '2013-04-21 00:00:00.000' as dt, 'NULL' as fd union all
select 1 as id, '2013-04-22 00:00:00.000' as df, '2013-05-21 00:00:00.000' as dt, '2013-04-30 00:00:00.000' as fd union all
select 1 as id, '2013-06-22 00:00:00.000' as df, '2013-07-21 00:00:00.000' as dt, '2013-07-12 00:00:00.000' as fd union all
select 1 as id, '2013-07-22 00:00:00.000' as df, '2013-08-21 00:00:00.000' as dt, '2013-08-14 00:00:00.000' as fd union all
select 1 as id, '2013-09-22 00:00:00.000' as df, '2013-10-21 00:00:00.000' as dt, 'NULL' as fd union all
select 1 as id, '2013-10-22 00:00:00.000' as df, '2013-11-21 00:00:00.000' as dt, '2013-10-28 00:00:00.000' as fd
Должны получиться след. 5 записей:
1 2013-02-22 00:00:00.000 2013-03-01 00:00:00.000
1 2013-03-22 00:00:00.000 2013-04-30 00:00:00.000
1 2013-06-22 00:00:00.000 2013-07-12 00:00:00.000
1 2013-07-22 00:00:00.000 2013-08-14 00:00:00.000
1 2013-09-22 00:00:00.000 2013-10-28 00:00:00.000
13 авг 18, 10:56    [21640036]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
BSCHECK
берем дату 3 из следующей строки и т.д
Чем определяется "следующая строка"?
Все id равны почему-то 1...

P.S. Забудьте на время слово "курсор".
13 авг 18, 11:10    [21640057]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
BSCHECK
Member

Откуда:
Сообщений: 8
iap
BSCHECK
берем дату 3 из следующей строки и т.д
Чем определяется "следующая строка"?
Все id равны почему-то 1...

P.S. Забудьте на время слово "курсор".


Следующая строка определяется df. c сортировкой по возрастанию.
Для примера привел id =1, т.к. рассматриваем строки и даты в рамках id.
Естественно, таких id в таблице много.
13 авг 18, 11:15    [21640066]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
invm
Member

Откуда: Москва
Сообщений: 9350
select
 t.id, min(t.df), b.fd
from
 [есть табличка] t outer apply
 (select top (1) fd from [есть табличка] where t.fd is null and id = t.id and df > t.df and fd is not null order by df) a cross apply
 (select isnull(t.fd, a.fd)) b(fd)
group by
 t.id, b.fd
order by
 t.id, min(t.df), b.fd;
13 авг 18, 11:49    [21640112]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
BSCHECK
Member

Откуда:
Сообщений: 8
invm
select
 t.id, min(t.df), b.fd
from
 [есть табличка] t outer apply
 (select top (1) fd from [есть табличка] where t.fd is null and id = t.id and df > t.df and fd is not null order by df) a cross apply
 (select isnull(t.fd, a.fd)) b(fd)
group by
 t.id, b.fd
order by
 t.id, min(t.df), b.fd;


Спасибо, INVM! Да вы прямо маг и чародей!
13 авг 18, 11:58    [21640129]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Спортивный интерес - так работает (не до конца понимаю условие)?

select id, min(df) df, max(fd) fd
from
(
	select *, sum (gs) over (partition by id order by df) gr
	from 
	(
		select 
			id, df, fd, iif((lag(fd) over (partition by id order by df) is not null) ,1, 0) gs
		from [есть табличка]
	) t
) t
group by id, gr
13 авг 18, 15:14    [21640637]     Ответить | Цитировать Сообщить модератору
 Re: Курсоры циклы консультация  [new]
BSCHECK
Member

Откуда:
Сообщений: 8
LameUser
Спортивный интерес - так работает (не до конца понимаю условие)?

select id, min(df) df, max(fd) fd
from
(
	select *, sum (gs) over (partition by id order by df) gr
	from 
	(
		select 
			id, df, fd, iif((lag(fd) over (partition by id order by df) is not null) ,1, 0) gs
		from [есть табличка]
	) t
) t
group by id, gr


Да, так тоже работает. Еще 1 маг ))
13 авг 18, 15:56    [21640736]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить