Oracle SQL优化：如何用更简洁方式计算分组阈值占比（替代WITH子句）

问题场景 #

现有数据表结构及数据如下：

datekey (number) | value (numeric) | other | values
20230101           10003
20230101           -3
20230101           282345
20230101           -3
20230101           285
20230102           10001
20230102           -3
20230102           22345
20230103           3
20230103           -3
20230103           282345

需要实现：按DATEKEY/100（月份维度）分组，计算每组中value低于阈值（此处为1000）的记录数占该组总记录数的比例。原实现使用了WITH子句进行两次子查询，现需要更简洁的写法。

简洁解决方案 #

方案1：CASE表达式+聚合函数（推荐） #

无需WITH子句，一次分组查询即可完成所有计算，仅扫描一次表，效率更高：

SELECT 
    DATEKEY/100 AS MONTH,
    COUNT(*) AS total,
    COUNT(CASE WHEN value < 1000 THEN 1 END) AS missing,
    COUNT(CASE WHEN value < 1000 THEN 1 END)/COUNT(*) AS perc
FROM mytable
WHERE DATEKEY/100 >= 202111
GROUP BY DATEKEY/100
ORDER BY MONTH DESC;

逻辑说明：

• COUNT(*)：统计每组的总记录数
• COUNT(CASE WHEN value < 1000 THEN 1 END)：当value小于1000时返回1，否则返回NULL；COUNT函数会自动忽略NULL值，最终得到每组中符合条件的记录数
• 直接通过符合条件数/总数计算占比，结果与原查询完全一致

方案2：SUM替代COUNT实现 #

逻辑与方案1一致，只是用SUM统计符合条件的数量：

SELECT 
    DATEKEY/100 AS MONTH,
    COUNT(*) AS total,
    SUM(CASE WHEN value < 1000 THEN 1 ELSE 0 END) AS missing,
    SUM(CASE WHEN value < 1000 THEN 1 ELSE 0 END)/COUNT(*) AS perc
FROM mytable
WHERE DATEKEY/100 >= 202111
GROUP BY DATEKEY/100
ORDER BY MONTH DESC;

关于ratio_to_report的使用 #

ratio_to_report函数用于计算某值在分组中的占比，但在此场景下，它无法简化查询逻辑，反而需要先统计出总数和符合条件数后再使用，写法如下：

WITH group_stats AS (
    SELECT 
        DATEKEY/100 AS MONTH,
        COUNT(*) AS total,
        COUNT(CASE WHEN value < 1000 THEN 1 END) AS missing
    FROM mytable
    WHERE DATEKEY/100 >= 202111
    GROUP BY DATEKEY/100
)
SELECT 
    MONTH,
    total,
    missing,
    RATIO_TO_REPORT(missing) OVER (PARTITION BY MONTH) AS perc
FROM group_stats
ORDER BY MONTH DESC;

该写法的结果与直接除法一致，但并未简化查询，因此推荐使用方案1或2。

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