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

Откуда: Саратов
Сообщений: 44
Привет коллеги!

Есть сравнительно большой набор данных следующей структуры:
CEN (Float), PR_ID (Int), DATE_IN DateTime, DATE_OUT DateTime

Набор данных организован так, что DATE_OUT предудущей строки на 1 день больше DATE_IN в следующей строке с тем же PR_ID. Значение DATE_OUT может быть неопределённым для некоторых строк.

Необходим запрос, позволяющий выбрать из данного набора данных набор, в котором все строки с совпадающими CEN и PR_ID были бы объединены в набор уникальных строк с DATE_IN первой повторяющейся строки и DATE_OUT последней повторяющейся.

Пример:

Строки:
5.99, 8, 01.01.2010, 31.03.2010
5.99, 8, 01.04.2010, 31.10.2010
5.99, 8, 01.11.2010, 31.01.2011
5.99, 8, 01.02.2011, NULL
1.90, 8, 01.04.2010, 31.10.2010
1.90, 8, 01.11.2010, 31.01.2011
1.90, 3, 01.02.2011, 31.05.2011

стали бы в итоге строками:

5.99, 8, 01.01.2010, NULL
1.90, 8, 01.04.2010, 31.01.2011
1.90, 3, 01.02.2011, 31.05.2011


Чтобы примерно представить особенности оптимизации, скажу, что первичный набор строк - примерно полмиллиона строк.

P.S. Использование курсора - нежелательно.
4 авг 11, 09:11    [11068599]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
MasterOfCode
Member

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

Как-то так

select cen, pr_id, start_period, case end_period when '2030-01-01' then null else end_period  end
from (
select cen, pr_id, min(date_in) as start_period, max(isnull(date_out, '2030-01-01')) as  end_period
group by cen, pr_id ) t
4 авг 11, 09:20    [11068625]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
BestiA
Member

Откуда: Саратов
Сообщений: 44
Нет. Не пойдёт. поле CEN может совпадать в начале и где-нибудь ближе к концу периода. Поэтому простая группировка по нему испортит всё дело.
4 авг 11, 10:16    [11068869]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
BestiA
Member

Откуда: Саратов
Сообщений: 44
P.S. вовсе необязательно, что CEN для каждого PR_ID одно и то же. Оно вообще-то может меняться. И ощутимо меняется в течение всех периодов.
4 авг 11, 10:20    [11068893]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
ilyaBS
Member

Откуда: Киев
Сообщений: 77
BestiA
P.S. вовсе необязательно, что CEN для каждого PR_ID одно и то же. Оно вообще-то может меняться. И ощутимо меняется в течение всех периодов.


тогда нужен пример. Покажите пожалуйста более реальные данные.
4 авг 11, 10:51    [11069088]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3265
BestiA
P.S. вовсе необязательно, что CEN для каждого PR_ID одно и то же. Оно вообще-то может меняться. И ощутимо меняется в течение всех периодов.


автор
совпадающими CEN и PR_ID были бы объединены в набор уникальных строк с DATE_IN первой повторяющейся строки и DATE_OUT последней повторяющейся.

Определитесь уж , что вы хотите
4 авг 11, 11:13    [11069271]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
BestiA
Member

Откуда: Саратов
Сообщений: 44
Ken@t
BestiA
P.S. вовсе необязательно, что CEN для каждого PR_ID одно и то же. Оно вообще-то может меняться. И ощутимо меняется в течение всех периодов.


автор
совпадающими CEN и PR_ID были бы объединены в набор уникальных строк с DATE_IN первой повторяющейся строки и DATE_OUT последней повторяющейся.

Определитесь уж , что вы хотите


Всё правильно написал. просто пример неудачный. Вот получше примерчик:

Строки:
5.99, 8, 01.01.2010, 31.03.2010
5.99, 8, 01.04.2010, 04.07.2010
5.00, 8, 05.07.2010, 31.07.2010
5.00, 8, 01.08.2010, 31.08.2010
5.00, 8, 01.09.2010, 30.09.2010
5.99, 8, 01.10.2010, 31.10.2010
5.99, 8, 01.11.2010, 31.01.2011
5.99, 8, 01.02.2011, NULL
1.90, 8, 01.04.2010, 31.10.2010
1.90, 8, 01.11.2010, 31.01.2011
1.90, 3, 01.02.2011, 31.05.2011

стали бы в итоге строками:
5.99, 8, 01.01.2010, 04.07.2010
5.00, 8, 05.07.2010, 30.09.2010
5.99, 8, 01.10.2010, NULL
1.90, 8, 01.04.2010, 31.01.2011
1.90, 3, 01.02.2011, 31.05.2011
4 авг 11, 12:30    [11070004]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
ё
Guest
+
declare @t table (CEN decimal(18,2), PR_ID Int, DATE_IN DateTime, DATE_OUT DateTime)

SET DATEFORMAT dmy

insert into @t
select 5.99, 8, '01.01.2010', '31.03.2010' union all
select 5.99, 8, '01.04.2010', '04.07.2010' union all
select 5.00, 8, '05.07.2010', '31.07.2010' union all
select 5.00, 8, '01.08.2010', '31.08.2010' union all
select 5.00, 8, '01.09.2010', '30.09.2010' union all
select 5.99, 8, '01.10.2010', '31.10.2010' union all
select 5.99, 8, '01.11.2010', '31.01.2011' union all
select 5.99, 8, '01.02.2011', NULL  union all
select 1.90, 8, '01.04.2010', '31.10.2010' union all
select 1.90, 8, '01.11.2010', '31.01.2011' union all
select 1.90, 3, '01.02.2011', '31.05.2011'

