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

Откуда:
Сообщений: 633
Добрый день.

/*Есть классическая задача, не знаю как ее принято называть,
но суть в том чтобы обьеденить последовательность строк в группу по критерию не_изменения одного (или нескольких) стб.
как только значение в этом стб. меняется, по отношению к пред. строке, начинается новая группа.

Поясню на примере:
Есть табличка, в ней указаны периоды действия остатков картошки на складе.
т.е. с  1 по  5 января на складе      было 10 кг
     с  6 по  8 января на складе тоже было 10 кг
	 с  9 по 12 января на складе тоже было 10 кг, 
	 с 13 по 15 - 5 кг
	 с 16 по 17 - 5 кг
	 с 18 по 27 - опять 10 кг

задача сводится к тому чтобы обьеденить эти интервалы и в итоге получить 
с  1 по 12 января на складе было 10 кг.
с 13 по 17 января на складе было  5 кг.
с 18 по 27 января на складе было 10 кг.

При этом:
- гарантируется что дырок между интервалами нет, т.е. интервалы идут последовательно и конец пред. интервала + 1 день  = началу следующего
- минимальный шаг - день. Часов, минут и т.п. - нет.
- NULL`ов в датах и остатках тоже нет.
*/

--- Тестовые данные
--- для простоты понимания добавил столбец "Virtual_group" - которого на самом деле нет, но который предстоит как бы вычислить в данной задаче.
declare @__TEMP table(ID int identity(1, 1), Date_from date,  Date_to date, Rest money, Virtual_group int)

insert into @__TEMP(Date_from,  Date_to, Rest, Virtual_group)
values  ('2016-01-01','2016-01-05', 10, 1),
		('2016-01-06','2016-01-08', 10, 1),
		('2016-01-09','2016-01-12', 10, 1),
		('2016-01-13','2016-01-15',  5, 2),
		('2016-01-16','2016-01-17',  5, 2),
		('2016-01-18','2016-01-27', 10, 3)


-- после группировки д.б. так
select min(t.Date_from) Date_from, max(t.Date_to) Date_to, max(t.Rest) Rest
from @__TEMP t 
group by t.Virtual_group
order by Date_from asc


Естественно хотелось бы получить решение без циклов и CTE.
Аналитические функции - использовать можно.

Прошу ткнуть носом в теорию.

С Уважением,
Георгий.
16 фев 16, 06:52    [18821512]     Ответить | Цитировать Сообщить модератору
 Re: Классическая задача.  [new]
Добрый Э - Эх
Guest
Jaffar,

классическая задача - классическое же решение. Либо start_of_group на lead/lag over() + sum() over(.. order by ), либо инвариант группы на разности двух разнооконных row_number-ах
16 фев 16, 07:47    [18821559]     Ответить | Цитировать Сообщить модератору
 Re: Классическая задача.  [new]
Добрый Э - Эх
Guest
Jaffar,

вариант на инварианте группы (какое-то масло-масленое получилось):
declare @__TEMP table(ID int identity(1, 1), Date_from date,  Date_to date, Rest money, Virtual_group int)

insert into @__TEMP(Date_from,  Date_to, Rest, Virtual_group)
values  ('2016-01-01','2016-01-05', 10, 1),
		('2016-01-06','2016-01-08', 10, 1),
		('2016-01-09','2016-01-12', 10, 1),
		('2016-01-13','2016-01-15',  5, 2),
		('2016-01-16','2016-01-17',  5, 2),
		('2016-01-18','2016-01-27', 10, 3)


-- после группировки д.б. так
select MIN(Date_from) as x_Date_from, MAX(Date_to) as x_Date_to, Rest
  from (
         select t.*
              , row_number() over(partition by Rest order by Date_from,  Date_to) -
                row_number() over(order by Date_from,  Date_to) as grp_id
           from @__TEMP t 
       ) as v
 group by grp_id, rest
 order by MIN(Date_from)
16 фев 16, 07:59    [18821566]     Ответить | Цитировать Сообщить модератору
 Re: Классическая задача.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
select min(Date_from) as Date_from
     , max(Date_to) as Date_to
     , max(Rest) as Rest
from ( select id - row_number() over (partition by rest order by id) i
            , Date_from 
            , Date_to
            , Rest
         from @__TEMP
     ) t
group by i
order by 1
16 фев 16, 08:30    [18821625]     Ответить | Цитировать Сообщить модератору
 Re: Классическая задача.  [new]
Добрый Э - Эх
Guest
Knyazev Alexey,

данные из таблицы не удаляются?
16 фев 16, 08:37    [18821643]     Ответить | Цитировать Сообщить модератору
 Re: Классическая задача.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
Добрый Э - Эх
Knyazev Alexey,

данные из таблицы не удаляются?


select min(Date_from) as Date_from
     , max(Date_to) as Date_to
     , max(Rest) as Rest
from ( select row_number() over ( order by id ) 
            - row_number() over (partition by rest order by id) i
            , Date_from 
            , Date_to
            , Rest
         from @__TEMP
     ) t
group by i
order by 1
16 фев 16, 08:43    [18821672]     Ответить | Цитировать Сообщить модератору
 Re: Классическая задача.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Добрый Э - Эх,

Спасибо.
Я так и думал что там что-то такое должно быть.
16 фев 16, 09:34    [18821884]     Ответить | Цитировать Сообщить модератору
 Re: Классическая задача.  [new]
Добрый Э - Эх
Guest
Knyazev Alexey,

есть уверенность, что данные вставляются в строгом хронологическом порядке? ;)
16 фев 16, 09:52    [18821957]     Ответить | Цитировать Сообщить модератору
 Re: Классическая задача.  [new]
NrP
Member [заблокирован]

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

declare @TEMP table(dF datetime, dT datetime, V int)

insert into @TEMP(dF,  dT, V) Values ('2016-01-01', '2016-01-05', 10)
insert into @TEMP(dF,  dT, V) Values ('2016-01-06', '2016-01-08', 10)
insert into @TEMP(dF,  dT, V) Values ('2016-01-09', '2016-01-12', 10)
insert into @TEMP(dF,  dT, V) Values ('2016-01-13', '2016-01-15',  5)
insert into @TEMP(dF,  dT, V) Values ('2016-01-16', '2016-01-17',  5)
insert into @TEMP(dF,  dT, V) Values ('2016-01-18', '2016-01-27', 10)
-- select * from @Temp

-- добавим дополнительно "обрамляющие" интервалы всех интервалов
insert @TEMP(dF, dT,   V)
select       dF, dF-1, V-1 from @TEMP where dF = (select min(dF) from @Temp)

insert @TEMP(dF,   dT, V)
select       dT+1, dT, V+1 from @TEMP where dT = (select max(dT) from @Temp)
--select * from @Temp order by dF, dT

declare @Res table(Id int identity(1, 1), D datetime, V int)

insert  @Res (D,   V)
select        dF2, V2 from
(	-- сцепим "конец" и "начало"
	select T1.dF dF1, T1.dT dT1, T1.V V1
		  ,T2.dF dF2, T2.dT dT2, T2.V V2
	from @Temp T1
	join @Temp T2 on T1.dT+1 = T2.dF
) T	where V1 != V2
	order by dF1, dT1
-- select * from @Res order by ID

select R1.D dF, R2.D-1 dT, R1.V
from @Res R1
join @Res R2 on R1.Id = R2.Id - 1
	order by R1.D
17 фев 16, 20:04    [18832088]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить