MySQL 优化百分比/比例计算

概览

通常使用 SQL 语句求百分比时，需求扫描表两次，一次是总数一次是满足条件的个数

其实可以使用 INTERVAL 函数优化，只扫描表一次（在表很大时，几乎可以少花费一半的时间）

示例

假设有一个分数表 TScore
，包含班级，学号，平均分这3个字段（class, no, score
）

现在想求每个班级的及格率（>=60人数/总人数*100）

一般方法

select Total.class, Hit.num  Total.num * 100 as pass_rate
from
(select class, count(1) as num from TScore group by class) Total
left join
(select class, count(1) as num from TScore where score >= 60 group by class) Total
on Total.class = Hit.class

复制

可以看出，需要扫描表 TScore
两次

优化后

select A.class, sum(if(A.i_s = 1, A.num, 0))  sum(A.num) * 100 as pass_rate
(select class, INTERVAL(score, 60) as i_s, count(1) as num from TScore group by class, i_s) A
group by A.class

复制

可以看出，只需要扫描表 TScore
一次，但需要对子查询结果 A 扫描两次

（INTERVAL
后分数大于等于60的索引为1，所以 sum(if(A.i_s = 1, A.num, 0))
只对及格人数求和）

结论

当 TScore
较大，而子查询结果 A 较小时，适合用这种方法优化