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

Откуда:
Сообщений: 320
Всем привет, застрял с решением... CTE готовить не умею =/, выручайте пожалуйста.

Мне нужно из таблицы @TestSCD (type 2 если не ошибаюсь) получить выборку строк с детализацией на конец месяца, желаемый результат в таблице @TestSCDRezult. Приоритет статуса внутри месяца - сортировка по полю EndDate.

Declare @TestSCD table 
       ( CustomerId int
       , CustStatus int
       , BeginDate date
       , EndDate date )

Declare @TestSCDRezult table
       ( CustomerId int
       , CustStatus int
       , BeginDate date
       , EndDate date ) 

Declare @TestDate table
       ( FirstDayMonth date )

Declare @DimDate date = '20160131'
While @DimDate < Getdate()
begin
     Insert into @TestDate
            select @DimDate

     Set @DimDate = EoMonth( DateAdd( Month
                                    , 1
                                    , @DimDate ) )
end

--- исходные данные
Insert into @TestSCD 
values ( 1, 1, '20160101', '20161224' )
     , ( 1, 4, '20161225', '99990101' )
     , ( 2, 1, '20160209', '20160218' )
     , ( 2, 2, '20160219', '20160418' )
     , ( 2, 1, '20160419', '20160420' )
     , ( 2, 2, '20160421', '20160801' )
     , ( 2, 3, '20160802', '20170112' )
     , ( 2, 2, '20170113', '99990101' )
     , ( 3, 1, '20170101', '20170108' )
     , ( 3, 4, '20170109', '20170115' )
     , ( 3, 3, '20170116', '99990101' )

--- желаемый результат
Insert into @TestSCDRezult 
values ( 1, 1, '20160101', '20161130' )
     , ( 1, 4, '20161201', '99990101' )
     , ( 2, 2, '20160209', '20160731' )
     , ( 2, 3, '20160801', '20161231' )
     , ( 2, 2, '20170101', '99990101' )
     , ( 3, 3, '20170101', '99990101' )


Таблица дат (последний день месяца) только для данного примера. Пока что у меня получается тупо раздуть таблицу по каждому месяцу, со значением на конец месяца и потом сгруппировать, но лезут дубли, рабочая таблица содержит 1300к.строк.

Select x.CustomerId
     , x.CustStatus
	 , x.BeginDate
	 , Max( x.EndDate) as EndDate
from ( Select scd.CustomerId
            , scd.CustStatus
            , DateAdd( Day
                     , 1
                     , EoMonth( scd.BeginDate
                              , -1 )) as BeginDate
                     --, cal.FirstDayMonth as EndDate
                     , EoMonth( scd.EndDate
                              , -1) as EndDate
                     , Row_Number()
                       over ( partition by scd.EndDate
                                         , scd.CustomerId
                                         , scd.CustStatus
                              order by scd.BeginDate asc) as num
       from @TestSCD as scd 
            inner join @TestDate as cal
                       on cal.FirstDayMonth between scd.BeginDate and scd.EndDate
      )x
where x.num = 1
group by x.CustomerId
     , x.CustStatus
	 , x.BeginDate
order by x.CustomerId
       , x.BeginDate


Версия:

[/SRC]

[src]Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)   Mar  6 2017 14:18:16   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 
27 мар 17, 15:37    [20337478]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

Откуда:
Сообщений: 320
Что-то я совсем застрял, решение без календаря, но слить дублирующие статусы не получается во вложенном запросе =/. Пните в правильное направление...

Select *
from (
Select CustomerId
     , CustStatus
	 --, BeginDate
	 --, EndDate
	 , DateAdd( day
              , 1
			  , EoMonth( BeginDate
                       , -1 )) as BeginDate_Month
	 , EoMonth( EndDate) as EndDate_Month
     , Row_Number()
       over ( partition by CustomerId
                         , DateAdd( day
                                  , 1
                                  , EoMonth( BeginDate
                                           , -1 ))
              order by BeginDate Desc) as RowidInMonth
from @TestSCD
)x
where x.RowidInMonth = 1


Всем заранее спасибо за помощь
28 мар 17, 08:59    [20339457]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
IDVT
Мне нужно из таблицы @TestSCD (type 2 если не ошибаюсь) получить выборку строк с детализацией на конец месяца, желаемый результат в таблице @TestSCDRezult. Приоритет статуса внутри месяца - сортировка по полю EndDate.

Совсем ничего не понял.
Как в результате получилось:
values ( 1, 1, '20160101', '20161130' )

Почему 30 ноября?
Нужно прибавить 11 месяцев, и взять последний день месяца?
В общем, не получилось у меня по озвученному алгоритму получить из первой таблицы вторую.
28 мар 17, 09:41    [20339651]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

Откуда:
Сообщений: 320
Добрый день, немного упростил исходные данные.

Задача - получить периодику статуса на конец месяца, т.е. конечное значение статуса - это то значение, которое ближе всего к концу каждого месяца. Статус может несколько раз меняться в одном месяце, на примере, группы строк RowId (1,2), (3,4) в итоге на данные периоды значение статуса = 2.

Declare @TestSCD table 
       ( RowId int
	   , CustomerId int
       , CustStatus int
       , BeginDate date
       , EndDate date )

--- исходные данные

Insert into @TestSCD 
values ( 1, 2, 1, '20160209', '20160218' )
     , ( 2, 2, 2, '20160219', '20160418' )
     , ( 3, 2, 1, '20160419', '20160420' )
     , ( 4, 2, 2, '20160421', '20160801' )
     , ( 5, 2, 3, '20160802', '20170112' )
     , ( 6, 2, 2, '20170113', '99990101' )
     , ( 7, 1, 1, '20160101', '20161224' )
     , ( 8, 1, 4, '20161225', '99990101' )
     , ( 9, 3, 1, '20170101', '20170108' )
     , (10, 3, 4, '20170109', '20170115' )
     , (11, 3, 3, '20170116', '99990101' )

------------------------------------------
Select *
from @TestSCD
------------------------------------------


Это все решил, но получается что значение статуса 3 и 4ой строки (сортировка по полям CustomerId, BeginDate_Month) логически размазана, мне необходимо эти строки собрать в одну с периодом BeginDate_Month = '2016-02-01' и EndDate_Month = '2016-08-31'.

Select *
from (
Select CustomerId
     , CustStatus
	 --, BeginDate
	 --, EndDate
	 , DateAdd( day
              , 1
			  , EoMonth( BeginDate
                       , -1 )) as BeginDate_Month
	 , EoMonth( EndDate) as EndDate_Month
     , Row_Number()
       over ( partition by CustomerId
                         , DateAdd( day
                                  , 1
                                  , EoMonth( BeginDate
                                           , -1 ))
              order by BeginDate Desc) as RowidInMonth
from @TestSCD
)x
where x.RowidInMonth = 1
order by CustomerId
       , BeginDate_Month


Также в моем запросе есть ошибка, которую Вы увидели (я проглядел), первая строка должна иметь значение EndDate_Month = '20161130' (но, значение второй строки верное) потому что, период значения статуса = 1 в декабре 2016 года, на конец месяца покрывает статус 2 (RowID исходных данных = 8), который начинается с '2016-12-25'

Знаю, плохо выражаю мысли, в итоге хочу получить это:
28 мар 17, 10:37    [20340041]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

Откуда:
Сообщений: 320
Забыл приложить финальную выборку

      select  1 as CustomerId , 1 as CustStatus, '20160101' as BeginDate_Month, '20161130' as RowidInMonth
union select  1, 4, '20161201', '99990101' 
union select  2, 2, '20160209', '20160731' 
union select  2, 3, '20160801', '20161231'
union select  2, 2, '20170101', '99990101' 
union select  3, 3, '20170101', '99990101' 
28 мар 17, 10:38    [20340052]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
IDVT,
У меня сегодня приступ телепатии
Declare @TestSCD table 
       ( CustomerId int
       , CustStatus int
       , BeginDate date
       , EndDate date )

Declare @TestDate table
       ( [Month] DATE,
         [NextMonth] DATE )

