PostgreSQL优化SQL：计算销售增长率及预测次年销售额

现有数据表数据 #

sale_info表原始数据如下：

id year sale_amount  
1 2021 400.0   
2 2021 450.0  
3 2022 500.0   
4 2022 600.0    
5 2023 400.0  
6 2023 500.0    
7 2023 700.0  

需求说明 #

• 增长率公式：当年增长率 = (当年销售额 - 上年销售额) / 上年销售额（注：原需求中2022年增长率分母写为2020年属于笔误，按业务逻辑修正为上年销售额）
• 计算历史年份的增长率，排除第一年的无效增长率后取平均值，以此预测次年销售额
• 自动适配新增年份，无需修改SQL即可更新计算结果，最终按年份展示所有历史数据及预测数据

现有问题SQL #

当前编写的SQL存在重复子查询、逻辑冗余、公式错误等问题：

# List information from previous years
select d.* from (SELECT
  year,
  SUM(sale_amount) as sales,
  (SUM(sale_amount) - LAG(SUM(sale_amount), 1, SUM(sale_amount)) OVER (ORDER BY year))/SUM(sale_amount) as growth_rate
FROM
  sale_info
GROUP BY
  1) d
union all
# Estimate the growth rate and sales for the next year based on the sales of the last year before
select (b.year+1),(1+avg(CASE WHEN growth_rate = 0 THEN NULL ELSE growth_rate END ))*max_sales as sales, avg(CASE WHEN growth_rate = 0 THEN NULL ELSE growth_rate END ) as growth_rate from 
# Calculate information from previous years
(SELECT
  year as year,
  SUM(sale_amount) as sales,
  (SUM(sale_amount) - LAG(SUM(sale_amount), 1, SUM(sale_amount)) OVER (ORDER BY year))/SUM(sale_amount) as growth_rate
FROM
  sale_info
GROUP BY
  1) a,
# Calculate the sales of the last year before
(select year,sales max_sales 
from  
( select
year as year,
SUM(sale_amount) as sales
FROM
sale_info
group by 1
) c
where year = (select max(year) from sale_info)
) b group by max_sales,b.year;

优化后的SQL方案 #

优化思路 #

1. 用CTE（公共表表达式）复用计算结果，避免重复聚合查询
2. 修正增长率计算逻辑，统一处理第一年的无效数据
3. 分离各步骤逻辑，提升代码可读性与可维护性
4. 自动适配新增年份，无需手动调整SQL

优化代码 #

WITH yearly_sales AS (
    -- 一次性计算每年总销售额
    SELECT 
        year,
        SUM(sale_amount) AS total_sales
    FROM sale_info
    GROUP BY year
    ORDER BY year
),
yearly_growth AS (
    -- 计算每年相对上年的增长率，第一年无上年数据则设为NULL
    SELECT 
        year,
        total_sales,
        CASE 
            WHEN LAG(total_sales) OVER (ORDER BY year) IS NOT NULL 
            THEN (total_sales - LAG(total_sales) OVER (ORDER BY year)) / LAG(total_sales) OVER (ORDER BY year)
            ELSE NULL 
        END AS growth_rate
    FROM yearly_sales
),
avg_growth AS (
    -- 计算有效增长率的平均值（排除第一年的NULL）
    SELECT AVG(growth_rate) AS avg_rate
    FROM yearly_growth
    WHERE growth_rate IS NOT NULL
),
last_year_sales AS (
    -- 获取最新年份的销售额数据
    SELECT year, total_sales
    FROM yearly_sales
    ORDER BY year DESC
    LIMIT 1
)
-- 合并历史数据与预测数据并按年份排序
SELECT 
    year,
    total_sales AS sales,
    growth_rate
FROM yearly_growth
UNION ALL
SELECT 
    ly.year + 1 AS year,
    ly.total_sales * (1 + ag.avg_rate) AS sales,
    ag.avg_rate AS growth_rate
FROM last_year_sales ly, avg_growth ag
ORDER BY year;

优化说明 #

• 减少重复计算：yearly_sales仅执行一次聚合，后续所有逻辑基于该结果，大幅提升查询效率
• 逻辑修正：将原错误的增长率公式（分母为当年销售额）修正为行业标准的(当年-上年)/上年，同时用NULL标记第一年的无效增长率
• 自动适配新增数据：新增年份后，CTE会自动计算新的增长率，预测年份也会自动更新为最新年份+1
• 可读性提升：每个CTE负责单一逻辑模块，代码结构清晰，便于后续修改与维护

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