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>, <default>) | 访问之前的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如第一行不存在之前行) |
| 11 | LEAD(expr, <offset>, <default>) | 访问之后的行,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(表达式值) |
