如何在PL/SQL中将多结果集与单结果集合并

需求是将各POD、任务定义的错误率明细，与全局错误率的平均值（avg）、标准差（stddev）合并为一个结果集，方便后续绘图。现有两个查询：

1. 错误率明细查询 #

该查询获取各POD、任务定义的错误率，以及对应任务的全局错误率：

select specific.POD, specific.DEFINITION, specific.error_rate as POD_JOB_RATE, general.error_rate as "General Rate for this JOB" 
from (
    select definition, total_run,failures,  round(failures/total_run,4) as error_rate 
    from (
        select
        DEFINITION,
        count(*)                                           as total_run,
        count(case when state in (10, 15, 19) then requestid end) as failures
        from FILTERED_REQUEST_HISTORY group by DEFINITION
    ))  general
join (
    select POD, DEFINITION, round(failures/total_run) as error_rate 
    from (
        select
        POD,
        DEFINITION,
        count(*)                                                  as total_run,
        count(case when state in (10, 15, 19) then requestid end) as failures
        from FILTERED_REQUEST_HISTORY group by POD, DEFINITION
    ) order by POD, DEFINITION, failures/total_run, total_run desc
)  specific
ON specific.DEFINITION = general.DEFINITION 
where specific.error_rate > general.error_rate 
order by POD;

输出字段：POD、DEFINITION、POD_JOB_RATE、General Rate for this JOB

2. 全局错误率统计查询 #

该查询计算所有任务全局错误率的平均值和标准差：

select avg(error_rate), stddev(error_rate) 
from (
    select failures/total_run as error_rate 
    from (
        select
        DEFINITION,
        count(*)                                           as total_run,
        count(case when state in (10, 15, 19) then requestid end) as failures
        from FILTERED_REQUEST_HISTORY group by DEFINITION
    )
) avg_error_rate_by_jd;

输出字段：AVG(error_rate)、STDDEV(error_rate)

解决方案 #

要实现合并，只需将明细查询的结果**交叉连接（CROSS JOIN）**全局统计查询的结果即可。因为全局统计只有一行数据，交叉连接会自动为每一行明细添加上对应的全局avg和stddev。

同时可以用公共表表达式（CTE）复用重复的子查询逻辑，提升查询效率：

WITH job_global_stats AS (
    -- 计算每个任务的全局错误率，复用这个CTE供后续查询使用
    select 
        DEFINITION,
        count(*) as total_run,
        count(case when state in (10, 15, 19) then requestid end) as failures,
        round(failures::numeric / count(*), 4) as error_rate
    from FILTERED_REQUEST_HISTORY 
    group by DEFINITION
),
global_avg_std AS (
    -- 基于job_global_stats计算全局的avg和stddev
    select 
        round(avg(error_rate), 4) as "avg (all pods)",
        round(stddev(error_rate), 4) as "stddev (all pods)"
    from job_global_stats
)
-- 合并明细与全局统计
select 
    specific.POD, 
    specific.DEFINITION, 
    specific.error_rate as POD_JOB_RATE, 
    general.error_rate as "General Rate for this JOB",
    global."avg (all pods)",
    global."stddev (all pods)"
from job_global_stats general
join (
    select 
        POD, 
        DEFINITION,
        round(failures::numeric / count(*), 4) as error_rate 
    from FILTERED_REQUEST_HISTORY 
    group by POD, DEFINITION
) specific ON specific.DEFINITION = general.DEFINITION
cross join global_avg_std global
where specific.error_rate > general.error_rate 
order by POD;

关键说明： #

• CROSS JOIN 在这里是安全的，因为global_avg_std只有一行数据，不会产生笛卡尔积冗余
• 使用CTEjob_global_stats避免了重复计算任务全局错误率的逻辑，减少了表扫描次数
• 把整数除法转换为数值除法（failures::numeric / count(*)），避免整数截断导致的错误率计算偏差

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