Oracle查询窗口功能
问题描述:
请假设我有一个名为MYDATA Oracle表,有如下内容:Oracle查询窗口功能
NAME, D1, D2
A, 01/01/2010, 02/03/2010
B, 03/03/2010, 20/03/2010
C, 10/03/2010, 20/09/2010
D, 10/12/2010, 31/12/2010
Insert into MYDATA
(NAME, D1, D2)
Values
('A', TO_DATE('01/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/02/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MYDATA
(NAME, D1, D2)
Values
('B', TO_DATE('03/03/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/20/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MYDATA
(NAME, D1, D2)
Values
('C', TO_DATE('03/10/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/20/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MYDATA
(NAME, D1, D2)
Values
('D', TO_DATE('12/10/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
我想与标记“S”具有重叠的时间间隔记录:在这种情况下,记录B和记录C重叠。
我写了下面的Oracle查询:
SELECT name, D1, D2, WMSYS.WM_CONCAT (OVERLAPPING)
FROM (SELECT T1.name, T1.D1, T1.D2, NULL OVERLAPPING
FROM MYDATA T1, MYDATA T2
WHERE NOT ((T1.D1, T1.D2) OVERLAPS (T2.D1, T2.D2))
AND T1.NAME <> T2.NAME
UNION
SELECT T1.name, T1.D1, T1.D2, 'S'
FROM MYDATA T1, MYDATA T2
WHERE ((T1.D1, T1.D2) OVERLAPS (T2.D1, T2.D2))
AND T1.NAME <> T2.NAME)
GROUP BY NAME, D1, D2;
结果是:
NAME, D1, D2, WMSYS.WM_CONCAT(OVERLAPPING)
A, 01/01/2010, 02/03/2010, NULL
B, 03/03/2010, 20/03/2010, S
C, 10/03/2010, 20/09/2010, S
D, 10/12/2010, 31/12/2010, NULL
正如你所看到的,MYDATA加入本身的表 'S' 重叠的记录标记。 我知道,当一个表连接时,相应的查询可以使用Oracle窗口函数重写。
最后,使用Oracle窗口函数重写查询如下:
SELECT NAME,
D1,
D2
FROM (SELECT T1.NAME,
T1.D1,
T1.D2
FROM MYDATA T1, MYDATA T2
WHERE ((T1.D1, T1.D2) OVERLAPS (T2.D1, T2.D2))
AND T1.NAME <> T2.NAME)
GROUP BY NAME, D1, D2;
你能我请帮忙重写查询,避免了自加入?
非常感谢您考虑我的要求。
答
您可以使用case
与lead
和lag
:
SELECT D.*,
CASE
WHEN LAG (D1) OVER (ORDER BY D1) IS NOT NULL
AND (LAG (D1) OVER (ORDER BY D1), LAG (D2) OVER (ORDER BY D1))
OVERLAPS (D1, D2)
OR LEAD (D1) OVER (ORDER BY D1) IS NOT NULL
AND (LEAD (D1) OVER (ORDER BY D1),
LEAD (D2) OVER (ORDER BY D1))
OVERLAPS (D1, D2)
THEN
'S'
ELSE
'N'
END
OVERLAP
FROM MYDATA D;
结果:
NAME D1 D2 OVERLAP
-------------------------------------------------- --------- --------- -------
A 01-JAN-10 02-MAR-10 N
B 03-MAR-10 20-MAR-10 S
C 10-MAR-10 20-SEP-10 S
D 10-DEC-10 31-DEC-10 N
非常感谢你对你的帮助!接受答案! – UltraCommit 2015-02-24 16:13:10
不客气的人。这是一个很好的做法,总是寻找分析解决方案,所以,很高兴帮助:) – Aramillo 2015-02-24 16:17:49