Posted on 2009-08-03 18:11
AntiquMan 阅读(314)
评论(0) 编辑 收藏 所属分类:
Oracle
上周在优化预警分析的工作中，发现由于分页功能需要提供总条数 ，项目组内的普遍做法是进行两次sql查询，一次用count(*)获得总条数，一次获取真正的展现数据。其实oracle提供了olap函数对此进行优化，可通过伪列：count(*) over()获得当前sql的总条数。
比如：select t.*,count(*) over() from dual 会返回总条数为1。
olap函数主要用于统计分析，熟练掌握能很好的提高sql执行效率。
count(*) over() 具体功能描述如下：
对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。
SAMPLE：下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水
例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数
sql如下：
SELECT last_name, salary, COUNT(*) OVER () AS cnt1,
COUNT(*) OVER (ORDER BY salary) AS cnt2,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS cnt3 FROM employees;
结果如下 ：
| LAST_NAME |
SALARY |
CNT2 |
CNT2 |
CNT3 |
| Olson |
2100 |
107 |
1 |
3 |
| Markle |
2200 |
107 |
3 |
2 |
| Philtanker |
2200 |
107 |
3 |
2 |
| Landry |
2400 |
107 |
5 |
8 |
| Gee |
2400 |
107 |
5 |
8 |
| Colmenares |
2500 |
107 |
11 |
10 |
| Patel |
2500 |
107 |
10 |
10
|