ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как "отмотать" историю?
22 сообщений из 22, страница 1 из 1
Как "отмотать" историю?
    #40112975
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Коллеги, приветствую!

Помогите решить очередную задачу.
Имеется некая таблица. В ней хранятся текущие значения данных.
Если производится апдейт записи, то информация о предыдущих значениях полей сваливается в некую историческую таблицу.
Вообще то в EAV, но предлагаю не заниматься онанизмом, и считать, что pivot этой исторической таблицы - уже сделан.
Код: 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.
USE tempdb
GO

CREATE TABLE tbl (
	id INT NOT NULL PRIMARY KEY CLUSTERED
	,a INT NULL
	,b VARCHAR(10) NULL
	,c DATETIME2(0) NULL
	)
GO

CREATE TABLE tbl_history (
	id INT
	,dt_change DATETIME2(0)
	,a INT NULL
	,b VARCHAR(10) NULL
	,c DATETIME2(0) NULL
	)
GO

insert into tbl 
Values 
 (1, 3, 'abc', '20010101')
,(2, 4, NULL, '20020101')
go

insert into tbl_history 
Values 
 (1, '20010110', NULL, 'c', '20010101')
,(1, '20010111', 1, NULL, NULL)
,(1, '20020110', NULL, Null, NULL)
,(1, '20030110', 2, 'd', '20010103')
,(1, '20050310', NULL, 'd', NULL)
,(2, '20010115', 1, NULL, NULL)
,(2, '20020120', NULL, Null, NULL)
,(2, '20030111', 6, 'rr', '20030103')
go

Select * from tbl Order by 1
go
Select * from tbl_history order by 1, 2 DESC
Go

drop table tbl, tbl_history
go



id a b c1 3 abc 2001-01-01 00:00:002 4 NULL 2002-01-01 00:00:00

id dt_change a b c1 2005-03-10 00:00:00 NULL d NULL1 2003-01-10 00:00:00 2 d 2001-01-03 00:00:001 2002-01-10 00:00:00 NULL NULL NULL1 2001-01-11 00:00:00 1 NULL NULL1 2001-01-10 00:00:00 NULL c 2001-01-01 00:00:002 2003-01-11 00:00:00 6 rr 2003-01-03 00:00:002 2002-01-20 00:00:00 NULL NULL NULL2 2001-01-15 00:00:00 1 NULL NULL

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

Т.е. для Ид = 1 на 2005-03-10 (эээ... до этой даты :-) ) значения будут:
1 3 d 2001-01-01
До 2003-01-01:
1 2 d 2001-01-03
До 2002-01-10:
1 2 d 2001-01-03

И т.д.

Как размотать эту "историю" с минимумом телодвижений?
В исходной таблице - примерно миллион записей (и около 200 полей), в исторической, уже отпивотированной - порядка 10 млн. записей.
Хотелось бы, чтобы считалось сколько нибудь обозримое время.
...
Рейтинг: 0 / 0
18.11.2021, 20:45
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113020
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
uaggster
В исходной таблице - примерно миллион записей (и около 200 полей), в исторической, уже отпивотированной - порядка 10 млн. записей.

не надо ничего пивотить
200 outer apply-ев типа
Код: sql
1.
outer apply (select top 1 value from tbl_history where field='a' and dt_change <= @dt order by dt_change desc) a

и все дела ! )
... нуу и индекс "правильный" на tbl_history, конечно
...
Рейтинг: 0 / 0
19.11.2021, 08:38
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113148
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
court, быстродействие будет чудовищным. Мне бы так, чтобы хоть за пару часов посчиталось. В оригинальной EAV таблице - под 500 млн. записей.
В рамках такой постановки не получится что-то сделать?
Я не знаю, рекурсию что-ли прикрутить...
...
Рейтинг: 0 / 0
19.11.2021, 15:23
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113159
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
uaggster,

я бы курсором накатил и спал спокойно. 10 млн вставок многовато, но обозримо.
...
Рейтинг: 0 / 0
19.11.2021, 15:57
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113167
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
uaggster,

В подобной задаче выбирал данные из источника с сортировкой по ключевым полям и дате (соотв. оптимизация запроса), вставляя по условию сохраненное поле предыдущей строки скриптом SSIS. Вкупе с пакетной вставкой это отработает очень быстро.
...
Рейтинг: 0 / 0
19.11.2021, 16:33
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113181
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
А в сторону temporal table не смотрели?
...
Рейтинг: 0 / 0
19.11.2021, 16:56
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113262
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
uaggster
Как размотать эту "историю" с минимумом телодвижений?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
Select 
	id, 
	dt_change, 
	a, 
	max(a) over(partition by a_grp, id) as a_lag,
	b, 
	max(b) over(partition by b_grp, id) as b_lag,
	c,
	max(c) over(partition by c_grp, id) as c_lag
