以下是公司培训教材,和自己的一点小总结。列举了几个常用的分析函数
ROW_NUMBER()
ROW_NUMBER() 在组内根据ORDER BY 指定的排序,为每一行分配一个唯一的行号
SELECT STUDENT_ID,NAME,COURSE,SCORE,
ROW_NUMBER() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 数学 100 1
3 机器猫 数学 80 2
4 蓝精灵 数学 80 3
5 Darwin 数学 50 4
1 灰太狼 数学 5
1 灰太狼 语文 5 1
求出各科前3名
SELECT STUDENT_ID,NAME,COURSE,SCORE,RANK
FROM (SELECT STUDENT_ID,NAME,COURSE,SCORE,
ROW_NUMBER() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE)
WHERE RANK <= 3;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 数学 100 1
3 机器猫 数学 80 2
4 蓝精灵 数学 80 3
1 灰太狼 语文 5 1
DENSE_RANK()
SELECT STUDENT_ID,NAME,COURSE,SCORE,
DENSE_RANK() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 数学 100 1
3 机器猫 数学 80 2
4 蓝精灵 数学 80 2
5 Darwin 数学 50 3
1 灰太狼 数学 4
1 灰太狼 语文 5 1
SELECT STUDENT_ID,NAME,COURSE,SCORE,RANK
FROM (SELECT STUDENT_ID,NAME,COURSE,SCORE,
DENSE_RANK() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE)
WHERE RANK <= 3;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 数学 100 1
3 机器猫 数学 80 2
4 蓝精灵 数学 80 2
5 Darwin 数学 50 3
1 灰太狼 语文 5 1
RANK()
SELECT STUDENT_ID,NAME,COURSE,SCORE,
RANK() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 数学 100 1
3 机器猫 数学 80 2
4 蓝精灵 数学 80 2
5 Darwin 数学 50 4
1 灰太狼 数学 5
1 灰太狼 语文 5 1
SELECT STUDENT_ID,NAME,COURSE,SCORE,RANK
FROM (SELECT STUDENT_ID,NAME,COURSE,SCORE,
RANK() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE)
WHERE RANK <= 3;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 数学 100 1
3 机器猫 数学 80 2
4 蓝精灵 数学 80 2
1 灰太狼 语文 5 1
LAG
SELECT STUDENT_ID,NAME,COURSE,SCORE,
LAG(SCORE,2) OVER (PARTITION BY COURSE ORDER BY SCORE) TOTAL
FROM SCORE;
STUDENT_ID NAME COURSE SCORE TOTAL
---------- -------- ------ ------- -------
5 Darwin 数学 50.00
4 蓝精灵 数学 80.00
3 机器猫 数学 80.00 50.00
2 喜羊羊 数学 99.30 80.00
1 灰太狼 数学 80.00
1 灰太狼 语文 5.00
SELECT STUDENT_ID,NAME,COURSE,SCORE,
LAG(SCORE,2,5) OVER (PARTITION BY COURSE ORDER BY SCORE) TOTAL
FROM SCORE;
STUDENT_ID NAME COUR SCORE TOTAL
---------- -------- ---- ------- -------
5 Darwin 数学 50.00 5.00
4 蓝精灵 数学 80.00 5.00
3 机器猫 数学 80.00 50.00
2 喜羊羊 数学 99.30 80.00
1 灰太狼 数学 80.00
1 灰太狼 语文 5.00 5.00