Posted on 2009-08-03 18:11
AntiquMan 閱讀(322)
評論(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
|