如何用SQL（PostgreSQL）计算季度汇总数据的环比增减百分比？

我之前用LAG()函数计算过当月和3个月前/1年前数据的增减百分比，现在需要实现以下逻辑：

• 先按季度汇总得到total_num
• 计算季度间的增减百分比，注意下一年Q1要和上一年Q4对比

示例结果 #

我的数据库兼容大部分PostgreSQL语法，可提供PostgreSQL示例。

样本数据 #

create table tb1( date date, num int); 
insert into tb1 values 
('2021-01-31', 359738), ('2021-02-28', 378564), ('2021-03-31', 349728),
('2021-04-30', 368945), ('2021-05-31', 321456), ('2021-06-30', 387456),
('2021-07-31', 310567), ('2021-08-31', 342189), ('2021-09-30', 395612),
('2021-10-31', 278945), ('2021-11-30', 365478), ('2021-12-31', 347856),
('2022-01-31', 319478), ('2022-02-28', 382456), ('2022-03-31', 324189),
('2022-04-30', 395612), ('2022-05-31', 367845), ('2022-06-30', 310567),
('2022-07-31', 382456), ('2022-08-31', 347856), ('2022-09-30', 324189),
('2022-10-31', 395612), ('2022-11-30', 319478), ('2022-12-31', 365478),
('2023-01-31', 302856), ('2023-02-28', 334531), ('2023-03-31', 364023),
('2023-04-30', 334534), ('2023-05-31', 313678), ('2023-06-30', 313460),
('2023-07-31', 357281), ('2023-08-31', 314578), ('2023-09-30', 307976),
('2023-10-31', 304567), ('2023-11-30', 311378), ('2023-12-31', 366222);

表结构 #

解决方案 #

可以通过两步实现：先按季度汇总数据，再用LAG()函数关联上一个季度的数据（跨年度Q1关联上一年Q4），最后计算增减百分比。

WITH quarterly_data AS (
    SELECT
        EXTRACT(YEAR FROM date)::INT AS year,
        EXTRACT(QUARTER FROM date)::INT AS quarter,
        SUM(num) AS total_num
    FROM tb1
    GROUP BY EXTRACT(YEAR FROM date), EXTRACT(QUARTER FROM date)
    ORDER BY year, quarter
)
SELECT
    year,
    quarter,
    total_num,
    -- 计算增减百分比，首行显示0.00%
    TO_CHAR(
        COALESCE(
            (total_num - LAG(total_num) OVER (ORDER BY year, quarter))::FLOAT / LAG(total_num) OVER (ORDER BY year, quarter),
            0
        ),
        'FM999.00%'
    ) AS quarter_change_pct
FROM quarterly_data;

逻辑说明 #

1. 季度汇总：用EXTRACT(YEAR/QUARTER FROM date)提取年份和季度，按这两个字段分组求和得到total_num。
2. 关联上季度数据：LAG(total_num) OVER (ORDER BY year, quarter)会按年份和季度的顺序，获取上一行的total_num，自然实现跨年度Q1关联上一年Q4的需求。
3. 计算百分比：用当前季度值减去上季度值，除以上季度值得到比例，再用TO_CHAR()格式化为带百分号的字符串，COALESCE()处理首行无数据的情况，显示0.00%。

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