Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: reset cumulative sum
"moose1729" <murali.lakshman_at_gmail.com> wrote in message news:1104207618.313457.136750_at_z14g2000cwz.googlegroups.com...
>
> Anurag Varma wrote:
> > <murali.lakshman_at_gmail.com> wrote in message
> news:1104080612.417167.244200_at_z14g2000cwz.googlegroups.com...
> > > I have a problem where I want to reset cumulative percentage on a
> table
> > > if it becomes greater than 1. I have solved this problem for now
> using
> > > collections but the solution is not very good in terms of
> performance.
> > > I would really prefer the solution to be more in SQL than pl/sql to
> > > help performance. Having said that, I think it is impossible to do
> > > this in one sql statement unless you use "MODEL BY" clause in 10g.
> I
> > > am currently using 9i.
> > >
> > > I know there can be clever solutions created using "Pipelined"
> > > functions and I want to see if some folks have ideas on this.
> > >
> > --snip--
> >
> > So did you try a solution using pipelined functions? What have you
> come up withso far?
> > I see this being a relatively simple pl/sql problem if you try doing
> it using pipelined functions...
> >
> > or are you looking for SQL solutions without using pipelined
> functions?
> >
> > Anurag
>
> I have come up with a solution in pl/sql (not using pipelined
> functions) using just collections. But we have to run 8 million rows
> against this process and doing the way I have done takes too much time.
>
> I want to try an approach using pipelined functions. If you can throw
> some ideas around how I can go about that would be a big help.
> Thanks for your time
>
Not sure about its performance...
but ...try for example, something like this:
create or replace type myGroupType
as object
( seq number,
order_id varchar2(10), order_dt date, agent_id varchar2(10),
create or replace type myGroupTabType as table of myGroupType; /
create or replace package mypkg
as
function sel return myGroupTabType PIPELINED;
end;
/
create or replace package body mypkg
as
function sel return myGroupTabType
PIPELINED
is
v_c number := 0; v_g pls_integer := 0; v_row myGroupTabType := myGroupTabType(); v_old_id txn.order_id%TYPE; v_old_dt txn.order_dt%TYPE; v_old_ag txn.agent_id%TYPE; begin for x in (select * from txn order by seq) loop v_c := v_c + x.percent; if (v_c > 1 or x.order_id != v_old_id or x.order_dt != v_old_dt or x.agent_id != v_old_ag) then if (x.order_id != v_old_id) then v_g := 0; end if; for i in 1 .. v_row.count loop v_row(i).group_seq := null; pipe row (v_row(i)); end loop; v_row := myGroupTabType(); v_row.extend; v_row(v_row.count) := myGroupType(x.seq, x.order_id, x.order_dt, x.agent_id, x.percent, null); v_c := x.percent; v_old_id := x.order_id; v_old_dt := x.order_dt; v_old_ag := x.agent_id; elsif (v_c = 1) then v_row.extend; v_g := v_g + 1; v_row(v_row.count) := myGroupType(x.seq, x.order_id, x.order_dt, x.agent_id, x.percent, v_g); for i in 1 .. v_row.count loop v_row(i).group_seq := v_g; pipe row (v_row(i)); end loop; v_row := myGroupTabType(); v_c := 0; else v_row.extend; v_row(v_row.count) := myGroupType(x.seq, x.order_id, x.order_dt, x.agent_id, x.percent, null); v_old_id := x.order_id; v_old_dt := x.order_dt; v_old_ag := x.agent_id; end if; end loop; for i in 1 .. v_row.count loop v_row(i).group_seq := null; pipe row (v_row(i)); end loop; return;
select * from table (mypkg.sel());
Anurag Received on Tue Dec 28 2004 - 08:04:50 CST