SQL中的Over学习

#RANK排名函数 先建立基础数据如下：

DROP TABLE CJB; CREATE TABLE CJB (fID Number,fName VARCHAR2(30),fCourse VARCHAR2(30),fScore NUMBER); INSERT INTO CJB VALUES (1,'姜渭云','语文',80.00); INSERT INTO CJB VALUES (1,'姜渭云','数学',0.00); INSERT INTO CJB VALUES (2,'徐楚云','数学',80.00); INSERT INTO CJB VALUES (2,'徐楚云','语文',50.00); INSERT INTO CJB VALUES (2,'徐楚云','Oracle',77.00); INSERT INTO CJB VALUES (3,'冶青辉','语文',10.00); INSERT INTO CJB VALUES (3,'冶青辉','数学',0); INSERT INTO CJB VALUES (3,'冶青辉','体育',100.00); INSERT INTO CJB VALUES (3,'冶青辉','Oracle',0.00); INSERT INTO CJB VALUES (4,'樊丽花','Java',90.00); INSERT INTO CJB VALUES (4,'樊丽花','Oracle',77.00); INSERT INTO CJB VALUES (4,'樊丽花','C++',80.00); COMMIT;

###查看表数据

SELECT * FROM CJB; 1 1 姜渭云 语文 80 2 1 姜渭云 数学 0 3 2 徐楚云 数学 80 4 2 徐楚云 语文 50 5 2 徐楚云 Oracle 77 6 3 冶青辉 语文 10 7 3 冶青辉 数学 0 8 3 冶青辉 体育 100 9 3 冶青辉 Oracle 0 10 4 樊丽花 Java 90 11 4 樊丽花 Oracle 77 12 4 樊丽花 C++ 80

###查询Oracle课程的排名情况 简单排名情况 对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名)

SELECT fID,fName,fCourse,fScore, RANK() OVER (ORDER BY fScore DESC) 名次 FROM CJB WHERE fCourse = 'Oracle'; 查询结果如下： fID fName fCourse fScore 名次 ------------------------------------ 2 徐楚云 Oracle 77 1 4 樊丽花 Oracle 77 1 3 冶青辉 Oracle 0 3 -- 或者 SELECT fID,fName,fCourse,fScore, Dense_Rank() OVER (ORDER BY fScore DESC) 名次 FROM CJB WHERE fCourse = 'Oracle'; 查询结果如下： fID fName fCourse fScore 名次 ------------------------------------ 2 徐楚云 Oracle 77 1 4 樊丽花 Oracle 77 1 3 冶青辉 Oracle 0 2

RANK()是跳跃排序，有两个第1名，接下来排名为第3名； Dense_Rank()是连续排序，有两个第1名，接下来排名仍然为第2名。

###查询各学生各科排名(分区排名)

SELECT fID,fName,fCourse,fScore, RANK() OVER (PARTITION BY fCourse ORDER BY fScore DESC) 名次 FROM CJB; fID fName fCourse fScore 名次 ------------------------------------ 4 樊丽花 C++ 80 1 4 樊丽花 Java 90 1 4 樊丽花 Oracle 77 1 2 徐楚云 Oracle 77 1 3 冶青辉 Oracle 0 3 2 徐楚云 数学 80 1 1 姜渭云 数学 0 2 3 冶青辉 数学 0 2 3 冶青辉 体育 100 1 1 姜渭云 语文 80 1 2 徐楚云 语文 50 2 3 冶青辉 语文 10 3

###根据总分查询名次

WITH TSB (fID,fName,fTotal_Score) AS ( SELECT fID,fName,SUM(fScore) fTotal_Score FROM CJB GROUP BY fID,fName ) SELECT fID,fName,fTotal_Score, RANK() OVER (ORDER BY fTotal_Score DESC) 名次 FROM TSB; fID fName fTotal_Score 名次 -------------------------------- 4 樊丽花 247 1 2 徐楚云 207 2 3 冶青辉 110 3 1 姜渭云 80 4

###查询各科前2名(分区排名)

WITH QSB (fID,fName,fCourse,fScore,名次) AS ( SELECT fID,fName,fCourse,fScore, RANK() OVER (PARTITION BY fCourse ORDER BY fScore DESC) 名次 FROM CJB ) SELECT * FROM QSB WHERE 名次 <= 2; fID fName fCourse fScore 名次 ------------------------------------ 4 樊丽花 C++ 80 1 4 樊丽花 Java 90 1 2 徐楚云 Oracle 77 1 4 樊丽花 Oracle 77 1 2 徐楚云 数学 80 1 1 姜渭云 数学 0 2 3 冶青辉 数学 0 2 3 冶青辉 体育 100 1 1 姜渭云 语文 80 1 2 徐楚云 语文 50 2

