Oracle中如何用ADD_MONTHS函数获取12个月前同期数据新增列

核心逻辑说明 #

你需要匹配的是相同人员、相同业务类别下，日期恰好比当前记录早12个月的利润值，Oracle内置的ADD_MONTHS函数正好可以实现日期偏移计算，语法为ADD_MONTHS(输入日期, 偏移月份数)，偏移量传-12即可得到去年同月的对应日期。

操作步骤 #

1. 新增目标列 #

先执行DDL语句给业务表新增Same_month_last_year字段，注意把代码里的你的业务表名替换成你实际的表名称：

-- 新增数值型字段存储去年同期利润，精度可以根据你的实际利润数值调整
ALTER TABLE 你的业务表名
ADD Same_month_last_year NUMBER(12,2);

2. 批量更新字段值 #

通过自关联匹配同维度下的去年同期记录，用ADD_MONTHS做日期匹配规则，执行更新语句：

UPDATE 你的业务表名 curr_row
SET Same_month_last_year = (
    SELECT last_year_row.profit
    FROM 你的业务表名 last_year_row
    WHERE
        -- 匹配相同人员维度
        last_year_row.person = curr_row.person
        -- 匹配相同业务类别维度
        AND last_year_row.category = curr_row.category
        -- 核心日期匹配：关联记录日期 = 当前记录日期往前推12个月
        AND last_year_row.month = ADD_MONTHS(curr_row.month, -12)
);

结果验证 #

以你给出的样例数据为例：

• 第一条记录是John、类别A、统计月份为2022年6月1日，执行ADD_MONTHS(TO_DATE('Jun-1-2022','MON-DD-YYYY'), -12)会返回2021年6月1日，正好匹配到表中John、类别A、2021年6月1日的记录，取到利润值430，和你预期的结果完全一致。
• 样例中Mary的2022年5月记录、John的2021年两条记录，因为没有对应的去年同期数据，更新后Same_month_last_year字段值为NULL，属于正常情况。

注意事项 #

• 如果你的month字段存储的是字符串类型而非日期类型，需要先用TO_DATE函数转成日期格式再传入ADD_MONTHS，例如ADD_MONTHS(TO_DATE(curr_row.month, 'MON-DD-YYYY'), -12)，格式掩码要和你存储的日期字符串格式对齐。
• 如果后续需要新增数据时自动填充该字段，可以在熟悉基础语法后尝试用虚拟列或者行级触发器实现自动计算，入门阶段先使用上述手动更新的方式即可满足需求。

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