oracle - 递归 Oracle 查询计算两个相互依赖的值

Question

我有这张包含 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 子句，但我对脚本感到困惑。 .

Answer 1

试试这个查询

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 |