Я создаю хранилище данных с несколькими слоями, в которых хранятся одни и те же данные. Все данные в одном из средних слоев имеют версии с начальной и конечной датами, как если бы это было медленно меняющееся измерение типа 2. Проблема возникает, когда я запрашиваю эти таблицы. Обычно в таблице больше столбцов, чем в запросе, поэтому смежные версии в запросе имеют разные даты начала и окончания, но в остальном идентичны. Я хочу объединить эти версии, чтобы показывать даты, когда изменяются столбцы в запросе, а не когда изменяются строки в таблице.
У меня есть SQL, который почти работает:
create table versions
(id int
, name varchar(100) Not null
, RowStartDate datetime Not null
, RowEndDate datetime Not null
, primary key (id,RowStartDate)
, check (RowStartDate < RowEndDate));
insert into versions values
(1,'A','2014-01-01','9999-12-31')
,(2,'B','2014-01-01','2014-12-31')
,(2,'B','2014-12-31','9999-12-31')
,(3,'C','2014-01-01','2014-12-31')
,(3,'CC','2014-12-31','2015-12-31')
,(3,'CC','2015-12-31','9999-12-31')
,(4,'D','2014-01-01','2014-12-31')
,(4,'DD','2014-12-31','2015-12-31')
,(4,'DD','2015-12-31','2016-12-31')
,(4,'D','2016-12-31','9999-12-31')
,(5,'E','2014-01-01','2014-12-31')
,(5,'E','2014-12-31','2015-12-31')
,(5,'E','2015-12-31','2016-12-31')
,(5,'E','2016-12-31','2017-12-31')
,(5,'E','2017-12-31','9999-12-31')
;
WITH CTE_detect_duplicates AS (SELECT [id]
,[name]
,[RowStartDate]
,[RowEndDate]
,LAST_VALUE(RowEndDate) OVER (PARTITION BY id, name ORDER BY RowStartDate, RowEndDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as LastEndDate
,rank() OVER (PARTITION BY id, name ORDER BY RowStartDate, RowEndDate) as duplicateNumber
FROM versions
)
SELECT [id]
,[name]
,[RowStartDate]
,LastEndDate as RowEndDate
FROM CTE_detect_duplicates
WHERE duplicateNumber = 1
The problem here is that it returns two rows for id "4", when three are desired. Actual:
id name RowStartDate RowEndDate 4 D 2014-01-01 00:00:00.000 9999-12-31 00:00:00.000 4 DD 2014-12-31 00:00:00.000 2016-12-31 00:00:00.000Desired:
id name RowStartDate RowEndDate 4 D 2014-01-01 00:00:00.000 2014-12-31 00:00:00.000 4 DD 2014-12-31 00:00:00.000 2016-12-31 00:00:00.000 4 D 2016-12-31 00:00:00.000 9999-12-31 00:00:00.000The value D is not correct for the period when the value DD is correct, so the version dates are incorrect on the first row (4,'D') from the my query.
Я хочу иметь возможность удалять эти дубликаты в чистом SQL или встроенной функции с табличным значением (у меня есть генератор, который создает многооператорные табличные функции, которые делают это, но результирующие функции работают плохо). У кого-нибудь есть идеи?