slowly changing dimensions type 2 / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / slowly changing dimensions type 2

18 сообщений из 18, страница 1 из 1

slowly changing dimensions type 2

#40130188

minya13_85

Участник

Откуда: Москва

Сообщения: 616

Рейтинг: 0 / 0

версия сервера

Код: sql

Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64)   Feb  9 2021 12:08:50   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

Есть таблица , которая хранится в виде SCD 2 , и она каждый день обновляется.
Хочу настроить , чтобы при обновлении запись правильно закрывалась.
дата = 01.01.1980 - это с начала времен, дата = 01.01.2079 - это значит запись действует по текущее время
Если по одному id пришла запись, то значит предшествующую запись закрываем предыдущим днем от даты начала новой записи, если и старая запись и новая запись действовали по текущее время, то старую запись (src=0) закрываем текущим днем, а новую открываем завтрашним.
Новая запись (src=1) считается главной, если придут перекрывающие условия.

Ключ таблицы , id + dt_to

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.

WITH src_data AS 
(
SELECT 1 AS id, 5 AS val,CAST('2020-05-01' AS date) as dt_from,CAST('2020-05-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 7 AS val,CAST('2020-06-01' AS date) as dt_from,CAST('2020-10-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 5 AS val,CAST('2020-11-01' AS date) as dt_from,CAST('2020-11-30' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 2 AS val,CAST('2020-12-01' AS date) as dt_from,CAST('2020-12-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 8 AS val,CAST('2021-01-01' AS date) as dt_from,CAST('2021-05-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 2 AS id, 8 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 0 AS src
UNION
SELECT 3 AS id, 11 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2020-01-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 4 AS id, 1 AS val,CAST('2020-01-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 0 AS src
)

,new_data AS 
(
SELECT 1 AS id, 6 AS val,CAST('2020-06-01' AS date) as dt_from,CAST('2020-06-30' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 7 AS val,CAST('2020-07-01' AS date) as dt_from,CAST('2020-07-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 12 AS val,CAST('2020-08-01' AS date) as dt_from,CAST('2020-08-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 8 AS val,CAST('2021-01-01' AS date) as dt_from,CAST('2021-02-28' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 5 AS val,CAST('2021-03-01' AS date) as dt_from,CAST('2021-03-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 2 AS id, 15 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 1 AS src
UNION
SELECT 3 AS id, 9 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2019-05-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 3 AS id, 7 AS val,CAST('2019-06-01' AS date) as dt_from,CAST('2019-10-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 4 AS id, 5 AS val,CAST('2020-03-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 1 AS src
)

SELECT b.*
FROM 
(
SELECT * FROM new_data
UNION
SELECT * FROM src_data
) b
ORDER BY id,dt_from

Исходя из контрольного примера, нужно получить таблицу вида:

id valdt_from dt_to1501.05.202031.05.20201601.06.202030.06.20201701.07.202031.07.202011201.08.202031.08.20201701.09.202031.10.20201501.11.202031.11.20201201.12.202031.12.20201801.01.202128.02.20211501.03.202131.03.20211801.04.202131.05.20212801.01.198028.01.202221529.01.202201.01.20793901.01.198031.05.20193701.06.201931.10.201931101.11.201931.01.20204101.01.202029.02.20204501.03.202001.01.2079

...

Рейтинг:

0 / 0

28.01.2022, 11:12

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130247

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Предлагаешь научить тебя пользоваться merge?

...

Рейтинг:

0 / 0

28.01.2022, 12:41

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130254

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Ах да, чуть не забыл.
Если ты откажешься от глупого желания иметь dt_to - ваще ничего делать не надо.

Ну а если желание иметь dt_to неизбывно - LEAD() OVER(partition by id order by dt_from) спасет отца русской демократии.

...

Рейтинг:

0 / 0

28.01.2022, 12:56

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130276

minya13_85

Участник

Откуда: Москва

Сообщения: 616

Рейтинг: 0 / 0

aleks222,
без dt_to не обойтись. Если обратите внимание, то срок действия у каждого значения бывает разный. А если значение одно, то без него точно не обойтись.

LAG, LEAD знаю, я даже уже написал скрипт, но он не срабатывает для некоторых случаев. Могу его привести тут. Скрипт немного перекрученный.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.

WITH src_data AS 
(
SELECT 1 AS id, 5 AS val,CAST('2020-05-01' AS date) as dt_from,CAST('2020-05-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 7 AS val,CAST('2020-06-01' AS date) as dt_from,CAST('2020-10-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 5 AS val,CAST('2020-11-01' AS date) as dt_from,CAST('2020-11-30' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 2 AS val,CAST('2020-12-01' AS date) as dt_from,CAST('2020-12-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 8 AS val,CAST('2021-01-01' AS date) as dt_from,CAST('2021-05-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 2 AS id, 8 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 0 AS src
UNION
SELECT 3 AS id, 11 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2020-01-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 4 AS id, 1 AS val,CAST('2020-01-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 0 AS src
)

,new_data AS 
(
SELECT 1 AS id, 6 AS val,CAST('2020-06-01' AS date) as dt_from,CAST('2020-06-30' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 7 AS val,CAST('2020-07-01' AS date) as dt_from,CAST('2020-07-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 12 AS val,CAST('2020-08-01' AS date) as dt_from,CAST('2020-08-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 8 AS val,CAST('2021-01-01' AS date) as dt_from,CAST('2021-02-28' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 5 AS val,CAST('2021-03-01' AS date) as dt_from,CAST('2021-03-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 2 AS id, 15 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 1 AS src
UNION
SELECT 3 AS id, 9 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2019-05-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 3 AS id, 7 AS val,CAST('2019-06-01' AS date) as dt_from,CAST('2019-10-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 4 AS id, 5 AS val,CAST('2020-03-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 1 AS src
)

SELECT ck.id,ck.val
,ck.dt_from
,coalesce(lead(ck.dt_from) over(partition by ck.id order by ck.dt_from)-1,ck.m_dt_to,CAST('2079-01-01' AS date)) dt_to
FROM 
(
select cx.id,cx.val
,cx.dt_from
,lag(cx.val) over(partition by cx.id order by cx.dt_to) lag_val
,cx.dt_to,cx.m_dt_to
from 
(               
select c.id
,c.val
,c.dt_from 
,coalesce(lead(c.dt_from) over(partition by c.id order by c.dt_from)-1,c.m_dt_to,CAST('2079-01-01' AS date)) dt_to
,c.m_dt_to
from 
(
    SELECT b.id,b.val
    ,isnull(lag(b.dt_to) over(partition by b.id order by b.dt_to)+1,b.dt_from) dt_from
    ,case when isnull(lag(b.dt_to) over(partition by b.id order by b.dt_to)+1,b.dt_from)> b.dt_to
          then max(b.m_dt_to)over(partition by b.id,b.val)
          else b.dt_to
            end dt_to
    ,max(b.m_dt_to)over(partition by b.id,b.val) m_dt_to
    FROM 
      (
        select a.id,a.val
        ,isnull(case when lag(a.dt_to) over(partition by a.id order by case when a.dt_to=CAST('2079-01-01' AS date) and a.src=1 then CAST('1980-01-01' AS date) else a.dt_to end,a.src)=CAST('2079-01-01' AS date) and a.dt_from!=CAST('1980-01-01' AS date)
              then a.dt_from
              when lag(a.dt_to) over(partition by a.id order by case when a.dt_to=CAST('2079-01-01' AS date) and a.src=1 then CAST('1980-01-01' AS date) else a.dt_to end,a.src)=CAST('2079-01-01' AS date) and a.dt_from=CAST('1980-01-01' AS date) 
              then CAST('1980-01-01' AS date)
              else DATEADD(d,1,LAG(a.dt_to) over(partition by a.id order by case when a.dt_to=CAST('2079-01-01' AS date) and a.src=1 then CAST('1980-01-01' AS date) else a.dt_to end,a.src))
            end,a.dt_from) dt_from
        ,isnull(case when lead(a.dt_from) over(partition by a.id order by case when a.dt_from=CAST('1980-01-01' AS date) and a.src=1 then CAST('2079-01-01' AS date) else a.dt_from end,a.src)=CAST('1980-01-01' AS date) and a.dt_to!=CAST('2079-01-01' AS date)
              then a.dt_to
              when lead(a.dt_from) over(partition by a.id order by case when a.dt_from=CAST('1980-01-01' AS date) and a.src=1 then CAST('2079-01-01' AS date) else a.dt_from end,a.src)=CAST('1980-01-01' AS date) and a.dt_to=CAST('2079-01-01' AS date) 
              then GETDATE()
              else DATEADD(d,-1,LEAD(a.dt_from) over(partition by a.id order by case when a.dt_from=CAST('1980-01-01' AS date) and a.src=1 then CAST('2079-01-01' AS date) else a.dt_from end,a.src))
            end,a.dt_to) dt_to 
        ,a.dt_to m_dt_to
       from 
            (
              select id,val,dt_from,dt_to,0 src
              from src_data  ss
              where NOT EXISTS 
                            ( 
                            select s.id,s.val,s.dt_from,s.dt_to
                            from new_data   n
                            join src_data s on s.id=n.id and s.dt_from between n.dt_from and n.dt_to
                            where s.id=ss.id and s.val=ss.val and s.dt_from=ss.dt_from and s.dt_to=ss.dt_to
                            and not (n.dt_from=CAST('1980-01-01' AS date) and n.dt_to=CAST('2079-01-01' AS date))
                             ) 
              union
              select id,val,dt_from,dt_to,1 src
              from new_data
             ) a 

 ) b
) c
) cx 
) ck 
where ck.val!=isnull(ck.lag_val,0)    
order by id,dt_from

Поэтому написал сюда, может кто поможет его докрутить.

...

Рейтинг:

0 / 0

28.01.2022, 13:47

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130299

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

minya13_85,

date_to можно не хранить, т.к. это избыточно при указанных требованиях. В таблицу вставляете новые записи с датой начала, а дату закрытия - вычисляете. можно использовать LEAD() или что-то ещё.

Просто посмотрите под другим углом, не привязывайтесь к императивной логике.
"закрываем текущим днем, а новую открываем завтрашним", это императивное описание, на самом деле "открываем и закрываем" - это условности.

...

Рейтинг:

0 / 0

28.01.2022, 14:23

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130321

minya13_85

Участник

Откуда: Москва

Сообщения: 616

Рейтинг: 0 / 0

Владислав Колосов

date_to можно не хранить, т.к. это избыточно при указанных требованиях. В таблицу вставляете новые записи с датой начала, а дату закрытия - вычисляете. можно использовать LEAD() или что-то ещё.

А как быть со строками
вот даже на примере. Значение 11 для id=3 существовало с начала времен до 31.01.2020

idvaldt_fromdt_tosrc31101.01.198031.01.20200

потом пришли новые значения для Id=3, что оказывается с начала времен было значение не 11, а 9 и оно действовало до 31.05.2019
и потом оно перешло в значение 7 , с 01.06.2019-31.10.2019, и только потом приняло значение 11.

id valdt_fromdt_tosrc3901.01.198031.05.201913701.06.201931.10.20191

Если я отброшу dt_to, то как я пойму срок действия и с какого числа начать запись для значения 11, с учетом того, какие значения пришли.

...

Рейтинг:

0 / 0

28.01.2022, 14:49

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130330

Wlr-l

Гость

"закрываем текущим днем, а новую открываем завтрашним", это императивное описание

Просто интересно, как это будет звучать на НЕ императивном описании?

...

Рейтинг:

0 / 0

28.01.2022, 14:55

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130338

a_voronin

Участник

Откуда: Москва

Сообщения: 4 636

Рейтинг: 0 / 0

minya13_85

Если знаешь про LAG LEAD зачем вообще держишь поле dt_to. Почему не оставить только dt_from, а dt_to не получать LEADом ? .

...

Рейтинг:

0 / 0

28.01.2022, 15:14

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130344

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Wlr-l

На неимперативном:
1. есть дата НАЧАЛА
2. ДАТА КОНЦА = дата следующего НАЧАЛА - 1.

...

Рейтинг:

0 / 0

28.01.2022, 15:29

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130345

minya13_85

Участник

Откуда: Москва

Сообщения: 616

Рейтинг: 0 / 0

[quot a_voronin#22427425]

minya13_85

Если знаешь про LAG LEAD зачем вообще держишь поле dt_to. Почему не оставить только dt_from, а dt_to не получать LEADом ? .

Прочтите мои посты выше с примерами. Если можете написать без dt_to подскажите как?

...

Рейтинг:

0 / 0

28.01.2022, 15:30

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130352

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.

WITH src_data AS 
(
SELECT 1 AS id, 5 AS val,CAST('2020-05-01' AS date) as dt_from,CAST('2020-05-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 7 AS val,CAST('2020-06-01' AS date) as dt_from,CAST('2020-10-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 5 AS val,CAST('2020-11-01' AS date) as dt_from,CAST('2020-11-30' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 2 AS val,CAST('2020-12-01' AS date) as dt_from,CAST('2020-12-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 1 AS id, 8 AS val,CAST('2021-01-01' AS date) as dt_from,CAST('2021-05-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 2 AS id, 8 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 0 AS src
UNION
SELECT 3 AS id, 11 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2020-01-31' AS date) AS dt_to, 0 AS src
UNION
SELECT 4 AS id, 1 AS val,CAST('2020-01-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 0 AS src
)

,new_data AS 
(
SELECT 1 AS id, 6 AS val,CAST('2020-06-01' AS date) as dt_from,CAST('2020-06-30' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 7 AS val,CAST('2020-07-01' AS date) as dt_from,CAST('2020-07-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 12 AS val,CAST('2020-08-01' AS date) as dt_from,CAST('2020-08-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 8 AS val,CAST('2021-01-01' AS date) as dt_from,CAST('2021-02-28' AS date) AS dt_to, 1 AS src
UNION
SELECT 1 AS id, 5 AS val,CAST('2021-03-01' AS date) as dt_from,CAST('2021-03-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 2 AS id, 15 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 1 AS src
UNION
SELECT 3 AS id, 9 AS val,CAST('1980-01-01' AS date) as dt_from,CAST('2019-05-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 3 AS id, 7 AS val,CAST('2019-06-01' AS date) as dt_from,CAST('2019-10-31' AS date) AS dt_to, 1 AS src
UNION
SELECT 4 AS id, 5 AS val,CAST('2020-03-01' AS date) as dt_from,CAST('2079-01-01' AS date) AS dt_to, 1 AS src
)
, t as ( SELECT b.* FROM ( SELECT * FROM new_data UNION all SELECT * FROM src_data ) b ) 

select dtFrom = t.dt_from
     , dtTo = isnull( dateadd( day, -1, lag( t.dt_from) over(partition by t.id order by t.dt_from ) ), t.dt_to)
     , * 
   from t
ORDER BY t.id, t.dt_from

...

Рейтинг:

0 / 0

28.01.2022, 15:43

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130363

Wlr-l

Гость

ТС сказал, что они используют SCD2. Возможно, просто так захотели и все.

SCD2 предполагает хранение исторических данных вместе со служебной информацией, которая отвечает за версионность, статус, временной интервал, в течение которого данные строки являются актуальными, и может быть еще за что-то.

Для чего придумаkи эти сложности? Запись новых и изменение имеющихся данных это редкие события, а вот читать эти данные бывает нужно часто и много раз.

Что важнее, один раз вычислить dt_to и потом его использовать много-много раз или отказаться от него и вычислять его каждый раз из много-много раз? Кстати случаи с dt_to и без него подходят под определение SCD2.

Это как золотое правило механики (вспомним школу, а не забудем все то, чему нас учили): нельзя одновременно выиграть в силе и расстоянии. ТС хочет выиграть в силе, а ему предлагают выиграть в расстоянии. В.Колосов назвал это желание императивизмом.

aleks222 продолжил:

На неимперативном:
1. есть дата НАЧАЛА
2. ДАТА КОНЦА = дата следующего НАЧАЛА - 1. <-- императивщина

Так это "закрываем предыдущим днем, а новую открываем текущим". Так что тема неимперативщины не раскрыта.

...

Рейтинг:

0 / 0

28.01.2022, 15:55

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130372

msLex

Участник

Сообщения: 8 523

Рейтинг: 0 / 0

a_voronin

minya13_85

Если знаешь про LAG LEAD зачем вообще держишь поле dt_to. Почему не оставить только dt_from, а dt_to не получать LEADом ? .

Например, из соображений перформанса

ну и дабы дать ссылку на "стандартную" реализацию всяких таблиц с версиями записи

https://docs.microsoft.com/ru-ru/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

там как раз from и to в каждой строке.

...

Рейтинг:

0 / 0

28.01.2022, 16:04

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130414

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

minya13_85,

авторпотом пришли новые значения для Id=3

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

Если атрибут val и dt_from входят в состав ключа, то проблем с определением даты следующей по порядку даты не будет. Скорее всего, ключ должен состоять из dt_from, val.

...

Рейтинг:

0 / 0

28.01.2022, 18:55

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130416

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

Wlr-l,

авторТак это "закрываем предыдущим днем, а новую открываем текущим"

База данных не имеет таких понятий, как "закрыть", "новую открыть". Это алгоритмическое описание. В базу можно вставить, удалить, обновить. В данном случае, чтобы выполнить оба этих действия "закрыть" и "новую открыть", достаточно выполнить вставку новой записи. То есть действия "закрыть" и "открыть" не будут выполнены, они не требуются.

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

...

Рейтинг:

0 / 0

28.01.2022, 19:01

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130422

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Владислав Колосов

Скорее всего, ключ должен состоять из dt_from, val.

С какого бодуна значение должно входить в ключ?
Новая реляционная тиория?

...

Рейтинг:

0 / 0

28.01.2022, 19:37

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130432

Wlr-l

Гость

Владислав Колосов

Wlr-l,

База данных не имеет таких понятий, как "закрыть", "новую открыть". Это алгоритмическое описание. В базу можно вставить, удалить, обновить. В данном случае, чтобы выполнить оба этих действия "закрыть" и "новую открыть", достаточно выполнить вставку новой записи. То есть действия "закрыть" и "открыть" не будут выполнены, они не требуются.

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

Если я непонятно объяснил что-то в сообщении ( 22427453 ), из которого вы взяли одну фразу, то начните отсюда

https://ru.wikipedia.org/wiki/Медленно_меняющееся_измерение

По поводу вашего сообщения 22427535 , помните у Маяковского:
Ужас из железа выжал стон!
По разработчикам баз данных прошло рыданье!

...

Рейтинг:

0 / 0

28.01.2022, 20:24

| Ответить | Цитировать | Написать

slowly changing dimensions type 2

#40130434

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

minya13_85

Владислав Колосов

А как быть со строками
вот даже на примере. Значение 11 для id=3 существовало с начала времен до 31.01.2020

idvaldt_fromdt_tosrc31101.01.198031.01.20200

потом пришли новые значения для Id=3, что оказывается с начала времен было значение не 11, а 9 и оно действовало до 31.05.2019
и потом оно перешло в значение 7 , с 01.06.2019-31.10.2019, и только потом приняло значение 11.

id valdt_fromdt_tosrc3901.01.198031.05.201913701.06.201931.10.20191

Если я отброшу dt_to, то как я пойму срок действия и с какого числа начать запись для значения 11, с учетом того, какие значения пришли.

minya13_85

Владислав Колосов

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.

-- тупо вставляем новые данные
insert data select * from new_data;

-- Перекрытия 
select top(0) * into #overlay from data;

with t0 as ( select * from data )
    , t  as ( select * from t0 as c 
                where exists( select * from t0 as c1 
                                where c1.id = c.id and c1.rowID <> c.rowID -- идентификатор строк необходим
                                    and ( c.dt_from <= c1.dt_to and c1.dt_from <= c.dt_to )
                            )
            )
    delete from t output deleted.* into #overlay
;
-- Уникальные даты и интервалы 
create table #dateInts( id int, dt_from date, dt_to date, primary key( id, dt_from ) );

with x as ( select * from #overlay )
    , d as ( select id, [date] = dt_from, DateEnd = 0 from x group by ID, dt_from
            union all
            select id, [date] = dt_to, DateEnd = 1 from x group by ID, dt_to
            )
    , i as ( select *
                    , leadDateEnd = lead(DateEnd) over(partition by Id order by [date], DateEnd )
                    , leadDate = lead(date) over(partition by Id order by [date], DateEnd )
                from d )
    , t as ( select Id
                    , dt_from = iif( DateEnd = 0, [date], dateadd( day, 1, [date] ) )
                    , dt_to = iif( leadDateEnd = 1, leadDate, dateadd( day, -1, leadDate ) )
                from i
            )
    insert #dateInts with(tablockx) 
    select Id, dt_from, dt_to from t where dt_from <= dt_to order by ID, dt_from
;
-- Новые данные без перекрытий
with t as ( select * from #dateInts )
    , c as ( select * from #overlay )
    , x as ( select t.ID, c.val, t.dt_from, t.dt_to
                from t inner join c on t.ID = c.ID and c.dt_from <= t.dt_from and t.dt_to <= c.dt_to  
                where not exists( select * from c as c1 where c1.ID = c.ID and c1.dt_from <= t.dt_from and t.dt_to <= c1.dt_to 
                                                      and  c1.rowID > c.rowID -- это признак "кто новее"
                                        )
            )
    select * into #dataNew from x
;
-- вставляем ИСПРАВЛЕННЫЕ новые данные взад
insert data select * from #dataNew;

...

Рейтинг:

0 / 0

28.01.2022, 20:29

| Ответить | Цитировать | Написать

18 сообщений из 18, страница 1 из 1

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / slowly changing dimensions type 2

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&fpage=4&tid=1683886]:	0ms
get settings:	11ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	77ms
get topic data:	13ms
get forum data:	3ms
get page messages:	61ms
get tp. blocked users:	2ms
others:	15ms

total:	204ms