Пробелы и острова с датой начала и окончания (ValidPeriod)

Я искал, но не могу найти решение следующей проблемы.

У меня есть несколько прайс-листов с парой миллионов строк, и я нашел много примеров, которые можно объединить в одну строку, потому что даты начала и окончания группы являются последовательными (enddate: 20151231, next startdate: 20160101)

Но я также обнаружил много пробелов, что означает, что прямой подход с использованием функций min () и max () неприменим, поскольку возможные пробелы будут проигнорированы.

Следующее содержит таблицу #Prices с примерами записей и таблицу #Target с результатом, для которого я стремлюсь:

Спасибо.

Мое определение разрыва - это когда между двумя последовательными записями больше одного дня.

if object_id('tempdb..#Prices', 'table') is not null
    drop table #Prices
;

create table #Prices (
    Product         varchar(50) not null
  , Value           decimal(18,5) not null
  , ValidFrom       date not null
  , ValidTo         date null
)

insert into #Prices
(
    Product
  , Value
  , ValidFrom   
  , ValidTo    
)
select
    Product          = 'Island A'
  , Value            = 10.10
  , ValidFrom        = '20140101'
  , ValidTo          = '20140606'
union all
select
    Product          = 'Island A'
  , Value            = 10.10
  , ValidFrom        = '20140607'
  , ValidTo          = '20141010'
union all
select
    Product          = 'Island A'
  , Value            = 10.11
  , ValidFrom        = '20141011'
  , ValidTo          = '20141231'
union all
select
    Product          = 'Island A'
  , Value            = 11.10
  , ValidFrom        = '20150101'
  , ValidTo          = '20151231'
union all
select
    Product          = 'Island A'
  , Value            = 10.10
  , ValidFrom        = '20160101'
  , ValidTo          = null
union all
select
    Product          = 'Gap B'
  , Value            = 20.10
  , ValidFrom        = '20140101'
  , ValidTo          = '20140606'
union all
select
    Product          = 'Gap B'
  , Value            = 20.10
  , ValidFrom        = '20140607'
  , ValidTo          = '20141010'
union all
select
    Product          = 'Gap B'
  , Value            = 20.10
  , ValidFrom        = '20150101'
  , ValidTo          = '20151231'
union all
select
    Product          = 'Gap B'
  , Value            = 20.10
  , ValidFrom        = '20160101'
  , ValidTo          = null

select * 
from #Prices as P
order by P.Product, P.ValidFrom
;


if object_id('tempdb..#Target', 'table') is not null
    drop table #Target
;

create table #Target (
    Product         varchar(50) not null
  , Value           decimal(18,5) not null
  , ValidFrom       date not null
  , ValidTo         date null
)

insert into #Target
(
    Product
  , Value
  , ValidFrom   
  , ValidTo    
)
select
    Product          = 'Island A'
  , Value            = 10.10
  , ValidFrom        = '20140101'
  , ValidTo          = '20141010'
union all
select
    Product          = 'Island A'
  , Value            = 10.11
  , ValidFrom        = '20141011'
  , ValidTo          = '20141231'
union all
select
    Product          = 'Island A'
  , Value            = 11.10
  , ValidFrom        = '20150101'
  , ValidTo          = '20151231'
union all
select
    Product          = 'Island A'
  , Value            = 10.10
  , ValidFrom        = '20160101'
  , ValidTo          = null
union all
select
    Product          = 'Gap B'
  , Value            = 20.10
  , ValidFrom        = '20140101'
  , ValidTo          = '20141010'
union all
select
    Product          = 'Gap B'
  , Value            = 20.10
  , ValidFrom        = '20150101'
  , ValidTo          = null

select * 
from #Target as P
order by P.Product, P.ValidFrom
;

ИЗМЕНИТЬ. Надеюсь, что это изменение - ответ на ваш вопрос. Последовательные записи (максимум 1 день между записями) можно агрегировать, взяв минимальное (ValidFrom) и максимальное (ValidTo). проблема в промежутках, они будут проигнорированы. Результатом для продукта «Разрыв B» будет одна запись. Любое попадание в эту запись с Date получит значение 20.10, даже если дата находится в периоде Gap.

Gap B    |    20.10    |    20140101    |     null

Поэтому мне нужны 2 записи, поэтому все объединения в таблице приведут к правильному значению и отсутствию значения в периоде разрыва.

Gap B    |    20.10    |    20140101    |     20141010
Gap B    |    20.10    |    20151231    |     null

sql tsql gaps-and-islands sql-server-2008-r2
person Alexander    schedule 16.11.2016    source источник
comment
Пожалуйста, просто покажите образец ваших табличных данных и объясните, почему это показывает, что у вас есть проблема с пробелами и островками.   -  person Tim Biegeleisen    schedule 16.11.2016
comment
@TimBiegeleisen, я сделал небольшое дополнение к исходному вопросу. Надеюсь, это понятно. Грзт Александр   -  person Alexander    schedule 16.11.2016

Ответы (2)


arrow_upward
1
arrow_downward

Пришлось запечь небольшую логику для NULL ValidTo's

