如何在SQL中计算各时间段的复合增长率_EXP与SUM OVER结合-SQL-PHP中文网

复合增长率（cagr）不能用sum over，因其本质是几何平均而非算术累加；正确方法是用first_value和last_value提取首末值后套用幂运算公式，并以实际时间跨度（非行数）作分母。

如何在sql中计算各时间段的复合增长率_exp与sum over结合

复合增长率计算为什么不能直接用 `SUM OVER`？

因为复合增长率（CAGR）本质是几何平均增长，不是线性累加——SUM OVER 算的是算术累计和，强行套用会得出完全错误的结果。比如某指标连续三期为 100 → 120 → 96，真实复合增速是 (96/100)^(1/2) − 1 ≈ −2.02%，但用 SUM OVER 对「每期环比增长率」求和再除以期数，得到的是 (0.2 − 0.2)/2 = 0%，彻底失真。

真正可行的路径是：先用窗口函数拿到期初值和期末值，再套用幂运算公式。关键在于避免对增长率本身做窗口聚合。

用 `FIRST_VALUE` 和 `LAST_VALUE` 提取首尾值

这是最稳定、兼容性最好的方式，适用于 PostgreSQL、SQL Server、Oracle、BigQuery 等主流引擎（注意 MySQL 8.0+ 才支持完整窗口函数语义）。

FIRST_VALUE(value) OVER (PARTITION BY group_col ORDER BY time_col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 确保取到每个分组内时间轴上的第一个实际值（不是 NULL）
LAST_VALUE(value) OVER (PARTITION BY group_col ORDER BY time_col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 同理取末值；但注意默认 LAST_VALUE 的 window frame 是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，必须显式重设 frame，否则拿不到真正的最后一个值
时间列必须严格有序且无重复，否则需加 ROW_NUMBER() 辅助去重排序

示例（按年计算各产品 CAGR）：

SELECT
  product,
  FIRST_VALUE(sales) OVER (PARTITION BY product ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS base_sales,
  LAST_VALUE(sales) OVER (PARTITION BY product ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_sales,
  COUNT(*) OVER (PARTITION BY product) - 1 AS n_years,
  POWER(final_sales * 1.0 / base_sales, 1.0 / NULLIF(n_years, 0)) - 1 AS cagr
FROM sales_history;

`EXP(SUM(LN(...)) OVER ...)` 的陷阱与适用场景

这个写法理论上等价于连乘积开方，即 EXP(SUM(LN(ratio)) OVER ...) 可还原出总倍数，再开 n 次方得 CAGR。但它只适用于「已知每期环比比率」的场景，且极易因数据质量问题崩掉：

求职精灵

一款AI办公效率工具，主要用于有见求职旗下AI求职平台，提供AI简历优化、AI模拟面试、岗位查询、职业规划等，适合需要提升相关任务效率的用户。

下载

任意一期 ratio ≤ 0 → LN 报错（如 PostgreSQL 抛 invalid argument for logarithm）
存在 NULL 或空值时，SUM 直接返回 NULL，整个链路中断
浮点精度误差在长周期（>10 年）下可能放大，结果偏离手工验算值
MySQL 不支持 LN 窗口聚合（5.7/8.0 均不允许可聚合函数嵌套窗口函数），PostgreSQL 允许但需确保 ratio 列非空

仅建议在清洗干净的环比比率表上使用，且必须包 NULLIF 和 CASE WHEN 防御：

EXP(
  SUM(LN(CASE WHEN ratio > 0 THEN ratio END)) 
    OVER (PARTITION BY product ORDER BY year)
  / NULLIF(COUNT(*) OVER (PARTITION BY product) - 1, 0)
) - 1 AS cagr

时间跨度不连续时如何处理？

真实业务中常遇到断点（如某产品 2020、2022 年有数据，2021 年缺失）。此时不能简单用 COUNT(*) - 1 当作年数——必须用实际最大最小时间差：

用 MAX(year) - MIN(year) 替代行数减一（前提是年份为整型或可减日期）
若时间字段是 DATETIME，用 DATEDIFF('year', MIN(dt), MAX(dt))（BigQuery/SQL Server）或 EXTRACT(YEAR FROM MAX(dt)) - EXTRACT(YEAR FROM MIN(dt))（PostgreSQL）
更严谨的做法是计算「实际天数差 / 365.25」作为年化指数，尤其跨多年度且需财务级精度时

漏掉这点，三年断续数据（2020→2022）会被误算成两年期 CAGR，结果偏高约 2–3%。

真正难的从来不是写对那个 POWER 表达式，而是确认分母用的是自然时间跨度，而不是数据库里恰好有几条记录。