from (
	Select 
		id, 
		dt_change, 
		a, 
		count(a) over (partition by id order by dt_change) as a_grp,
		b, 
		count(b) over (partition by id order by dt_change) as b_grp,
		c,
		count(c) over (partition by id order by dt_change) as c_grp
	from tbl_history
) t
order by id, dt_change



uaggster
Хотелось бы, чтобы считалось сколько нибудь обозримое время.
но план не очень :-)
...
Рейтинг: 0 / 0
19.11.2021, 22:07
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113263
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
msLex
А в сторону temporal table не смотрели?
Ага, или в сторону логирования в нормальную таблицу.
Надо же, EAV... Ну, первое в жизни логирование все делают EAV, чужой опыт тут бессилен :-)
...
Рейтинг: 0 / 0
19.11.2021, 22:09
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113264
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
uaggster,

авторсваливается в некую историческую таблицу. Вообще то в EAV
На самом деле выгодно хранить историю в виде полных строк, а EAV формировать на лету. EAV сложно обрабатывать средствами T-SQL, особенно, если количество строк от 100кк и выше.

Поэтому надо восстановить полные строки в таблице по такой истории один раз и пополнять эту таблицу.
...
Рейтинг: 0 / 0
19.11.2021, 22:13
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113570
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Коллеги мопед не мой, да и гараж - тоже.
Я только занимаюсь ... как будет правильно по-русски... ассенизацией данных.
...
Рейтинг: 0 / 0
21.11.2021, 17:53
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113577
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
.Евгений
uaggster,

В подобной задаче выбирал данные из источника с сортировкой по ключевым полям и дате (соотв. оптимизация запроса), вставляя по условию сохраненное поле предыдущей строки скриптом SSIS. Вкупе с пакетной вставкой это отработает очень быстро.
Кстати да, для быстрее всего сделать это любым средством ETL.
Будет балк, и миллиарды строк не будут проблемой.
Если срок десяток миллионов, то можно сделать курсором.
Ну или ждать, когда в сиквеле допилят lag для not null :-)
...
Рейтинг: 0 / 0
21.11.2021, 18:28
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113630
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
alexeyvg,

через потоки это тоже самое, что я писал: "я бы курсором накатил и спал спокойно". Но вставка будет быстрее, согласен.

2 uaggster: если непонятно, о чем я писал. Для каждого поля надо создать переменную, все переменные имеют начальное значение null. Читаем из потока первую строку и находим результат переменная = isnull(переменная, полученное_значение). Если получили null, то в результирующую таблицу отправляется прежнее значение переменной, иначе новое значение поля.
...
Рейтинг: 0 / 0
22.11.2021, 01:04
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113663
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Получилось вот что:
Код: 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.
USE tempdb
GO

CREATE TABLE tbl (
	id INT NOT NULL PRIMARY KEY CLUSTERED
	,a INT NULL
	,b VARCHAR(10) NULL
	,c DATETIME NULL
	)
GO

CREATE TABLE tbl_history (
	id INT
	,dt_change DATETIME2(0)
	,a INT NULL
	,b VARCHAR(10) NULL
	,c DATETIME NULL
	)
GO

CREATE TABLE tbl_history2 (
	id INT
	,dt_change DATETIME2(0)
	,a INT NULL
	,b VARCHAR(10) NULL
	,c DATETIME NULL
	)
GO

insert into tbl 
Values 
 (1, 3, 'abc', '20010101')
,(2, 4, NULL, '20020101')
go

insert into tbl_history 
Values 
 (1, '20010110', NULL, 'c', '20010101')
,(1, '20010111', 1, NULL, NULL)
,(1, '20020110', NULL, Null, NULL)
,(1, '20030110', 2, 'd', '20010103')
,(1, '20050310', NULL, 'd', NULL)
,(2, '20010115', 1, NULL, NULL)
,(2, '20020120', NULL, Null, NULL)
,(2, '20030111', 6, 'rr', '20030103')
go

Declare @id0 int = NULL, @dt_change0 datetime2(0) = NULL, @a0 INT = NULL, @b0 VARCHAR(10) = NULL, @c0 DATETIME = NULL
Declare @id1 int, @dt_change1 datetime2(0), @a1 INT, @b1 VARCHAR(10), @c1 DATETIME

Declare cur CURSOR LOCAL READ_ONLY FORWARD_ONLY for
Select id, CURRENT_TIMESTAMP dt_change, a, b, c from tbl
Union ALL
Select id, dt_change, a, b, c from  tbl_history
Order by id, dt_change DESC

Open cur
FETCH NEXT FROM cur INTO @id1, @dt_change1, @a1, @b1, @c1
Select @id0=@id1, @dt_change0=@dt_change1, @a0=@a1, @b0=@b1, @c0=@c1
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
	if @id0 = @id1
		Select @id0 = Coalesce(@id1, @id0), @dt_change0 = Coalesce(@dt_change1, @dt_change0), @a0 = Coalesce(@a1, @a0), @b0=Coalesce(@b1, @b0), @c0=Coalesce(@c1, @c0)
	Else 
		Select @id0=@id1, @dt_change0=@dt_change1, @a0=@a1, @b0=@b1, @c0=@c1

