如何在PostgreSQL中用SQL计算相邻月份销售线索的月度差值及变化率

前提假设 #

我们默认你的原始月度线索统计表结构如下：

• 表名：monthly_leads
• 字段：
  • stat_month：统计月份，格式建议为YYYY-MM字符串或DATE类型（取每月1日即可）
  • leads_cnt：对应月份的销售线索总数量

核心SQL实现 #

以下方案支持MySQL 8.0+、PostgreSQL、Hive、Spark SQL等所有支持标准窗口函数的数据库：

SELECT
    stat_month,
    leads_cnt AS current_month_leads,
    -- 取上一个月的线索数
    LAG(leads_cnt, 1) OVER (ORDER BY stat_month) AS last_month_leads,
    -- 计算相邻月份线索差值
    leads_cnt - LAG(leads_cnt, 1) OVER (ORDER BY stat_month) AS leads_diff,
    -- 计算变化率（保留2位小数，兼容上月线索为0的除0异常场景）
    ROUND(
        (leads_cnt - LAG(leads_cnt, 1) OVER (ORDER BY stat_month)) 
        / NULLIF(LAG(leads_cnt, 1) OVER (ORDER BY stat_month), 0) 
        * 100,
        2
    ) AS leads_change_rate_pct
FROM monthly_leads
ORDER BY stat_month;

逻辑说明 #

• LAG(leads_cnt, 1) OVER (ORDER BY stat_month)是核心窗口函数，会按照月份排序后，取当前行上1行的线索数，也就是上月线索量
• 首月没有上月数据，差值和变化率返回NULL属于正常情况
• NULLIF(上月线索数, 0)用于处理上月线索为0的场景，避免除0报错，返回NULL代表该月无法计算变化率
• ROUND(xxx, 2)是将变化率保留2位小数，可根据你的需求调整精度

如果你需要按部门、产品线等维度分别计算相邻月份的差值和变化率，只需要修改窗口函数为LAG(leads_cnt, 1) OVER (PARTITION BY 维度字段 ORDER BY stat_month)即可。

示例返回结果 #

对应你给出的7月1000条、8月1200条、9月1500条的测试数据，返回结果如下：

内容的提问来源于stack exchange，提问作者Luis Carías