Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Peter.

Asked: August 01, 2005 - 3:16 pm UTC

Last updated: February 23, 2006 - 6:34 pm UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

Hi Tom,

I would like to get the starting consecutive date range and ending consecutive date range for each id.

Take for example the following table:

create table holiday (id int, dt date);

insert into holiday values(1, to_date(20041231, 'yyyymmdd'));
insert into holiday values(1, to_date(20050101, 'yyyymmdd'));
insert into holiday values(1, to_date(20050131, 'yyyymmdd'));
insert into holiday values(2, to_date(20050201, 'yyyymmdd'));
insert into holiday values(3, to_date(20050301, 'yyyymmdd'));
insert into holiday values(3, to_date(20050302, 'yyyymmdd'));
insert into holiday values(3, to_date(20050305, 'yyyymmdd'));
insert into holiday values(3, to_date(20050320, 'yyyymmdd'));
insert into holiday values(3, to_date(20050321, 'yyyymmdd'));
insert into holiday values(3, to_date(20050322, 'yyyymmdd'));


select * from holiday;

ID DT
---------- --------
1 20041231
1 20050101
1 20050131
2 20050201
3 20050301
3 20050302
3 20050305
3 20050320
3 20050321
3 20050322

10 rows selected.

The output I would like is:

ID FRSD FRED LRSD LRED
--------- -------- -------- -------- --------
1 20041231 20050101 20050131 20050131
2 20050201 20050201 20050201 20050201
3 20050301 20050302 20050320 20050322

Where,

FRSD = First Range Start Date
FRED = First Range End Date
LRSD = Last Range Start Date
LRED = Last Range End Date

Is this possible to do with one SQL using analytics? If not, I'm assuming it would be possible with PL/SQL.

Thanks,
-Peter

and Tom said...

ops$tkyte@ORA9IR2> select id,
2 min(case when mgrp1=1 then dt end) frsd,
3 max(case when mgrp1=1 then dt end) fred,
4 min(case when mgrp2=1 then dt end) lrsd,
5 max(case when mgrp2=1 then dt end) lred
6 from (
7 select id, dt,
8 max(grp1) over (partition by id order by dt) mgrp1,
9 max(grp2) over (partition by id order by dt desc) mgrp2
10 from (
11 select id, dt,
12 case when nvl(lag(dt) over (partition by id order by dt),dt) <> dt-1
13 then row_number() over (partition by id order by dt)
14 end grp1,
15 case when nvl(lag(dt) over (partition by id order by dt desc),dt) <> dt+1
16 then row_number() over (partition by id order by dt desc)
17 end grp2
18 from holiday
19 )
20 )
21 group by id
22 /

ID FRSD FRED LRSD LRED
---------- --------- --------- --------- ---------
1 31-DEC-04 01-JAN-05 31-JAN-05 31-JAN-05
2 01-FEB-05 01-FEB-05 01-FEB-05 01-FEB-05
3 01-MAR-05 02-MAR-05 20-MAR-05 22-MAR-05


is the final answer... What I did was first set up the "contigous groups":

ops$tkyte@ORA9IR2> select id, dt,
2 case when nvl(lag(dt) over (partition by id order by dt),dt) <> dt-1
3 then row_number() over (partition by id order by dt)
4 end grp1
5 from holiday
6 order by id, dt
7 /

ID DT GRP1
---------- --------- ----------
1 31-DEC-04 1
1 01-JAN-05
1 31-JAN-05 3
2 01-FEB-05 1
3 01-MAR-05 1
3 02-MAR-05
3 05-MAR-05 3
3 20-MAR-05 4
3 21-MAR-05
3 22-MAR-05

10 rows selected.


What we need to do is carry down the last non-null GRP1 value for each row and we have our first range, likewise:

ops$tkyte@ORA9IR2> select id, dt,
2 case when nvl(lag(dt) over (partition by id order by dt desc),dt) <> dt+1
3 then row_number() over (partition by id order by dt desc)
4 end grp2
5 from holiday
6 order by id, dt desc
7 /