Declare @DimDate date = '20160101'
While @DimDate < Getdate()
begin
     Insert into @TestDate
            select @DimDate, DateAdd( Month
                                    , 1
                                    , @DimDate )

     Set @DimDate = DateAdd( Month
                                    , 1
                                    , @DimDate )
end

--- исходные данные
Insert into @TestSCD 
values ( 1, 1, '20160101', '20161224' )
     , ( 1, 4, '20161225', '99990101' )
     , ( 2, 1, '20160209', '20160218' )
     , ( 2, 2, '20160219', '20160418' )
     , ( 2, 1, '20160419', '20160420' )
     , ( 2, 2, '20160421', '20160801' )
     , ( 2, 3, '20160802', '20170112' )
     , ( 2, 2, '20170113', '99990101' )
     , ( 3, 1, '20170101', '20170108' )
     , ( 3, 4, '20170109', '20170115' )
     , ( 3, 3, '20170116', '99990101' )
;
WITH
t AS (
  SELECT
    cc.[Month],
    sc.[CustomerId],
    sc.[CustStatus],
    [rn] = ROW_NUMBER() OVER ( PARTITION BY sc.[CustomerId], cc.[Month] ORDER BY sc.[EndDate] DESC )
  FROM
    @TestDate cc
    INNER JOIN @TestSCD sc ON (
          cc.[Month] <= sc.[BeginDate]
      AND cc.[NextMonth] > sc.[BeginDate] )
),
t2 AS (
  SELECT
    [Month],
    [CustomerId],
    [CustStatus],
    [rn] = ROW_NUMBER() OVER ( PARTITION BY [CustomerId] ORDER BY [Month] ) 
         - ROW_NUMBER() OVER ( PARTITION BY [CustomerId], [CustStatus] ORDER BY [Month] )
  FROM
    t 
  WHERE
    [rn] = 1
),
t3 AS (
  SELECT
    [CustomerId],
    [CustStatus],
    [BeginDate] = MIN( [Month] )
  FROM
    t2
  GROUP BY
    [CustomerId],
    [CustStatus],
    [rn]
)
SELECT
  [CustomerId],
  [CustStatus],
  [BeginDate],
  [EndDate] = LEAD( [BeginDate], 1, '99990101' ) OVER ( PARTITION BY [CustomerId] ORDER BY [BeginDate] )
FROM
  t3
ORDER BY
  1, 3
28 мар 17, 11:14    [20340303]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

Откуда:
Сообщений: 320
Ошибку исправил, осталось слить строки (3 и 4) в один период....

Select CustomerId
     , CustStatus
     , BeginDate_Month
	 , ( Case when Lead(BeginDate_Month)
                   over ( partition by CustomerId
                          order by BeginDate_Month ) < EndDate_Month
              then EoMonth( EndDate_Month, -1 )
			  else EndDate_Month end ) as EndDate_Month
from (
Select CustomerId
     , CustStatus
	 , DateAdd( day
              , 1
			  , EoMonth( BeginDate
                       , -1 )) as BeginDate_Month
	 , EoMonth( EndDate) as EndDate_Month
     , Row_Number()
       over ( partition by CustomerId
                         , DateAdd( day
                                  , 1
                                  , EoMonth( BeginDate
                                           , -1 ))
              order by BeginDate Desc) as RowidInMonth
from @TestSCD
)x
where x.RowidInMonth = 1
order by CustomerId
       , BeginDate_Month
28 мар 17, 11:18    [20340335]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
Но честно говоря, я не вижу, как полученный результат соответствует ТЗ:
IDTV
Задача - получить периодику статуса на конец месяца, т.е. конечное значение статуса - это то значение, которое ближе всего к концу каждого месяца.

но это, конечно, не мое дело, я просто помог просящему написать запрос...
28 мар 17, 11:19    [20340339]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

Откуда:
Сообщений: 320
Руслан Дамирович
Но честно говоря, я не вижу, как полученный результат соответствует ТЗ:
IDTV
Задача - получить периодику статуса на конец месяца, т.е. конечное значение статуса - это то значение, которое ближе всего к концу каждого месяца.

