Re: multiply column values within a partition

Ora schrieb:
> Hello,
>
> Is there a PRODUCT() OVER() equivalent to SUM() OVER() analytical
> function which can help me with the query output below?
>
> In Oracle 9.2.x, given the following source data, how could I get the
> output shown, using SQL (no plsql).
>
> Source Data:
> c1 c2 c3
> ---------------------
> abc jan 10
> abc feb 20
> abc mar 30
> def jan -40
> def feb 0
> def mar -50
>
> Desired Output (with new c4 calculated column):
> c1 c2 c3 c4
> -----------------------------------------------
> abc jan 10 200 -- 10*20
> abc feb 20 600 -- 20*30
> abc mar 30 -1200 -- 30*-40
> def jan -40 2000 -- -40*-50
> def feb -50 0 -- -50*0
> def mar 0 0
>
> Thanks.
>

Hi, as Rene correct pointed, according to your desired output you would rather need c3 * lag(c3,-1) or c3 * lead(c3,1) , in general you can easily get product instead of sum using exp(sum(ln)) with special handling of values <= 0

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

SQL> SELECT rn,sum(rn)over(ORDER BY rn) summa,exp(SUM(ln(rn)) over(ORDER BY rn)) product
  2 FROM (SELECT ROWNUM rn FROM tabs WHERE ROWNUM < 6)   3 /

        RN SUMMA PRODUCT
---------- ---------- ----------

         1          1          1
         2          3          2
         3          6          6
         4         10         24
         5         15        120

SQL> Best regards

Maxim Received on Tue May 03 2005 - 15:45:41 CDT