我有以下数据集(表: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和谷歌上搜索,但我不确定我应该搜索什么.
任何帮助非常感谢.
您需要识别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演示.