This discussion is archived
4 Replies Latest reply: Jun 23, 2009 12:25 AM by 85972 RSS

window function

85972 Explorer
Currently Being Moderated
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;
  • 1. Re: window function
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    I used sense of Tabibitosan B-)
    Query Help - Trying to get single date field into Date Range

    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
  • 2. Re: window function
    Frank Kulash Guru
    Currently Being Moderated
    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.
  • 3. Re: window function
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    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
  • 4. Re: window function
    85972 Explorer
    Currently Being Moderated
    Thank you. I worked :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points