SQL中的Over学习

来源：互联网发布：成龙张蓝心知乎编辑：程序博客网时间：2024/06/01 23:55

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   姜渭云 语文  802   1   姜渭云 数学  03   2   徐楚云 数学  804   2   徐楚云 语文  505   2   徐楚云 Oracle  776   3   冶青辉 语文  107   3   冶青辉 数学  08   3   冶青辉 体育  1009   3   冶青辉 Oracle  010  4   樊丽花 Java    9011  4   樊丽花 Oracle  7712  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      14   樊丽花 Oracle  77      13   冶青辉 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      14   樊丽花 Oracle  77      13   冶青辉 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      14   樊丽花 Java    90      14   樊丽花 Oracle  77      12   徐楚云 Oracle  77      13   冶青辉 Oracle  0       32   徐楚云 数学      80      11   姜渭云 数学      0       23   冶青辉 数学      0       23   冶青辉 体育      100     11   姜渭云 语文      80      12   徐楚云 语文      50      23   冶青辉 语文      10      3

根据总分查询名次

WITH TSB (fID,fName,fTotal_Score) AS(  SELECT fID,fName,SUM(fScore) fTotal_Score    FROM CJBGROUP BY fID,fName)SELECT fID,fName,fTotal_Score, RANK() OVER (ORDER BY fTotal_Score DESC) 名次  FROM TSB;fID fName   fTotal_Score    名次--------------------------------4   樊丽花 247             12   徐楚云 207             23   冶青辉 110             31   姜渭云 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      14   樊丽花 Java    90      12   徐楚云 Oracle  77      14   樊丽花 Oracle  77      12   徐楚云 数学      80      11   姜渭云 数学      0       23   冶青辉 数学      0       23   冶青辉 体育      100     11   姜渭云 语文      80      12   徐楚云 语文      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   801   姜渭云 语文      80      0   802   徐楚云 语文      50      50  802   徐楚云 Oracle  77      50  802   徐楚云 数学      80      50  803   冶青辉 Oracle  0       0   1003   冶青辉 数学      0       0   1003   冶青辉 语文      10      0   1003   冶青辉 体育      100     0   1004   樊丽花 Oracle  77      77  904   樊丽花 C++     80      77  904   樊丽花 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) FROWFROM CJB;fID fName   fCourse fScore  FROW    DESC----------------------------------------------------3   冶青辉 Oracle  0       0       =01   姜渭云 数学      0       10      =0+0+0+103   冶青辉 数学      0       60      =0+0+0+10+503   冶青辉 语文      10      137     =0+0+10+50+772   徐楚云 语文      50      214     =0+10+50+77+772   徐楚云 Oracle  77      294     =10+50+77+77+804   樊丽花 Oracle  77      364     =50+77+77+80+804   樊丽花 C++     80      394     =77+77+80+80+802   徐楚云 数学      80      407     =77+80+80+80+901   姜渭云 语文      80      430     =80+80+80+90+1004   樊丽花 Java    90      350     =80+80+90+1003   冶青辉 体育      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) TotalFROM CJB;fID fName   fCourse fScore  Total   DESC-------------------------------------3   冶青辉 Oracle  0       0       =0+0+01   姜渭云 数学      0       0       =0+0+03   冶青辉 数学      0       0       =0+0+03   冶青辉 语文      10      10      =102   徐楚云 语文      50      50      =502   徐楚云 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      =903   冶青辉 体育      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 allselect 1 a from dual           union  allselect 1 a from dual           union allselect 2 a from dual           union all select 3 a from dual           union allselect 4 a from dual           union allselect 4 a from dual           union allselect 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 allselect 1 a from dual           union  allselect 1 a from dual           union allselect 2 a from dual           union all select 3 a from dual           union allselect 4 a from dual           union allselect 4 a from dual           union allselect 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 allselect 1 a from dual           union  allselect 1 a from dual           union allselect 2 a from dual           union all select 3 a from dual           union allselect 4 a from dual           union allselect 4 a from dual           union allselect 5 a from dual           )select a, ratio_to_report(a)over() b from agroup 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名；

阅读全文

0 0