时间戳

首页 > 解决方案 > 3 个月滚动平均

sql-server - 3 个月滚动平均

问题描述

问题：需要查询帮助以显示为 3 个月的滚动平均值

这是我目前拥有的

SELECT
    Date,
    month(date) AS[Month],
    year(date) AS[Year],
    AVG(ALL Amount) OVER (PARTITION BY Date ORDER BY Date ASC) AS Average
FROM
    FactFinance
SELECT
    YEAR(Date) AS Year,
    MONTH(Date) AS Month,
    AVG(Amount) AS AvgAmt
FROM
    FactFinance
GROUP BY
    YEAR(Date),
    MONTH(Date)
ORDER BY
    Year,
    Month;
GO;

SQL 语句 2

SQL 语句 1

标签： sql-serverrolling-computationrolling-average

解决方案

我会做两件事来测试这个 1) 选择一年以使数据更易于消化 2) 修改第二个查询以包含一个总和。应该修改第一个查询以使用 cte 或子查询（我选择使用子查询）预先计算每月数字，并且您需要使用前面的 .. 子句来告诉 sql server 您有多少个月想

select  month,year,
        sum(amt) over (order by month,year  ROWS BETWEEN 2 PRECEDING AND current row) sumamount,
        avg(amt) over (order by month,year  ROWS BETWEEN 2 PRECEDING AND current row) '3monthra'
from
(
SELECT  month(date) as [Month], 
        year(date) as [Year], 
        sum(Amount) amt,
        count(*) as cnt
FROM FactFinance 
GROUP BY YEAR([Date]), MONTH([Date]) 
) s
where year = 2005
order by year, month


SELECT 
        YEAR(Date) AS Year, 
        MONTH(Date) AS Month, 
        sum(amount) as sumamt,
        count(*) as cnt,
        AVG(Amount) AS AvgAmt 
FROM FactFinance 
where YEAR(Date) = 2005
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY Year, Month; 

GO

month       year        sumamount               3monthra
----------- ----------- ---------------------- ----------------------
7           2005        11384884.51            11384884.51
8           2005        36016653.13            18008326.565
9           2005        58029544.31            19343181.4366667
10          2005        66734589.35            22244863.1166667
11          2005        79778854.28            26592951.4266667
12          2005        88791927.09            29597309.03

(6 row(s) affected)

Year        Month       sumamt                 cnt         AvgAmt
----------- ----------- ---------------------- ----------- ----------------------
2005        7           11384884.51            1130        10075.1190353982
2005        8           24631768.62            1122        21953.4479679145
2005        9           22012891.18            1116        19724.8128853047
2005        10          20089929.55            1122        17905.463057041
2005        11          37676033.55            1124        33519.6028024911
2005        12          31025963.99            1126        27554.1420870338

(6 row(s) affected)

请注意，这是取自 aw2012 ，希望您的版本有 2005。

推荐阅读