【文章內(nèi)容簡介】 00 11000 30 115 3100 11400 30 116 2900 10800 30 117 2800 10800 30 118 2600 5100 30 119 2500 5100 40 203 6500 6500 50 121 8200 24100 50 120 8000 15900 50 122 7900 15900 50 123 6500 6500 50 124 5800 5800 因為 salary desc,所以 100 preceding含義是比當(dāng)前行的 salary大 100，以 employee_id=117為例，也就是起點是 2800+100=2900,終點是比當(dāng)前行小 500， 2800500=2300。那么employee_id=117行分析函數(shù)計算的窗口范圍是 employee_id between 116 and 119。 16 當(dāng)前行對應(yīng)的分析函數(shù)結(jié)果是根據(jù)當(dāng)前行對應(yīng)的窗口（邏輯 OR物理）范圍計算的。 理解窗口的含義對分析函數(shù)的使用至關(guān)重要 。窗口只有起點的，起點必須是 CURRENT ROW之前（包含 CURRENT ROW）。窗口內(nèi)數(shù)據(jù)移動順序必須是按照排序順序，從上到下，否則要么報錯，要么結(jié)果有問題。 示例 說明 ROWS/RANGE UNBOUNDED PRECEDING 終點 CURRENT ROW，相當(dāng)于 BETWEEN 起點 BTEWEEN CURRENT ROW ROWS/RANGE CURRENT ROW 相當(dāng)于 BETWEEN CURRENT ROW AND CURRENT ROW ROWS/RANGE value_expr PRECEDING 相當(dāng)于 BETWEEN value_expr PRECEDING AND CURRENT ROW value_expr FOLLOWING 報錯 ,無效窗口 表 1只指定起點的 表 2有起點和終點 示例 說明 ROWS/RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 和無 ORDER BY一樣，等價于沒有寫 ORDER By和WINDOW子句 ROWS/RANGE BETWEEN CURRENT ROW AND value_expr FOLLOWING 當(dāng)前行到對應(yīng) value_exp偏移量的行 ROWS/RANGE BETWEEN value_expr FOLLOWING AND value_expr1 FOLLOWING 起點是 FOLLOWING,終點必須是 FOLLOWING ROWS/RANGE BETWEEN value_expr PRECEDING AND value_expr1 FOLLOWING 終點是 PRECEDING,起點必須是 PRECEDING ROWS/RANGE BETWEEN CURRENT ROW AND value_expr PRECEDING 報錯 ,無效窗口 17 分類 內(nèi)容 說明 使用頻率 排名分析函數(shù) ROW_NUMBER、 RANK、DENSE_RANK、 FIRST、 LAST、 LAST_VALUE,FIRST_VALUE FIRST,LAST不應(yīng)該算做函數(shù)，它主要與聚合函數(shù)聯(lián)合使用 強(qiáng) 聚合分析函數(shù) AVG、 COUNT、MAX,MIN,SUM 強(qiáng) 行比較分析函數(shù) LEAD、 LAG 解決當(dāng)前行與前后行之間的關(guān)系 中 統(tǒng)計分析函數(shù) RATIO_TO_REPORT 解決報表占比問題 中 行連接分析函數(shù) LISTAGG 11g新特性 中 其他分析函數(shù) CORR、 CORVAR_POP、CORVAR_SAMP、 CUME_DIST、NTH_VALUE、 NTILE、PERCENT_RANK、PERCENTILE_CONT、PERCENTILE_DISC、 REGR_ (Linear Regression) Functions、STDDEV、 STDDEV_POP、STDDEV_SAMP 、 VAR_POP,VAR_SAMP、VARIANCE 弱 按照各種分析函數(shù)使用頻率以及業(yè)務(wù)特點，將分析函數(shù)分為排名分析函數(shù)、聚合分析函數(shù)、行比較分析函數(shù)、統(tǒng)計分析函數(shù)、行連接分析函數(shù)、其他分析函數(shù)（ 基本都是數(shù)學(xué)統(tǒng)計函數(shù)，很少使用 ）。很多分析函數(shù)，同時也可以作為普通的組函數(shù)使用， 有的分析函數(shù)，比如 FIRST,LAST、 LISTAGG，很多時候，主要用它的普通分組函數(shù)功能，而不是它的分析函數(shù)功能。 排名分析函數(shù)有 row_number,dense_rank,rank,first,last,first_value,last_value等 ，其中row_number,dense_rank,rank,first,last都需要 order by,first_value和 last_value可以不指定。 SELECT empno,sal,deptno, row_number() over(PARTITION BY deptno ORDER BY sal) row_rn , rank() over(PARTITION BY deptno ORDER BY sal) rank_rn, dense_rank() over(PARTITION BY deptno ORDER BY sal) dense_rn FROM emp EMPNO SAL DEPTNO ROW_RN RANK_RN DENSE_RN 7934 1300 10 1 1 1 7782 2450 10 2 2 2 7839 5000 10 3 3 3 7369 800 20 1 1 1 7876 1100 20 2 2 2 7566 2975 20 3 3 3 7902 3000 20 4 4 4 7788 3000 20 5 4 4 7900 950 30 1 1 1 7521 1250 30 2 2 2 7654 1250 30 3 2 2 7844 1500 30 4 4 3 7845 1500 30 5 4 3 7846 1500 30 6 4 3 7499 1600 30 7 7 4 7698 2850 30 8 8 5 19 在 9i以及之前的版本中使用 row_number做分頁，會有一定的問題，它沒有做到謂詞推進(jìn)， 10g之后已經(jīng)做了優(yōu)化。 SELECT empno,ename,job,mgr,deptno FROM ( SELECT empno,ename,job,mgr,deptno FROM ORDER BY empno ) WHERE ROWNUM10。 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 9 | 468 | 2 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 9 | 468 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| EMP | 12 | 300 | 2 (0)| 00:00:01 | | 4 | INDEX FULL SCAN | PK_EMP | 9 | | 1 (0)| 00:00:01 | Predicate Information (identified by operation id): 1 filter(ROWNUM10) Statistics 1 recursive calls 0 db block gets 4 consistent gets SELECT empno,ename,job,mgr,deptno FROM ( SELECT a.*,row_number() over(ORDER BY empno) rn FROM a ) WHERE rn10。 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 12 | 780 | 2 (0)| 00:00:01 | |* 1 | VIEW | | 12 | 780 | 2 (0)| 00:00:01 | |* 2 | WINDOW NOSORT STOPKEY | | 12 | 300 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| EMP | 12 | 300 | 2 (0)| 00:00:01 | | 4 | INDEX FULL SCAN | PK_EMP | 12 | | 1 (0)| 00:00:01 | Predicate Information (identified by operation id): 1 filter(RN10) 2 filter(ROW_NUMBER() OVER ( ORDER BY EMPNO)10) Statistics 0 recursive calls 0 db block gets 4 consistent gets FIRST/LAST可以作為組函數(shù)和分析函數(shù)，組函數(shù)不帶 OVER,分析函數(shù)帶 OVER,它必須結(jié)合KEEP關(guān)鍵字， KEEP關(guān)鍵字就是起到一個語義的作用：說明按照指定的排序規(guī)則找到 TOP 1或BOTTOM 1，因為是 DENSE_RANK,所以 TOP1和 BOTTOM1是個集合，然后