Oracle query using Window function

Question

Please suppose I have an Oracle table called MYDATA, with the following content:

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;

I would like to mark with 'S' the records which have overlapping intervals: in this case, record B and record C overlap.

I have written the following Oracle query:

  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;

The result is:

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

As you can see, the table MYDATA joins itself, marking with 'S' the record which overlap. I know that when a table joins itself, the corresponding query could be rewritten using Oracle windows functions.

At the end, the query to rewrite using Oracle Window Functions is the following:

  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;

Could you me please help to rewrite the query avoiding the self join?

Thank you very much for considering my request.

UltraCommit · Accepted Answer · 2015-02-24 16:59:45Z

3

You can use case with lead and 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;

Results:

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

edited Feb 24, 2015 at 16:59

UltraCommit

2,2927 gold badges43 silver badges61 bronze badges

answered Feb 24, 2015 at 16:03

Aramillo

3,2263 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aramillo Over a year ago

You're welcome man. It's a good practice always look for analytics solutions, so, glad to help :)

Collectives™ on Stack Overflow

Oracle query using Window function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related