###First_Value() 和Last_value()的使用

SELECT fID,fName,fCourse,fScore, First_Value(fScore) OVER (PARTITION BY fID ORDER BY fScore) low, Last_Value(fScore) OVER (PARTITION BY fID ORDER BY fScore ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) High FROM CJB; fID fName fCourse fScore low High ------------------------------------- 1 姜渭云 数学 0 0 80 1 姜渭云 语文 80 0 80 2 徐楚云 语文 50 50 80 2 徐楚云 Oracle 77 50 80 2 徐楚云 数学 80 50 80 3 冶青辉 Oracle 0 0 100 3 冶青辉 数学 0 0 100 3 冶青辉 语文 10 0 100 3 冶青辉 体育 100 0 100 4 樊丽花 Oracle 77 77 90 4 樊丽花 C++ 80 77 90 4 樊丽花 Java 90 77 90

只有使用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 这个语句，才能告知范围的取值区间是fID，否则取值区间就会按fScore来设置，结果如下：

###表示在上下两行之间的范围内

SELECT fID,fName,fCourse,fScore, SUM(fScore) OVER (ORDER BY fScore ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) FROW FROM CJB; fID fName fCourse fScore FROW DESC -------------------------------------------------------------- 3 冶青辉 Oracle 0 0 = 0 1 姜渭云 数学 0 10 = 0 + 0 + 0 + 10 3 冶青辉 数学 0 60 = 0 + 0 + 0 + 10 + 50 3 冶青辉 语文 10 137 = 0 + 0 + 10 + 50 + 77 2 徐楚云 语文 50 214 = 0 + 10 + 50 + 77 + 77 2 徐楚云 Oracle 77 294 = 10 + 50 + 77 + 77 + 80 4 樊丽花 Oracle 77 364 = 50 + 77 + 77 + 80 + 80 4 樊丽花 C++ 80 394 = 77 + 77 + 80 + 80 + 80 2 徐楚云 数学 80 407 = 77 + 80 + 80 + 80 + 90 1 姜渭云 语文 80 430 = 80 + 80 + 80 + 90 + 100 4 樊丽花 Java 90 350 = 80 + 80 + 90 + 100 3 冶青辉 体育 100 270 = 80 + 90 + 100

###表示加3或3的范围内的求和

SELECT fID,fName,fCourse,fScore, SUM(fScore) OVER (ORDER BY fScore RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) Total FROM CJB; fID fName fCourse fScore Total DESC ------------------------------------- 3 冶青辉 Oracle 0 0 =0+0+0 1 姜渭云 数学 0 0 =0+0+0 3 冶青辉 数学 0 0 =0+0+0 3 冶青辉 语文 10 10 =10 2 徐楚云 语文 50 50 =50 2 徐楚云 Oracle 77 394 =77+77+80+80+80(所加之数均在+-3之间) 4 樊丽花 Oracle 77 394 =77+77+80+80+80(所加之数均在+-3之间) 4 樊丽花 C++ 80 394 =77+77+80+80+80(所加之数均在+-3之间) 2 徐楚云 数学 80 394 =77+77+80+80+80(所加之数均在+-3之间) 1 姜渭云 语文 80 394 =77+77+80+80+80(所加之数均在+-3之间) 4 樊丽花 Java 90 90 =90 3 冶青辉 体育 100 100 =100

这是开窗子句，意为处理数据的范围为指定分区内的所有数据（从无限向前到无限向后）

例如：

OVER (ORDER BY fScore RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) OVER (ORDER BY fScore ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SUM(fScore) OVER (ORDER BY fScore ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) 表示在上下两行之间的范围内 SUM(fScore) OVER (ORDER BY fScore RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) Total 表示加3或3的范围内的求和

###其它

--lag() over()函数用法（取出前n行数据） lag(expresstion,<offset>,<default>) with a 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) from a; --lead() over()函数用法（取出后N行数据） lead(expresstion,<offset>,<default>) with a 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) from a; --ratio_to_report(a)函数用法 Ratio_to_report() 括号中就是分子，over() 括号中就是分母 with a 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 a order by a; with a 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 a --分母缺省就是整个占比 order by a; with a 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 a group by a order by a;--分组后的占比

###语法： rank() over (order by 排序字段 顺序) rank() over (partition by 分组字段 order by 排序字段 顺序)

问题：分区与分组有什么区别? 分区只是将原始数据进行名次排列(记录数不变)。 分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意：聚合。 问题：RANK()与Dense_Rank()有什么区别? Rank()是跳跃排序，非连续排序，有两个第1名，接下来排名是第3名； Dense_Rank()是连续排序，有两个第1名，接下来排名是第2名；