ORACLE SQL日期范围交集

Question

ORACLE SQL日期范围交集

15

我有一个名为T1的表格，它包含一个NAME值（不唯一）和一个日期范围（D1和D2是日期）。当NAME相同时，我们将日期范围进行合并（例如B）。

但是作为结果（X），我们需要对所有日期范围进行交集操作。

编辑：表格T1

NAME | D1       | D2
A    | 20100101 | 20101211
B    | 20100120 | 20100415
B    | 20100510 | 20101230
C    | 20100313 | 20100610

结果:

X    | 20100313 | 20100415
X    | 20100510 | 20100610

从视觉上看，这将呈现如下：

NAME        : date range
A           : [-----------------------]-----
B           : --[----]----------------------
B           : ----------[---------------]---
C           : -----[--------]---------------

结果：

X           : -----[-]----------------------
X           : ----------[---]---------------

有没有想法用SQL / PL SQL来获取这个？

- guigui42

1

你可以尝试使用重叠 - 但这是一个未记录的功能，http://oraclesponge.wordpress.com/2008/06/12/the-overlaps-predicate/ 它只检查数据范围是否与期间重叠。 - Michael Pakhantsov

你可以展示一列/行实际的例子来呈现你表中的列值以及你想要通过这个SQL查询返回的数据值吗？ - Mark Baker

结果中的日期交集是哪两个名称？A和C是不同的名称，而B的两个范围似乎没有包含相关的日期范围。 - Rajesh Chamarthi

@Mark Baker：我按照你的要求添加了表格行。 @Rajesh：A、B和C是我已经在表格中拥有的数据，X是我想要的结果。如果有多行具有相同的名称（如B），那么我们将这些日期范围合并。 - guigui42

@Michael Pakhantsov：正如http://www.orafaq.com/node/2067所述，它在生产环境中使用是不安全的（可能会在下一次Oracle更新后出现问题）。尽管如此，这仍然很有趣，感谢提供信息。 - guigui42

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Vincent Malgrat · Accepted Answer

这里有一个快速解决方案（可能不是最有效的）：

SQL> CREATE TABLE myData AS
  2  SELECT 'A' name, date'2010-01-01' d1, date'2010-12-11' d2 FROM DUAL
  3  UNION ALL SELECT 'B', date'2010-01-20', date'2010-04-15' FROM DUAL
  4  UNION ALL SELECT 'B', date'2010-05-10', date'2010-12-30' FROM DUAL
  5  UNION ALL SELECT 'C', date'2010-03-13', date'2010-06-10' FROM DUAL;

Table created

SQL> WITH segments AS (
  2  SELECT dat seg_low, lead(dat) over(ORDER BY dat) seg_high
  3    FROM (SELECT d1 dat FROM myData
  4           UNION
  5           SELECT d2 dat FROM myData)
  6  )
  7  SELECT s.seg_low, s.seg_high
  8    FROM segments s
  9    JOIN myData m ON s.seg_high > m.d1
 10                 AND s.seg_low < m.d2
 11   GROUP BY s.seg_low, s.seg_high
 12  HAVING COUNT(DISTINCT NAME) = 3;

SEG_LOW     SEG_HIGH
----------- -----------
13/03/2010  15/04/2010
10/05/2010  10/06/2010

我会建立所有可能的连续日期范围并将这个“日历”与示例数据连接。这将列出所有包含3个值的范围。如果您添加了行，可能需要合并结果:

SQL> insert into mydata values ('B',date'2010-04-15',date'2010-04-16');

1 row inserted

SQL> WITH segments AS (
  2  SELECT dat seg_low, lead(dat) over(ORDER BY dat) seg_high
  3    FROM (SELECT d1 dat FROM myData
  4           UNION
  5           SELECT d2 dat FROM myData)
  6  )
  7  SELECT MIN(seg_low), MAX(seg_high)
  8    FROM (SELECT seg_low, seg_high, SUM(gap) over(ORDER BY seg_low) grp
  9             FROM (SELECT s.seg_low, s.seg_high,
 10                           CASE
 11                              WHEN s.seg_low
 12                                   = lag(s.seg_high) over(ORDER BY s.seg_low)
 13                              THEN 0
 14                              ELSE 1
 15                           END gap
 16                      FROM segments s
 17                      JOIN myData m ON s.seg_high > m.d1
 18                                   AND s.seg_low < m.d2
 19                     GROUP BY s.seg_low, s.seg_high
 20                    HAVING COUNT(DISTINCT NAME) = 3))
 21   GROUP BY grp;

MIN(SEG_LOW) MAX(SEG_HIGH)
------------ -------------
13/03/2010   16/04/2010
10/05/2010   10/06/2010