SQL进阶技巧：如何计算复合增长率？

会飞的一十六 2024-12-02

740

点击上方【蓝色】字体关注我们

01 场景描述

复合增长率是第N期的数据除以第一期的基准数据，然后开N-1次方再减去1得到的结果。假如2018年的产品销售额为10000，2019年的产品销售额为12500，2020年的产品销售额为15000（销售额单位省略，下同）。那么这两年的复合增长率的计算方式如下

复合增长率的计算公式如下：

复合增长率 = (最终值初始值)^( 1 n) - 1

其中：

最终值是指期末的数值；

初始值是指起始的数值；

n 时间段数量是指经过 n 个时间段的增长所到达的值。

举例来说，假设某项指标在起始时刻（一月份）为 100，经过 6 个月（到达七月份）的增长，最终值为 200，则可以按照以下步骤计算月均复合增长率：

月均复合增长率 = ( (200 100)^(1 (7-1)) -1) * 100%

这样就得到了某指标经过 6 个月的整体增长率（复合增长率）为12.25%。

在计算月均或年均复合增长率时，需要使用连续的起始值和结束值来进行计算。假设有 n 个连续的月份数据，那么起始值到结束值的时间跨度为 n-1 个月，只计算后续的增长情况。

以年度为单位计算的复合增长率被称为年均复合增长率，以月度为单位计算的复合增长率被称为月均复合增长率。查询自2018年1月以来不同产品的月均销售额复合增长率？

02 数据准备

-- 创建销量表sales_monthly
-- product表示产品名称，ym表示年月，amount表示销售金额（元）
CREATE TABLE sales_monthly(product varchar2(20), ym varchar2(6), amount decimal(10, 2));
-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);

03 问题分析

步骤1：计算初始值并生成连续序列值

       select product,
               ym,
               total_amount,
               first_value(total_amount) over (partition by product order by ym) first_total_amount,
               row_number() over (partition by product order by ym)              rn
        from (select product,
                     ym,
                     sum(amount) total_amount
              from sales_monthly
              group by product, ym
             ) t1

PRODUCT  YM  TOTAL_AMOUNT  FIRST_TOTAL_AMOUNT  RN
桔子  201801  10154  10154  1
桔子  201802  10183  10154  2
桔子  201803  10245  10154  3
桔子  201804  10325  10154  4
桔子  201805  10465  10154  5
桔子  201806  10505  10154  6
桔子  201807  10578  10154  7
桔子  201808  10680  10154  8
桔子  201809  10788  10154  9
桔子  201810  10838  10154  10
桔子  201811  10942  10154  11
桔子  201812  10988  10154  12
桔子  201901  11099  10154  13
桔子  201902  11181  10154  14
桔子  201903  11302  10154  15
桔子  201904  11327  10154  16
桔子  201905  11423  10154  17
桔子  201906  11524  10154  18
苹果  201801  10159  10159  1
苹果  201802  10211  10159  2
苹果  201803  10247  10159  3
苹果  201804  10376  10159  4
苹果  201805  10400  10159  5
苹果  201806  10565  10159  6
苹果  201807  10613  10159  7
苹果  201808  10696  10159  8
苹果  201809  10751  10159  9
苹果  201810  10842  10159  10
苹果  201811  10900  10159  11
苹果  201812  10972  10159  12
苹果  201901  11155  10159  13
苹果  201902  11202  10159  14
苹果  201903  11260  10159  15
苹果  201904  11341  10159  16
苹果  201905  11459  10159  17
苹果  201906  11560  10159  18
香蕉  201801  10138  10138  1
香蕉  201802  10194  10138  2
香蕉  201803  10328  10138  3
香蕉  201804  10322  10138  4
香蕉  201805  10481  10138  5
香蕉  201806  10502  10138  6
香蕉  201807  10589  10138  7
香蕉  201808  10681  10138  8
香蕉  201809  10798  10138  9
香蕉  201810  10829  10138  10
香蕉  201811  10913  10138  11
香蕉  201812  11056  10138  12
香蕉  201901  11161  10138  13
香蕉  201902  11173  10138  14
香蕉  201903  11288  10138  15
香蕉  201904  11408  10138  16
香蕉  201905  11469  10138  17
香蕉  201906  11528  10138  18

步骤2：利用公式计算月均复合增长率

