Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

window function

85972Jun 22 2009 — edited Jun 22 2009
I have a table with following data...

prod order_date order_nbr
ABC 6/12/2009 1
ABC 6/13/2009 1
ABC 6/14/2009 1
ABC 6/15/2009 0
ABC 6/16/2009 0
ABC 6/17/2009 1
ABC 6/18/2009 1
ABC 6/19/2009 1
ABC 6/20/2009 1
ABC 6/21/2009 1
ABC 6/22/2009 1
DEF 6/18/2009 1
DEF 6/19/2009 1
DEF 6/20/2009 0
DEF 6/21/2009 0
DEF 6/22/2009 0

output I want is
------
ABC 6/12/09 3 -- consecutive days with order_nbr > 0
ABC 6/17/09 6 -- consecutive days with order_nbr > 0 exclude previous window
DEF 6/18/09 2

First_Value is going all the way to min value and not considering just that window...any trick would be helpful..

select product_name ,
order_date,
order_nbr,
first_value(order_date) over (partition by product_name, order_nbr order by product_name,order_date,order_nbr)
from tt1 where order_nbr <>0 order by 1,2

sample script
=================

CREATE TABLE TT1
(
product_name VARCHAR2(10),
order_date DATE,
order_nbr VARCHAR2(10)
)
/

INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/12/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/13/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/14/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/17/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/18/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/19/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/20/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/21/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'DEF', TO_Date( '06/18/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'DEF', TO_Date( '06/19/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '1');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'DEF', TO_Date( '06/20/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '0');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'DEF', TO_Date( '06/21/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '0');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'DEF', TO_Date( '06/22/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '0');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/15/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '0');
INSERT INTO TT1 ( product_name, order_date, order_nbr ) VALUES (
'ABC', TO_Date( '06/16/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '0');
COMMIT;

Comments

Aketi Jyuuzou
I used sense of Tabibitosan B-)
870646

In this case,I imagine four traveler ;-)
create table t(prod,order_date,order_nbr) as
select 'ABC', to_date('6/12/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/13/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/14/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/15/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'ABC', to_date('6/16/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'ABC', to_date('6/17/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/18/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/19/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/20/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/21/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/22/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/18/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/19/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/20/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'DEF', to_date('6/21/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'DEF', to_date('6/22/2009','fmmm/dd/yyyy'), 0 from dual;

select prod,min(order_date),count(*)
from (select prod,order_date,order_nbr,
       Row_Number() over(partition by prod           order by order_date)
      -Row_Number() over(partition by prod,order_nbr order by order_date)
      as distance
        from T)
 where order_nbr = 1
group by prod,distance
order by min(order_date);

PRO  MIN(ORDE   COUNT(*)
---  --------  ---------
ABC  09-06-12          3
ABC  09-06-17          6
DEF  09-06-18          2
1 person found this helpful
Frank Kulash
Hi,

Thanks for posting the CREATE TABLE and INSERT statements: that really helps!

Aketi seems to be assuming that order_nbr is always 0 or 1, and that the order_dates in your table are consecutive days (which happens to be true in your sample data).
If that's not the case, you can modify Aketi's solution like this: 
WITH	got_grp	    AS
(
	SELECT	product_name
	,	order_date
	,	order_nbr
	,	ROW_NUMBER () OVER ( PARTITION BY  product_name
			      	     ORDER BY  	   order_date
				   ) + ( TRUNC (SYSDATE) 
				       - TRUNC (order_date)
				       )      		AS d_grp
	FROM	tt1
	WHERE	order_nbr	> 0
)
SELECT	  product_name
,	  MIN (order_date)	AS start_date
,	  COUNT (*)		AS consecutive_days
FROM	  got_grp
GROUP BY  product_name
,	  d_grp
ORDER BY  product_name
,     	  start_date
;
As written, this assumes that the combination (product_name, TRUNC (start_date)) is unique. If that's no the case, it can be adapted.
1 person found this helpful
Aketi Jyuuzou
Oh I did not realize that I can use "where clause" in first select :-)
Likewise my previous solution,I imagine four traveler 
select prod,min(order_date),count(*)
from (select prod,order_date,order_nbr,
      order_date-Row_Number() over(partition by prod order by order_date)
      as distance
        from T
       where order_nbr > 0)
group by prod,distance
order by min(order_date);

PRO  MIN(ORDE   COUNT(*)
---  --------  ---------
ABC  09-06-12          3
ABC  09-06-17          6
DEF  09-06-18          2
1 person found this helpful
85972
Thank you. I worked :)
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 20 2009
Added on Jun 22 2009
4 comments
4,835 views