This discussion is archived
3 Replies Latest reply: Sep 28, 2007 5:06 AM by Aketi Jyuuzou RSS

Is it possible to reset agg function row_number()

449047 Newbie
Currently Being Moderated
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()
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi

    I do.

    Thanks for the very quick response.

    Cheers
    Ian
  • 3. Re: Is it possible to reset agg function row_number()
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    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