select product,
       ym,
       first_total_amount,
       total_amount,
       concat(cast(nvl((power(total_amount  first_total_amount, 1  nullif((rn - 1), 0)) - 1) * 100,
                       0.00) as decimal(10, 2)),
              '%') month_avg_compound_growth_rate
from (
         select product,
                ym,
                total_amount,
                first_value(total_amount) over (partition by product order by ym) first_total_amount,
                row_number() over (partition by product order by ym)              rn
         from (select product,
                      ym,
                      sum(amount) total_amount
               from sales_monthly
               group by product, ym
              ) t1
     ) t2;

PRODUCT  YM  FIRST_TOTAL_AMOUNT  TOTAL_AMOUNT  MONTH_AVG_COMPOUND_GROWTH_RATE
桔子  201801  10154  10154  0%
桔子  201802  10154  10183  .29%
桔子  201803  10154  10245  .45%
桔子  201804  10154  10325  .56%
桔子  201805  10154  10465  .76%
桔子  201806  10154  10505  .68%
桔子  201807  10154  10578  .68%
桔子  201808  10154  10680  .72%
桔子  201809  10154  10788  .76%
桔子  201810  10154  10838  .73%
桔子  201811  10154  10942  .75%
桔子  201812  10154  10988  .72%
桔子  201901  10154  11099  .74%
桔子  201902  10154  11181  .74%
桔子  201903  10154  11302  .77%
桔子  201904  10154  11327  .73%
桔子  201905  10154  11423  .74%
桔子  201906  10154  11524  .75%
苹果  201801  10159  10159  0%
苹果  201802  10159  10211  .51%
苹果  201803  10159  10247  .43%
苹果  201804  10159  10376  .71%
苹果  201805  10159  10400  .59%
苹果  201806  10159  10565  .79%
苹果  201807  10159  10613  .73%
苹果  201808  10159  10696  .74%
苹果  201809  10159  10751  .71%
苹果  201810  10159  10842  .73%
苹果  201811  10159  10900  .71%
苹果  201812  10159  10972  .7%
苹果  201901  10159  11155  .78%
苹果  201902  10159  11202  .75%
苹果  201903  10159  11260  .74%
苹果  201904  10159  11341  .74%
苹果  201905  10159  11459  .76%
苹果  201906  10159  11560  .76%
香蕉  201801  10138  10138  0%
香蕉  201802  10138  10194  .55%
香蕉  201803  10138  10328  .93%
香蕉  201804  10138  10322  .6%
香蕉  201805  10138  10481  .84%
香蕉  201806  10138  10502  .71%
香蕉  201807  10138  10589  .73%
香蕉  201808  10138  10681  .75%
香蕉  201809  10138  10798  .79%
香蕉  201810  10138  10829  .74%
香蕉  201811  10138  10913  .74%
香蕉  201812  10138  11056  .79%
香蕉  201901  10138  11161  .8%
香蕉  201902  10138  11173  .75%
香蕉  201903  10138  11288  .77%
香蕉  201904  10138  11408  .79%
香蕉  201905  10138  11469  .77%
香蕉  201906  10138  11528  .76%

04 小结

本文介绍了复合增长率的概念及计算公式，并提供了SQL代码示例来计算商品的月度复合增长率。复合增长率是连续时间段内平均增长的速率。

复合增长率是一个时间序列概念。当考虑一个资产或经济指标的长期增长时，不能仅看其绝对增长值，因为不同时间段内的增长速度可能会有所不同。复合增长率就是用来解决这个问题的一个很好的工具。它考虑了从过去到现在，再到预测未来的增长趋势。计算复合增长率时，通常会考虑多个时间点的数据，然后计算这些时间段内增长率的平均值。通过这种方式，我们可以得到一个相对稳定的增长指标，从而更好地预测未来的发展趋势。在金融市场、企业决策以及宏观经济分析中，复合增长率是一个非常重要的指标。通过了解和掌握复合增长率的概念和计算方法，投资者和企业决策者可以更好地把握市场动态，做出更为明智的决策。

往期精彩

数据科学与SQL：如何计算Teager能量算子(TEO)？| 基于SQL实现

SQL进阶技巧：如何对不同表结构合并计算 | FULL JOIN问题

业务质疑：我们已经有报表了为什么还要上BI系统？| BI与报表区别和联系

SQL进阶技巧：如何获取数组中前N个元素？

SQL进阶技巧：每年在校人数统计？