Oracle DB如何实现单记录多列值计算运行总计返回两行结果

现有存储Unique ID及12个独立月度数值字段的数据库宽表，需开发供前端调用的存储过程：传入指定Unique ID作为入参时，返回两行结果：

• 第一行：该ID对应的各月原始数值
• 第二行：各月的累计运行总计（running total，即截止当月的年初至今累计值）

当前原始月度值可通过简单SELECT语句直接查询，但累计值计算存在两个核心限制：

• 常规SUM() OVER()分析函数仅适配单列存储的时序数据，无法直接处理12个月份独立存为列的宽表结构
• 不希望采用逐列硬编码累加的冗余实现（即手动逐列编写Jan22、Jan22+Feb22、Jan22+Feb22+Mar22……的累加逻辑，维护成本高且易出错）

简化示例（以3个月度字段演示逻辑）：

1. 表结构：字段包含UniqueID、Jan22、Feb22、Mar22
2. 测试数据：ID=1的记录三个月度值分别为5000、1000、3000，ID=2的记录对应值为3000、2000、8000
3. 期望输出（传入Unique ID=1时）：第一行值为5000、1000、3000，第二行累计运行总计值为5000、6000、9000

核心思路是通过列转行→用分析函数算累计→行转列还原宽表结构的流程实现，全程不需要硬编码逐列累加逻辑，后续字段扩展维护成本极低。

以下以SQL Server环境的存储过程为例，Oracle、PostgreSQL、MySQL 8.0+均可基于相同逻辑调整语法实现：

CREATE OR ALTER PROCEDURE Get_Monthly_With_RunningTotal
    @Input_UID INT -- 入参：传入要查询的Unique ID
AS
BEGIN
    SET NOCOUNT ON;

    -- 步骤1：将指定ID的宽表月度数据转为单列时序结构
    WITH Unpivoted_Raw AS (
        SELECT 
            Month_Seq,  -- 月份排序序号，保证累计顺序和自然月一致
            Month_Val,
            Month_Col
        FROM (
            -- 此处填入需要查询的月度字段，扩展12个月时直接补字段名即可
            SELECT Jan22, Feb22, Mar22
            FROM Your_Target_Table
            WHERE UniqueID = @Input_UID
        ) src
        UNPIVOT (
            Month_Val FOR Month_Col IN (Jan22, Feb22, Mar22) -- 和上方字段列表保持一致
        ) unpvt
        -- 匹配每个月对应的排序序号
        CROSS APPLY (
            SELECT Month_Seq = CASE Month_Col
                WHEN 'Jan22' THEN 1
                WHEN 'Feb22' THEN 2
                WHEN 'Mar22' THEN 3
                -- 扩展12个月时在此处补充对应月份的序号即可
            END
        ) seq_map
    ),
    -- 步骤2：调用分析函数计算逐月累加值
    Calc_Running AS (
        SELECT
            Month_Seq,
            Month_Col,
            Origin_Value = Month_Val,
            Running_Value = SUM(Month_Val) OVER (ORDER BY Month_Seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        FROM Unpivoted_Raw
    )
    -- 步骤3：将原始值、累计值分别行转列，拼接为两行宽表结果返回
    SELECT 
        Result_Type = '原始月度值',
        Jan22, Feb22, Mar22 -- 输出列和原表字段保持一致
    FROM (SELECT Month_Col, Origin_Value FROM Calc_Running) t
    PIVOT (MAX(Origin_Value) FOR Month_Col IN (Jan22, Feb22, Mar22)) pvt
    UNION ALL
    SELECT 
        Result_Type = '累计运行总计',
        Jan22, Feb22, Mar22
    FROM (SELECT Month_Col, Running_Value FROM Calc_Running) t
    PIVOT (MAX(Running_Value) FOR Month_Col IN (Jan22, Feb22, Mar22)) pvt
END
GO

方案优势 #

• 累计逻辑完全交给原生分析函数实现，避免手动逐列写累加公式出现的笔误
• 字段扩展成本低：后续补全12个月度字段、新增下一年度月度字段时，仅需要在3处字段列表位置补充对应字段名即可，不需要修改累计计算逻辑
• 返回结果格式完全匹配前端需求，列名和原表月度字段完全一致，前端不需要做额外字段映射处理

扩展优化 #

如果业务中月度字段会定期新增，可以通过查询系统视图INFORMATION_SCHEMA.COLUMNS动态获取表内的月度字段列表，拼接动态SQL实现全自动字段适配，完全不需要手动维护字段列表。不同数据库的PIVOT/UNPIVOT语法存在细微差异：PostgreSQL可通过UNNEST数组实现列转行，Oracle的UNPIVOT语法和SQL Server基本一致，MySQL 8.0+可借助JSON函数实现相同的行列转换逻辑，核心思路完全通用。

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