4 Replies Latest reply: Jun 22, 2009 7:25 PM by 85972 RSS

    window function

    85972
      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
          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
            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
              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
                Thank you. I worked :)