如何在PostgreSQL中计算当前月与六个月前的金额百分比变化？

问题背景 #

已创建如下PostgreSQL表并插入示例数据：

create table your_table(type text,compdate date,amount numeric);
insert into your_table values
('A','2022-01-01',50),
('A','2022-02-01',76),
('A','2022-03-01',300),
('A','2022-04-01',234),
('A','2022-05-01',14),
('A','2022-06-01',9),
  
('B','2022-01-01',201),
('B','2022-02-01',33),
('B','2022-03-01',90),
('B','2022-04-01',41),
('B','2022-05-01',11),
('B','2022-06-01',5),
  
('C','2022-01-01',573),
('C','2022-02-01',77),
('C','2022-03-01',109),
('C','2022-04-01',137),
('C','2022-05-01',405),
('C','2022-06-01',621);

需求：计算每种type从当前日期六个月前到当前月的金额百分比变化，示例结果如下：

• Type A六个月内下降82%
• Type B六个月内下降97.5%
• Type C六个月内增长8.4%

核心解决方案SQL #

WITH date_range AS (
    SELECT
        type,
        -- 提取当前月的金额（适配单月单条数据场景）
        MAX(CASE WHEN compdate >= date_trunc('month', CURRENT_DATE) THEN amount END) AS current_month_amount,
        -- 提取六个月前对应月份的金额
        MAX(CASE WHEN compdate >= date_trunc('month', CURRENT_DATE - INTERVAL '6 months') 
                 AND compdate < date_trunc('month', CURRENT_DATE - INTERVAL '5 months') THEN amount END) AS six_months_ago_amount
    FROM your_table
    GROUP BY type
)
SELECT
    type,
    CONCAT(
        'Type ', type, '六个月内',
        CASE
            WHEN current_month_amount > six_months_ago_amount THEN '增长'
            WHEN current_month_amount < six_months_ago_amount THEN '下降'
            ELSE '持平'
        END,
        ROUND(ABS((current_month_amount - six_months_ago_amount) / six_months_ago_amount * 100), 1),
        '%'
    ) AS change_description
FROM date_range
WHERE six_months_ago_amount IS NOT NULL -- 排除无历史数据的类型
ORDER BY type;

语句逻辑解释 #

1. CTE date_range：通过分组聚合，精准匹配每个type的当前月数据和六个月前的同月数据，使用date_trunc确保月份区间的准确性。
2. 百分比计算：通过(当前金额 - 历史金额)/历史金额 * 100计算变化率，用ABS取绝对值、ROUND保留1位小数，保证结果格式统一。
3. 结果格式化：用CONCAT拼接成需求指定的自然语言描述，自动判断增长/下降/持平状态。

与其他语句结合的场景示例 #

1. 过滤特定类型 #

如果只需要计算指定类型的变化，在分组前添加过滤条件：

WITH date_range AS (
    SELECT
        type,
        MAX(CASE WHEN compdate >= date_trunc('month', CURRENT_DATE) THEN amount END) AS current_month_amount,
        MAX(CASE WHEN compdate >= date_trunc('month', CURRENT_DATE - INTERVAL '6 months') 
                 AND compdate < date_trunc('month', CURRENT_DATE - INTERVAL '5 months') THEN amount END) AS six_months_ago_amount
    FROM your_table
    WHERE type IN ('A', 'B') -- 仅计算A、B类型
    GROUP BY type
)
SELECT ... -- 后续逻辑同核心SQL

2. 关联外部表补充信息 #

如果需要结合类型的详细信息（比如从type_details表获取类型全称），使用JOIN关联：

WITH date_range AS (
    SELECT
        t.type,
        MAX(CASE WHEN t.compdate >= date_trunc('month', CURRENT_DATE) THEN t.amount END) AS current_month_amount,
        MAX(CASE WHEN t.compdate >= date_trunc('month', CURRENT_DATE - INTERVAL '6 months') 
                 AND t.compdate < date_trunc('month', CURRENT_DATE - INTERVAL '5 months') THEN t.amount END) AS six_months_ago_amount
    FROM your_table t
    JOIN type_details td ON t.type = td.type_code -- 关联类型详情表
    GROUP BY t.type
)
SELECT
    td.full_name, -- 使用关联表的类型全称
    CONCAT(...) AS change_description
FROM date_range dr
JOIN type_details td ON dr.type = td.type_code
WHERE dr.six_months_ago_amount IS NOT NULL
ORDER BY td.full_name;

3. 将结果存入报表表 #

如果需要把计算结果持久化到报表表，使用INSERT INTO ... SELECT：

-- 先创建报表表（若不存在）
CREATE TABLE IF NOT EXISTS monthly_change_report (
    type text PRIMARY KEY,
    change_description text,
    report_generated_date date DEFAULT CURRENT_DATE
);

WITH date_range AS (
    SELECT
        type,
        MAX(CASE WHEN compdate >= date_trunc('month', CURRENT_DATE) THEN amount END) AS current_month_amount,
        MAX(CASE WHEN compdate >= date_trunc('month', CURRENT_DATE - INTERVAL '6 months') 
                 AND compdate < date_trunc('month', CURRENT_DATE - INTERVAL '5 months') THEN amount END) AS six_months_ago_amount
    FROM your_table
    GROUP BY type
)
INSERT INTO monthly_change_report (type, change_description)
SELECT
    type,
    CONCAT(...) AS change_description
FROM date_range
WHERE six_months_ago_amount IS NOT NULL
ON CONFLICT (type) DO UPDATE SET
    change_description = EXCLUDED.change_description,
    report_generated_date = CURRENT_DATE;

内容的提问来源于stack exchange，提问作者A. Chap