我有这张包含 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 withconnect 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 | 


评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!