計算年復一年和月復一個月

我在 BigQuery 中有一個表格，它跟踪從 2019 年 6 月開始的每個季度的支出金額。我需要計算年同比和月同比百分比變化。我已經提到了他們兩個的適當公式。對 YoY 和 MoM 跨行執行計算對我來說有點棘手。有人可以幫忙嗎？

桌子：

period      report_date  spend_dollar
30-jun-19   2019-06-30    5022087
30-sept-19  2019-09-30    4958617
31-dec-19   2019-09-30    5038630
31-mar-20   2020-03-31    5156327
30-jun-20   2020-06-30    5344183
30-sept-20  2020-09-30    5562796
31-dec-20   2020-12-31    5696796
31-mar-21.  2021-03-31    5749467
30-jun-20.  2021-06-30    5680087

同比預期產出：

period      report_date  spend_dollar.   year_over_year
30-jun-19   2019-06-30    5022087        -
30-sept-19  2019-09-30    4958617      -
31-dec-19   2019-09-30    5038630        -
31-mar-20   2020-03-31    5156327        -
30-jun-20   2020-06-30    5344183        6.4
30-sept-20  2020-09-30    5562796        12.18
31-dec-20   2020-12-31    5696796        13.1
31-mar-21.  2021-03-31    5749467        11.5
30-jun-20.  2021-06-30    5680087        6.3

YoY formula example formula: 
((spend_dollar value for date 2021-06-30/spend_dollar for date 2020--06-30)-1)*100, 
((spend_dollar value for date 2020-03-30/spend_dollar for date 2020--03-30)-1)*100 
..

月度預期產出：

period      report_date  spend_dollar.   month_over_month
30-jun-19   2019-06-30    5022087        -
30-sept-19  2019-09-30    4958617.       -1.2
31-dec-19   2019-09-30    5038630.       1.6
31-mar-20   2020-03-31    5156327        2.3
30-jun-20   2020-06-30    5344183.       6.4
30-sept-20  2020-09-30    5562796.       4.1
31-dec-20   2020-12-31    5696796        2.4
31-mar-21.  2021-03-31    5749467        0.092
30-jun-20.  2021-06-30    5680087        -1.2

Month-over-Month example formula: 
((spend_dollar value for date 2021-06-30/spend_dollar for date 2021--03-30)-1)*100 ,
((spend_dollar value for date 2021-03-30/spend_dollar for date 2020--12-31)-1)*100
..

您需要的是通常"window functions"在 SQL 中呼叫的名稱，但 BigQuery 似乎有不同的名稱 -"navigation functions"例如，它會呼叫其中的一些名稱。

我不是 BigQuery 專家，但如果我是你，我首先要做的是查看 BigQuery 文件：

• Analytic function concepts in Standard SQL
• Navigation function concepts
• Navigation functions in Standard SQL

視窗函式非常強大（這裡有簡短的 PostgreSQL 教程-這裡有一系列很好的教程），並且會回報您多次學習它們所付出的任何努力。

這些功能似乎都與 PostgreSQL 兼容，因此下面的範例應該適合您。下面的所有程式碼都可以在 PostgreSQL fiddle here上找到。

你需要這樣的東西：

SELECT 
 period, spend_dollars, 

 ROUND((spend_dollars / LAG(spend_dollars, 1)      ---  <<=== note offset 1!
   OVER (ORDER BY period))::NUMERIC * 100, 2) AS quarter,  

 ROUND((spend_dollars / LAG(spend_dollars, 4)      ---  <<=== note offset 4!
   OVER (ORDER BY period))::NUMERIC * 100, 2) AS annual
   
FROM product_spend
ORDER BY period;

結果：

period     spend_dollars  quarter    annual
30-jun-20   5344183         
30-jun-21   5680087        106.29   
30-sept-19  4958617         87.30   
30-sept-20  5562796        112.18   
31-dec-19   5038630         90.58    94.28
31-dec-20   5696796        113.06   100.29
31-mar-20   5156327         90.51   103.99
31-mar-21   5749467        111.50   103.36

請注意，對於 with LAG()(and LEAD()) 函式，有一個offset

(LAG (value_expression[, offset [, default_expression]])) 

您可以使用它來確定目前記錄與從函式獲得的值之間的差距。

這意味著即使在同一個查詢中，您也可以在年度期間（4 個期間偏移量）或僅一個季度（偏移量 1）（或每月、每週……無論您的數據允許）獲得分析！

引用自：https://dba.stackexchange.com/questions/299826