如何在SQL（优先PostgreSQL）中按月摊销收入与成本并转换表结构

1. 明确原始表结构 #

假设你的长格式数据表名为revenue_cost_long，字段如下（可根据实际情况调整）：

• record_id: 唯一记录ID
• item_category: 项目/类别名称（对应目标表的行）
• flow_type: 收支类型（收入/成本）
• total_value: 总金额
• start_month: 摊销起始年月（格式如'2023-01'，或日期类型）
• end_month: 摊销结束年月（格式如'2023-06'，或日期类型）

2. 生成全量年月维度 #

先生成覆盖所有记录起止周期的所有年月，作为后续匹配的基础：

WITH all_periods AS (
    -- 生成从最早起始月到最晚结束月的所有月份
    SELECT generate_series(
        (SELECT date_trunc('month', start_month) FROM revenue_cost_long ORDER BY start_month LIMIT 1),
        (SELECT date_trunc('month', end_month) FROM revenue_cost_long ORDER BY end_month DESC LIMIT 1),
        '1 month'::interval
    ) AS period_date
),
formatted_periods AS (
    -- 转成YYYY-MM格式的年月字符串
    SELECT to_char(period_date, 'YYYY-MM') AS month FROM all_periods
)

3. 计算每月摊销金额 #

关联原始数据和年月维度，筛选出每个记录覆盖的月份，并计算每月摊销额（这里按平摊处理，可根据实际规则修改）：

, monthly_amortization AS (
    SELECT
        r.item_category,
        r.flow_type,
        fp.month,
        -- 计算总摊销月份数
        (EXTRACT(YEAR FROM r.end_month) - EXTRACT(YEAR FROM r.start_month)) * 12 +
        (EXTRACT(MONTH FROM r.end_month) - EXTRACT(MONTH FROM r.start_month)) + 1 AS total_amort_months,
        -- 平摊每月金额（保留2位小数）
        ROUND(r.total_value / (
            (EXTRACT(YEAR FROM r.end_month) - EXTRACT(YEAR FROM r.start_month)) * 12 +
            (EXTRACT(MONTH FROM r.end_month) - EXTRACT(MONTH FROM r.start_month)) + 1
        ), 2) AS monthly_value
    FROM revenue_cost_long r
    CROSS JOIN formatted_periods fp
    -- 判断当前年月是否在记录的摊销周期内
    WHERE to_date(fp.month, 'YYYY-MM') BETWEEN date_trunc('month', r.start_month) AND date_trunc('month', r.end_month)
)

4. 交叉表转宽格式 #

PostgreSQL需要依赖tablefunc扩展实现交叉表，先确保扩展已启用：

-- 仅需执行一次，启用tablefunc扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

然后用crosstab生成宽表：

SELECT * FROM crosstab(
    -- 源数据查询：按类别、类型、年月排序
    'SELECT item_category, flow_type, month, monthly_value
     FROM monthly_amortization
     ORDER BY item_category, flow_type, month',
    -- 指定宽表的列（所有年月，按顺序排列）
    'SELECT month FROM formatted_periods ORDER BY month'
) AS result_table (
    item_category text,
    flow_type text,
    -- 这里要替换成实际生成的年月列，示例为2023-01到2023-06
    "2023-01" numeric,
    "2023-02" numeric,
    "2023-03" numeric,
    "2023-04" numeric,
    "2023-05" numeric,
    "2023-06" numeric
);

关键调整说明 #

• 如果原始表的起止日期是完整日期（如'2023-01-15'），只需把date_trunc('month', r.start_month)替换为date_trunc('month', r.start_date)即可。
• 若摊销规则不是平摊，直接修改monthly_value的计算逻辑即可。
• 若需要将收入和成本分别作为行或列，可调整crosstab的分组字段，或者拆分查询后合并。

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