Используйте SQL для удаления дубликатов из медленно меняющегося измерения типа 2

Я строю хранилище данных с несколькими слоями, хранящими одни и те же данные. Все данные в одном из промежуточных слоев имеют версии с начальной и конечной датами, как если бы это было медленно изменяющееся измерение типа 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

Проблема здесь в том, что он возвращает две строки для идентификатора "4", когда три желательны. Актуально:

id имя RowStartDate RowEndDate
4 Д 2014-01-01 00: 00: 00.000 9999-12-31 00: 00: 00.000
4 Дня 2014-12-31 00: 00: 00.000 2016-12-31 00: 00: 00.000
Желаемая: 
id имя 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.000
Значение D не является правильным для периода, когда значение DD является правильным, поэтому даты версии неверны в первой строке (4, 'D') из запроса my.

Я хочу иметь возможность удалить эти дубликаты в чистом SQL или встроенной табличной функции (у меня есть генератор, который создает функции с несколькими операторами таблиц, которые делают это, но полученные функции работают плохо). У кого-нибудь есть какие-либо идеи?

Источник

1 ответ

Решение

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

1 Первые ранги присваиваются в каждой группе идентификаторов на основе RowStartDate.

2 Далее максимальный ранг (next_rank_no) диапазона рангов, который имеет одинаковое значение для NAME определен. Таким образом, для данных примера строка 1 с id=5 будет иметь значение next_rank_no=5, а строка 2 с идентификатором =4 будет иметь значение next_rank_no=3. Эта версия обрабатывает только NAME колонка. Если вы хотите обработать дополнительные столбцы, они также должны быть включены в условие. Например, если вы хотите включить LOCATION столбец, тогда условия соединения будут выглядеть так:

  left join sorted_versions sv2 on sv2.id = sv1.id and sv2.rank_no > sv1.rank_no and sv2.name = sv1.name and sv2.location = sv1.location
  left join sorted_versions sv3 on sv3.id = sv1.id and sv3.rank_no > sv1.rank_no and (sv3.name <> sv1.name or sv3.location <> sv1.location)

3 Наконец, выбирается первая строка для каждого идентификатора. Затем строка, соответствующая next_rank_no выбран рекурсивным способом.

with sorted_versions as --ranks are assigned within each id group
(
  select 
    v1.id,
    v1.name,
    v1.RowStartDate,
    v1.RowEndDate,
    rank() over (partition by v1.id order by v1.RowStartDate) rank_no
  from versions v1
  left join versions v2 on (v1.id = v2.id and v2.RowStartDate = v1.RowEndDate)
),
next_rank as --the maximum rank of the range of ranks which has the same value for NAME
(
  select 
  sv1.id id, sv1.rank_no rank_no, COALESCE(min(sv3.rank_no)-1 , COALESCE(max(sv2.rank_no), sv1.rank_no)) next_rank_no
  from sorted_versions sv1
  left join sorted_versions sv2 on sv2.id = sv1.id and sv2.rank_no > sv1.rank_no and sv2.name = sv1.name
  left join sorted_versions sv3 on sv3.id = sv1.id and sv3.rank_no > sv1.rank_no and sv3.name <> sv1.name
  group by sv1.id, sv1.rank_no
),
versions_cte as --the rowenddate of the "maximum rank" is selected 
(
  select sv.id, sv.name, sv.rowstartdate, sv3.rowenddate, nr.next_rank_no rank_no
  from sorted_versions sv
  inner join next_rank nr on sv.id = nr.id and sv.rank_no = nr.rank_no and sv.rank_no = 1
  inner join sorted_versions sv3 on nr.id = sv3.id and nr.next_rank_no = sv3.rank_no  
  union all
  select
    sv2.id,
    sv2.name, 
    sv2.rowstartdate,
    sv3.rowenddate,
    nr.next_rank_no
  from versions_cte vc
  inner join sorted_versions sv2 on sv2.id = vc.id and sv2.rank_no = vc.rank_no + 1
  inner join next_rank nr on sv2.id = nr.id and sv2.rank_no = nr.rank_no  
  inner join sorted_versions sv3 on nr.id = sv3.id and nr.next_rank_no = sv3.rank_no
)
select id, name, rowstartdate, rowenddate
from versions_cte
order by id, rowstartdate;

SQL Fiddle demo

Источник
Другие вопросы по тегам