PostgreSQL 14中计算不同类型成功率的SQL优化咨询

原查询的问题 #

你的原查询虽能得到结果，但存在重复计算的冗余逻辑：每个ftype的成功数和总行数都要单独通过CASE WHEN过滤统计，相当于对表进行了多次逻辑扫描，当数据量较大时，性能会明显下降。

更高效的实现方式 #

方式1：分组聚合（行式输出）

先按ftype分组，一次扫描表完成所有统计，这是最基础且高效的方案：

SELECT
    ftype,
    COALESCE(
        COUNT(CASE WHEN status = 'success' THEN 1 END)::real / 
        NULLIF(COUNT(*), 0)::real,
        0
    ) AS success_rate
FROM mydata
GROUP BY ftype;

该查询仅对表做一次全表（或索引）扫描，分组后直接计算每个类型的成功率，完全避免了原查询的重复过滤逻辑。

方式2：分组聚合转列（保持原查询的列式输出）

如果需要保留原查询中foo_rate、bar_rate这类列格式，可以先通过子查询完成一次核心统计，再在外层将行转成列：

SELECT
    COALESCE(MAX(CASE WHEN ftype = 'foo' THEN success_rate END), 0) AS foo_rate,
    COALESCE(MAX(CASE WHEN ftype = 'bar' THEN success_rate END), 0) AS bar_rate,
    COALESCE(MAX(CASE WHEN ftype = 'baz' THEN success_rate END), 0) AS baz_rate
FROM (
    SELECT
        ftype,
        COUNT(CASE WHEN status = 'success' THEN 1 END)::real / 
        NULLIF(COUNT(*), 0)::real AS success_rate
    FROM mydata
    GROUP BY ftype
) AS sub_query;

这种方式同样只扫描表一次，子查询完成核心统计逻辑，外层仅做简单的行转列操作，性能远优于原查询。

PARTITION BY窗口函数的作用 #

使用窗口函数的PARTITION BY在这里帮助不大，比如：

SELECT DISTINCT
    ftype,
    COALESCE(
        SUM(CASE WHEN status = 'success' THEN 1 END) OVER (PARTITION BY ftype)::real / 
        NULLIF(COUNT(*) OVER (PARTITION BY ftype), 0)::real,
        0
    ) AS success_rate
FROM mydata;

窗口函数会为表中每一行计算对应的分组统计值，最后还要通过DISTINCT去重，相比GROUP BY的聚合方式，额外增加了计算和去重开销，仅在极小表场景下性能差异可忽略，大表场景下反而更慢。

额外性能优化：索引 #

如果mydata表数据量较大，可创建联合索引加速统计：

CREATE INDEX idx_mydata_ftype_status ON mydata(ftype, status);

该索引能让Postgres直接通过索引扫描完成分组和统计，避免全表扫描，进一步提升查询速度。

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