初始值为0时如何计算CAGR及规避除以0报错方案

核心逻辑调整 #

常规CAGR公式是 CAGR = (期末值/期初值)^(1/年数) - 1，但期初为0或Null时要针对性处理：

• 初始值为0，期末非0：属于从无到有的增长，直接用 CAGR = (期末值)^(1/年数) - 1 计算。比如第1年0访客、第3年100访客（年数为2），CAGR为900%，能准确反映爆发式增长。
• 初始值为Null：先做预处理：
  • 若Null代表实际访客为0，直接替换为0后按上面的0值方案计算；
  • 若Null是数据缺失，可选用后续第一个非0值作为期初基准，同时调整计算年数（从有数据的第一年开始算），或用同类型区域的平均初始值填充。
• 期末值也为0：直接设CAGR为0，代表无增长。

常用工具的实现代码 #

Excel公式

=IF(OR(期初值=0, ISBLANK(期初值)), 
    IF(期末值=0, 0, (期末值)^(1/年数)-1), 
    (期末值/期初值)^(1/年数)-1)

SQL语句

SELECT 
    区域,
    CASE 
        WHEN 期初访客量 IS NULL OR 期初访客量 = 0 THEN
            CASE WHEN 期末访客量 = 0 THEN 0 ELSE POWER(期末访客量, 1/年数) - 1 END
        ELSE POWER(期末访客量/期初访客量, 1/年数) - 1
    END AS 复合年增长率
FROM 访客量表;

内容的提问来源于stack exchange，提问作者Raktim Mazumdar