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!

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,853 views