我有这张包含 Material 库存数据的表格:
Date(MM/DD) |received_qty| returned_qty | used_qty
01/01 | 5000 | 0 | 3500
01/02 | 0 | 0 | 1500
01/03 | 7500 | 0 | 1250
01/04 | 0 | 0 | 0
我需要再添加两列来计算“起始数量”和“每日库存”,假设如下:
“起始数量”第一天为零,之后为“每日 前一天的库存”。
“每日库存”为“起始数量”+收货数量-返回数量-使用数量
如您所见,每个值都相互依赖...
所以,添加这两列后的数据是这样的:
Date(MM/DD) |Start_qty |received_qty| returned_qty | used_qty | daily_stock
01/01 | 0 | 5000 | 0 | 3500 | 1500
01/02 | 1500 | 0 | 0 | 1500 | 0
01/03 | 0 | 7500 | 0 | 1250 | 6250
01/04 | 6250 | 0 | 0 | 0 | 6250
我确信这些列可以使用递归查询生成,并使用 Oracle 中存在的 start with
和 connect by
子句,但我对脚本感到困惑。 .
请您参考如下方法:
试试这个查询
Select t.*,
Coalesce(
Sum( "received_qty" - "returned_qty" - "used_qty" )
Over ( order by "Date" ) ,
0) as daily_stock,
Coalesce(
Sum( "received_qty" - "returned_qty" - "used_qty" )
Over ( order by "Date"
Rows between unbounded preceding
And 1 preceding ),
0) as start_quantity
from table1 t
演示 http://sqlfiddle.com/#!4/94417/13
| Date | received_qty | returned_qty | used_qty | DAILY_STOCK | START_QUANTITY |
|-----------------------|--------------|--------------|----------|-------------|----------------|
| 2001-01-01 00:00:00.0 | 5000 | 0 | 3500 | 1500 | 0 |
| 2001-01-02 00:00:00.0 | 0 | 0 | 1500 | 0 | 1500 |
| 2001-01-03 00:00:00.0 | 7500 | 0 | 1250 | 6250 | 0 |
| 2001-01-04 00:00:00.0 | 0 | 0 | 0 | 6250 | 6250 |