cumulative sum with weight ratio

[dba_admin]

Hi, I have column like this and I can do cumulative sum using analytical function. The question is that if I want to apply weight ratio to the sum. I mean , instead of adding the whole number from previous row, I want to apply 60% weight onto the previous number and 40% to the current row.
How do I do it?

(In Excel, this is very easy.)


select sal from emp;

SAL
----------
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
950
3000
1300

14 rows selected.

select sal, sum(sal) over(order by empno) from emp;

SAL SUM(SAL)OVER(ORDERBYEMPNO)
---------- --------------------------
800 800
1600 2400
1250 3650
2975 6625
1250 7875
2850 10725
2450 13175
3000 16175
5000 21175
1500 22675
1100 23775
950 24725
3000 27725
1300 29025

14 rows selected.

[dba_admin]

from Excel I can get this:
800 800
1600 1120
1250 1172
2975 1893.2
1250 1635.92
2850 2121.552
2450 2252.9312
3000 2551.75872
5000 3531.055232
1500 2718.633139
1100 2071.179884
950 1622.70793
3000 2173.624758
1300 1824.174855

[ales]

select 0.6 * nvl(lag(sal) over(order by empno), sal) + 0.4 * sal from emp;

[dba_admin]

Ales,

Thank you for your reply. However, I guess I did not describe it accurately. For every row of the weighted sum, it will be 0.4*current sal + 0.6 from the previous "weighted sum". This is why it's so hard (or maybe impossible if not using PL/SQL).

I can use PL/SQL to do this but I want to see if it can be done by SQL. Any thought?

declare
wsum number:=0;
begin
for rec in (select empno, sal from emp)
loop
wsum:=round(0.6*wsum+0.4*rec.sal);
dbms_output.put_line(wsum);
end loop;
end;