SQL：用有效的from/有效的to欄位連接表 -有解無憂

我在我們的Postgresql資料庫中面臨一個非常普遍的問題。許多表都包含只在一段時間內有效的條目，例如，可能會隨著時間的推移而變化的合同細節。

為了處理這個問題，我們提供了兩個欄位，valid from和valid to，以表明行內容的有效期。每當一個合同發生變化時，就會有一行被添加到表中，其中包含實際的資訊和相應的有效日期。

主要的問題出現在對具有重疊有效期的表進行連接時。更確切地說，給定第一個表：

fg valid_from valid_to attr_table1
key1 2020-01-01-18 A
key1 2020-01-19 null B
key2 2020-01-01-30 A
key2 2020-0130 null B

和第二個表

fg valid_from valid_to attr_table2
key1 20200101 2020-01-10 1。 0
key1 2020-01-10 null 3.0
key2 20200101 2020-01-30 10。 0
key2 2020-01-30 null 11.

我想建立一個帶有有效期欄位的聯接表，該欄位嵌入了兩個表的有效期，例如：

我想建立一個帶有有效期欄位的聯接表，該欄位嵌入了兩個表的有效期。

fg valid_from valid_to attr_table1 attr_table2
key1 20200101 2020-01-10 A 1. 0
key1 20200110 2020-01-18 A 3。 0
key1 2020-01-18 null B 3．
key2 20200101 2020-01-30 A 10. 0
key2 2020-01-30 null B 11.0

直到現在，我最有說服力的嘗試是切換到Postgresql特有的型別daterange，并使用&&運算子（"有公共點"）。我把valid from和valid to兩個欄位都串聯到一個validity欄位中，接下來的查詢似乎可以完成這個作業：

select t1.fg。
       (case when upper(t1.validity) is null
             then case when (upper(t2. validity） is null) 
                       then case when lower（t1. validity) > lower(t2.validity) 
                                 then daterange(lower(t1.validity), null)
                                 else daterange(lower(t2.validity), null)
                                 結束。
                       else case when lower（t1. validity) > lower(t2.validity) 
                                 then daterange(lower(t1.validity), upper(t2.validity) 
                                 else daterange(lower(t2.validity), upper(t2.validity) 
                                 結束。
                       end
             when upper(t2.validity) is null
             then case when (upper(t1. validity） is null) 
                       then case when lower（t1. validity) > lower(t2.validity) 
                                 then daterange(lower(t1.validity), null)
                                 else daterange(lower(t2.validity), null)
                                 結束。
                       else case when lower（t1. validity) > lower(t2.validity) 
                                 then daterange(lower(t1.validity), upper(t1.validity) 
                                 else daterange(lower(t2.validity), upper(t1.validity) 
                                 結束。
                       end
             when lower(t1.validity) <= lower(t2.validity)
             then case when upper(t1. validity) >= upper(t2.validity) 
                       then daterange(lower(t2.validity), upper(t2.validity)
                       else daterange(lower(t2.validity), upper(t1.validity)
                       結束。
             else case when upper(t1. validity) >= upper(t2.validity) 
                       then daterange(lower(t1.validity), upper(t2.validity)
                       else daterange(lower(t1.validity), upper(t1.validity)
                       結束。
             結束 
            ) as validity,
       t1.attr_table1, 
       t2.attr_table2
  from table1 as t1 
       join table2 as t2
         on t1.fg = t2.fg
        and t1.validity && t2.validity
order by fg, validity

然而，當第一個表的起點與第二個表的任何條目不匹配時，這個查詢就會失敗。例如，在第一個和第二個表中有一個額外的行，比如

在表1中：

key1 2019-12-25 2020-01-01 A

在表2中：

key1 2019-12-27 2020-01-01 -1

結果輸出表的第一行是

key1 2019-12-27 2020-01-01 A -1

而不是

key1 20191225 20191227 A null
key1 20191227 2020-01-01 A -1

有誰知道有更好的方法嗎？

編輯：依靠daterange創建table1和table2的代碼：

create table table1
( 
  fg文本。
  validity daterange, 
  attr_table1 text
);
insert into table1 
values[/span
('key1', daterange('2020-01-01', '2020-01-18'), 'A') 。
('key1', daterange('2020-01-18', null ) , 'B')。
('key2'/span>, daterange('2020-01-01'/span>, '2020-01-30'/span>), 'A'/span>)。
('key2', daterange('2020-01-30', null ) , 'B')

并且

create table table2
( 
  fg文本。
  validity daterange,  
  attr_table2 text
);
insert into table2 
values[/span
('key1', daterange('2020-01-01', '2020-01-10'), 1.0) 。
('key1', daterange('2020-01-10', null ) , 3.0)。
('key2', daterange('2020-01-01', '2020-01-30'), 10.0) 。
('key2', daterange('2020-01-30', null ) ，11.0)

uj5u.com熱心網友回復：

趕著去參加下一次會議，稍后會寫一個解釋，但現在......

主要是依賴一個人的力量。

主要依賴于行與行之間沒有任何空隙

。

也依賴于attrib1或attrib2的值從不為空（空值會被替換成之前的非空值）

給...

with
  合并AS
(
  select fg, lower(validity) AS valid_from, attr_table1, NULL as attr_table2 from table1
  unionall
  select fg, lower(validity) AS valid_from, NULL AS attr_table1, attr_table2 from table2
),
  聚合 AS
(
  select
    fg,
    valid_from,
    max(attr_table1) asattr_table1,
    max(attr_table2) asattr_table2,
    count(max(attr_table1)) over (partition by fg order by valid_from） attrib1_grp,
    count(max(attr_table2) over (partition by fg order by valid_from) Attrib2_grp
  來自
    組合
  組 by
    fg,
    有效來自
)
SELECT 
  fg,
  valid_from,
  lead(valid_from) over（partition by fg order by valid_from) as valid_to,
  max(attr_table1) over (partition by fg, attrib1_grp) as attr_table1,
  max(attr_table2) over (partition by fg，attrib2_grp) as attr_table2
來自的
  聚合的
order by 
  fg,
  有效來自

演示。https://dbfiddle.uk/?rdbms=postgres_13&fiddle=7d97c9623e5f9efb4d729775ff61e7b5

編輯：

上面的代碼依賴于這樣一個前提：如果一個鍵的屬性在either表中發生變化，結果集也需要在該日期發生變化。

這意味著我們只需聯合兩個表，只保留 valid_from，并使用 LEAD() 計算 valid_to（它有時會從同一個表中獲取 valid_from，有時從另一個表中獲取）。

這就在屬性列中留下了一堆空值。如果attrib1發生變化，attrib2將在聯合的集合中成為NULL，反之亦然。

要填補這些空值，需要回顧新的時間序列，以找到該屬性的最新的NOT NULL值。由于LAST_VALUE()沒有一個SKIP NULLS選項，我自己做了一個......

有一個累積的計數器，用于記錄屬性被NOT NULL的次數，并將其作為一個組的識別符號
根據定義，組中的第一個屬性將是NOT NULL，而所有的行將是NULL
因此，采取MAX(attributee)的組允許我得到所有NULL行的前NOT NULL值

這個演示讓我們更容易看到計算的發生......

這個演示讓我們更容易看到計算的發生。

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=a25459b60bc875e47f6311f79d0ce9ba

編輯：

我認為這現在可以在間隙（屬性被隱式設定為NULL）和行（屬性被顯式設定為NULL）中作業。

假設沒有兩個人被設定為 "NULL"。
假設沒有兩行（相同的鍵，相同的表）開始于同一日期
- 如果發生了這種情況，那么就會有兩行（相同的鍵，相同的表）在同一日期開始
- 如果發生了這種情況，該屬性將選擇該日期的MAX()值
假設在前一行（相同的鍵，相同的表）結束之前，沒有行可以開始。
- 如果發生這種情況，就會回傳胡言亂語

（盡管我建議進行更嚴格的測驗......）

with
  組合(
    fg, valid_from, attr_table1, attr_table2, atrib1_set, atrib2_set
  ) AS[/span
(
  select fg, lower(validity), attr_table1, NULL , 1, NULL: :int from table1
  unionall
  select fg, upper(validity), NULL。       NULL , 1, NULL: :int from table1
  unionall
  select fg, lower(validity), NULL , attr_table2, NULL, 1 from table2
  unionall
  select fg, upper(validity), NULL , NULL , NULL, 1 from table2
),
  聚合 AS
(
  選擇 
    fg,
    valid_from,
    max(attr_table1) asattr_table1,
    max(attr_table2) asattr_table2,
    count(max(atrib1_set) over (partition by fg order by valid_from) attrib1_grp,
    count(max(atrib2_set) over (partition by fg order by valid_from) Attrib2_grp
  來自
    組合
  where
    valid_from is not null
  group by
    fg,
    有效的_from
)
SELECT[/span
  fg,
  valid_from,
  lead(valid_from) over（partition by fg order by valid_from) as valid_to,
  max(attr_table1) over (partition by fg, attrib1_grp) as attr_table1,
  max(attr_table2) over (partition by fg，attrib2_grp) as attr_table2
來自的
  聚合的
order by 
  fg,
  有效來自

演示：

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=20ed6974aace9ec52535d78c36594e9f

uj5u.com熱心網友回復：

[UPDATE]

按fg
LEFT連接表1和表2到這個表
[為了便于比較，我把valid_to的NULL改為infinity]/li>

create table table1
(
  fg文本。
  validity daterange,
  attr_table1 text
);

insert into table1
values[/span
('key1', daterange('2019-12-25', '2020-01-01'), 'A'), --NEW
('key1'/span>, daterange('2020-01-01'/span>, '2020-01-18'/span>), 'A'/span>)。
('key1', daterange('2020-01-19', '無窮大' ), 'B') 。
('key2'/span>, daterange('2020-01-01'/span>, '2020-01-30'/span>), 'A'/span>)。
('key2', daterange('2020-01-30', '無窮大' ), 'B') 。

創建 表格 table2
(
  fg文本。
  有效性daterange。
  attr_table2 text
);
insert into table2
values[/span
('key1', daterange('2019-12-27', '2020-01-01'), -1 ) , -- NEW
('key1', daterange('2020-01-01', '2020-01-10'), 1.0) 。
('key1', daterange('2020-01-10', '無窮大' ), 3.0)。
('key2', daterange('2020-01-01', '2020-01-30'), 10.0) 。
('key2', daterange('2020-01-30', '無窮大' ), 11.0)。


        --制作一個包含所有時間點的'CALENDAR'表（每個fg）。
        -- ---------------------------------------------------------
WITH pits AS (
        select distinct fg, lower(validity) as pit FROM table1
UNION[/span]。
        select distinct fg, upper(validity) as pit FROM table1
UNION[/span]。
        select distinct fg, lower(validity) as pit FROM table2
UNION[/span]。
        select distinct fg, upper(validity) as pit FROM table2
        )
        --結合所有相鄰的PITs到范圍。
        -- ---------------------------------------
，對AS (
        SELECT fg, pit as opit
        , lead(pit) OVER（PARTITION BY fg ORDER BY pit) AS npit
        from pits
        )
        -- 從它們中做出dateranges。
        -- --------------------------
, tablex AS (
        SELECT fg
        , daterange(opit,npit) AS validity
        FROM對
        WHERE npit IS NOT NULL-- ORDER BY 1,2;
        )
        --左鍵連接table1和table2到all_rages。
        -- ----------------------------------------------
SELECT tx.fg
        , tx.validity
        , t1.validity * t2.validity AS overlapped
        , t1.attr_table1
        , t2.attr_table2
FROM tablex tx
LEFT JOIN table1 t1 ON t1. fg = tx.fg AND t1.validity &&/span> tx.validity
LEFT JOIN table2 t2 ON t2. fg = tx.fg AND t2.validity &&/span> tx.validity
ORDER BY 1,2
        ;

結果：

DROP SCHEMA
CREATE SCHEMA
SET[/span
CREATE TABLE
INSERT 0 5
CREATE TABLE
INSERT 0 5
  fg | validity | overlapped | attr_table1 | attr_table2 
------ ------------------------- ------------------------- ------------- -------------
 key1 | [20191225, 2019-12-27) | | A | 
 key1 | [20191227, 2020-01-01) | [20191227, 2020-01-01) | A | -1
 key1 | [2020-01-01。 2020-01-10) | [2020-01-01, 2020-01-10) | A | 1.0
 key1 | [20200110, 2020-0118) | [2020-01-10, 2020-0118) | A |  3.0
 key1 | [20200118, 2020-01-19) | | | 3.0
 key1 | [20200119, infinity) | [2020-01-19, infinity) | B | 3. 0
 key2 | [20200101, 2020-0130) | [2020-01-01, 2020-0130) | A | 10.0
 key2 | [20200130, infinity) | [20200130, infinity) | B | 11. 0
(8 rows)

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/320145.html

標籤：

上一篇：postgresql的頂值多重連接

下一篇：如何用唯一的用戶ID連接兩個資料框架