重设累计金额? [英] Reset a cumulative sum?

问题描述

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

I have the following dataset (table: 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:

The closest I've got so far is the following 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

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

It is close to what I want, but understandably, the sum is continued. Is it possible to reset this cumulative sum and start it again?

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

I've tried searching around on stackoverflow and google, but I'm not really sure what I should be searching for.

非常感谢您的帮助.

推荐答案

您需要标识oos = 1或0的连续天组.这可以通过使用LAG函数查找oos列何时发生变化，然后求和来完成.它.

You need to identify groups of consecutive days where oos = 1 or 0. This can be done by using LAG function to find when oos column changes and then summing over it.

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天.

Then, you can sum over this oos, partitioned by grp column to get consecutive oos days.

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 .

这篇关于重设累计金额?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！