;with cte as
(
select CEN, PR_ID, DATE_IN, DATE_OUT
from @t 

union all

select cte.CEN, cte.PR_ID, cte.DATE_IN, t.DATE_OUT 
from @t t 
inner join cte
  on    dateadd(d, 1, cte.DATE_OUT) =  t.DATE_IN
    and cte.CEN		 = t.CEN
    and cte.PR_ID	 = t.PR_ID
)

select a.CEN, a.PR_ID, a.DATE_IN, nullif(max(a.DATE_OUT), '2999-01-01') as DATE_OUT
from

(select cte.CEN, cte.PR_ID, min(cte.DATE_IN) as DATE_IN, isnull(cte.DATE_OUT, '2999-01-01') as DATE_OUT
from cte
group by cte.CEN, cte.PR_ID, cte.DATE_OUT) a

group by a.CEN, a.PR_ID, a.DATE_IN
order by a.DATE_IN

CEN                                     PR_ID       DATE_IN                 DATE_OUT
--------------------------------------- ----------- ----------------------- -----------------------
5.99                                    8           2010-01-01 00:00:00.000 2010-07-04 00:00:00.000
1.90                                    8           2010-04-01 00:00:00.000 2011-01-31 00:00:00.000
5.00                                    8           2010-07-05 00:00:00.000 2010-09-30 00:00:00.000
5.99                                    8           2010-10-01 00:00:00.000 NULL
1.90                                    3           2011-02-01 00:00:00.000 2011-05-31 00:00:00.000
4 авг 11, 13:49    [11070608]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
BestiA
Member

Откуда: Саратов
Сообщений: 44
Спасибо за решение. Почти все условия задачи соблюдены. Однако данный вариант решения работает крайне медленно с большими наборами данных. Похоже, что несмотря на возгласы отдельных эстетствующих товарищей с моей работы, придётся оставить вариант, обрабатывающий этот набор данных при помощи курсора.
4 авг 11, 17:13    [11072457]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
Anddros
Member

Откуда:
Сообщений: 1077
select CEN,PR_ID,min(DATE_IN)DATE_IN,nullif(max(isnull(DATE_OUT,'20990909')),'20990909')DATE_OUT
from @t t
cross apply (select min(t2.DATE_IN)dd from @t t2 where t.CEN = t2.CEN and t.PR_ID = t2.PR_ID and t2.DATE_IN >= t.DATE_IN
and not exists(select 1 from @t t3 where t2.CEN = t3.CEN and t2.PR_ID = t3.PR_ID and dateadd(dd,1,t2.DATE_OUT) = t3.DATE_IN))tt
group by CEN,PR_ID,dd
4 авг 11, 17:42    [11072671]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
Anddros
Member

Откуда:
Сообщений: 1077
И хорошо бы иметь индекс по (CEN,PR_ID,DATE_IN). Иначе тоже все умрет.
4 авг 11, 17:47    [11072703]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
ё
Guest
BestiA
Спасибо за решение. Почти все условия задачи соблюдены. Однако данный вариант решения работает крайне медленно с большими наборами данных. Похоже, что несмотря на возгласы отдельных эстетствующих товарищей с моей работы, придётся оставить вариант, обрабатывающий этот набор данных при помощи курсора.


попробуйте ещё такой вариант, - может быстрее "чистого курсора" будет ... (?)
--
declare @result table (CEN decimal(18,2), PR_ID Int, DATE_IN DateTime, DATE_OUT DateTime)
declare @rc int

insert into @result
select top 1 with ties *
from @t
order by row_number() over (partition by CEN, PR_ID order by DATE_IN)

set @rc = @@rowcount
--
while 1=1
begin
  --
  while @rc <> 0
  begin
    update r
    set r.DATE_OUT = t.DATE_OUT
    from @result r 
    inner join @t t
      on    r.DATE_OUT	= dateadd(d, -1, t.DATE_IN) 
        and r.CEN		= t.CEN
        and r.PR_ID		= t.PR_ID
    --
    set @rc = @@rowcount
  end
  --
  insert into @result
  select top 1 with ties a.CEN, a.PR_ID, a.DATE_IN, a.DATE_OUT
  from 

  (select t.CEN, t.PR_ID, t.DATE_IN, t.DATE_OUT
   from @t t
   left join @result r
     on    t.DATE_IN between r.DATE_IN and isnull(r.DATE_OUT, '2999-01-01')
       and t.CEN		= r.CEN
       and t.PR_ID		= r.PR_ID
   where r.CEN is null) a
 
  order by row_number() over (partition by a.CEN, a.PR_ID order by a.DATE_IN)
  --
  set @rc = @@rowcount

  if @rc=0 break 
end

select * from @result
order by DATE_IN
5 авг 11, 13:56    [11076424]     Ответить | Цитировать Сообщить модератору
 Re: Объединение периодов. Нужна помощь в написании запроса.  [new]
BestiA
Member

Откуда: Саратов
Сообщений: 44
Всем, кто откликнулся - спасибо за помощь.
10 авг 11, 12:18    [11096580]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить