Calculation average costing using with recursive sql (postgres 9.1)

Question

The closest thing I got from searching this site is this : Inventory Average Cost Calculation in SQL

But unfortunately it was oracle specific, using model clause.

So let's begin.

There are two tables:
-the one that holds inventory transactions, and
-the one that holds the latest inventory valuation

I am trying to make an inventory valuation report using average costing method based on a certain date. Doing it the normal way, calculating from the beginning until that specific date, will yield variable response time.
Imagine calculating on five years worth of data ( and thousands different inventory items ). It will take considerable amount of time ( and my company is not silicon-valley grade. meaning, 2 core cpu and 8 GB of RAM only) so I am calculating it backwardly: from the latest (current) backtrack to that specific date.

(Every month the accounting dept will check on data, so the calculation will only deal with 1 month's worth of data, forever. equal to consistent unchanging performance)

I have merged the table into one on the script below

create table test3 ( rn integer, amt numeric, qty integer, oqty integer);
insert into test3 (rn,amt,qty,oqty) values (0,2260038.16765793,8,0);
insert into test3 (rn,amt,qty,oqty) values (1,1647727.2727,3,0);
insert into test3 (rn,amt,qty,oqty) values (2,2489654.75326715,0,1);
insert into test3 (rn,amt,qty,oqty) values (3,2489654.75326715,0,1);
insert into test3 (rn,amt,qty,oqty) values (4,1875443.6364,1,0);
insert into test3 (rn,amt,qty,oqty) values (5,1647727.2727,3,0);
insert into test3 (rn,amt,qty,oqty) values (6,3012987.01302857,0,1);
insert into test3 (rn,amt,qty,oqty) values (7,3012987.01302857,0,1);

select * from test3; (already sorted desc so rn=1 is the newest transaction)

rn  amt        qty  oqty
0   2260038.168 8   0    --> this is the current average
1   1647727.273 3   0
2   2489654.753 0   1
3   2489654.753 0   1
4   1875443.636 1   0
5   1647727.273 3   0
6   3012987.013 0   1
7   3012987.013 0   1


with recursive 
runsum (id,amt,qty,oqty,sqty,avg) as 
    (select data.id, data.amt, data.qty, data.oqty, data.sqty, data.avg
     from (
        select rn as id,amt,qty, oqty, 
        sum(case when rn=0 then qty else 
             case when oqty=0 then qty*-1 
                else oqty end end) over (order by rn) as sqty, lag(amt) over (order by rn) as avg
          from test3 ) data
         ),
trans (id,amt,qty,oqty,sqty,prevavg,avg) as
    (select id,amt,qty,oqty, sqty,avg,avg
     from runsum 
     union
    select runsum.id,trans.amt,trans.qty, trans.oqty, trans.sqty, lag(trans.avg) over (order by 1), 
    case when runsum.sqty=0 then runsum.amt else 
    ((trans.prevavg*(runsum.sqty+trans.qty))-(runsum.amt*trans.qty)+(trans.prevavg*trans.oqty))/(runsum.sqty+trans.oqty)
    end
    from runsum join trans using (id))
select * 
from trans
where prevavg is null and avg is not null
order by id; 

The result is supposed to be like this

rn  amt        qty   oqty   sum avg
1   1647727.273 3   0   5   2627424.705
2   2489654.753 0   1   6   2627424.705
3   2489654.753 0   1   7   2627424.705
4   1875443.636 1   0   6   2752754.883
5   1647727.273 3   0   3   3857782.493
6   3012987.013 0   1   4   3857782.493
7   3012987.013 0   1   5   3857782.493

but instead I get this

id  amt        qty   oqty   sqty    avg
1   1647727.273 3   0   5   2627424.705
2   2489654.753 0   1   6   2627424.705
3   2489654.753 0   1   7   2627424.705
5   1647727.273 3   0   3   3607122.137 --> id=4 is missing thus                       
                                                            screwing the calculation 
                                                       and id=6 in turn dissappears tpp
7   3012987.013 0   1   5   3607122.137

I am flabbergasted. Where is the mistake?

Thank you for your kind help.

EDITED

Average Costing Method backtracking ( given current avg calculate last transaction avg, and so on until nth transactions )

Avg (n) = ((Avg(n-1) * (Cum Qty(n)+In Qty(n))) - (In Amount(n) * In Qty (n)) + (Avg(n-1) * Out Qty(n))/(Cum Qty(n)+Out Amount(n))

Cumulative qty for backtracking transactions would be minus for in, plus for out. So if current qty is 8, transaction in qty before is 3, then cumulative qty for that transaction is 5.

To calculate the average for one transaction before last, then we use current average to use in that transaction calculation.

CURRENT ANSWER BY @kordirko's help

with recursive 
runsum (id,amt,qty,oqty,sqty,avg) as 
    (select data.id, data.amt, data.qty, data.oqty, data.sqty, data.avg
     from (
        select rn as id,amt,qty, oqty, 
        sum(case when rn=0 then qty else 
             case when oqty=0 then qty*-1 
                else oqty end end) over (order by rn) as sqty, lag(amt) over (order by rn) as avg
          from test3 ) data
         ),
counter (maximum) as
         (select count(rn)
          from test3
         ),
trans (n, id,amt,qty,oqty,sqty,prevavg,avg) as
    (select 0 n, id,amt,qty,oqty, sqty,avg,avg
      from runsum 
     union 
    select trans.n+1, runsum.id,trans.amt,trans.qty, trans.oqty, trans.sqty, 
    lag(trans.avg) over (order by 1), 
    case when runsum.sqty=0 then runsum.amt else 
    ((trans.prevavg*(runsum.sqty+trans.qty))-(runsum.amt*trans.qty)+(trans.prevavg*trans.oqty))/(runsum.sqty+trans.oqty)
    end
    from runsum join trans using (id)
    where trans.n<(select maximum*2 from counter))
select * 
from trans
where prevavg is null and avg is not null
order by id; 

Answer 1

This is probably not the "best" answer to your question, but while struggling with this tricky problem, I hit - just by accident - some ugly workaround :).

Click on this SQL Fiddle demo

with recursive 
trans (n, id, amt, qty, oqty, sqty, prevavg, avg) as (
    select 0 n, id, amt, qty, oqty, sqty, avg, avg
    from runsum 
    union 
    select trans.n + 1, runsum.id, trans.amt, trans.qty, trans.oqty, trans.sqty, 
           lag(trans.avg) over (order by 1), 
           case when runsum.sqty=0 then runsum.amt 
                 else 
                 ((trans.prevavg *(runsum.sqty+trans.qty))-(runsum.amt*trans.qty)+(trans.prevavg*trans.oqty))/(runsum.sqty+trans.oqty)
           end
    from runsum 
    join trans using (id)
    where trans.n < 20
)
select * 
from trans
where prevavg is null and avg is not null
order by id; 

It seems that the source of the problem is UNION clause in the recursive query.
Read this link: http://www.postgresql.org/docs/8.4/static/queries-with.html
They wrote that for UNION the recursive query discards duplicate rows while evaluating recursive query.