OVER(PARTITION BY)開窗函數用法

開窗函數，Oracle從8.1.6開始提供開窗函數，開窗函數用于計算基于組的某種聚合值，它和聚合函數的不同之處是：

對于每個組返回多行，而聚合函數對于每個組只返回一行。

開窗函數指定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變化而變化。

開窗的窗口范圍

-- 窗口范圍為當前行數據幅度減3加3后的范圍內的.

/*SELECT

T1.STUDENT_NAME

,T1.CLASS_NAME

,T1.COURSE_SCORE

,SUM(T1.COURSE_SCORE) OVER(ORDER BY COURSE_SCORE RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) SUM_SCORE

FROM V_RPT_COURSE_STUDENT T1

WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'

AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'

AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88'*/

/*1 高蓉蓉 1班 76 155 76,79

2 雷蕾 1班 79 237 76,79,82

3 楊正坤 1班 82 331 79,82,85,85

4 李永承 1班 85 252 82,85,85

5 敬金鳳 1班 85 252 82,85,85 */

窗口范圍為當前行前后各移動1,0行

/*SELECT

T1.STUDENT_NAME

,T1.CLASS_NAME

,T1.COURSE_SCORE

,SUM(T1.COURSE_SCORE) OVER(ORDER BY COURSE_SCORE ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) SUM_SCORE

FROM V_RPT_COURSE_STUDENT T1

WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'

AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'

AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88'

*/

/*1 高蓉蓉 1班 76 76 76

2 雷蕾 1班 79 155 76,79

3 楊正坤 1班 82 161 79,82

4 李永承 1班 85 167 82,85

5 敬金鳳 1班 85 170 85,85 */

窗口不做限制

/*SELECT

T1.STUDENT_NAME

,T1.CLASS_NAME

,T1.COURSE_SCORE

,SUM(T1.COURSE_SCORE) OVER(ORDER BY COURSE_SCORE RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SUM_SCORE1

,SUM(T1.COURSE_SCORE) OVER(ORDER BY COURSE_SCORE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SUM_SCORE2

,SUM(T1.COURSE_SCORE) OVER() SUM_SCORE3

FROM V_RPT_COURSE_STUDENT T1

WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'

AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'

AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88'*/

/*1 高蓉蓉 1班 76 407 407 407

2 雷蕾 1班 79 407 407 407

3 楊正坤 1班 82 407 407 407

4 李永承 1班 85 407 407 407

5 敬金鳳 1班 85 407 407 407*/

與OVER函數結合的幾個函數介紹

/*與OVER函數結合的幾個函數介紹

ROW_NUMBER() OVER()、RANK() OVER()和DENSE_RANK() OVER()函數的使用

RANK 跳躍排序

DENSE_RANK 連續排序

*/

/*SELECT T.*

,RANK() OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE DESC) SCORE_RANK

,ROW_NUMBER() OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE DESC) SCORE_ROW_NUMBER

,DENSE_RANK() OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE DESC) SCORE_DENSE_RANK

,SUM(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE DESC) SCORE_SUM

FROM (

SELECT

T1.STUDENT_NAME

,T1.CLASS_NAME

,T1.COURSE_SCORE

FROM V_RPT_COURSE_STUDENT T1

WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'

AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'

AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88' ) T

*/

/*1 敬金鳳 1班 85 1 1 1 170

2 李永承 1班 85 1 2 1 170

3 楊正坤 1班 82 3 3 2 252

4 雷蕾 1班 79 4 4 3 331

5 高蓉蓉 1班 76 5 5 4 407*/

-- 加上IGNORE NULLS后，如果第一條是判斷的那個字段是空的，則默認取下一條

-- FIRST_VALUE和LAST_VALUE常用在計算排過序的結果集中的最大值和最小值。

SELECT T.*

,FIRST_VALUE(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) LOW_SCORE1

,FIRST_VALUE(T.COURSE_SCORE IGNORE NULLS) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) LOW_SCORE2

,LAST_VALUE(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HIGH_SCORE1

,LAST_VALUE(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) HIGH_SCORE2

,MIN(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) MIN_SCORE1

,MAX(T.COURSE_SCORE) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.COURSE_SCORE) MAX_SCORE1

,MIN(T.COURSE_SCORE) OVER() MIN_SCORE2

,MAX(T.COURSE_SCORE) OVER() MAX_SCORE2

