sql - 如何查询过去 12 个月的平均值

Question

我有一张表格，上面有每月的统计数据

Calendar_Date   Month       Monthly Stat
1 Jun 16        Jun-16      1011    
1 Jul 16        Jul-16      1034            
1 Aug 16        Aug-16      1003            
1 Sep 16        Sep-16      991             
1 Oct 16        Oct-16      988             
1 Nov 16        Nov-16      999             
1 Dec 16        Dec-16      994             
1 Jan 17        Jan-17      981             
1 Feb 17        Feb-17      964             
1 Mar 17        Mar-17      966             
1 Apr 17        Apr-17      948             
1 May 17        May-17      942             
1 Jun 17        Jun-17      945             
1 Jul 17        Jul-17      938             
1 Aug 17        Aug-17      942             
1 Sep 17        Sep-17      941             
1 Oct 17        Oct-17      909             
1 Nov 17        Nov-17      911             
1 Dec 17        Dec-17      914             
1 Jan 18        Jan-18      929             
1 Feb 18        Feb-18      913             

我想找到前 12 条记录的年度统计数据因此，2017 年 12 月的统计数据将是 2017 年 12 月之前的 12 条记录的总和，即 Dec-2017 + Nov-17 + Oct-17 + ... + Jan-17 除以 12

Calendar_Date   Month       Monthly Stat    Yearly Stat
1 Jun 16        Jun-16      1011            -
1 Jul 16        Jul-16      1034            -
1 Aug 16        Aug-16      1003            -
1 Sep 16        Sep-16      991             -
1 Oct 16        Oct-16      988             -
1 Nov 16        Nov-16      999             -
1 Dec 16        Dec-16      994             -
1 Jan 17        Jan-17      981             -
1 Feb 17        Feb-17      964             -
1 Mar 17        Mar-17      966             -
1 Apr 17        Apr-17      948             -
1 May 17        May-17      942             985.08
1 Jun 17        Jun-17      945             979.58
1 Jul 17        Jul-17      938             971.58
1 Aug 17        Aug-17      942             966.50
1 Sep 17        Sep-17      941             962.33
1 Oct 17        Oct-17      909             955.75
1 Nov 17        Nov-17      911             948.33
1 Dec 17        Dec-17      914             941.75
1 Jan 18        Jan-18      929             937.42
1 Feb 18        Feb-18      913             933.17

我可以通过将表与自身连接 12 次来做到这一点

SELECT *
FROM MyTable A
INNER JOIN  MyTable B ON DATEADD(M,-1,a.Calendar_Date) = b.Calendar_Date
INNER JOIN  MyTable C ON DATEADD(M,-2,a.Calendar_Date) = c.Calendar_Date
...
INNER JOIN  MyTable L ON DATEADD(M,-12,a.Calendar_Date) = l.Calendar_Date

这不是很实用有更好的办法吗？

Answer 1

你想要的是一个窗口函数。

SELECT Calendar_Date, Month, MonthlyStat, 
  IIF(ROW_NUMBER() OVER(ORDER BY Calendar_Date)>=12,
    ROUND(AVG(CAST(MonthlyStat AS DECIMAL))
      OVER (ORDER BY Calendar_Date ASC ROWS BETWEEN 11 PRECEDING AND CURRENT ROW),2),
    NULL) AS YearlyStat
FROM MyTable ORDER BY Calendar_Date

AVG(MonthlyStat) OVER(ORDER BY Calendar_Date ASC ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) 获取当前行和它之前的 11 行(按 Calendar_Date 排序)，并给出平均值。我们 CAST MonthlyStat 为十进制，因此代码不会返回整数，然后 ROUND 到小数点后 2 位，因为这是货币。

如果您想要本月之前的 12 个月，您的窗口函数中的 ROWS 子句将变为 ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING

IIF(ROW_NUMBER() OVER(ORDER BY Calendar_Date)>=12,[...],NULL) 测试这是否是第 12 行或更晚的行，并且只计算年平均值数据存在 12 个月的那些行，第 1-11 行返回 null。如果您确实更改了 ROWS 子句，您还需要将其更改为再跳过一行，只需将 >= 更改为 >。

SQLFiddle