Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,008 Comments

Discussions

window function

85972
85972 Member Posts: 137
edited June 2009 in SQL & PL/SQL
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;

Answers

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited June 2009
    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
    Aketi Jyuuzou
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    edited June 2009
    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.
    Frank Kulash
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited June 2009
    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
    Aketi Jyuuzou
  • 85972
    85972 Member Posts: 137
    Thank you. I worked :)
This discussion has been closed.