主页 / dba / 问题 / 299826
Accepted
Rachit Singh
Asked: 2021-09-20 03:02:09 +0800 CST

计算年复一年和月复一个月

  • 772

我在 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
..
google-bigquery

1 个回答

  1. Best Answer

    您需要的是通常"window functions"在 SQL 中调用的名称，但 BigQuery 似乎有不同的名称 -"navigation functions"例如，它会调用其中的一些名称。

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

    窗口函数非常强大（这里有简短的 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）（或每月、每周......无论您的数据允许）获得分析！

    • 0

相关问题