sql - 前 3 个月的滚动总和 SQL Server

Question

我的表结构如下:

Name           |Type
---------------|-----
fiscal year    | varchar
period         | varchar
country        | varchar
value          | int

我在 SQL Server 2012 中遇到了一个查询，该查询应该计算表中每个不同月份的前三个月的总和。月份之间可能存在间隔，每个月的数据都不存在，并且对于给定的年份、月份和国家/地区可能有几行。

EG:

Year    |Period |Country   |Value
--------|-------|----------|------
2016    |2      |Morovia   |100
2016    |9      |Morovia   |100
2016    |10     |Elbonia   |-20
2016    |10     |Elbonia   |2000
2016    |10     |Elbonia   |200
2016    |10     |Elbonia   |-100
2016    |10     |Elbonia   |1000
2016    |10     |Morovia   |200
2016    |10     |Elbonia   |-200
2016    |10     |Elbonia   |-200
2016    |10     |Elbonia   |100
2016    |10     |Elbonia   |60
2016    |10     |Elbonia   |40
2016    |11     |Morovia   |200
2016    |11     |Elbonia   |100

我正在尝试创建一个如下所示的结果集:

Year    |Period |Country   |3M Value
--------|-------|----------|--------
2016    |2      |Morovia   |100        - data only for this month
2016    |9      |Morovia   |100        - data only for this month
2016    |10     |Morovia   |300        - current month (200) + previous(100)
2016    |10     |Elbonia   |2880       - data only for this month
2016    |11     |Morovia   |500        - current + previous + 2 month ago
2016    |11     |Elbonia   |2980       - current month(100) + previous(2880)

Answer 1

另一种使用Outer JOIN

的方法

;WITH cte
     AS (SELECT year,
                period,
                country,
                Sum(value) AS sumvalue
         FROM   Yourtable 
         GROUP  BY year,
                   period,
                   country)
SELECT a.Year,
       a.Period,
       a.Country,
       a.sumvalue + Isnull(Sum(b.sumvalue), 0) as [3M Value]
FROM   cte a
       LEFT JOIN cte b
              ON a.Country = b.Country
                 AND Datefromparts(b.[Year], b.Period, 1) IN ( Dateadd(mm, -1, Datefromparts(a.[Year], a.Period, 1)), Dateadd(mm, -2, Datefromparts(a.[Year], a.Period, 1)) )
GROUP  BY a.Year,
          a.Period,
          a.Country,
          a.sumvalue 

• Live Demo