ID DT GRP2
---------- --------- ----------
1 31-JAN-05 1
1 01-JAN-05 2
1 31-DEC-04
2 01-FEB-05 1
3 22-MAR-05 1
3 21-MAR-05
3 20-MAR-05
3 05-MAR-05 4
3 02-MAR-05 5
3 01-MAR-05

we have to "carry up" (desc sort) the last non-null. We can do that in 9i with max:


ops$tkyte@ORA9IR2> select id, dt,
2 max(grp1) over (partition by id order by dt) mgrp1,
3 max(grp2) over (partition by id order by dt desc) mgrp2
4 from (
5 select id, dt,
6 case when nvl(lag(dt) over (partition by id order by dt),dt) <> dt-1
7 then row_number() over (partition by id order by dt)
8 end grp1,
9 case when nvl(lag(dt) over (partition by id order by dt desc),dt) <> dt+1
10 then row_number() over (partition by id order by dt desc)
11 end grp2
12 from holiday
13 )
14 order by id, dt
15 /

ID DT MGRP1 MGRP2
---------- --------- ---------- ----------
1 31-DEC-04 1 2
1 01-JAN-05 1 2
1 31-JAN-05 3 1
2 01-FEB-05 1 1
3 01-MAR-05 1 5
3 02-MAR-05 1 5
3 05-MAR-05 3 4
3 20-MAR-05 4 1
3 21-MAR-05 4 1
3 22-MAR-05 4 1

10 rows selected.

Now, we have the first range with mgrp1 = 1 and the last range with mgrp2 = 1. We just min/max the dates for each of those groups by id and ... there you go :)



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Analytics does rock-n-roll

Peter Tran, August 02, 2005 - 10:00 am UTC


That's pretty amazing. Thank you very much!

-Peter

"Carry Down" Question

M. Jhala, February 23, 2006 - 11:22 am UTC

Firstly, "Thank you" for being such an excellent resource for all things Oracle.

I have read all threads related to "carry down" and am stumped with a challenge.

DB Version: 10.1 EE

The goal: Given (equities) orders, quotes and their timestamps, I want to "carry down" the "quote timestamp" for the orders that follow a quote.
If an order timestamp is the same as a quote timestamp then the order must belong to a previous quote time stamp.

Set up:

CREATE TABLE orcl_quotes_and_orders
( TYPE VARCHAR2(16),
stamp DATE )
/


