Forum Stats

  • 3,757,560 Users
  • 2,251,244 Discussions
  • 7,869,866 Comments

Discussions

Is it possible to reset agg function row_number()

449047
449047 Member Posts: 65
edited Sep 28, 2007 8:06AM in SQL & PL/SQL
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

Comments

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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.
  • 449047
    449047 Member Posts: 65
    Hi

    I do.

    Thanks for the very quick response.

    Cheers
    Ian
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Sep 28, 2007 8:06AM
    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
    467872
    569024
    450745
This discussion has been closed.