问题描述
我有一个表 T1,它包含一个 NAME 值(不是唯一的)和一个日期范围(D1 和 D2 是日期)当 NAME 相同时,我们对日期范围进行联合(例如 B).
I have a table T1, it contains a NAME value (not unique), and a date range (D1 and D2 which are dates) When NAME are the same, we make a union of the date ranges (e.g. B).
但作为结果 (X),我们需要对所有日期范围进行交集
But as a result (X), we need to make intersection of all the date ranges
表T1
NAME | D1 | D2
A | 20100101 | 20101211
B | 20100120 | 20100415
B | 20100510 | 20101230
C | 20100313 | 20100610
结果:
X | 20100313 | 20100415
X | 20100510 | 20100610
在视觉上,这将给出以下内容:
Visually, this will give the following :
NAME : date range
A : [-----------------------]-----
B : --[----]----------------------
B : ----------[---------------]---
C : -----[--------]---------------
结果:
X : -----[-]----------------------
X : ----------[---]---------------
知道如何使用 SQL/PL SQL 获得它吗?
Any idea how to get that using SQL / PL SQL ?
推荐答案
这里有一个快速的解决方案(可能不是最有效的):
here is a quick solution (may not be the most efficient):
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 个值的所有范围.如果添加行,您可能需要合并结果:
I build all the possible successive date ranges and join this "calendar" with the sample data. This will list all ranges that have 3 values. You may need to merge the result if you add rows:
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
这篇关于ORACLE SQL 日期范围交集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!