Блог

Я хотел бы получить QoQ из набора данных с данными Q3 и Q4, в котором также есть столбец даты отчета, каждая строка должна иметь значение QoQ для каждого финансового месяца (представленное датой отчета), Q4 должен сравниваться с Q3, но мое утверждение, похоже, сравнивается только в пределах одного квартала, т.Е. Q4сравнивается с Q4 вместо Q4 по сравнению с Q3..

Я использую lag функцию, но не уверен, что я делаю неправильно, если кто-нибудь может посмотреть код ниже.

  SELECT [Year], 
  [SalesDate] as Report_Date,
       [Quarter], 
       Sales,
     
       LAG(Sales,  1, 0) OVER(
       PARTITION BY [Year] ,[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC) AS [QuarterSales_Offset],
                sales - LAG(Sales) OVER(
       PARTITION BY [Year] ,[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC) as diff,
Case When 
LAG(Sales,1,0) OVER(
       PARTITION BY [Year],[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC) = 0 then null else

(
sales - LAG(Sales,1,0) OVER(
       PARTITION BY [Year],[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC))/ LAG(Sales,1,0) OVER(
       PARTITION BY [Year],[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC) end as QoQ
FROM dbo.ProductSales_2;
  

Вывод запроса:

Поскольку LAG() при смещении 1 возвращается предыдущая строка, а ваши данные находятся на уровне месяца, вы фактически сравниваете месяц за месяцем в каждом квартале. Рассмотрите другой подход, например, объединение двух подмножеств ваших данных по кварталам и месяцам в квартале.

QuarterMonth столбец может быть вычислен с ROW_NUMBER() помощью выражения (т. Е. Текущего количества месяцев в каждом квартале). Поскольку потенциально могут возникнуть месячные пробелы в данных о продажах, используйте year_quarter_month календарную таблицу, выровненную по вашему финансовому году. В целом, это позволяет сравнивать первый финансовый месяц 4 квартала ( 2020-08-31 ) с первым финансовым месяцем 3 квартала ( 2020-05-31 ) по столбцам.

 WITH unit AS (
   SELECT yqm.[Year]
        , yqm.[Quarter]
        , yqm.[Month]
        , COALESCE(p.[Report_Date], DATEADD(DAY, -1, DATEFROMPARTS(yqm.[Year], yqm.[Month] 1, 1))) AS [Report_Date]
        , p.[Sales]
   FROM year_quarter_month_table yqm
   LEFT JOIN dbo.ProductSales_2 p
      ON yq.[Year] = p.[Year]
      AND yq.[Quarter] = p.[Quarter]
      AND yq.[Month] = p.[Month]

), sub AS (
   SELECT [Year]
        , [Quarter]
        , [Month]
        , ROW_NUMBER() OVER(PARTITION BY [Year], [Quarter] 
                            ORDER BY [Report_Date]) AS [QuarterMonth]
        , [Report_Date]
        , [Sales]
   FROM unit
 )

SELECT q4.[Year]
     , q4.[Report_Date] AS Q4_Date
     , q4.[Sales] AS Q4_Sales
     , q3.[Report_Date] AS Q3_Date
     , q3.[Sales] AS Q3_Sales
     , q4.[Sales] - q3.[Sales] AS Diff
     , COALESCE((q4.[Sales] - q3.[Sales]) / q3.[Sales], 0) AS QoQ
FROM sub q4
LEFT JOIN sub q3
   ON  q4.[Year] = q3.[Year]
   AND q4.[Quarter] = 4
   AND q3.[Quarter] = 3
   AND q4.[QuarterMonth] = q3.[QuarterMonth]
  

Возможно, вы сможете выполнить обобщение для любого расчета за квартал, а не только за 3 и 4 кварталы:

 WITH sub AS (
  -- SAME CTEs AS ABOVE
)

SELECT curr_qtr.[Year]
     , curr_qtr.[Report_Date] AS Curr_Qtr_Date
     , curr_qtr.[Sales] AS Curr_Qtr_Sales
     , last_qtr.[Report_Date] AS Last_Qtr_Date
     , last_qtr.[Sales] AS Last_Qtr_Sales
     , curr_qtr.[Sales] - last_qtr.[Sales] AS Diff
     , COALESCE((curr_qtr.[Sales] - last_qtr.[Sales]) / last_qtr.[Sales], 0) AS QoQ
FROM sub curr_qtr
LEFT JOIN sub last_qtr
   ON  curr_qtr.[Year] = last_qtr.[Year]  -- ASSUMING FISCAL YEAR AND NOT CALENDAR YEAR
   AND curr_qtr.[Quarter] = last_qtr.[Quarter]   1
   AND curr_qtr.[QuarterMonth] = last_qtr.[QuarterMonth]