单表查询-分析函数的应用

1、问题

最近遇到一个有趣的优化问题，本身的需求是统计每个人超过他自己分数的人数，并显示自身分数情况。
于是开发商就把当前的需求直译成如下sql
select a.*,(select count(1) from test0824 b where a.score>b.score) as cnt,rownum rn from test0824 a
order by score desc

这样的语句主查询越大，即（test0824表数据量越大）查询越慢，这里相当于每次主查询查出一条数据，就要和子查询比较a.score>b.score，子查询有多少条数据就比较多少次，实际上的效率就是主查询条数*子查询数据量，可以说效率低下
那怎么办呢？

2、需求

这个需求到底是什么意思，实际上就是分数排名，这里我们可以用到rank()over()进行处理

3 、优化改写

select a.*,rank()over(order by score desc)-1 as cnt,rownum rn from test0824 a
order by SCORE asc

执行时间：

排完序后减1就是计算超过分数的人数。查询性能提升几倍以上

4、小结

（1）优化的思路其实就是减少比较次数即扫描表的次数。
（2）当拿到一个需求，应该合理分析需求，寻求更快地效率去实现，而非直译。