3 Replies Latest reply: Sep 28, 2007 7:06 AM by Aketi Jyuuzou RSS

    Is it possible to reset agg function row_number()

    449047
      Hi

      I am having a little trouble with aggregate functions.

      In this scenario an item always has a slot for each period. Periods are sequential.

      What I want is that if an item changes slot, the number of periods in slot resets to one even if it has been in that slot before and the start period in slot be the last time it moved into that slot rather than demonstrated below.
      CREATE TABLE tmp_table (item VARCHAR2(10),period NUMBER,slot VARCHAR2(10));
      INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',1,'A');
      INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',2,'A');
      INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',3,'A');
      INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',4,'B');
      INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',5,'B');
      INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',6,'C');
      INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',7,'A');
      INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',8,'A');
      INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',9,'C');
      INSERT INTO tmp_table (item,period,slot) VALUES ('def456',1,'D');
      INSERT INTO tmp_table (item,period,slot) VALUES ('def456',2,'D');
      INSERT INTO tmp_table (item,period,slot) VALUES ('def456',3,'E');
      INSERT INTO tmp_table (item,period,slot) VALUES ('def456',4,'E');
      INSERT INTO tmp_table (item,period,slot) VALUES ('def456',5,'D');
      INSERT INTO tmp_table (item,period,slot) VALUES ('def456',6,'D');
      INSERT INTO tmp_table (item,period,slot) VALUES ('def456',7,'E');
      INSERT INTO tmp_table (item,period,slot) VALUES ('def456',8,'E');
      INSERT INTO tmp_table (item,period,slot) VALUES ('def456',9,'D');


      SELECT
        item
      , period
      , slot
      , FIRST_VALUE(period)
        OVER (PARTITION BY item, slot
              ORDER BY period) start_period_at_slot  
      , ROW_NUMBER()
        OVER (PARTITION BY item, slot
              ORDER BY period) periods_in_slot
      FROM tmp_table
      ORDER BY item,period
      gives me
       
      ITEM           PERIOD SLOT       START_PERIOD_AT_SLOT PERIODS_IN_SLOT
      ---------- ---------- ---------- -------------------- ---------------
      abc123              1 A                             1               1
      abc123              2 A                             1               2
      abc123              3 A                             1               3
      abc123              4 B                             4               1
      abc123              5 B                             4               2
      abc123              6 C                             6               1
      abc123              7 A                             1               4
      abc123              8 A                             1               5
      abc123              9 C                             6               2
      def456              1 D                             1               1
      def456              2 D                             1               2
      def456              3 E                             3               1
      def456              4 E                             3               2
      def456              5 D                             1               3
      def456              6 D                             1               4
      def456              7 E                             3               3
      def456              8 E                             3               4
      def456              9 D                             1               5

      18 rows selected.
      Notice when the item abc123 moves back into slot A it picks up where it left off with periods in slot of 4 but I would like to find a way to manipulate this to reset to zero and the start period in the slot be 7 rather than 1.

      and subsequent rows ordered on period to increment appropriately.

      Any help much appreciated.
      Thanks
      Ian
        • 1. Re: Is it possible to reset agg function row_number()
          Rob van Wijk
          You mean this?
          SQL> select item
            2       , period
            3       , slot
            4       , row_number() over (partition by item, sumx order by period) rn_period_at_slot
            5    from ( select t.*
            6                , sum(x) over (partition by item order by period) sumx
            7             from ( select t.*
            8                         , case lag(slot) over (partition by item order by period)
            9                           when slot then 0
          10                           else 1
          11                           end x
          12                      from tmp_table t
          13                  ) t
          14         )
          15   order by item
          16       , period
          17  /

          ITEM           PERIOD SLOT       RN_PERIOD_AT_SLOT
          ---------- ---------- ---------- -----------------
          abc123              1 A                          1
          abc123              2 A                          2
          abc123              3 A                          3
          abc123              4 B                          1
          abc123              5 B                          2
          abc123              6 C                          1
          abc123              7 A                          1
          abc123              8 A                          2
          abc123              9 C                          1
          def456              1 D                          1
          def456              2 D                          2
          def456              3 E                          1
          def456              4 E                          2
          def456              5 D                          1
          def456              6 D                          2
          def456              7 E                          1
          def456              8 E                          2
          def456              9 D                          1

          18 rijen zijn geselecteerd.
          Regards,
          Rob.
          • 2. Re: Is it possible to reset agg function row_number()
            449047
            Hi

            I do.

            Thanks for the very quick response.

            Cheers
            Ian
            • 3. Re: Is it possible to reset agg function row_number()
              Aketi Jyuuzou
              If PERIODs are sequence,
              we can use this alternative solution.
              SQL> select ITEM,PERIOD,SLOT,
                2  Row_Number() over(partition by ITEM,SLOT,SubRank order by PERIOD) as Rank
                3  from (select ITEM,PERIOD,SLOT,
                4        PERIOD-Row_Number() over(partition by SLOT order by PERIOD) as SubRank
                5        from tmp_table)
                6  order by ITEM,PERIOD;
              ITEM     PERIOD  SLOT  RANK
              -------  ------  ----  ----
              abc123        1  A        1
              abc123        2  A        2
              abc123        3  A        3
              abc123        4  B        1
              abc123        5  B        2
              abc123        6  C        1
              abc123        7  A        1
              abc123        8  A        2
              abc123        9  C        1
              def456        1  D        1
              def456        2  D        2
              def456        3  E        1
              def456        4  E        2
              def456        5  D        1
              def456        6  D        2
              def456        7  E        1
              def456        8  E        2
              def456        9  D        1
              If PERIODs are not sequence,
              I think that we have to use Rob's solution
                                or to create sequence with using Row_Number.
              similar threads
              SQL - Hierarchical View
              Version Key creation
              Group by preserving the order