如何合并两个版本表？

Sergey 发表于 Dev

106

谢尔盖

有两个表：

表1（id，p1，p2，date_from，date_to）：

(1, 'z', 55, '01.05.2010 12:30:20', '17.05.2010 13:10:14'),
(1, 'c', null, '17.05.2010 13:10:15', '18.01.2010 04:13:15'),
(1, 'c', 25, '18.01.2010 04:13:16', '01.01.9999 00:00:00');

表2（id，p3，date_from，date_to）：

(1, 15, '01.04.2010 12:30:20', '02.05.2010 13:10:14'),
(1, 35, '02.05.2010 13:10:15', '01.01.9999 00:00:00');

合并2个表时，必须有包含每个参数更改历史记录的字段。那是：

选择（id，p1，p2，p3，date_from，date_to）：

(1, null, null, 10, '01.04.2010 12:30:20', '01.05.2010 12:30:19'),
(1, 'z', 55, 15, '01.05.2010 12:30:20', '02.05.2010 13:10:14'),
(1, 'z', 55, 35, '02.05.2010 13:10:15', '17.05.2010 13:10:14'),
(1, 'c', null, 35, '17.05.2010 13:10:15', '18.01.2010 04:13:15'),
(1, 'c', 25, 35, '18.01.2010 04:13:16', '01.01.9999 00:00:00');

如果两个表中的记录在时间上（或部分时间）不重叠，则应输出null。

到目前为止，我已经完成了：

select 
    a.id, 
    case 
        when a.date_to between b.date_from and b.date_to 
            or a.date_from between b.date_from and b.date_to 
        then p1 
    end as p1,
    case 
        when a.date_to between b.date_from and b.date_to 
            or a.date_from between b.date_from and b.date_to 
        then p2 
    end as p2, 
    p3, 
    a.date_from as af, 
    b.date_from as bf, 
    a.date_to as at, 
    b.date_to as bt 
from 
    Table1 a 
    left join Table2 b 
on 
    a.id=b.id 
    and (b.date_from between a.date_from and a.date_to 
         or a.date_from between b.date_from and b.date_to)

缺口

您可以通过首先id使用一系列CTE计算每个值的所有范围来获得所需的结果：

WITH dates_from AS (
  SELECT id, date_from FROM Table1
  UNION
  SELECT id, date_to + interval '1 second' FROM Table1
  UNION
  SELECT id, date_from FROM Table2
  UNION
  SELECT id, date_to + interval '1 second' FROM Table2
),
dates_to AS (
  SELECT id, date_to FROM Table1
  UNION
  SELECT id, date_from - interval '1 second' FROM Table1
  UNION
  SELECT id, date_to FROM Table2
  UNION
  SELECT id, date_from - interval '1 second' FROM Table2
),
ranges AS (
  SELECT df.id, date_from, MIN(date_to) AS date_to
  FROM dates_from df
  JOIN dates_to dt ON dt.id = df.id AND dt.date_to > df.date_from
  GROUP BY df.id, date_from
)
SELECT *
FROM ranges

对于您的样本数据（进行了调整，使to第2行中Table1的from日期大于该日期），得出：

id  date_from               date_to
1   2010-05-17 13:10:15     2011-01-18 04:13:15
1   2010-05-01 12:30:20     2010-05-02 13:10:14
1   2010-04-01 12:30:20     2010-05-01 12:30:19
1   2010-05-02 13:10:15     2010-05-17 13:10:14
1   2011-01-18 04:13:16     9999-01-01 00:00:00

然后可以将此表LEFT JOIN编辑到两个表（在重叠范围内）以提取适当的p1，p2以及p3每个范围的值：

SELECT r.id,
       t1.p1, t1.p2, t2.p3,
       r.date_from, r.date_to
FROM ranges r
LEFT JOIN Table1 t1 ON t1.id = r.id AND t1.date_from <= r.date_to and t1.date_to >= r.date_from
LEFT JOIN Table2 t2 ON t2.id = r.id AND t2.date_from <= r.date_to and t2.date_to >= r.date_from
ORDER BY r.id, r.date_from

输出：

id  p1  p2  p3  date_from               date_to
1           15  2010-04-01 12:30:20     2010-05-01 12:30:19
1   z   55  15  2010-05-01 12:30:20     2010-05-02 13:10:14
1   z   55  35  2010-05-02 13:10:15     2010-05-17 13:10:14
1   c       35  2010-05-17 13:10:15     2011-01-18 04:13:15
1   c   25  35  2011-01-18 04:13:16     9999-01-01 00:00:00

iddbfiddle上的演示（包括一秒钟的值）

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。