insert into tbl_history2 Values (@id0, @dt_change0, @a0, @b0, @c0)
FETCH NEXT FROM cur INTO @id1, @dt_change1, @a1, @b1, @c1

END   
CLOSE cur;  
DEALLOCATE cur; 
go
Select * from tbl_history2
Order by id, dt_change DESC


Правда, меня пугает вставка 10 млн. строк по одной... Надо подумать как это либо пакетировать, либо, я не знаю, отложенную стабильность, что-ли, вглючить...
...
Рейтинг: 0 / 0
22.11.2021, 10:23
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113677
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
Владислав Колосов,

на всех этапах это будет быстрее. И чтение селекта (вместо курсора), и трансформация C# (вместо SQL), и вставка.
...
Рейтинг: 0 / 0
22.11.2021, 10:47
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113681
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
with t (id, dt_changed, a, b, c) as
(
 select
  id, dt_change, 
  max(format(dt_change, 'yyyyMMdd') + cast(a as varchar(10))) over (partition by id order by dt_change rows between unbounded preceding and current row),
  max(format(dt_change, 'yyyyMMdd') + b) over (partition by id order by dt_change rows between unbounded preceding and current row),
  max(format(dt_change, 'yyyyMMdd') + format(c, 'yyyyMMdd')) over (partition by id order by dt_change rows between unbounded preceding and current row)
from
 tbl_history
)
select
 id, dt_changed,
 cast(substring(a, 9, a.l) as int),
 substring(b, 9, a.l),
 cast(substring(c, 9, a.l) as datetime)
from
 t cross apply
 (select cast(0x7fffffff as int)) a(l);
...
Рейтинг: 0 / 0
22.11.2021, 10:55
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113762
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Владислав Колосов
через потоки это тоже самое, что я писал: "я бы курсором накатил и спал спокойно". Но вставка будет быстрее, согласен.
Так я про запись и написал.
Для чтения само собой, так чтение всегда чтение, чего там. Всё равно надо как минимум один раз прочитать таблицу с сотрировкой.
А вот для записи разница огромная, сделать миллиард инсёртов, или один раз балк инсёрт миллиарда записей...
...
Рейтинг: 0 / 0
22.11.2021, 14:05
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113794
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
uaggster,


Странная система ниппель с храненем исторических данных. Лучше сделайте якорную модель -- история по каждому аттрибуту отдельно.

А TEMPORAL заюзать версия не позволяет?
...
Рейтинг: 0 / 0
22.11.2021, 15:18
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113840
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
a_voronin
uaggster,


Странная система ниппель с храненем исторических данных. Лучше сделайте якорную модель -- история по каждому аттрибуту отдельно.

А TEMPORAL заюзать версия не позволяет?

Да не мой это мопед, даже близко.
Я вытаскиваю данные из работающей системы, чтобы передать их в перспективную.
Там чёрт ногу сломит, как обычно. Что хранится, где хранится, как соотносится с бизнес-процессом. Полный Пэ.
Т.к. весь этот перенос в новую систему еще многократно будет перепроверяться бизнесом, с т.з. правильности конвертации - эта задача не однократная (но и не постоянная, т.к. за n итераций - должна кончиться). Поэтому быстродействие, в принципе, волнует. Но не так, чтобы очень.
Я ж говорю - это больше работа ассенизатора.
Ну вот, попалась вот такая "система ниппель". Чего сделаешь то? ниппель-не ниппель, а говно качать надо.

invm , не алле. Запрос неверный.
...
Рейтинг: 0 / 0
22.11.2021, 17:04
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113845
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
uaggster,

чужой мопед не трожь! Собери свой.
...
Рейтинг: 0 / 0
22.11.2021, 17:10
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113851
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Наверное, курсором обойдусь. Там, правда, не 10 млн, а 30 млн записей :-(

Думаю in memory table сделать буферную, тысяч на 10 записей, писать в нее по одной записи, а потом делать из нее select в целевую.
Будет быстрее?
...
Рейтинг: 0 / 0
22.11.2021, 17:20
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113870
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
uaggster,

упс... перепутал тему )))
...
Рейтинг: 0 / 0
22.11.2021, 17:46
| Ответить | Цитировать | Написать  
Как "отмотать" историю?
    #40113979
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
uaggster
Наверное, курсором обойдусь. Там, правда, не 10 млн, а 30 млн записей :-(

Думаю in memory table сделать буферную, тысяч на 10 записей, писать в нее по одной записи, а потом делать из нее select в целевую.
Будет быстрее?
Быстрее, думаю.

Но вы всё таки подумайте о SSIS
Там можно будет прямо с EAV данные брать, получится быстро.
...
Рейтинг: 0 / 0
22.11.2021, 20:48
| Ответить | Цитировать | Написать  
22 сообщений из 22, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как "отмотать" историю?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=9&tid=1684085]:
 0ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 30ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 13ms
total:  136ms
созд. / загр.: 136ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]