时间戳

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

sql - CTE 滚动 3 个月平均

问题描述

所有 3 个查询的输出

正在处理一项任务，以下是问题，她已指示我们使用 CTE

编写用于探索数据库表的 SQL 查询代码并编写一个查询，从“AdventureWorksDW2016CTP3”数据库中的“FactFinance”检索财务金额并返回这些金额，按月组织，并显示 3 个月的滚动平均值

SELECT DateKey,
  month(date) as [Month],
  year(date) as [Year],
SUM ( ALL Amount) OVER (PARTITION BY Date ORDER BY Date ASC) AS Amount
FROM FactFinance

SELECT
    YEAR(Date) AS Year,
    MONTH(Date) AS Month,
    SUM(Amount) AS Amount
FROM FactFinance
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY Year, Month;


WITH CTE AS (
SELECT 
    DateKey AS Month, 
      AVG(Amount) AS AvgAmt
  from FactFinance
  group by DateKey
  )
  SELECT 
    Month,
    AvgAmt
FROM CTE

GO

最后一次查询的 OUTPUT 需要 3 个月的滚动平均值

标签： sqlsql-servercommon-table-expressionrolling-average

解决方案

首先，你应该知道回答这个问题的正确方法。假设您拥有所有三个月的数据，那么：

SELECT YEAR(Date) AS Year,
       MONTH(Date) AS Month,
       SUM(Amount) AS Amount,
       AVG(SUM(Amount)) OVER (ORDER BY MIN(DATE)
                              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as rolling_3month_avg
FROM FactFinance
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY Year, Month;

如果有人告诉我为此使用 CTE，我可能会很想这样做：

WITH unnecessary_cte as (
      SELECT YEAR(Date) AS Year,
             MONTH(Date) AS Month,
             SUM(Amount) AS Amount,
             AVG(SUM(Amount)) OVER (ORDER BY MIN(DATE)
                                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as rolling_3month_avg
      FROM FactFinance
      GROUP BY YEAR(Date), MONTH(Date)
     )
SELECT *
FROM unnecessary_cte
ORDER BY YEAR, MONTH;

但是，我们可以尝试读懂你的导师的想法，并推测她希望你写这样的东西：

WITH ym as (
      SELECT YEAR(Date) AS Year,
             MONTH(Date) AS Month,
             SUM(Amount) AS Amount
      FROM FactFinance
      GROUP BY YEAR(Date), MONTH(Date)
     )
SELECT ym.*,
       (SELECT AVG(Amount)
        FROM ym ym2
        WHERE 12 * ym2.year + ym2.month
                  BETWEEN 12 * ym.year + ym.month - 2 AND
                          12 * ym.year + ym.month
       ) as rolling_3month_avg
FROM ym
ORDER BY YEAR, MONTH;

推荐阅读