时间戳

首页 > 解决方案 > SQL Server：从每周总和到每周平均数

sql - SQL Server：从每周总和到每周平均数

问题描述

我有每日生产数据，我将其分组为每周数据，如下所示：

declare @rundate datetime = '2019-04-22'

select 
    convert(date, max (usedate)) as recorddate, 
    DATEDIFF(d, usedate, @rundate)/7 as weeks_ago, 
    sum(usecount) as usecountsum
from 
    usagetable
where 
    usedate <> @rundate and
    DATEDIFF(d, usedate, @rundate) >= 0 
group by 
    DATEDIFF(d, usedate, @rundate)/7
order by 
    weeks_ago asc

任何人都可以建议有效的方法来从上面获取另一个表格，其以下列标题应该是不言自明的：

recorddate  mostrecentweek      weeklyavglast4weeks weeklyavglast26weeks    weeklyavglast52weeks

谢谢

标签： sqlsql-server

解决方案

这是我最后的结果：

declare @rundate datetime = '2019-04-22'

;with grp as (
select 
    convert(date, max (usedate)) as recorddate, 
    DATEDIFF(d, usedate, @rundate)/7 as weeks_ago, 
    sum(usecount) as usecountsum
from 
    usagetable
where 
    usedate <> @rundate and
    DATEDIFF(d, usedate, @rundate) >= 0 
group by 
    DATEDIFF(d, usedate, @rundate)/7
order by 
    weeks_ago asc )
select 
        usedate, 
        usecount as LastWk,  
        avg(usecount) OVER (ORDER BY usedate desc  ROWS BETWEEN 0 PRECEDING AND 3 FOLLOWING) as FourWkMA,
        avg(usecount) OVER (ORDER BY usedate desc  ROWS BETWEEN 0 PRECEDING AND 25 FOLLOWING) as TwentySixWkMA,
        avg(usecount) OVER (ORDER BY usedate desc ROWS BETWEEN 0 PRECEDING AND 51 FOLLOWING) as FiftyTwoWkMA
    from 
        grp
    order by 
        usedate desc

推荐阅读