Oracle两层嵌套SQL查询优化：取批次最后完成任务及PO完成率

将PO金额统计的窗口求和逻辑下沉到最内层查询后，后续筛选last_task_finish_seq=1的操作会过滤掉同批次下其他PO关联行，导致金额统计仅基于筛选后的单条任务行，若该行不符合po_status=101或po_type=100的条件，就会出现求和结果为0的错误。

SELECT DISTINCT ON (custom_lot_no)
    custom_lot_no,
    -- 最后修改的已完成任务相关字段
    task_id,
    task_name,
    task_finish_time,
    task_modify_time,
    -- 全批次已完成PO总额
    SUM(CASE WHEN po_status = 101 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no) AS finished_po_amount,
    -- 全批次总承诺PO总额
    SUM(CASE WHEN po_type = 100 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no) AS committed_po_amount,
    -- 完成率，添加除0防护
    CASE 
        WHEN SUM(CASE WHEN po_type = 100 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no) = 0 THEN 0 
        ELSE ROUND(
            SUM(CASE WHEN po_status = 101 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no) 
            / SUM(CASE WHEN po_type = 100 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no)
            ,4
        )
    END AS po_completion_rate
FROM project_complete_data
-- 过滤已完成的任务，可替换为实际的任务完成判定条件
WHERE task_status = '已完成'
-- 同批次内按任务修改时间倒序，DISTINCT ON会自动保留每个分组的第一条
ORDER BY custom_lot_no, task_modify_time DESC;

SELECT 
    custom_lot_no,
    task_id,
    task_name,
    task_finish_time,
    finished_po_amount,
    committed_po_amount,
    po_completion_rate
FROM (
    SELECT 
        custom_lot_no,
        task_id,
        task_name,
        task_finish_time,
        SUM(CASE WHEN po_status = 101 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no) AS finished_po_amount,
        SUM(CASE WHEN po_type = 100 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no) AS committed_po_amount,
        CASE 
            WHEN SUM(CASE WHEN po_type = 100 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no) = 0 THEN 0 
            ELSE ROUND(
                SUM(CASE WHEN po_status = 101 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no) 
                / SUM(CASE WHEN po_type = 100 THEN po_total_amount ELSE 0 END) OVER (PARTITION BY custom_lot_no)
                ,4
            )
        END AS po_completion_rate,
        -- 同批次已完成任务按修改时间倒序排序
        ROW_NUMBER() OVER (PARTITION BY custom_lot_no ORDER BY task_modify_time DESC) AS rn
    FROM project_complete_data
    WHERE task_status = '已完成'
) t
WHERE rn = 1;

• 所有窗口计算在同一层级完成，数据源为全量已完成任务关联PO行，保证PO统计覆盖同批次所有符合要求的行
• 两个窗口计算逻辑互相独立：按批次分区统计全批次PO金额汇总，同时按批次分区给已完成任务按修改时间倒序排序
• 最后仅过滤排序首位的行，此时PO统计值已计算完成，不会受过滤操作影响
• 相比原两层嵌套写法，仅需一次原表扫描，执行效率更高，结构更简洁

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