Oracle 的开窗函数 rank,dense_rank,row_number

Wesley13
• 阅读 663

1、开窗函数和分组函数的区别

分组函数是指按照某列或者某些列分组后进行某种计算,比如计数,求和等聚合函数进行计算。

开窗函数是指基于某列或某些列让数据有序,数据行数和原始数据数相同,依然能曾现个体数据的原貌。

事例数据

create table student_scores(
stu_id varchar2(10),--学号
stu_name varchar2(20),--姓名
course varchar2(5),--课程
score number(5,2),--分数
constraint pk_stuid_course primary key (stu_id,course)
);

insert into student_scores(stu_id,stu_name,course,score) values('10001','周杰伦','语文','85');
insert into student_scores(stu_id,stu_name,course,score) values('10001','周杰伦','数学','96');
insert into student_scores(stu_id,stu_name,course,score) values('10001','周杰伦','英语','67');
insert into student_scores(stu_id,stu_name,course,score) values('10002','谢霆锋','语文','71');
insert into student_scores(stu_id,stu_name,course,score) values('10002','谢霆锋','数学','97');
insert into student_scores(stu_id,stu_name,course,score) values('10002','谢霆锋','英语','100');
insert into student_scores(stu_id,stu_name,course,score) values('10003','苏有朋','语文','79');
insert into student_scores(stu_id,stu_name,course,score) values('10003','苏有朋','数学','65');
insert into student_scores(stu_id,stu_name,course,score) values('10003','苏有朋','英语','80');
insert into student_scores(stu_id,stu_name,course,score) values('10004','刘德华','语文','41');
insert into student_scores(stu_id,stu_name,course,score) values('10004','刘德华','数学','61');
insert into student_scores(stu_id,stu_name,course,score) values('10004','刘德华','英语','62');
insert into student_scores(stu_id,stu_name,course,score) values('10005','小龙女','语文','94');
insert into student_scores(stu_id,stu_name,course,score) values('10005','小龙女','数学','95');
insert into student_scores(stu_id,stu_name,course,score) values('10005','小龙女','英语','96');

2、分组函数:group by 

按照课程进行分组,可以进行如下计算

select course,count(*) cou,avg(score) avgs,max(score) maxs,min(score) mins
from student_scores
group by course;

COURSE

COU

AVGS

MAXS

MINS

1

数学

5

82.8

97

61

2

语文

5

74

94

41

3

英语

5

81

100

62

3、开窗函数:rank() ,dense_rank(),row_number()

每个学科按照分数倒序排名 

select stu_id,stu_name,course,score,rank() over(partition by course order by score desc) rank

from student_scores;

STU_ID

STU_NAME

COURSE

SCORE

RANK

1

10002

谢霆锋

数学

97.00

1

2

10001

周杰伦

数学

96.00

2

3

10005

小龙女

数学

95.00

3

4

10003

苏有朋

数学

65.00

4

5

10004

刘德华

数学

61.00

5

6

10002

谢霆锋

英语

100.00

1

7

10005

小龙女

英语

96.00

2

8

10003

苏有朋

英语

80.00

3

9

10001

周杰伦

英语

67.00

4

10

10004

刘德华

英语

62.00

5

11

10005

小龙女

语文

94.00

1

12

10001

周杰伦

语文

85.00

2

13

10003

苏有朋

语文

79.00

3

14

10002

谢霆锋

语文

71.00

4

15

10004

刘德华

语文

41.00

5

 注意分组函数和开窗函数针对数据在维度上的变化,分组函数使个体数据小时。开窗函数保留个体。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
3年前
Lua基础(对象)
:和.区别.   stu{id100,name"Tom",age21}成员变量   function stu.toString()成员函数    return stu.id .. stu.name .. stu.age   endprint(stu
Stella981 Stella981
3年前
R语言的xtabs函数
今天在做一个列联表独立性检验的时候,总是无法处理好要求的数据类型,偶然的机会,看到了xtabs()函数,感觉很适合用来做列联表,适合将一列数据转换成列联表。shifou<c("yes","yes","no","no")xinbie<c("nan","nv","nan","nv")freq<c(34,38,28
Stella981 Stella981
3年前
JS 对象数组Array 根据对象object key的值排序sort,很风骚哦
有个js对象数组varary\{id:1,name:"b"},{id:2,name:"b"}\需求是根据name或者id的值来排序,这里有个风骚的函数函数定义:function keysrt(key,desc) {  return function(a,b){    return desc ? ~~(ak
Wesley13 Wesley13
3年前
CAST()函数可以进行数据类型的转换。
CAST()函数可以进行数据类型的转换。CAST()函数的参数有两部分,源值和目标数据类型,中间用AS关键字分隔。以下例子均通过本人测试。一、转换列或值语法:cast(列名/值as数据类型)用例:1)、转换列\将empno的类型(number)转换为varchar2类型。selectcast(empnoasv
Wesley13 Wesley13
3年前
mysql——GROUP BY和HAVING
GROUPBY语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。select子句中的列名必须为分组列或列函数,列函数对于groupby子句定义的每个组返回一个结果。某个员工信息表结构和数据如下:  id  name  dept  salary  edlevel     hiredate   1  张
Wesley13 Wesley13
3年前
Oracle 行列转换函数pivot、unpivot的使用(二)
一、行转列pivot关键函数pivot,其用法如下pivot(聚合函数for列名in(类型))select\fromtable\_namepivot(max(column\_name)                           行转列后的列的值value,聚合函数是必须要有的                  
Wesley13 Wesley13
3年前
2、Linux汇编——函数的工作原理
第四章函数的工作原理1、函数的组成部分    函数主要由以下几个成分组成:函数名、函数参数、局部变量、静态变量、全局变量、返回地址、返回值(1)函数参数及几个变量:这是在逻辑上对函数的涉及到的数据进行规划,实际上当前运行的指令只能通过直接、间接、立即数三种方式访问数据。(2)返回地址:在汇编语言中,实际上是某
混世魔王 混世魔王
1年前
皕杰报表之数据集函数
所谓数据集函数就是与数据集相关,从数据集取数的函数。这些函数不仅可以将数据直接从数据集取出,而且可以将取出的数据分组、求和、求最大值最小值、求第一条数据和最后一条数据、求前n条数据以及对取出的数据进行按段分割,还能对列和记录进行统计。在皕杰报表中共提供了1