FROM (

SELECT

T1.STUDENT_NAME

,T1.CLASS_NAME

,T1.COURSE_SCORE

FROM V_RPT_COURSE_STUDENT T1

WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'

AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'

AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88' ) T

/*1 高蓉蓉 1班 76 76 76 85 76 76 76 76 85

2 雷蕾 1班 79 76 76 85 79 76 79 76 85

3 楊正坤 1班 82 76 76 85 82 76 82 76 85

4 李永承 1班 85 76 76 85 85 76 85 76 85

5 敬金鳳 1班 85 76 76 85 85 76 85 76 85*/

LAG() OVER()函數用法(取出前N行數據)

WITH T AS

(SELECT 1 ID ,'A' NAME FROM DUAL

UNION

SELECT 2 ID ,'B' NAME FROM DUAL

UNION

SELECT 3 ID ,'C' NAME FROM DUAL

UNION

SELECT 4 ID ,'D' NAME FROM DUAL

UNION

SELECT 5 ID ,'E' NAME FROM DUAL

)

SELECT ID,NAME,LAG(ID,1,'') OVER(ORDER BY NAME) AS LAG_ID FROM T;

/*1 A

2 B 1

3 C 2

4 D 3

5 E 4

*/

LEAD() OVER()函數用法(取出后N行數據)

WITH T AS

(SELECT 1 ID,'A' NAME FROM DUAL

UNION

SELECT 2 ID,'B' NAME FROM DUAL

UNION

SELECT 3 ID,'C' NAME FROM DUAL

UNION

SELECT 4 ID,'D' NAME FROM DUAL

UNION

SELECT 5 ID,'E' NAME FROM DUAL

)

SELECT ID,NAME,LEAD(ID,1,'') OVER(ORDER BY NAME) AS LEAD_ID FROM T;

/*1 A 2

2 B 3

3 C 4

4 D 5

5 E */

計算總數百分比

-- RATIO_TO_REPORT(A)函數用法 RATIO_TO_REPORT() 括號中就是分子，OVER() 括號中就是分母

-- 計算總數百分比

WITH T AS (SELECT 1 A FROM DUAL

UNION ALL

SELECT 1 A FROM DUAL

UNION ALL

SELECT 1 A FROM DUAL

UNION ALL

SELECT 2 A FROM DUAL

UNION ALL

SELECT 3 A FROM DUAL

UNION ALL

SELECT 4 A FROM DUAL

UNION ALL

SELECT 4 A FROM DUAL

UNION ALL

SELECT 5 A FROM DUAL

)

SELECT A

, RATIO_TO_REPORT(A) OVER(PARTITION BY A) B FROM T

ORDER BY A;

/*1 1 0.333333333333333 1/3

2 1 0.333333333333333 1/3

3 1 0.333333333333333 1/3

4 2 1 1/1

5 3 1 1/1

6 4 0.5 1/2

7 4 0.5 1/2

8 5 1 1/1 */

分母缺省就是整個占比

WITH T AS (

SELECT 1 A FROM DUAL

UNION ALL

SELECT 1 A FROM DUAL

UNION ALL

SELECT 1 A FROM DUAL

UNION ALL

SELECT 2 A FROM DUAL

UNION ALL

SELECT 3 A FROM DUAL

UNION ALL

SELECT 4 A FROM DUAL

UNION ALL

SELECT 4 A FROM DUAL

UNION ALL

SELECT 5 A FROM DUAL

)

SELECT A, RATIO_TO_REPORT(A) OVER() B FROM T

ORDER BY A;

/*

1 1 0.0476190476190476 1/21

2 1 0.0476190476190476 1/21

3 1 0.0476190476190476 1/21

4 2 0.0952380952380952 2/21

5 3 0.142857142857143 3/21

6 4 0.19047619047619 4/21

7 4 0.19047619047619 4/21

8 5 0.238095238095238 5/21

21 1

*/

分組后的占比

WITH T AS (

SELECT 1 A FROM DUAL

UNION ALL

SELECT 1 A FROM DUAL

UNION ALL

SELECT 1 A FROM DUAL

UNION ALL

SELECT 2 A FROM DUAL

UNION ALL

SELECT 3 A FROM DUAL

UNION ALL

SELECT 4 A FROM DUAL

UNION ALL

SELECT 4 A FROM DUAL

UNION ALL

SELECT 5 A FROM DUAL

)

SELECT A, RATIO_TO_REPORT(A) OVER() B FROM T

GROUP BY A ORDER BY A;

/*

1 1 0.0666666666666667 1/15

2 2 0.133333333333333 2/15

3 3 0.2 3/15

4 4 0.266666666666667 4/15

5 5 0.333333333333333 5/15

15 1

*/

評級函數

用于等級、百分點、n分片等。

PERCENT_RANK 返回某個值相對于一組值的百分比排名

CUME_DIST 返回特定值對于一組值的位置“CUMULATIVE DISTRIBUTION”(累積分布)

反百分比函數

PERCENTILE_CONT 輸入一個百分比(該百分比就是按照PERCENT_RANK函數計算的值)，返回該百分比位置的平均值

PERCENTILE_DISC 返回一個與輸入的分布百分比值相對應的數據值，分布百分比的計算方法見函數CUME_DIST，如果沒有正好對應的數據值，就取大于該分布值的下一個值。

SELECT T.*

,PERCENT_RANK() OVER (PARTITION BY CLASS_NAME ORDER BY COURSE_SCORE ) PR1

,PERCENT_RANK() OVER (ORDER BY COURSE_SCORE ASC ) PR2

,CUME_DIST() OVER(PARTITION BY CLASS_NAME ORDER BY COURSE_SCORE ) PR3

,PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY COURSE_SCORE) OVER(PARTITION BY CLASS_NAME) PERCENTILE_CONT

