sql - 使用递归 sql 计算平均成本(postgres 9.1)

Question

我搜索这个网站得到的最接近的结果是: Inventory Average Cost Calculation in SQL

但不幸的是，它是特定于 oracle 的，使用了模型子句。

让我们开始吧。

有两个表:
- 持有库存交易的，以及
-持有最新库存估价的

我正在尝试根据特定日期使用平均成本法制作库存估价报告。以正常方式进行计算，从开始计算到特定日期，将产生可变的响应时间。
想象一下计算五年的数据(以及数千种不同的库存项目)。这将花费大量时间(而且我的公司不是硅谷级别的。意思是，只有 2 核 cpu 和 8 GB 内存)所以我正在向后计算:从最新的(当前)回溯到那个特定日期。

(会计部门每个月都会核对数据，所以计算永远只处理1个月的数据。等于一致不变的性能)

我已经在下面的脚本中将表格合并为一个

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; 

结果应该是这样的

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

但是我得到了这个

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

我惊呆了。哪里错了？

感谢您的帮助。

已编辑

平均成本法回溯(给定当前平均计算最后一笔交易的平均，依此类推直到第n笔交易)

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

回溯交易的累计数量对于输入为负，对于输出为正。所以如果当前数量为8，之前的交易数量为3，则该交易的累计数量为5。

要计算最后一笔交易的平均值，然后我们使用当前平均值用于该交易计算。

@kordirko 帮助下的当前答案

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

这可能不是您问题的“最佳”答案，但在与这个棘手的问题作斗争时，我偶然遇到了一些丑陋的解决方法:)。

点击这个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; 

问题的根源似乎是递归查询中的 UNION 子句。
阅读此链接:http://www.postgresql.org/docs/8.4/static/queries-with.html
他们写道，对于 UNION，递归查询会在评估递归查询时丢弃重复行。