Postgres Fifo查询计算利润率

Question

我正在研究我的库存系统查询，以根据PostgreSQL(9.3 (中的FIFO(第一名(计算利润。大多数答复都是针对MS SQL Server的目标，因此我不确定如何为PostgreSQL解决。我曾尝试使用Windows功能，但由于以前从未使用过，我不确定是否需要/可以使用光标(

( (

• 销售(负数(大约(20*4 30*1(= 110
• 基于FIFO出售的商品成本为(5*2 10*2 10*1(= 40
• 利润应为110-40 = 70

我到现在已经设法计算了运行总计。有人可以帮忙吗？

http://sqlfiddle.com/#！15/50b12/6

product_id product_name product_price purchase_date        product_quantity
1          Notebook     5             2017-05-05 00:00:00  2
1          Notebook     10            2017-05-06 00:00:00  4
1          Notebook     15            2017-05-07 00:00:00  6
1          Notebook     20            2017-05-08 00:00:00  -4 (this is sale)
1          Notebook     30            2017-05-09 00:00:00  -1 (this is sale)

所需的结果应显示销售和利润率。只要我能获得利润率，就可以解决我的问题。

Answer 1

select *,
    sum(price_sold - price_purchased) over(order by rn) as profit 
from
    (
        select 
            row_number() over(order by purchase_date, product_id) as rn,
            product_id, product_price as price_purchased
        from inv_test, generate_series(1, abs(product_quantity))
        where product_quantity > 0
    ) p
    full join
    (
        select
            row_number() over(order by purchase_date, product_id) as rn,
            product_id, product_price as price_sold
        from inv_test, generate_series(1, abs(product_quantity))
        where product_quantity < 0
    ) s using (rn, product_id)
;
 rn | product_id | price_purchased | price_sold | profit 
----+------------+-----------------+------------+--------
  1 |          1 |               5 |         20 |     15
  2 |          1 |               5 |         20 |     30
  3 |          1 |              10 |         20 |     40
  4 |          1 |              10 |         20 |     50
  5 |          1 |              10 |         30 |     70
  6 |          1 |              10 |            |     70
  7 |          1 |              15 |            |     70
  8 |          1 |              15 |            |     70
  9 |          1 |              15 |            |     70
 10 |          1 |              15 |            |     70
 11 |          1 |              15 |            |     70
 12 |          1 |              15 |            |     70