累计计数 [英] Cumulative Sum of Count

问题描述

我想知道一个计数上的累计和是否可能.我想与之合作的一个例子是今年影响美国的风暴.我想要一个结果集，其中列出了2014年的月份以及到该月份为止影响美国的风暴总数.我希望得到类似以下内容的3列-月，风暴数和累积总和:

I would like to know whether a cumulative sum over a count is possible. An example I would like to work with is storms that affected the US this year. I would like a result-set that lists the months in 2014, along with the cumulative sum of storms that affected the US until that month. I am hoping to get something 3 columns - Month, NumberofStorms and CumulativeSum that look like this:

Month        NumberofStorms     CumulativeSum
-----------------------------------------------
Jan              2                   2
Feb              1                   3
Mar              1                   4
Apr              0                   4
May              0                   4
Jun              0                   4
Jul              0                   4
Aug              0                   4    
Sep              1                   5
Oct              3                   8
Nov              5                  13 
Dec              8                  21

我该如何实现?

推荐答案

您可以将窗口添加到 SUM():

SELECT "Month"
      ,"NumberofStorms"
      ,SUM("NumberofStorms") OVER(ORDER BY "Month" ROWS BETWEEN unbounded preceding and current row) AS CumulativeSum
FROM Table1

演示: SQL小提琴

注意:在演示中，月份是按字母顺序排序的，因为这只是一个示例场景，所以不必费心输入实际日期.

Note: The month is sorting alphabetically in the demo, didn't bother to input real dates since it's just a sample scenario.

更新:让我们假设基表只是 Month 和 StormName ，您需要cte/subquery首先获取一个计数，然后可以在高于运行总计:

Update: Let's pretend the base table is just Month And StormName, you'll need a cte/subquery to first get a count that you can then use in the above running total:

SELECT "Month"
      ,"NumberofStorms"
      ,SUM("NumberofStorms") OVER(ORDER BY "Month" ROWS BETWEEN unbounded preceding and current row) AS CumulativeSum
FROM (SELECT "Month",COUNT("Name") AS "NumberofStorms"
      FROM Weather
      GROUP BY "Month"
      ) AS Sub

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