DB2中实现两条SELECT语句查询结果相除的CTE使用方法

你可以通过定义两个CTE分别存储两个查询的结果，再关联计算比值，完整可运行代码如下：

WITH -- 定义CTE存储查询1的结果，仅返回单条总计数
q1_total AS (
    SELECT count(*) AS q1_total
    FROM ltl400tst3.frp001
    JOIN ETLLIB.CYMD_TO_DATE
        ON fhpdat = cymd_date
    LEFT JOIN ltl400mod3.pup092
        ON pcpro = fhpro
    LEFT JOIN LTL400MOD3.PUP090
        ON PCNUM = PANUM AND PCTID = PATID
    LEFT JOIN ltl400mod3.pup300 
        ON pctid = cotid AND pcnum = conum AND pcline = coline AND fhccd = coccd
    WHERE fhbtc IN ('6A16579', '6R16579') 
      AND PASTAT != 'C' 
      AND DATE = '2021-09-01'
),
-- 定义CTE存储查询2的明细统计结果
q2_detail AS (
    SELECT 
        Date, 
        CASE cqtype 
            WHEN 'O' THEN 'Override' 
            WHEN 'S' THEN 'Alert' 
            ELSE 'Other' 
        END record_type, 
        count(*) AS q2_total
    FROM LTL400MOD3.PUP301
    LEFT JOIN LTL400MOD3.PUP300
        ON COTID = CQTID AND CONUM = CQNUM AND COLINE = CQLINE
    JOIN ETLLIB.CYMD_TO_DATE
        ON (CAST(CQRSSTMP AS DATE)) = DATE
    LEFT JOIN MASTER.ACCOUNT_HIERARCHY
        ON COCCD = ACCOUNT
    WHERE ((CQSACIID = '17') OR (TOP_LEVEL_ACCOUNT = '0P16579')) 
      AND DATE = '2021-09-01'
    GROUP BY date, CQTYPE
)
-- 主查询关联两个CTE计算比值
SELECT 
    q2.Date,
    q2.record_type,
    q2.q2_total,
    q1.q1_total,
    -- 乘以1.0避免整数除法，用NULLIF避免除以0报错
    q2.q2_total * 1.0 / NULLIF(q1.q1_total, 0) AS ratio
FROM q2_detail q2
-- q1仅返回1行，直接笛卡尔关联不会影响q2的行数
CROSS JOIN q1_total q1;

关键说明 #

• 由于查询1仅返回单条计数结果，使用CROSS JOIN关联不会额外增加查询2的行数，关联逻辑简单高效
• 运算时添加*1.0是为了规避DB2默认的整数除法规则，否则两个整数相除会默认截断小数部分返回整数结果
• NULLIF(q1.q1_total, 0)的作用是当查询1返回结果为0时，自动返回空值避免除以0的报错
• 如果需要修改统计日期，建议单独新增一个参数CTE统一管理日期，避免两处修改出现不一致：

  WITH param AS (
      SELECT '2021-09-01' AS stat_date FROM SYSIBM.SYSDUMMY1
  ),
  q1_total AS (
      -- 原有查询1的WHERE条件中DATE改为引用param.stat_date
      ...
      AND DATE = (SELECT stat_date FROM param)
  ),
  q2_detail AS (
      -- 原有查询2的WHERE条件中DATE改为引用param.stat_date
      ...
      AND DATE = (SELECT stat_date FROM param)
  )
  -- 后续主查询逻辑不变
  

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