前 3 个月的滚动总和 SQL Server [英] Rolling sum previous 3 months SQL Server

问题描述

我有以下表结构:

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

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

I am struggling in SQL Server 2012 with a query that should compute the the sum of previous three months for every distinct month in the table. There might be gaps between months, data is not present for every month and for a given year, month and country could be several rows.

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

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

I am trying to create a result set that looks like:

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)

推荐答案

另一种使用 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 

• 现场演示

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