oracle - 重设累计金额？

Question

我有以下数据集(表:stk):

S_Date       Qty     OOS (Out of Stock - 1 true, 0 false)
01/01/2013   0       1
02/01/2013   0       1
03/01/2013   0       1
04/01/2013   5       0
05/01/2013   0       1
06/01/2013   0       1

我想要的是:

S_Date       Qty     Cumulative_Days_OOS
01/01/2013   0       1
02/01/2013   0       2
03/01/2013   0       3
04/01/2013   5       0  -- No longer out of stock
05/01/2013   0       1
06/01/2013   0       2

到目前为止，我得到的最接近的是以下SQL:

SELECT
  S_DATE, QTY, 
  SUM(OOS) OVER (PARTITION BY OOS ORDER BY S_DATE) CUMLATIVE_DAYS_OOS
FROM
  STK
GROUP BY
  S_DATE, QTY, OOS
ORDER BY
  1

这给了我以下输出:

S_Date       Qty     Cumulative_Days_OOS
01/01/2013   0       1
02/01/2013   0       2
03/01/2013   0       3
04/01/2013   5       0
05/01/2013   0       4
06/01/2013   0       5

它接近我想要的，但可以理解，总和仍在继续。
是否可以重置此累计和重新启动？

我已经尝试过在stackoverflow和google上搜索，但是我不确定我应该搜索什么。

任何帮助，不胜感激。

Answer 1

您需要确定oos = 1或0的连续天数组。这可以通过使用LAG函数查找oos列何时更改然后对其求和来完成。

with x (s_date,qty,oos,chg) as (
  select s_date,qty,oos,
         case when oos = lag(oos,1) over (order by s_date)
                then 0
                else 1
         end
  from stk
  )
select s_date,qty,oos,
       sum(chg) over (order by s_date) grp
from x;

输出 :

|                         S_DATE | QTY | OOS | GRP |
|--------------------------------|-----|-----|-----|
| January, 01 2013 00:00:00+0000 |   0 |   1 |   1 |
| January, 02 2013 00:00:00+0000 |   0 |   1 |   1 |
| January, 03 2013 00:00:00+0000 |   0 |   1 |   1 |
| January, 04 2013 00:00:00+0000 |   5 |   0 |   2 |
| January, 05 2013 00:00:00+0000 |   0 |   1 |   3 |
| January, 06 2013 00:00:00+0000 |   0 |   1 |   3 |

然后，您可以对这个oos求和，并按grp列进行划分以获得连续的oos天。

with x (s_date,qty,oos,chg) as (
  select s_date,qty,oos,
         case when oos = lag(oos,1) over (order by s_date)
                then 0
                else 1
         end
  from stk
  ),
y (s_date,qty,oos,grp) as (
  select s_date,qty,oos,
         sum(chg) over (order by s_date)
  from x
  )
select s_date,qty,oos,
       sum(oos) over (partition by grp order by s_date) cum_days_oos
from y;

输出:

|                         S_DATE | QTY | OOS | CUM_DAYS_OOS |
|--------------------------------|-----|-----|--------------|
| January, 01 2013 00:00:00+0000 |   0 |   1 |            1 |
| January, 02 2013 00:00:00+0000 |   0 |   1 |            2 |
| January, 03 2013 00:00:00+0000 |   0 |   1 |            3 |
| January, 04 2013 00:00:00+0000 |   5 |   0 |            0 |
| January, 05 2013 00:00:00+0000 |   0 |   1 |            1 |
| January, 06 2013 00:00:00+0000 |   0 |   1 |            2 |

在 sqlfiddle进行演示。