но это, конечно, не мое дело, я просто помог просящему написать запрос...

Да, действительно, не корректно выражаюсь, нужно результат не раздуть на каждый месяц, а оставить в SCD
28 мар 17, 11:23    [20340380]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

Откуда:
Сообщений: 320
Руслан Дамирович,

Спасибо! возможно ли в рекурсии уйти от таблицы календаря? мои попытки не дают желаемого результата
28 мар 17, 12:55    [20340990]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
IDVT
возможно ли в рекурсии уйти от таблицы календаря?
Возможно. Но ведь она тогда будет создаваться каждый раз для каждого запроса, причём в цикле, по одной записи.
Идеально - иметь таблицу-календарь, если же нет на это прав, то можно использовать spt_values
28 мар 17, 14:14    [20341413]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

Откуда:
Сообщений: 320
alexeyvg,
Ок, спасибо! я Вас понял...
28 мар 17, 14:39    [20341580]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
Massa52
Member

Откуда:
Сообщений: 387
IDVT,
Тут https://www.codeproject.com/Articles/167399/Using-Table-Valued-Functions-in-SQL-Server используется функция
CREATE FUNCTION DatesBetween(@startDate date, @endDate date)
RETURNS @dates TABLE (
   DateValue date NOT NULL
) 
AS
BEGIN
   WHILE (@startDate <= @endDate) BEGIN
      INSERT INTO @dates VALUES (@startDate);
      SET @startDate = DATEADD(day, 1, @startDate);
   END;
   
   RETURN;
END;
28 мар 17, 15:15    [20341883]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

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

Спасибо! за ответ. Но все же таблица календарь лучший вариант.
28 мар 17, 15:17    [20341918]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
Massa52
Member

Откуда:
Сообщений: 387
IDVT,
Так данная функция строит таблицу.
Можно строить на лету - как это сделано в статье
SELECT d.DateValue,
       (SELECT COUNT(*)
        FROM   TrackingItem ti
        WHERE  d.DateValue = ti.Issued) AS Items
