报表查询SQL计算加权总和、列求和及查询优化问题咨询

安全阿岳

2026-9-28

解决方案

核心优化逻辑

消除重复计算：通过CTE提前计算全局总PO金额、单条记录占比，避免SELECT子句中重复调用窗口函数、重复计算百分比
汇总值仅展示一次：使用ROLLUP生成分组汇总行，明细行不展示全局汇总字段，仅在末尾汇总行输出
重复列求和：直接在汇总行对预计算的百分比值求和即可

优化后查询代码

-- 提前计算全局总金额、单条占比，避免重复计算
WITH total_base AS (
    SELECT SUM(PO_AMOUNT) total_po FROM sample_po_data
),
detail_base AS (
    SELECT 
        PO_NUMBER,
        PO_AMOUNT,
        TASK_NAME,
        ROUND(PO_AMOUNT * 100 / tb.total_po) percent_val
    FROM sample_po_data, total_base tb
)
SELECT
    -- 明细行展示字段，汇总行留空
    NVL(TO_CHAR(PO_NUMBER), '') "PO #",
    NVL(TO_CHAR(percent_val) || '%', '') "Weighted Value",
    NVL(TO_CHAR(PO_AMOUNT), '') "PO Amount",
    NVL(TO_CHAR(percent_val) || '%', '') "Total Percent Complete",
    -- 仅汇总行展示全局统计值
    CASE WHEN GROUPING_ID(PO_NUMBER, PO_AMOUNT, percent_val) = 1 THEN TO_CHAR((SELECT total_po FROM total_base)) ELSE '' END "Total Committed And Budgeted",
    CASE WHEN GROUPING_ID(PO_NUMBER, PO_AMOUNT, percent_val) = 1 THEN TO_CHAR(SUM(percent_val)) || '%' ELSE '' END "Total Weighted / % Completed"
FROM detail_base
GROUP BY ROLLUP((PO_NUMBER, PO_AMOUNT, TASK_NAME, percent_val))
ORDER BY PO_NUMBER NULLS LAST;