sql - 来自重叠间隔的 Oracle 时间线报告

Question

我正在使用 Oracle 10g，并且我有一个具有重叠间隔的表。以简化形式:

create TABLE INTERVAL_TEST (
    STARTDATE DATE NOT NULL,
    ENDDATE DATE,
    ITEM VARCHAR2(100) NOT NULL
)

insert into INTERVAL_TEST (STARTDATE, ENDDATE, ITEM) values (to_date('2012-01-01', 'YYYY-MM-DD'), null, 'AAA');
insert into INTERVAL_TEST (STARTDATE, ENDDATE, ITEM) values (to_date('2012-02-01', 'YYYY-MM-DD'), to_date('2012-03-01', 'YYYY-MM-DD'), 'BBB');

如您所见，存在重叠条目。第一个中的 null 表示这是“永远”有效的。
我需要生成一个报告，在时间轴上显示历史记录，列出每行的有效项目数。输出中不应该有时间重叠，而是应该为每个没有变化的子间隔列出所有有效项目:

START       END          VALID_ITEMS
----------  ----------   --------------
2012-01-01  2012-02-01   AAA
2012-02-01  2012-03-01   AAA, BBB
2012-03-01  NULL         AAA

有没有生成这样结果的SQL语句？

Answer 1

你可以用一个感叹词来做到这一点:

SQL> WITH timeline AS
  2          (SELECT mydate startdate,
  3                  lead(mydate) OVER (ORDER BY mydate) - 1 enddate
  4             FROM (SELECT startdate mydate FROM interval_test
  5                   UNION
  6                   SELECT enddate FROM interval_test)
  7            WHERE mydate IS NOT NULL)
  8  SELECT startdate,
  9         enddate,
 10         max(substr(sys_connect_by_path(item, ','), 2)) items
 11    FROM (SELECT t.startdate,
 12                 t.enddate,
 13                 item,
 14                 row_number() OVER (PARTITION BY t.startdate, t.enddate
 15                                    ORDER BY i.item) rn
 16            FROM    timeline t
 17                 JOIN
 18                    interval_test i
 19                 ON nvl(i.enddate, DATE '9999-12-31') - 1 >= t.startdate
 20                AND i.startdate <= nvl(t.enddate, DATE '9999-12-31'))
 21  START WITH rn = 1
 22  CONNECT BY rn = PRIOR rn + 1
 23         AND startdate = PRIOR startdate
 24  GROUP BY startdate, enddate
 25  ORDER BY startdate;

STARTDATE  ENDDATE    ITEMS
---------- ---------- --------------------
2012-01-01 2012-01-31 AAA
2012-02-01 2012-02-29 AAA,BBB
2012-03-01            AAA

我使用第一个子查询列出所有间隔:

SQL> SELECT mydate startdate,
  2                  lead(mydate) OVER (ORDER BY mydate) - 1 enddate
  3             FROM (SELECT startdate mydate FROM interval_test
  4                   UNION
  5                   SELECT enddate FROM interval_test)
  6            WHERE mydate IS NOT NULL;

STARTDATE  ENDDATE
---------- ----------
2012-01-01 2012-01-31
2012-02-01 2012-02-29
2012-03-01

加入以下查询，该查询在给定两个日期的情况下列出一行中的所有项目:

SELECT max(substr(sys_connect_by_path(item, ','), 2)) items
  FROM (SELECT item, row_number() OVER (ORDER BY item) rn
          FROM interval_test
         WHERE nvl(enddate, DATE '9999-12-31') >= :startdate
           AND startdate <= :enddate)
CONNECT BY rn = PRIOR rn + 1
START WITH rn = 1;