PostgreSQL中基于现有列生成月度计算列的技术咨询

API 探路者

2026-5-6

在PostgreSQL中基于现有列生成新月份列的方案

当然可以！在PostgreSQL里完全能实现你想要的需求——基于StartDate和Size生成12个2020年月份列，我们可以通过查询直接生成结果，或者把这些列永久添加到表中（甚至用自动更新的生成列）。

一、直接查询生成预期结果

下面的SQL会直接返回你要的15列结果，严格遵循你的规则：

StartDate所属月份的列值为Size
该月份之后的每个月，值为前一个月的值减50，直到结果≤0则显示0
该月份之前的所有月份值为0

SELECT
    Project,
    Size,
    StartDate,
    -- 1月：只有当StartDate是1月及之后的月份才可能有值，否则0
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 1 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 2 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 3 THEN GREATEST(Size - 100, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 4 THEN GREATEST(Size - 150, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 5 THEN GREATEST(Size - 200, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 6 THEN GREATEST(Size - 250, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 7 THEN GREATEST(Size - 300, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 8 THEN GREATEST(Size - 350, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 9 THEN GREATEST(Size - 400, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size - 450, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 500, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 550, 0)
        ELSE 0 
    END AS Jan,
    -- 2月：逻辑类似，只处理StartDate是2月及之后的情况
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 2 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 3 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 4 THEN GREATEST(Size - 100, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 5 THEN GREATEST(Size - 150, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 6 THEN GREATEST(Size - 200, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 7 THEN GREATEST(Size - 250, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 8 THEN GREATEST(Size - 300, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 9 THEN GREATEST(Size - 350, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size - 400, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 450, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 500, 0)
        ELSE 0 
    END AS Feb,
    -- 3月
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 3 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 4 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 5 THEN GREATEST(Size - 100, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 6 THEN GREATEST(Size - 150, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 7 THEN GREATEST(Size - 200, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 8 THEN GREATEST(Size - 250, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 9 THEN GREATEST(Size - 300, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size - 350, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 400, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 450, 0)
        ELSE 0 
    END AS Mar,
    -- 4月
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 4 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 5 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 6 THEN GREATEST(Size - 100, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 7 THEN GREATEST(Size - 150, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 8 THEN GREATEST(Size - 200, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 9 THEN GREATEST(Size - 250, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size - 300, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 350, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 400, 0)
        ELSE 0 
    END AS Apr,
    -- 5月（对应你预期中的Mai）
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 5 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 6 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 7 THEN GREATEST(Size - 100, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 8 THEN GREATEST(Size - 150, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 9 THEN GREATEST(Size - 200, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size - 250, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 300, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 350, 0)
        ELSE 0 
    END AS Mai,
    -- 6月
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 6 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 7 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 8 THEN GREATEST(Size - 100, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 9 THEN GREATEST(Size - 150, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size - 200, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 250, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 300, 0)
        ELSE 0 
    END AS Jun,
    -- 7月
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 7 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 8 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 9 THEN GREATEST(Size - 100, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size - 150, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 200, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 250, 0)
        ELSE 0 
    END AS Jul,
    -- 8月
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 8 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 9 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size - 100, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 150, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 200, 0)
        ELSE 0 
    END AS Aug,
    -- 9月
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 9 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 100, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 150, 0)
        ELSE 0 
    END AS Sep,
    -- 10月
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 10 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size - 50, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 100, 0)
        ELSE 0 
    END AS Oct,
    -- 11月
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 11 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size - 50, 0)
        ELSE 0 
    END AS Nov,
    -- 12月
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 12 THEN GREATEST(Size, 0)
        ELSE 0 
    END AS Dec
FROM your_table_name;

逻辑说明

EXTRACT(MONTH FROM StartDate)：提取StartDate的月份数字（1=1月，12=12月）
GREATEST(计算值, 0)：确保最终结果不会小于0，符合你“直至值≤0则为0”的规则
每个月份列的CASE语句：判断当前月份是否在StartDate月份及之后，计算对应的递减值，之前的月份直接返回0

二、将新列永久添加到表中

如果你希望这些月份列永久存在于表中，有两种方式：

方式1：手动添加并更新列

先添加列，再批量更新值：

-- 第一步：添加12个月份列
ALTER TABLE your_table_name
ADD COLUMN Jan INT DEFAULT 0,
ADD COLUMN Feb INT DEFAULT 0,
ADD COLUMN Mar INT DEFAULT 0,
ADD COLUMN Apr INT DEFAULT 0,
ADD COLUMN Mai INT DEFAULT 0,
ADD COLUMN Jun INT DEFAULT 0,
ADD COLUMN Jul INT DEFAULT 0,
ADD COLUMN Aug INT DEFAULT 0,
ADD COLUMN Sep INT DEFAULT 0,
ADD COLUMN Oct INT DEFAULT 0,
ADD COLUMN Nov INT DEFAULT 0,
ADD COLUMN Dec INT DEFAULT 0;

-- 第二步：更新列的值，逻辑和上面的查询一致
UPDATE your_table_name
SET
    Jan = CASE 
            WHEN EXTRACT(MONTH FROM StartDate) = 1 THEN GREATEST(Size, 0)
            WHEN EXTRACT(MONTH FROM StartDate) = 2 THEN GREATEST(Size - 50, 0)
            ... -- 同查询中的Jan逻辑
            ELSE 0 
          END,
    Feb = CASE 
            WHEN EXTRACT(MONTH FROM StartDate) = 2 THEN GREATEST(Size, 0)
            ... -- 同查询中的Feb逻辑
            ELSE 0 
          END,
    Mai = CASE 
            WHEN EXTRACT(MONTH FROM StartDate) = 5 THEN GREATEST(Size, 0)
            WHEN EXTRACT(MONTH FROM StartDate) = 6 THEN GREATEST(Size - 50, 0)
            ELSE 0 
          END,
    Jun = CASE 
            WHEN EXTRACT(MONTH FROM StartDate) = 6 THEN GREATEST(Size, 0)
            WHEN EXTRACT(MONTH FROM StartDate) = 7 THEN GREATEST(Size - 50, 0)
            ELSE 0 
          END
    -- 其他列按同样逻辑补充
;

方式2：使用PostgreSQL生成列（推荐，PostgreSQL 12+）

生成列会自动根据Size和StartDate的值更新，不需要手动维护：

-- 添加生成列，以Jan为例
ALTER TABLE your_table_name
ADD COLUMN Jan INT GENERATED ALWAYS AS (
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 1 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 2 THEN GREATEST(Size - 50, 0)
        ... -- 同查询中的Jan逻辑
        ELSE 0 
    END
) STORED;

-- 依次添加其他月份的生成列，比如Mai：
ALTER TABLE your_table_name
ADD COLUMN Mai INT GENERATED ALWAYS AS (
    CASE 
        WHEN EXTRACT(MONTH FROM StartDate) = 5 THEN GREATEST(Size, 0)
        WHEN EXTRACT(MONTH FROM StartDate) = 6 THEN GREATEST(Size - 50, 0)
        ELSE 0 
    END
) STORED;

-- 其他列按同样方式添加

注意事项

替换SQL中的your_table_name为你实际的表名
如果你发现结果和预期有差异（比如你示例中Project1的Mai列值为88），可能是月份标签的拼写/顺序问题，只需要调整对应CASE语句中的月份判断即可

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

PostgreSQL中基于现有列生成月度计算列的技术咨询

一、直接查询生成预期结果 #

逻辑说明 #

二、将新列永久添加到表中 #

方式1：手动添加并更新列 #

方式2：使用PostgreSQL生成列（推荐，PostgreSQL 12+） #