INSERT INTO orcl_quotes_and_orders VALUES ( 'QUOTE' , TO_DATE('22-FEB-2006 09:29:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO orcl_quotes_and_orders VALUES ( 'ORDER' , TO_DATE('22-FEB-2006 09:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO orcl_quotes_and_orders VALUES ( 'ORDER' , TO_DATE('22-FEB-2006 09:31:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO orcl_quotes_and_orders VALUES ( 'ORDER' , TO_DATE('22-FEB-2006 09:31:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO orcl_quotes_and_orders VALUES ( 'ORDER' , TO_DATE('22-FEB-2006 09:32:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO orcl_quotes_and_orders VALUES ( 'QUOTE' , TO_DATE('22-FEB-2006 09:33:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO orcl_quotes_and_orders VALUES ( 'QUOTE' , TO_DATE('22-FEB-2006 09:34:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO orcl_quotes_and_orders VALUES ( 'ORDER' , TO_DATE('22-FEB-2006 09:34:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO orcl_quotes_and_orders VALUES ( 'ORDER' , TO_DATE('22-FEB-2006 09:35:00','DD-MON-YYYY HH24:MI:SS'));

commit;

The output should look like:

TYPE, STAMP, QUOTE_STAMP
QUOTE, 2/22/2006 9:29:00 AM, null
ORDER, 2/22/2006 9:30:00 AM, 2/22/2006 9:29:00 AM
ORDER, 2/22/2006 9:31:00 AM, 2/22/2006 9:29:00 AM
ORDER, 2/22/2006 9:31:00 AM, 2/22/2006 9:29:00 AM
ORDER, 2/22/2006 9:32:00 AM, 2/22/2006 9:29:00 AM
QUOTE, 2/22/2006 9:33:00 AM, null
QUOTE, 2/22/2006 9:34:00 AM, null
ORDER, 2/22/2006 9:34:00 AM, 2/22/2006 9:33:00 AM
ORDER, 2/22/2006 9:35:00 AM, 2/22/2006 9:34:00 AM

Note: the order on line 8 has a quote timestamp of 9.33 AM

Could this be solved using analytical functions? We currently have a function that gets the previous quote stamp but would like a more elegent solution. Tried, but to no avail.

thank you,
Manny







Tom Kyte
February 23, 2006 - 6:34 pm UTC 

it is hard with that "if equal" constraint.  Here was the first idea that popped into my head:

ops$tkyte@ORA9IR2> select type, stamp,
  2         case when stamp = qstamp
  3              then (select max(stamp) from orcl_quotes_and_orders 
                        where type = 'QUOTE' and stamp < x.stamp)
  4              else qstamp
  5          end new_qstamp
  6    from (
  7  select type, stamp,
  8         case when type = 'ORDER'
  9              then max( case when type = 'QUOTE' then stamp end ) 
                       over (order by stamp)
 10          end qstamp
 11    from orcl_quotes_and_orders
 12         ) X
 13   order by stamp
 14  /

TYPE             STAMP                  NEW_QSTAMP
---------------- ---------------------- ----------------------
QUOTE            02/22/2006 09:29:00 am
ORDER            02/22/2006 09:30:00 am 02/22/2006 09:29:00 am
ORDER            02/22/2006 09:31:00 am 02/22/2006 09:29:00 am
ORDER            02/22/2006 09:31:00 am 02/22/2006 09:29:00 am
ORDER            02/22/2006 09:32:00 am 02/22/2006 09:29:00 am
QUOTE            02/22/2006 09:33:00 am
QUOTE            02/22/2006 09:34:00 am
ORDER            02/22/2006 09:34:00 am 02/22/2006 09:33:00 am
ORDER            02/22/2006 09:35:00 am 02/22/2006 09:34:00 am

9 rows selected.

Another analytic solution for the "Carry Down" Question

Frank Zhou, February 27, 2006 - 5:18 pm UTC 

Tom,

      Here is an other SQL solution for the Carry Down" Question 
Basically I found out "All the Quote time"  and  "all the quote time 'minus' the identical Order time" first and then get the "running max" from them.

 
Thanks,

Frank


SQL> SELECT TYPE, stamp,
  2         CASE WHEN TYPE = 'ORDER'
  3              THEN CASE WHEN lag(TYPE)OVER(ORDER BY stamp) ='QUOTE'
  4                             AND lag(stamp)OVER(ORDER BY stamp)=stamp
  5                  THEN MAX(not_equal_quote_stamp)OVER(ORDER BY stamp)
  6                  ELSE MAX(CASE WHEN TYPE = 'QUOTE' THEN stamp END)OVER(ORDER BY stamp) END
  7              END new_qstamp
  8  FROM (
  9         SELECT TYPE, stamp,
 10          CASE TYPE
 11               WHEN 'QUOTE'
 12               THEN CASE WHEN lead(TYPE) OVER (ORDER BY stamp) = 'ORDER'
 13                                 AND lead(stamp)OVER(ORDER BY stamp)= stamp
 14                      THEN NULL ELSE stamp END
 15            END not_equal_quote_stamp
 16   FROM ORCL_QUOTES_AND_ORDERS ); 



TYPE    STAMP                  NEW_QSTAM                                            
------- --------------------   ---------------------   
QUOTE   2/22/2006 9:29:00 AM  
ORDER   2/22/2006 9:30:00 AM  2/22/2006 9:29:00 AM
ORDER   2/22/2006 9:31:00 AM  2/22/2006 9:29:00 AM
ORDER   2/22/2006 9:31:00 AM  2/22/2006 9:29:00 AM
ORDER   2/22/2006 9:32:00 AM  2/22/2006 9:29:00 AM
QUOTE   2/22/2006 9:33:00 AM 
QUOTE   2/22/2006 9:34:00 AM 
ORDER   2/22/2006 9:34:00 AM  2/22/2006 9:33:00 AM
ORDER   2/22/2006 9:35:00 AM  2/22/2006 9:34:00 AM

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.