CTE Rolling 3 мес.

Question

вывод для всех 3 запросов

работая над заданием, ниже - вопрос, она указала нам использовать CTE

Написать код запроса SQL, используемый для изучения таблиц базы данных, и написать запрос, который получает финансовые суммы из базы данных "FactFinance" в базе данных "AdventureWorksDW2016CTP3" и возвращает эти суммы, упорядоченные по месяцам, и показывая скользящее среднее за 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

ВЫХОД для последнего запроса Требуется скользящее среднее за 3 месяца

Answer 1

Во-первых, вы должны знать правильный способ ответить на этот вопрос. Если у вас есть данные за все три месяца, то:

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;