Re: reset cumulative sum

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Tue, 28 Dec 2004 14:04:50 GMT
Message-ID: <6KdAd.103940$AL5.17458@twister.nyroc.rr.com>

"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),

  percent number,
  group_seq number
);
/

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;

   end;
end;
/

select * from table (mypkg.sel());

Anurag Received on Tue Dec 28 2004 - 08:04:50 CST