首页 文章

使用范围在SQL Server 2012中滚动平均值

提问于
浏览 次
1

我正在尝试计算按地区和月份分组的3个月滚动平均值，如

Region  Month                 Avg(var_a)    Avg(var_b)
Northland   Dec-Jan-Feb       7.1           5.9
Southland   Dec-Jan-Feb       7.2               6.1
Northland   Nov-Dec-Jan       7.4           6.1
Southland   Nov-Dec-Jan       7.5           6.2
Northland   Oct-Nov-Dec       7.5               6.2
Southland   Oct-Nov-Dec       7.5           6.1

请注意，月份是为了说明目的而扩展的，我真的希望输出只是说一个月 .

现在我可以通过按区域和月份创建CTE分组，然后加入它几次就可以做到这一点

With month_rollup_cte as
    (Select region,month,sum(var_a) a_sum, sum(var_b) b_sum, count(1) cnt
From vw_score_by_region
    Group by region,month)
Select c1.region, c1.month,sum(c1.a_sum + c2.a_sum + c3.a_sum) / sum(c1.cnt + c2.cnt + c3.cnt) a_avg, sum(c1.b_sum + c2.b_sum + c3.b_sum) / sum(c1.cnt + c2.cnt + c3.cnt) b_avg
From month_rollup_cte c1
Join month_rollup_cte c2 on c1.region = c2. Region and c1.month = dateadd(mm,1,c2.month)
Join month_rollup_cte c3 on c1.region = c3. Region and c1.month = dateadd(mm,2,c3.month)
Group by c1.region, c1.month;

但这很难看，想象一下如果你不得不做6个月的滚动平均值或12个月的滚动平均值......我正在尝试使用t-sql 2012分析函数，特别是RANGE选项 . 我之前使用的是ROWS，但从未使用范围 .

我试过的是

select region,avg(var_a) OVER (order by (year(entry_month) * 100 + month(entry_month)) range between 2 preceding and 1 following)    
from [dbo].[vw_score_by_region]
group by region

但我得到一个语法错误：

*消息8120，级别16，状态1，行2列'dbo.vw_score_by_region.month'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中 . *

显然我做的很傻，但我不确定是什么 .

sql tsql sql-server-2012 analytic-functions

1 回答

  • 1

    首先 RANGE 仅支持 UNBOUNDED 和 CURRENT ROW 帧分隔符，不能与 N PRECEDING 或 N FOLLOWING 一起使用 .
    从你的 Headers ，看起来你想要3个月滚动平均（滑动平均），那么你最好使用 ROWS
    使用 ROWS （这更有可能是你需要的）SQl Fiddle Demo

    select region,
       avg(var_a) OVER (partition by region 
                        order by (entry_month) 
                        rows between 2 preceding and current row) as ThreeMonthSlidingAvg  
from [dbo].[vw_score_by_region]

    注意：

    No need to calcuate year+month, if entry_month is date or datetime, it is sortable already, thanks for Steve's correction.

    使用RANGE：

    select region,
       avg(var_a) OVER (partition by region,(year(entry_month) * 12 + month(entry_month))/3 
                  order by (entry_month) 
                  range between unbounded preceding and current row) as ThreeMonthSlidingAvg 
from [dbo].[vw_score_by_region]

    注意：使用RANGE您必须控制分区宽度，因为您希望聚合3个月，并且范围不支持 N PRECEDING 和 N FOLLOWING ，它仅支持以下内容：

    |  UNBOUNDED PRECEDING  | Starts the window at first row of the partition 
|  UNBOUNDED FOLLOWING  | Ends the window at last row of the partition 
|  CURRENT ROW          | Starts or Ends the window at current row
    回复于

相关问题