SQL Server-遇到0时重置的累积总和 [英] SQL Server - Cumulative Sum that resets when 0 is encountered

问题描述

我想对列进行累加，但是每当遇到0时就重置聚合值

I would like to do a cumulative sum on a column, but reset the aggregated value whenever a 0 is encountered

这是我尝试做的一个例子:

Here is an example of what i try to do :

此数据集:

pk    price
1     10
2     15
3     0
4     10
5     5

给出以下内容:

pk    price
1     10
2     25
3     0
4     10 
5     15

推荐答案

在SQL Server 2008中，由于无法使用解析函数，因此受到了严重限制.以下方法效率不高，但可以解决您的问题:

In SQL Server 2008, you are severely limited because you cannot use analytic functions. The following is not efficient, but it will solve your problem:

with tg as (
      select t.*, g.grp
      from t cross apply
           (select count(*) as grp
            from t t2
            where t2.pk <= t.pk and t2.pk = 0
           ) g
     )
select tg.*, p.running_price
from tg cross apply
     (select sum(tg2.price) as running_price
      from tg tg2
      where tg2.grp = tg.grp and tg2.pk <= tg.pk
     ) p;

A，在SQL Server 2012之前，最有效的解决方案可能涉及游标.在SQL Server 2012+中，您只需执行以下操作:

Alas, prior to SQL Server 2012, the most efficient solution might involve cursors. In SQL Server 2012+, you simply do:

select t.*,
       sum(price) over (partition by grp order by pk) as running_price
from (select t.*,
             sum(case when price = 0 then 1 else 0 end) over (order by pk) as grp
      from t
     ) t;

这篇关于SQL Server-遇到0时重置的累积总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！