最简单的方法是CROSS JOIN，因为整体计算的结果是单行，这不会乘以最终的行数，而是将两个想要的列添加到每行中。

SELECT
      specific.POD
    , specific.DEFINITION
    , specific.error_rate AS POD_JOB_RATE
    , general.error_rate AS "General Rate for this JOB"
    , oall.av_err_rate 
    , oall.sdev_err_rate
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
        )
    /* don't do this  ORDER BY POD, DEFINITION, failures / total_run, total_run  DESC */
    ) specific ON specific.DEFINITION = general.DEFINITION
CROSS JOIN (
    SELECT avg(error_rate) av_err_rate , stddev(error_rate) sdev_err_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
            )
        )
    ) oall
WHERE specific.error_rate > general.error_rate
ORDER BY POD, DEFINITION;

字符串

nb不要在子查询中使用order by，这是浪费精力，只需使用最后的order by来控制结果顺序。

另外：你可以使用“窗口函数”来简化你的查询，例如：

WITH error_rate
AS (
    SELECT POD
        , DEFINITION
        , COUNT(*) AS total_run
        , COUNT(CASE WHEN STATE IN (10, 15, 19) THEN requestid END) AS failures
        , COUNT(CASE WHEN STATE IN (10, 15, 19) THEN requestid END) / COUNT(*) AS error_rate
    FROM FILTERED_REQUEST_HISTORY
    GROUP BY POD
        , DEFINITION
    )
    , general_error_rate
AS (
    SELECT DEFINITION
        , AVG(error_rate) OVER (PARTITION BY DEFINITION) AS general_error_rate
    FROM error_rate
    )
    , overall_error_rate
AS (
    SELECT AVG(error_rate) OVER () AS av_err_rate
        , STDDEV(error_rate) OVER () AS sdev_err_rate
    FROM error_rate
    )
SELECT e.POD
    , e.DEFINITION
    , e.error_rate AS POD_JOB_RATE
    , g.general_error_rate AS "General Rate for this JOB"
    , o.av_err_rate
    , o.sdev_err_rate
FROM error_rate e
JOIN general_error_rate g ON e.DEFINITION = g.DEFINITION
CROSS JOIN overall_error_rate o
WHERE e.error_rate > g.general_error_rate
ORDER BY e.POD, DEFINITION;

型
甚至可以将其组合以减少数据的传递：

WITH error_rate
AS (
    SELECT POD
        , DEFINITION
        , COUNT(*) AS total_run
        , COUNT(CASE WHEN STATE IN (10, 15, 19) THEN requestid END) AS failures
        , COUNT(CASE WHEN STATE IN (10, 15, 19) THEN requestid END) * 1.0 / COUNT(*) AS error_rate
        , AVG(COUNT(CASE WHEN STATE IN (10, 15, 19) THEN requestid END) * 1.0 / COUNT(*)) OVER (PARTITION BY DEFINITION) AS general_error_rate
        , AVG(COUNT(CASE WHEN STATE IN (10, 15, 19) THEN requestid END) * 1.0 / COUNT(*)) OVER () AS av_err_rate
        , STDDEV(COUNT(CASE WHEN STATE IN (10, 15, 19) THEN requestid END) * 1.0 / COUNT(*)) OVER () AS sdev_err_rate
    FROM FILTERED_REQUEST_HISTORY
    GROUP BY POD
        , DEFINITION
    )
SELECT POD
    , DEFINITION
    , error_rate AS POD_JOB_RATE
    , general_error_rate AS "General Rate for this JOB"
    , av_err_rate
    , sdev_err_rate
FROM error_rate
WHERE error_rate > general_error_rate
ORDER BY POD, DEFINITION;

型
引入ps* 1.0以确保十进制结果

不要使用JOIN，使用解析函数来执行“一般”计算，并计算平均值和标准差：

SELECT *
FROM   (
  SELECT pod,
         definition,
         pod_job_rate,
         general_job_rate,
         AVG(CASE rn WHEN 1 THEN general_job_rate END) OVER () AS avg_all_pods,
         STDDEV(CASE rn WHEN 1 THEN general_job_rate END) OVER () AS stddev_all_pods
  FROM   (
    SELECT pod,
           definition,
           ROUND(failures/total_run, 4) AS pod_job_rate,
           ROUND(
             SUM(failures) OVER (PARTITION BY definition)
             / SUM(total_run) OVER (PARTITION BY definition),
             4
           ) AS general_job_rate,
           ROW_NUMBER() OVER (PARTITION BY definition ORDER BY ROWNUM) AS rn
    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
    )
  )
)
--WHERE  pod_job_rate > general_job_rate
ORDER BY pod;

字符串
或者，您可以完全使用分析函数（而不使用聚合函数）：

SELECT *
FROM   (
  SELECT pod,
         definition,
         pod_job_rate,
         general_job_rate,
         avg_all_pods,
         STDDEV(CASE def_rn WHEN 1 THEN general_job_rate END) OVER ()
           AS stddev_all_pods
  FROM   (
    SELECT pod,
           definition,
           ROUND(
             AVG(
               CASE
               WHEN state IN (10, 15, 19) AND requestid IS NOT NULL
               THEN 1
               ELSE 0
               END
             ) OVER (PARTITION BY pod, definition),
             4
           ) AS pod_job_rate,
           ROUND(
             AVG(
               CASE
               WHEN state IN (10, 15, 19) AND requestid IS NOT NULL
               THEN 1
               ELSE 0
               END
             ) OVER (PARTITION BY definition),
             4
           ) AS general_job_rate,
           ROUND(
             AVG(
               CASE
               WHEN state IN (10, 15, 19) AND requestid IS NOT NULL
               THEN 1
               ELSE 0
               END
             ) OVER (),
             4
           ) AS avg_all_pods,
           ROW_NUMBER() OVER (PARTITION BY pod, definition ORDER BY ROWNUM) AS rn,
           ROW_NUMBER() OVER (PARTITION BY definition ORDER BY ROWNUM) AS def_rn
    FROM   FILTERED_REQUEST_HISTORY
  )
  WHERE  rn = 1
)
WHERE  pod_job_rate > general_job_rate
ORDER BY pod;

型
fiddle