FROM DatesBetween(DATEADD(day, 1, GETDATE()), DATEADD(day, 7, GETDATE())) d
ORDER BY d.DateValue;
28 мар 17, 15:32    [20342066]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Massa52
Можно строить на лету - как это сделано в статье
Ужас, да ещё и циклом :-(
28 мар 17, 15:35    [20342084]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

Откуда:
Сообщений: 320
Остановился на этом варианте, план отличный, за счет таблицы пустышки с колоночным индексом. Пока еще не все мне понятно, почему агрегированные окна в 2016 версии, компилятор использует только при наличии в связанных таблицах колоночного индекса, Разве оконные функции не являются поводом для этого?

+

create table dbo.ColumnStoreCrutch 
       ( RowId int not null
       , index RowId_CS clustered ColumnStore);

Create table #TestSCD  
       ( RowId int
	   , CustomerId int
       , CustStatus int
       , BeginDate date
       , EndDate date 
	   , constraint PK_TestSCD primary key clustered 
                   ( RowId asc
                   , CustomerId asc)
	   )

--- исходные данные

Insert into #TestSCD 
values ( 1, 2, 1, '20160209', '20160218' )
     , ( 2, 2, 2, '20160219', '20160418' )
     , ( 3, 2, 1, '20160419', '20160420' )
     , ( 4, 2, 2, '20160421', '20160801' )
     , ( 5, 2, 1, '20160819', '20160820' )
     , ( 6, 2, 2, '20160821', '20160901' )
     , ( 7, 2, 3, '20160902', '20170112' )
     , ( 8, 2, 2, '20170113', '99990101' )
     , ( 9, 1, 1, '20160101', '20161224' )
     , (10, 1, 4, '20161225', '99990101' )
     , (11, 3, 1, '20170101', '20170108' )
     , (12, 3, 4, '20170109', '20170115' )
     , (13, 3, 3, '20170116', '99990101' )


--------------------------------------------
--dbcc dropcleanbuffers
set statistics time on
set statistics io on

go

Select CustomerId
     , CustStatus
     , BeginDate_Month
	 , EndDate_Month
from (
Select CustomerId
     , CustStatus
	 , DateAdd( day
              , 1
			  , EoMonth( BeginDate
                       , -1 )) as BeginDate_Month
	 , ( Case when Lead( DateAdd( day
                                , 1
                                , EoMonth( BeginDate
                                         , -1 )))
                   over ( partition by CustomerId
                          order by BeginDate ) < EoMonth( EndDate)
              then EoMonth( EndDate, -1 )
			  else EoMonth( EndDate) end ) as EndDate_Month
     , Row_Number()
       over ( partition by CustomerId
                         , DateAdd( day
                                  , 1
                                  , EoMonth( BeginDate
                                           , -1 ))
              order by BeginDate Desc) as RowidInMonth
from #TestSCD 
     left join dbo.ColumnStoreCrutch
               on 1=1                  
)x
where x.RowidInMonth = 1

-------------------------------------------------------------
Select CustomerId
     , CustStatus
     , BeginDate_Month
	 , EndDate_Month
from (
Select CustomerId
     , CustStatus
	 , DateAdd( day
              , 1
			  , EoMonth( BeginDate
                       , -1 )) as BeginDate_Month
	 , ( Case when Lead( DateAdd( day
                                , 1
                                , EoMonth( BeginDate
                                         , -1 )))
                   over ( partition by CustomerId
                          order by BeginDate ) < EoMonth( EndDate)
              then EoMonth( EndDate, -1 )
			  else EoMonth( EndDate) end ) as EndDate_Month
     , Row_Number()
       over ( partition by CustomerId
                         , DateAdd( day
                                  , 1
                                  , EoMonth( BeginDate
                                           , -1 ))
              order by BeginDate Desc) as RowidInMonth
from #TestSCD
)x
where x.RowidInMonth = 1

---
Drop table dbo.ColumnStoreCrutch
         , #TestSCD

но в инете явно есть статьи.....
28 мар 17, 15:50    [20342165]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
IDVT
Member

Откуда:
Сообщений: 320
агрегированные окна* - в плане запроса...
28 мар 17, 15:52    [20342178]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
Massa52
IDVT,
Так данная функция строит таблицу.
Можно строить на лету - как это сделано в статье

А можно какать стоя на голове...
WITH
ct AS (
  SELECT
    [date] = CONVERT( DATE, '20160101' )
  UNION ALL
  SELECT
    [date] = DATEADD( MONTH, 1, [date] )
  FROM
    ct
  WHERE
    [date] < GETDATE()
)
SELECT
  *
FROM
  ct
OPTION (
  MAXRECURSION 0 )
28 мар 17, 16:29    [20342449]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Руслан Дамирович
Massa52
IDVT,
Так данная функция строит таблицу.
Можно строить на лету - как это сделано в статье

А можно какать стоя на голове...
WITH
ct AS (
  SELECT
    [date] = CONVERT( DATE, '20160101' )
  UNION ALL
  SELECT
    [date] = DATEADD( MONTH, 1, [date] )
  FROM
    ct
  WHERE
    [date] < GETDATE()
)
SELECT
  *
FROM
  ct
OPTION (
  MAXRECURSION 0 )
Конечно, это намного лучше, но ведь всё равно это цикл, оформленный как CTE
Правильно всегда использовать таблицу-календарь.
Типа, делать в программе число PI константой, а не гордо находить в инете функцию его вычисления.
28 мар 17, 21:01    [20343594]     Ответить | Цитировать Сообщить модератору
 Re: SCD2 детализировать до месяца  [new]
Massa52
Member

Откуда:
Сообщений: 387
alexeyvg,
Ну вот - всю идею загадили стоя на голове.
Ссылка на статью давалась, как еще один вариант оформления временной таблицы.
Вроде - если оформить как функцию возвращающую таблицу дат - получается более гибко.
Так как если чтот надо менять в основном скрипте с датами - то нужно изменять только функцию.
29 мар 17, 04:56    [20344296]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить