sql 计算连续零

Question

我有下表

date         N
---------------
2016-06-22  0.0 
2016-06-23  0.0 
2016-06-24  0.0 
2016-06-25  0.0 
2016-06-26  14.0    
2016-06-27  0.0 
2016-06-28  0.0 
2016-06-29  3.0

我正在尝试弄清楚如何计算 N 列中连续零的累积和，并在出现非零值时重置。这就是我正在寻找的输出类型。

date         N    cum0 
----------------------
2016-06-22  0.0     1
2016-06-23  0.0     2
2016-06-24  0.0     3
2016-06-25  0.0     4
2016-06-26  14.0    0
2016-06-27  0.0     1 
2016-06-28  0.0     2
2016-06-29  3.0     0

Answer 1

您可以根据其前面的非零数字的数量来定义每个组。然后使用row_number():

select date, n,
       (row_number() over (partition by grp order by date) -
        (grp <> 0)::int
       ) as cum0
from (select t.*,
             count(*) filter (where n <> 0.0) over (order by date) as grp
      from t
     ) t

Here是一个 SQL fiddle 。