Oracle中按子组计算12个月滚动累计值的实现方案

一、实现分组的12个月滚动累计 #

先修正查询逻辑，核心是通过PARTITION BY LGroup实现分组独立计算，组切换时累计自动重置。同时用Oracle的区间范围窗口精准计算12个月滚动（避免因缺失月份导致的行数计算误差）。

假设你的Product表结构为：

CREATE TABLE Product (
    LGroup VARCHAR2(50),
    MonthDate DATE,
    Sales NUMBER
);

正确的滚动累计查询语句 #

SELECT
    LGroup,
    MonthDate,
    Sales,
    SUM(Sales) OVER (
        PARTITION BY LGroup
        ORDER BY MonthDate
        RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW
    ) AS Rolling12Sales
FROM Product
ORDER BY LGroup, MonthDate;

关键细节：

• PARTITION BY LGroup：将数据按LGroup拆分为独立分组，每个组单独计算滚动累计，组间互不干扰，切换分组时自动重置累计值。
• RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW：定义滚动窗口为当前日期往前推11个月至当前月，刚好覆盖12个月周期。如果数据每月固定一条记录，用ROWS BETWEEN 11 PRECEDING AND CURRENT ROW也可行，但区间范围方式更适配可能存在的月份缺失场景。

二、将计算结果写入表 #

1. 写入新表 #

要将结果保存到全新表中，直接使用CREATE TABLE ... AS SELECT最便捷：

CREATE TABLE Product_Rolling AS
SELECT
    LGroup,
    MonthDate,
    Sales,
    SUM(Sales) OVER (
        PARTITION BY LGroup
        ORDER BY MonthDate
        RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW
    ) AS Rolling12Sales
FROM Product;

如果需要保留原表的主键、约束等结构，可先创建空表再插入数据：

-- 复制原表空结构
CREATE TABLE Product_Rolling AS SELECT * FROM Product WHERE 1=0;
-- 添加滚动累计列
ALTER TABLE Product_Rolling ADD Rolling12Sales NUMBER;
-- 插入计算后的数据
INSERT INTO Product_Rolling (LGroup, MonthDate, Sales, Rolling12Sales)
SELECT
    LGroup,
    MonthDate,
    Sales,
    SUM(Sales) OVER (
        PARTITION BY LGroup
        ORDER BY MonthDate
        RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW
    ) AS Rolling12Sales
FROM Product;
COMMIT;

2. 更新原表（添加滚动累计列） #

要将结果写入原表，先新增存储累计值的列，再用MERGE语句更新（比普通UPDATE效率更高）：

-- 第一步：给原表添加滚动累计列
ALTER TABLE Product ADD Rolling12Sales NUMBER;

-- 第二步：用MERGE更新数据
MERGE INTO Product t
USING (
    SELECT
        LGroup,
        MonthDate,
        SUM(Sales) OVER (
            PARTITION BY LGroup
            ORDER BY MonthDate
            RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW
        ) AS Rolling12Sales
    FROM Product
) s
ON (t.LGroup = s.LGroup AND t.MonthDate = s.MonthDate)
WHEN MATCHED THEN
    UPDATE SET t.Rolling12Sales = s.Rolling12Sales;
COMMIT;

三、扩展：计算区域总计 #

如果后续需要按区域（假设为LGroup的上级分类）计算总计，直接基于分组后的滚动累计再聚合即可：

SELECT
    Region, -- 假设存在Region字段，或从LGroup推导上级分类
    MonthDate,
    SUM(Rolling12Sales) AS Region_Rolling12Total
FROM Product_Rolling -- 或使用已更新滚动累计列的原表
GROUP BY Region, MonthDate
ORDER BY Region, MonthDate;

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