SQL Server 中分组的滚动总和 [英] Rollover sum with grouping in SQL Server

问题描述

我需要在 SQL Server 中使用分组进行滚动求和

I need to do Rollover sum with grouping in SQL Server

我需要为以后的季度计算同年销售额的总和.

I need to sum of sales within same year for the later quaters.

数据如下图

年度 Qtr 销售额

2011         1        10

2011         2        5

2011         5        30

2012         4        30

2012         5        2

我需要像下面这样的输出

I would need the output like below

年份 |四分之一|salestillnowThisYear

2011 1 10

2011 2 15(即同年之前季度的 10 + 5)

2011 2 15 (which is 10 + 5 from previous quaters within same year)

2011 5 45(10 + 5 + 30 来自今年前几个季度)

2011 5 45 ( 10 + 5 + 30 from previous quaters of this year)

2012 4 30

2012 4 32(30 + 2 即今年上一季度的 30)

2012 4 32 (30 + 2 which is 30 from previous quater of this year)

推荐答案

实现Rolling Sum的方法有很多.这里有几个选项.

There are many ways to achieve Rolling Sum. Here are few options.

CREATE TABLE #test
  (years INT, id INT, sal INT )

INSERT #test
VALUES (2011,1,10),
       (2011,2,5),
       (2011,5,30),
       (2012,4,30),
       (2012,5,2)

方法一:使用Correlated Sub-Query

SELECT *,
       (SELECT Sum(sal)
        FROM   #test b
        WHERE  a.years = b.years
               AND a.id >= b.id)
FROM   #test a

方法二:使用Inner Join

SELECT a.years,
       a.id,
       Sum(b.sal) AS total
FROM   #test a
       INNER JOIN #test b
               ON a.id >= b.id
                  AND a.years = b.years
GROUP  BY a.years,
          a.id 

方法 3 : 使用 SUM() 和 OVER() 子句.点击此处了解更多关于 Over Clause 的信息

Method 3 : Using SUM() with the OVER() clause. Check here for more info on Over Clause

SQL FIDDLE DEMO

SELECT a.years,
       a.id,
       sal,
       Sum(sal)
         OVER(
           partition BY years
           ORDER BY id rows UNBOUNDED PRECEDING) AS runningtotal
FROM   #test 

注意: 方法 3 解决方案适用于 SQL SERVER 2012+

这篇关于SQL Server 中分组的滚动总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！