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

Откуда:
Сообщений: 1155
Коллеги, приветствую!

Помогите решить очередную задачу.
Имеется некая таблица. В ней хранятся текущие значения данных.
Если производится апдейт записи, то информация о предыдущих значениях полей сваливается в некую историческую таблицу.
Вообще то в EAV, но предлагаю не заниматься онанизмом, и считать, что pivot этой исторической таблицы - уже сделан.
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 c
1 3 abc 2001-01-01 00:00:00
2 4 NULL 2002-01-01 00:00:00


id dt_change a b c
1 2005-03-10 00:00:00 NULL d NULL
1 2003-01-10 00:00:00 2 d 2001-01-03 00:00:00
1 2002-01-10 00:00:00 NULL NULL NULL
1 2001-01-11 00:00:00 1 NULL NULL
1 2001-01-10 00:00:00 NULL c 2001-01-01 00:00:00
2 2003-01-11 00:00:00 6 rr 2003-01-03 00:00:00
2 2002-01-20 00:00:00 NULL NULL NULL
2 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 млн. записей.
Хотелось бы, чтобы считалось сколько нибудь обозримое время.
18 ноя 21, 20:45    [22397690]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
court
Member

Откуда:
Сообщений: 2440
uaggster
В исходной таблице - примерно миллион записей (и около 200 полей), в исторической, уже отпивотированной - порядка 10 млн. записей.

не надо ничего пивотить
200 outer apply-ев типа
outer apply (select top 1 value from tbl_history where field='a' and dt_change <= @dt order by dt_change desc) a
и все дела ! )
... нуу и индекс "правильный" на tbl_history, конечно
19 ноя 21, 08:38    [22397778]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
uaggster
Member

Откуда:
Сообщений: 1155
court, быстродействие будет чудовищным. Мне бы так, чтобы хоть за пару часов посчиталось. В оригинальной EAV таблице - под 500 млн. записей.
В рамках такой постановки не получится что-то сделать?
Я не знаю, рекурсию что-ли прикрутить...
19 ноя 21, 15:23    [22397998]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
Владислав Колосов
Member

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

я бы курсором накатил и спал спокойно. 10 млн вставок многовато, но обозримо.

Сообщение было отредактировано: 19 ноя 21, 15:57
19 ноя 21, 15:57    [22398015]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
.Евгений
Member

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

В подобной задаче выбирал данные из источника с сортировкой по ключевым полям и дате (соотв. оптимизация запроса), вставляя по условию сохраненное поле предыдущей строки скриптом SSIS. Вкупе с пакетной вставкой это отработает очень быстро.
19 ноя 21, 16:33    [22398031]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
msLex
Member

Откуда:
Сообщений: 9542
А в сторону temporal table не смотрели?
19 ноя 21, 16:56    [22398047]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32173
uaggster
Как размотать эту "историю" с минимумом телодвижений?
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
Хотелось бы, чтобы считалось сколько нибудь обозримое время.
но план не очень :-)
19 ноя 21, 22:07    [22398192]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32173
msLex
А в сторону temporal table не смотрели?
Ага, или в сторону логирования в нормальную таблицу.
Надо же, EAV... Ну, первое в жизни логирование все делают EAV, чужой опыт тут бессилен :-)
19 ноя 21, 22:09    [22398193]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
Владислав Колосов
Member

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

автор
сваливается в некую историческую таблицу. Вообще то в EAV

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

Поэтому надо восстановить полные строки в таблице по такой истории один раз и пополнять эту таблицу.
19 ноя 21, 22:13    [22398194]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
uaggster
Member

Откуда:
Сообщений: 1155
Коллеги мопед не мой, да и гараж - тоже.
Я только занимаюсь ... как будет правильно по-русски... ассенизацией данных.
21 ноя 21, 17:53    [22398719]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32173
.Евгений
uaggster,

В подобной задаче выбирал данные из источника с сортировкой по ключевым полям и дате (соотв. оптимизация запроса), вставляя по условию сохраненное поле предыдущей строки скриптом SSIS. Вкупе с пакетной вставкой это отработает очень быстро.
Кстати да, для быстрее всего сделать это любым средством ETL.
Будет балк, и миллиарды строк не будут проблемой.
Если срок десяток миллионов, то можно сделать курсором.
Ну или ждать, когда в сиквеле допилят lag для not null :-)
21 ноя 21, 18:28    [22398739]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
Владислав Колосов
Member

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

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

2 uaggster: если непонятно, о чем я писал. Для каждого поля надо создать переменную, все переменные имеют начальное значение null. Читаем из потока первую строку и находим результат переменная = isnull(переменная, полученное_значение). Если получили null, то в результирующую таблицу отправляется прежнее значение переменной, иначе новое значение поля.

Сообщение было отредактировано: 22 ноя 21, 01:04
22 ноя 21, 01:04    [22398848]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
uaggster
Member

Откуда:
Сообщений: 1155
Получилось вот что:
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 млн. строк по одной... Надо подумать как это либо пакетировать, либо, я не знаю, отложенную стабильность, что-ли, вглючить...
22 ноя 21, 10:23    [22398931]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
.Евгений
Member

Откуда:
Сообщений: 707
Владислав Колосов,

на всех этапах это будет быстрее. И чтение селекта (вместо курсора), и трансформация C# (вместо SQL), и вставка.
22 ноя 21, 10:47    [22398950]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
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);
22 ноя 21, 10:55    [22398958]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32173
Владислав Колосов
через потоки это тоже самое, что я писал: "я бы курсором накатил и спал спокойно". Но вставка будет быстрее, согласен.
Так я про запись и написал.
Для чтения само собой, так чтение всегда чтение, чего там. Всё равно надо как минимум один раз прочитать таблицу с сотрировкой.
А вот для записи разница огромная, сделать миллиард инсёртов, или один раз балк инсёрт миллиарда записей...
22 ноя 21, 14:05    [22399099]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
uaggster,


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

А TEMPORAL заюзать версия не позволяет?
22 ноя 21, 15:18    [22399141]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
uaggster
Member

Откуда:
Сообщений: 1155
a_voronin
uaggster,


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

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

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

invm, не алле. Запрос неверный.

К сообщению приложен файл. Размер - 22Kb
22 ноя 21, 17:04    [22399215]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
Владислав Колосов
Member

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

чужой мопед не трожь! Собери свой.
22 ноя 21, 17:10    [22399224]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
uaggster
Member

Откуда:
Сообщений: 1155
Наверное, курсором обойдусь. Там, правда, не 10 млн, а 30 млн записей :-(

Думаю in memory table сделать буферную, тысяч на 10 записей, писать в нее по одной записи, а потом делать из нее select в целевую.
Будет быстрее?
22 ноя 21, 17:20    [22399231]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
Oleg_SQL
Member

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

упс... перепутал тему )))

Сообщение было отредактировано: 22 ноя 21, 17:55
22 ноя 21, 17:46    [22399250]     Ответить | Цитировать Сообщить модератору
 Re: Как "отмотать" историю?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32173
uaggster
Наверное, курсором обойдусь. Там, правда, не 10 млн, а 30 млн записей :-(

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

Но вы всё таки подумайте о SSIS
Там можно будет прямо с EAV данные брать, получится быстро.
22 ноя 21, 20:48    [22399403]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить