Oracle 用时间范围连接两个表

亲爱的AskTom-团队!

我想知道是否有可能加入两个有时间范围的表。例如，表 “公司名称” 保存公司的名称，该公司的名称具有来自 _year和to_year的两列，它们定义了该名称有效的年份。表 “地址” 保存了一家公司的地址，其中从 _year和to_year两列定义了该地址有效的年份。我想有一个表格，显示我每年的有效姓名和有效地址。

CREATE TABLE firmname (
    id           NUMBER NOT NULL,
    firm_id      NUMBER NOT NULL,
    name         VARCHAR2(150) NOT NULL,
    from_year    SMALLINT NOT NULL,
    to_year      SMALLINT DEFAULT NULL
);

ALTER TABLE firmname ADD CONSTRAINT pk_fnam PRIMARY KEY ( id );


CREATE SEQUENCE fnam_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER fnam_id_trg BEFORE
    INSERT ON firmname
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := fnam_id_seq.nextval;
END;
/

CREATE TABLE address (
    id           NUMBER NOT NULL,
    firm_id      NUMBER NOT NULL,
    street       VARCHAR2(70),
    zipcode      CHAR(5 CHAR) NOT NULL,
    city         VARCHAR2(40) NOT NULL,
    from_year    SMALLINT NOT NULL,
    to_year      SMALLINT DEFAULT NULL
    );


ALTER TABLE address ADD CONSTRAINT pk_addr PRIMARY KEY ( id );


CREATE SEQUENCE addr_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER addr_id_trg BEFORE
    INSERT ON address
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := addr_id_seq.nextval;
END;
/
insert into firmname(firm_id,name,from_year,to_year)values(1,'Müller GmbH',2011,NULL);
insert into firmname(firm_id,name,from_year,to_year)values(2,'Maier AG',2015,2016);
insert into firmname(firm_id,name,from_year,to_year)values(2,'Maier GmbH',2017,NULL);
insert into firmname(firm_id,name,from_year,to_year)values(3,'Mustermann und Söhne',2017,2018);
insert into firmname(firm_id,name,from_year,to_year)values(3,'Mustermann',2019,NULL);


insert into address(firm_id,street,zipcode,city,from_year,to_year)values(1,'Hufestr. 185', '66764', 'Dortmund',2011, 2015);
insert into address(firm_id,street,zipcode,city,from_year,to_year)values(1,'Parkstr. 95', '66794', 'Dortmund',2016, NULL);
insert into address(firm_id,street,zipcode,city,from_year,to_year)values(2,'Feldmark 19', '57223', 'Kreuztal',2015, NULL);
insert into address(firm_id,street,zipcode,city,from_year,to_year)values(3,'Siegener Str. 28', '59368', 'Werne',2017, 2017);
insert into address(firm_id,street,zipcode,city,from_year,to_year)values(3,'Aachener Str. 30', '52062', 'Aachen',2018, NULL);
复制

谢谢 :-)

所以你想在每次显示每个公司的活动名称和地址？

当然可以。但是首先我要按摩一下数据。

我假设数据中的to_year是包容性的。所以从/到2017的firm_id 3的地址意味着:

整个2017年 (1月1日00:00-12月31日23:59)。

即所有日期，但不包括2018年1月1日。

我将在所有to_yes中添加一个，并使它们具有排他性。因此，上面描述的行变为 (从，到) (2017，2018)，并且在以下情况下处于活动状态:

1 Jan 2017 <= sysdate < 1 Jan 2018
复制

正如我们将看到的，这使得join更容易编写。但这也避免了关于差距的尴尬问题 (如果某件事2017年开始和结束，它到底什么时候结束？一行是否在它后面直接2018年开始？)。

另外，我将 “当前” 行 (to_year为null) 设置为今年1。

我已经把这些放在命名子查询中。并加入了firm_id上的结果。

现在每一行的开始日期是:

greatest ( firms.from_year, addresses.from_year ) 
复制

和结束日期:

least ( firms.to_year, addresses.to_year )
复制

所有这些都给出了:

with firms as (
  select f.from_year from_year, 
         nvl ( f.to_year, extract ( year from sysdate ) ) + 1 to_year,
         f.name, f.id, f.firm_id
  from   firmname f
), addresses as (
  select a.from_year, 
         nvl ( a.to_year, extract ( year from sysdate ) ) + 1 to_year,
         a.street, a.id, a.firm_id
  from   address a
)
select f.firm_id, 
       greatest ( f.from_year, a.from_year ) st,
       least ( f.to_year, a.to_year ) en,
       f.name, a.street
from   firms f
join   addresses a
on     a.firm_id = f.firm_id
order  by f.firm_id, st, en; 

FIRM_ID    ST      EN      NAME                    STREET             
         1    2011    2016 Müller GmbH             Hufestr. 185        
         1    2016    2020 Müller GmbH             Parkstr. 95         
         2    2015    2017 Maier AG                Feldmark 19         
         2    2017    2020 Maier GmbH              Feldmark 19         
         3    2017    2018 Mustermann und Söhne    Siegener Str. 28    
         3    2018    2019 Mustermann und Söhne    Aachener Str. 30    
         3    2019    2018 Mustermann              Siegener Str. 28     -- start after end?!
         3    2019    2020 Mustermann              Aachener Str. 30
复制

如您所见，在结束日期之后有一个带有开始日期的行。

要排除它，请确保每一行的开始都在结束之前:

with firms as (
  select f.from_year from_year, 
         nvl ( f.to_year, extract ( year from sysdate ) ) + 1 to_year,
         f.name, f.id, f.firm_id
  from   firmname f
), addresses as (
  select a.from_year, 
         nvl ( a.to_year, extract ( year from sysdate ) ) + 1 to_year,
         a.street, a.id, a.firm_id
  from   address a
)
select f.firm_id, 
       greatest ( f.from_year, a.from_year ) st,
       least ( f.to_year, a.to_year ) en,
       f.name, a.street
from   firms f
join   addresses a
on     a.firm_id = f.firm_id
and    greatest ( f.from_year, a.from_year ) < least ( f.to_year, a.to_year )
order  by f.firm_id, st, en;

FIRM_ID    ST      EN      NAME                    STREET             
         1    2011    2016 Müller GmbH             Hufestr. 185        
         1    2016    2020 Müller GmbH             Parkstr. 95         
         2    2015    2017 Maier AG                Feldmark 19         
         2    2017    2020 Maier GmbH              Feldmark 19         
         3    2017    2018 Mustermann und Söhne    Siegener Str. 28    
         3    2018    2019 Mustermann und Söhne    Aachener Str. 30    
         3    2019    2020 Mustermann              Aachener Str. 30 
复制