,PERCENTILE_DISC(0.6) WITHIN GROUP(ORDER BY COURSE_SCORE) OVER(PARTITION BY CLASS_NAME) PERCENTILE_DISC

FROM (

SELECT

T1.STUDENT_NAME

,T1.CLASS_NAME

,T1.COURSE_SCORE

FROM V_RPT_COURSE_STUDENT T1

WHERE T1.EXAM_COURSE_ID = '730F9DDBD98543F2B595D3898F86AC67'

AND T1.EXAM_ID = '8793AF04C83F468A89BE12229257D65A'

AND T1.CLASS_ID = 'B3469FDB0F674523AACF1C3F2E015A88' ) T

/*1 高蓉蓉 1班 76 0 0 0.2 79 82

2 雷蕾 1班 79 0.25 0.25 0.4 79 82

3 楊正坤 1班 82 0.5 0.5 0.6 79 82

4 李永承 1班 85 0.75 0.75 1 79 82

5 敬金鳳 1班 85 0.75 0.75 1 79 82*/

序號

函數

說明

1

RANK()

返回數據項在分組中的排名，排名相等會在名次中留下空位

2

DENSE_RANK()

返回數據項在分組中的排名，排名相等會在名次中不會留下空位

3

CUME_DIST()

返回特定值對于一組值的位置“cumulative distribution”(累積分布) ，累積分布,即行在組中的相對位置,返回0 ~ 1

4

PERCENT_RANK()

返回某個值相對于一組值的百分比排名，類似CUME_DIST,1/(行的序數 - 1)

5

NTILE()

返回n分片后的值

6

ROW_NUMBER()

為每條記錄返回一個數字

7

DENSE_RANK

行的相對排序(與ORDER BY搭配),相同的值具有一樣的序數(NULL計為相同),并不留空序數

8

FIRST_VALUE

一個組的第一個值

9

LAST_VALUE

一個組的最后一個值

10

LAG(expr, , )

訪問之前的行,OFFSET是缺省為1 的正數,表示相對行數,DEFAULT是當超出選定窗范圍時的返回值(如第一行不存在之前行)

11

LEAD(expr, , )

訪問之后的行,OFFSET是缺省為1 的正數,表示相對行數,DEFAULT是當超出選定窗范圍時的返回值(如最后行不存在之前行)

12

STDDEV(expr)

標準差

13

STDDEV_POP(expr)

總體標準差

14

STDDEV_SAMP(expr)

樣本標準差

15

VARIANCE(expr)

方差

16

VAR_POP(expr)

總體方差

17

VAR_SAMP(expr)

樣本方差

18

COVAR_POP(expr, expr)

總體協方差

19

COVAR_SAMP(expr, expr)

樣本協方差

20

RATIO_TO_REPORT(expr)

表達式值 / SUM(表達式值)

總結

以上是生活随笔為你收集整理的oracle 排序的分析函数,Oracle分析函数用法详解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。