Oracle SQL在零时重置运行和

Question

我想这是可能的，但是大量的研究和无数的时间都没有得到回报。

我在从仓库里卖产品。我知道我什么时候会收到货物，我有每日销售预测(在数量上)。我需要计算期末存货。这基本上是库存预测的总结性。

问题是，当我的产品用完后，第二天我就不会有负库存了，就像运行中的金额所显示的那样。在我收到另一批货之前，库存将为零。在预测中，它可以多次降到零(比下面的例子要多得多)。

数据集的可视化(所需的黄色列)

破解案件的SQL Fiddle

下面是我的实际查询：

SELECT FORECAST_DATE, DAYS_OUT, INBOUND_INVENTORY, FORECAST,
       ENDING_INVENTORY AS DESIRED_RESULT,
       SUM(INBOUND_INVENTORY) OVER (PARTITION BY NULL ORDER BY DAYS_OUT) -
           SUM(FORECAST) OVER (PARTITION BY NULL ORDER BY DAYS_OUT) AS ENDING_INVENTORY
FROM MRP

列

日期:预测日期(由今日开始) 天数:从今天到预测日期之间的天数。 入站库存:产品进货(为今天，产品在此) 预测:我的预计销售量 期末库存:入站库存-预测+如果昨天的期末库存<=为0，那么昨天的期末库存为0。

Answer 1

下面是使用MODEL子句的解决方案(在Oracle 10中引入)。

我没有包括forecast_date列-- days_out就足够了。

with
     inputs ( days_out, inbound_inventory, forecast ) as (
       select  0,  24,   0 from dual union all
       select  1,   0, 124 from dual union all
       select  2,   0, 154 from dual union all
       select  3,   0, 316 from dual union all
       select  4, 780, 119 from dual union all
       select  5, 780, 148 from dual union all
       select  6, 780, 123 from dual union all
       select  7,   0, 168 from dual union all
       select  8,   0, 323 from dual union all
       select  9,   0, 184 from dual union all
       select 10,   0, 331 from dual union all
       select 11,   0, 149 from dual union all
       select 12,   0, 431 from dual union all
       select 13,   0, 153 from dual union all
       select 14,   0, 183 from dual union all
       select 15,   0, 169 from dual union all
       select 16,   0, 169 from dual union all
       select 17, 780, 331 from dual
     )
select days_out, inbound_inventory, forecast, ending_inventory
from   inputs
model
  dimension by ( days_out )
  measures     ( inbound_inventory, forecast, 0 ending_inventory )
  rules update 
  iterate(1000000) until (previous(ending_inventory[iteration_number + 1]) is null)
  (
    ending_inventory[iteration_number] = 
           greatest ( 0, inbound_inventory[cv()] - forecast[cv()] 
                           + nvl(ending_inventory[cv() - 1], 0 )
                    )
  )
;

输出

  DAYS_OUT INBOUND_INVENTORY   FORECAST ENDING_INVENTORY
---------- ----------------- ---------- ----------------
         0                24          0               24
         1                 0        124                0
         2                 0        154                0
         3                 0        316                0
         4               780        119              661
         5               780        148             1293
         6               780        123             1950
         7                 0        168             1782
         8                 0        323             1459
         9                 0        184             1275
        10                 0        331              944
        11                 0        149              795
        12                 0        431              364
        13                 0        153              211
        14                 0        183               28
        15                 0        169                0
        16                 0        169                0
        17               780        331              449

18 rows selected.

Answer 2

这是对的吗？这是一种叫做递归公共表表达式的东西。

WITH
cte_mrp as
(
    Select row_number() over (partition by null order by forecast_date) as line, mrp.*
    From mrp
),
RCTE (line, forecast_date, days_out, inbound_inventory, forecast, /*iteration, anchor,*/ ending_inventory) as
(
    Select line, forecast_date, days_out, inbound_inventory, forecast, /*0 iteration, 'anchor' anchor,*/
        CASE WHEN inbound_inventory-forecast < 0 THEN 0 ELSE inbound_inventory-forecast END ending_inventory
    From cte_mrp
    Where line = 1

    union all

    Select m.line, m.forecast_date, m.days_out, m.inbound_inventory, m.forecast, /*r.iteration + 1, 'rcte' anchor,*/
        CASE WHEN r.ending_inventory+m.inbound_inventory - m.forecast < 0 THEN 0 ELSE r.ending_inventory+m.inbound_inventory - m.forecast END ending_inventory
    From cte_mrp m
    Inner join rcte r on (r.line = (m.line-1))
)

Select * From RCTE;