SQL — среднее значение за следующие двенадцать месяцев

Я пытаюсь рассчитать среднее значение за следующие двенадцать месяцев.

Я рассчитал коэффициент, который зависит от месяца, на который мы хотим посмотреть:

365-DATEPART(dy,'2017-06-26')/365  & 1-365-DATEPART(dy,'2017-06-26')/365

Мне удалось получить желаемый ответ для одного указанного Date_Month, но я хотел бы иметь возможность обрезать его (удалить нули, содержащие NULL) и сделать возможным применение ко множеству Date_Months.

Вот текущий вывод:

Company_Id  Sales   Date_Year   Date_Month  NTMA_Factor Sales_Down  Sales_Up    NTMA_Sales
        1   675.051     2014    2013-03-31  0.7534      675.051     NULL        NULL
        2   47946.200   2014    2013-03-31  0.7534      47946.200   50364.200   48542.4788000
        2   50364.200   2015    2013-03-31  0.7534      50364.200   NULL        NULL
        3   6891.430    2014    2013-03-31  0.7534      6891.430    6917.450    6897.8465320
        3   6917.450    2015    2013-03-31  0.7534      6917.450    NULL        NULL
        5   2190.140    2014    2013-03-31  0.7534      2190.140    2318.250    2221.7319260
        5   2318.250    2015    2013-03-31  0.7534      2318.250    NULL        NULL

Есть ли способ сделать это в SQL для большего набора Date_Months?

Запрос до сих пор:

SELECT top 7
    S.Company_Id
    , S.Sales
    , Y.Date_Year
    , M.Date_Month
    , N.NTMA_Factor
    , (S.Sales) AS Sales_Down
    , (LEAD(S.Sales, 1) OVER (Partition by S.Company_Id ORDER BY Y.Date_Year ASC)) AS Sales_Up
    , ((S.Sales * N.NTMA_Factor)  + (LEAD(S.Sales, 1) OVER (Partition by S.Company_Id ORDER BY Y.Date_Year ASC))*(1-NTMA_Factor)) As NTMA_Sales 
FROM Sales AS S
      INNER JOIN Date_Year AS Y ON Y.Date_Year_Id = S.Date_Year_Id
      INNER JOIN Date_Month AS M ON M.Date_Month_Id = S.Date_Month_Id
      INNER JOIN NTMA_Factor AS N ON N.Date_Month_Id = M.Date_Month_Id
where
    Date_Year in (2014,2015, 2013) and Date_Month in ('2013-03-31');

В идеале я хотел бы иметь точно такой же вывод, но удалить строки, содержащие NULL, и сделать возможным просмотр большего количества Date_Months (это означает изменение операторов Lead & Over, чтобы они просто не брали первый ниже, как кажется в настоящее время). Я также хотел бы удалить столбцы Sales_Down и Sales_Up в конце, но это не проблема.

Я использую Microsoft SQL Server Management Studio и, к сожалению, плохо знаком с функциями в SQL.

Спасибо.


sql function sql-server tsql datepart
person Cedric Vongheer    schedule 26.06.2017    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Если я правильно интерпретирую ваш вопрос (что не точно), вы можете использовать LEAD() с OVER(), например:

SELECT
      S.Sales
    , Y.Date_Year
    , M.Date_Month
    , N.NTMA_Factor
    , (S.Sales * N.NTMA_Factor) AS NTMA_Sales_Down
    , LEAD(S.Sales, 1) OVER (ORDER BY Y.Date_Year ASC) AS SalesNextYear
FROM Sales AS S
      INNER JOIN Date_Year AS Y ON Y.Date_Year_Id = S.Date_Year_Id
      INNER JOIN Date_Month AS M ON M.Date_Month_Id = S.Date_Month_Id
      INNER JOIN NTMA_Factor AS N ON N.Date_Month_Id = M.Date_Month_Id
;

Функции LEAD и LAG позволяют вам «смотреть вперед» (опережать) или «смотреть назад» (отставать) по строкам, и я думаю, что это функция, которую вы ищете. Это предложение OVER, которое обеспечивает «управление» работой функций. В этом случае необходимо упорядочить по номеру года.

Примечание: существует ряд очень полезных "аналитических" функций, которые также нуждаются или могут использовать предложение over. SUM() OVER(), например, может предоставить промежуточный итог, или ROW_NUMBER() OVER() исключительно полезен для возврата первых/последних/самых ранних/последних строк

person Paul Maxwell    schedule 26.06.2017
comment
Спасибо, я включил код с предложением where, и он, кажется, дает то, что я хочу для отдельной компании, знаете ли вы, как сделать это обобщенным, чтобы он применял один и тот же вывод для множества Date_Years & Date_Months вместо того, чтобы просто брать значение из строки под ним? (В выходных данных много Date_Year = 2014, поэтому это не работает, потому что код принимает неправильную строку в качестве входных данных для продаж в следующем году) - person Cedric Vongheer; 27.06.2017
comment
извините, мы перепутали комментарии. Чтобы обобщить, я подозреваю, что вам нужно использовать PARTITION BY company, обратитесь к ссылкам на документацию, которые я предоставил. При использовании LEAD/LAG будет возобновляться для каждого нового значения в определяемом вами разделе. попытайся. - person Paul Maxwell; 27.06.2017