;with cte0(N)   as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N))
     ,cte1(R,D) as (Select Row_Number() over (Order By (Select Null))
                          ,DateAdd(DD,Row_Number() over (Order By (Select Null)) -1,(Select min(ValidFrom) From  #Prices)) 
                     From  cte0 N1, cte0 N2, cte0 N3, cte0 N4) 
Select Product
      ,Value    
      ,ValidFrom = Min(ValidFrom)
      ,ValidTo   = nullif(max(isnull(ValidTo,'2099-12-31')),'2099-12-31')
 From (
         Select *
               ,Island = R - Row_Number() over (Partition By Product,Value Order by ValidFrom)
          From  #Prices  A
          Join  cte1     B on D Between ValidFrom and IsNull(ValidTo,'2099-12-31')
      ) A
 Group By Product,Value,Island
 Order By 1 Desc,3

Возврат

Product     Value       ValidFrom   ValidTo
Island A    10.10000    2014-01-01  2014-10-10
Island A    10.11000    2014-10-11  2014-12-31
Island A    11.10000    2015-01-01  2015-12-31
Island A    10.10000    2016-01-01  NULL
Gap B       20.10000    2014-01-01  2014-10-10
Gap B       20.10000    2015-01-01  NULL
person John Cappelletti    schedule 16.11.2016
comment
Я схожу с ума или эта строка case when Max(IsNull(ValidTo,'2099-12-31'))='2099-12-31' then null else Max(IsNull(ValidTo,'2099-12-31')) end делает то же самое, что и nullif(max(isnull(ValidTo,'2099-12-31')),'2099-12-31')? - person iamdave; 16.11.2016
comment
@JohnCappelletti, даже более впечатляющий, чем iamdave. Но это решение требует некоторого исследования работы. Я вернусь к вам в ближайшее время. гтз александр - person Alexander; 16.11.2016
comment
@Alexander: Честно говоря, любое решение подходит вам лучше всего ... Производительность и / или возможность поддержки - person John Cappelletti; 16.11.2016
comment
@Alexander В любом случае я все же дал iamdave плюс1 за его решение - person John Cappelletti; 16.11.2016
comment
@JohnCappelletti, это подойдет ... спасибо! просто пометил этот пост как решение. ты гениальный человек. грц Александр - person Alexander; 16.11.2016
comment
@Alexander Brilliant Попробуй убедить мою жену в этом :) - person John Cappelletti; 16.11.2016
comment
@JohnCappelletti, код отлично работает в тестовой среде, но когда он был запущен в производство, код создал много дубликатов. после некоторой отладки я обнаружил, что:, Island = R - Row_Number () over (Partition By Product, Value Order by ValidFrom) не завершен. для порядка по также требуется D (дата) из CTE1 для выравнивания номеров строк .., Island = R - Row_Number () над (Разделение по продукту, Порядок значений по ValidFrom, D) .. теперь даты выравниваются и, следовательно, острова будут созданы правильно. по-прежнему большой плюс для решения! спасибо за это! - person Alexander; 30.05.2017
comment
@Alexander: Рад, что вы смогли решить производственную проблему. При переходе в производственную среду всегда возникает хотя бы одна проблема. - person John Cappelletti; 30.05.2017

arrow_upward
2
arrow_downward

Вот другое решение, использующее рекурсивный cte, который, я думаю, немного легче понять по сравнению с решением Джона. При таком количестве данных это также намного эффективнее, хотя вам нужно будет самостоятельно протестировать производительность для большего набора данных:

;with rownum
as
(
    select row_number() over (order by Product, ValidFrom) as rn
            ,Product
            ,Value
            ,ValidFrom
            ,ValidTo
    from #Prices
)
,cte
as
(
    select rn
            ,Product
            ,Value
            ,ValidFrom
            ,ValidFrom as ValidFrom2
            ,ValidTo
    from rownum
    where rn = 1

    union all

    select r.rn
            ,r.Product
            ,r.Value

            ,r.ValidFrom
            ,case when c.Product = r.Product
                    then case when dateadd(d,1,c.ValidTo) = r.ValidFrom
                            then c.ValidFrom
                            else r.ValidFrom
                            end
                    else r.ValidFrom
                    end as ValidFrom2

            ,isnull(r.ValidTo,'29990101') as ValidTo
    from rownum r
        inner join cte c
            on(r.rn = c.rn+1)
)
select Product
        ,Value
        ,ValidFrom2 as ValidFrom
        ,nullif(max(ValidTo),'29990101') as ValidTo
from cte
group by Product
        ,Value
        ,ValidFrom2
order by Product
        ,ValidFrom2;
person iamdave    schedule 16.11.2016
comment
Не уверен, что согласен, что легче понять, но у всех нас есть свои предпочтения и догмы. На больших данных рекурсивные ctes могут быть неприятными. Например: использование r-cte для построения иерархии 200K может занять почти 5 минут, в то время как мой другой подход занимает 7 секунд (в комплекте с ключами диапазона) - person John Cappelletti; 16.11.2016
comment
@iamdave, молодец! Только то, что доктор прописал. Дополнительные баллы за скорость, которую вы доставили. много спасибо. грц Александр - person Alexander; 16.11.2016
comment
@JohnCappelletti Согласен, следовательно, требуется тестирование. Я думаю, что на меньших наборах данных мое решение будет работать лучше, хотя отсутствие рекурсии в вашем определенно будет лучше на больших. - person iamdave; 16.11.2016
comment
@iamdave Ради интереса я протестировал ваш и свой. С предоставленными данными ваш был в среднем на 15 мс быстрее (по 5 запусков каждый). - person John Cappelletti; 16.11.2016
comment
@JohnCappelletti Любая мелочь помогает! - person iamdave; 16.11.2016