Блог

У меня есть двумерные таблицы в формате SCD Type 2, которые я хотел бы объединить. Вторая таблица содержит дополнительные строки, относящиеся к конечной структуре первой таблицы.

Первая таблица (foo):

 | employee_id | location_id | team_id | date_effective | date_expired |
|-------------|-------------|---------|----------------|--------------|
| 40          | 1           | 6       | 20180101       | 20190331     |
| 40          | 2           | 6       | 20190331       | 99991231     |
  

Вторая таблица (панель):

 | team_id | manager_id | date_effective | date_expired |
|---------|------------|----------------|--------------|
| 6       | 15         | 20180301       | 20180630     |
| 6       | 27         | 20180630       | 99991231     |
  

Желаемый результат после объединения:

 | employee_id | location_id | team_id | manager_id | date_effective | date_expired |
|-------------|-------------|---------|------------|----------------|--------------|
| 40          | 1           | 6       | NULL       | 20180101       | 20180301     |
| 40          | 1           | 6       | 15         | 20180301       | 20180630     |
| 40          | 1           | 6       | 27         | 20180630       | 20190331     |
| 40          | 2           | 6       | 27         | 20190331       | 99991231     |
  

Я знаю, как объединить две таблицы по датам, но не знаю, как эффективно генерировать дополнительные строки, необходимые для вывода. Вот мой текущий код:

 with foo as (
select * 
from 
(values (40,1,6,20180101,20190331),(40,2,6,20190331,99991231))t(employee_id, location_id, team_id, date_effective, date_expired)
)

,bar as (
select * 
from 
(values (6,15,20180301,20180630),(6,27,20180630,99991231))t(team_id, manager_id, date_effective, date_expired)
)

select * 
from foo f
left join bar b on f.team_id = b.team_id 
    and ((f.date_effective between b.date_effective and b.date_expired)
    or (b.date_effective >= f.date_effective and b.date_effective < f.date_expired))
  

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

Заранее спасибо!

Возможным решением является поиск разных дат как в foo, так и в bar для team_id и объединение обратно в foo и bar .

 with foo as (
select * 
from 
(values (40,1,6,20180101,20190331),(40,2,6,20190331,99991231))t(employee_id, location_id, team_id, date_effective, date_expired)
)
,bar as (
select * 
from 
(values (6,15,20180301,20180630),(6,27,20180630,99991231))t(team_id, manager_id, date_effective, date_expired)
)
,dist as (
select date_effective, team_id
from foo
union
select date_effective, team_id
from bar
)
select *
from dist d
left join foo f on d.team_id = f.team_id and d.date_effective >= f.date_effective and d.date_effective < f.date_expired
left join bar b on d.team_id = b.team_id and d.date_effective >= b.date_effective and d.date_effective < b.date_expired
order by 1
  

Я бы попытался решить проблему, создав cte для диапазона before, где в одной из таблиц SCD есть данные, а в другой нет, и перекрывающийся диапазон, где в обеих таблицах есть данные. Аналогично, если бы у меня была ситуация, когда 1 таблица перестала записывать историческую информацию через определенное время, я бы создал диапазон after.

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

 WITH foo(employee_id, location_id, team_id, date_effective, date_expired) AS ( VALUES 
  (40,1,6,'2018-01-01'::TIMESTAMP,'2019-03-31'::TIMESTAMP),    
  (40,2,6,'2019-03-31','9999-12-31')    
)
, bar(team_id, manager_id, date_effective, date_expired) AS( VALUES
  (6,15,'2018-03-01'::TIMESTAMP,'2018-06-30'::TIMESTAMP),     
  (6,27,'2018-06-30','9999-12-31')
)
, overlapping AS (
SELECT
  team_id
, employee_id
, location_id
, manager_id
, GREATEST(foo.date_effective, bar.date_effective) date_effective
, LEAST(foo.date_expired, bar.date_expired) date_expired
FROM foo JOIN bar USING (team_id)
WHERE tsrange(foo.date_effective, foo.date_expired) amp;amp; tsrange(bar.date_effective, bar.date_expired)
)
, before AS (
SELECT 
  team_id
, employee_id
, location_id
, NULL::INTEGER manager_id
, MIN(foo.date_effective) date_effective
, MIN(bar.date_effective) date_expired
FROM foo
LEFT JOIN bar USING (team_id)
GROUP BY team_id, employee_id, location_id
HAVING NOT EXISTS (SELECT FROM overlapping WHERE overlapping.date_effective = MIN(foo.date_effective) AND overlapping.team_id = foo.team_id)
)
SELECT * FROM before
UNION ALL
SELECT * FROM overlapping
ORDER BY 5
  

Это дает результат:

 team_id employee_id location_id manager_id  date_effective  date_expired
      6          40           1       NULL      2018-01-01    2018-03-01
      6          40           1         15      2018-03-01    2018-06-30
      6          40           1         27      2018-06-30    2019-03-31
      6          40           2         27      2019-03-31    9999-12-31