Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Is it possible to reset agg function row_number()

449047Sep 27 2007 — edited Sep 28 2007

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

94799
See replies to similar problem...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4274185613870#69744480450437
jeneesh

One method

sql>
create or replace function fn1(p1 varchar2) return number
as
 a number;
begin
 execute immediate 'select '||p1||' from dual' into a;
 return a;
end;
Function created.
sql>
select id,p_id,health,fn1(str)
from(
select id, p_id, health, level lvl,'0'||sys_connect_by_path(health,'+') str
from t
start with p_id is null
connect by prior id=p_id);
ID P_ID HEALTH FN1(STR)  
1     10  10  
2  1  20  30  
4  2  15  45  
3  1  30  40

jeneesh
541081
thank you but i can not use pl/sql in here

C.
jeneesh 
sql>
select id,p_id,health,lvl,
      sum(to_number(substr(str,instr(str,'+',1,n)+1,(instr(str,'+',1,n+1))-(instr(str,'+',1,n)+1)))) sm
from(
    select id, p_id, health, level lvl,sys_connect_by_path(health,'+')||'+' str
    from t
    start with p_id is null
    connect by prior id=p_id) t1,(select rownum n
                                  from t) t2
where t2.n <=  t1.lvl
group by id,p_id,health,lvl;
ID P_ID HEALTH LVL SM  
1     10  1  10  
2  1  20  2  30  
3  1  30  2  40  
4  2  15  3  45

Message was edited by: 
        jeneesh
Formatted..
ushitaki 
with abc as
(select 1 Id, cast(null as number) p_id, 10 health from dual
union all
select 2,1,20 from dual
union all
select 3,1,30 from dual
union all
select 4,2,15 from dual
)
/* Main */
select id, p_id, health, level
,(select sum(health)
    from abc t2 start with t2.id = t1.id
    connect by prior p_id = id) calc_health
from abc t1
start with p_id is null
connect by prior id=p_id
;

        ID       P_ID     HEALTH      LEVEL CALC_HEALTH
---------- ---------- ---------- ---------- -----------
         1                    10          1          10
         2          1         20          2          30
         4          2         15          3          45
         3          1         30          2          40

This is inefficient, but is suitable for you?

Laurent Schneider
See replies to similar problem...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P1
1_QUESTION_ID:4274185613870#69744480450437
;-) nice try

what about this :
with abc as
(select 1 Id, cast(null as number) p_id, 10 health from dual
union all
select 2,1,20 from dual
union all
select 3,1,30 from dual
union all
select 4,2,15 from dual
)
/* Main */
select id,p_id,health,l "LEVEL", calc_health from (
select r,id,p_id,health,l, sum(ch) over (partition by id) calc_health,cp from (
select rownum r,id, p_id, health, level l, connect_by_root health ch, connect_by_root p_id cp
from abc t1
connect by prior id=p_id))
where cp is null
order by r
/

        ID       P_ID     HEALTH      LEVEL CALC_HEALTH
---------- ---------- ---------- ---------- -----------
         1                    10          1          10
         2          1         20          2          30
         4          2         15          3          45
         3          1         30          2          40
Laurent Schneider
this will not work in 9i
ushitaki

In query that I wrote, a:b is 1:n is assumed to be a precondition.
In such assumption, it is possible to be written a little more simple,
and not necessary to be applied 'connect by' twice.

thus,

/* Main */
select id,p_id,health
    ,max(lvl) as "LEVEL"
    ,sum(calc_health) calc_health
from (
    select
        connect_by_root(id) id
        ,connect_by_root(p_id) p_id
        ,connect_by_root(health) health
        ,level lvl
        ,health calc_health
    from abc
    connect by prior p_id = id
)
group by id,p_id,health
;

_(This works over Oracle10g, 'cause of connect_by_root function)
Message was edited by:
ushitaki
_

Laurent Schneider
sounds good...
94799
Well yes but you are now traversing and summing the entire hierarchy from the child nodes upwards.

I don't think this is going to be very useful or efficient if you want to start at a given parent node and traverse down the hierarchy.
Avinash Tripathi

Hi,

This will work is 9i also.

SQL> SELECT * FROM t;

        ID       P_ID     HEALTH
---------- ---------- ----------
         1                    10
         2          1         20
         3          1         30
         4          2         15

SQL> 

SQL> ed
Wrote file afiedt.buf

  1  SELECT id, p_id, health, LEVEL LVL,
  2      ( SELECT  SUM(health)
  3        FROM  t
  4        START WITH id =abc.id
  5        CONNECT BY PRIOR  p_id = id
  6        ) SUM_HEALTH
  7  FROM t ABC
  8  START WITH p_id is NULL
  9* CONNECT BY PRIOR  id = p_id
 10  /

        ID       P_ID     HEALTH        LVL SUM_HEALTH
---------- ---------- ---------- ---------- ----------
         1                    10          1         10
         2          1         20          2         30
         4          2         15          3         45
         3          1         30          2         40

SQL>

Regards

541081
Sorry, it is a little more complicated.

Let say I got
Id ----- p_id ----- health ----- multi
1 ------ null ------ 10 --------- 2
2 --------1 --------- 0 ---------- 5
3 -------- 1 --------- 0 ---------3
4 -------- 2 ----------0 ---------4

and i need to get this
Id----p_id----health----level----multi ------ calc
-------------------------------------------------------
1 ----null----10---------1--------2------------2*10
2 ----1-------0 ----------2---------5-----------20*5
3 ----1-------0 ----------2---------3-----------20*3
4 ----2-------0 ----------3---------4-----------100*4

the calcolation i need to do is much more complecated, but the base of the calcolation is the calc field of the father.

Thank you
C.
94799
Did you read the AskTom link yet?
Laurent Schneider
out of the solutions given above, you could try 
with abc as
(select 1 Id, cast(null as number) p_id, 10 health, 2 multi from dual
union all
select 2,1,0,5 from dual
union all
select 3,1,0,3 from dual
union all
select 4,2,0,4 from dual
)
/* Main */
select id,p_id,health,max(lvl) "LEVEL",multi,max(xhealth)*exp(sum(ln(xmulti))) calc
from (
    select
        connect_by_root id id
        ,connect_by_root p_id p_id
        ,connect_by_root health health
        ,connect_by_root multi multi
        ,level lvl
        ,health xhealth
        ,multi xmulti
    from abc
    connect by prior p_id = id
)
group by id,p_id,multi,health
order by id
/
        ID       P_ID     HEALTH      LEVEL      MULTI       CALC
---------- ---------- ---------- ---------- ---------- ----------
         1                    10          1          2         20
         2          1          0          2          5        100
         3          1          0          2          3         60
         4          2          0          3          4        400
Not sure what does 0 Health mean ...
John Spencer
Laurent:

"Not sure what does 0 Health mean" In most gaming contexts, which this seems to be, it usually means dead :-)

john
Laurent Schneider
yes, I know the feeling 8-)
1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 25 2007
Added on Sep 27 2007
3 comments
5,278 views