manbet手机客户端3.0开窗函数

发布时间:2018-12-14  栏目:SQL  评论:0 Comments

一个学习性任务:每个人发出不同次数的成就,总括出每个人之危成绩。

1.开立测试表score

其一题材应有要相对简便易行,其实就是用聚合函数就好了。

create table score(
class_no varchar2(10),      --班级
student_name varchar2(20),  --姓名
score number                --分数
);

select id,name,max(score) from Student group by id,name order by name

2.最先化数据

下边这种场合无非适用id 和name是逐一对应的,否则查询出来的数据是休科学的。

insert into score(class_no,student_name,score) values('n001','park',99);
insert into score(class_no,student_name,score) values('n001','ning',99);
insert into score(class_no,student_name,score) values('n001','tom',79);
insert into score(class_no,student_name,score) values('n001','cat',87);
insert into score(class_no,student_name,score) values('n001','sandy',95);
insert into score(class_no,student_name,score) values('n002','cake',85);
insert into score(class_no,student_name,score) values('n002','mavom',69);
insert into score(class_no,student_name,score) values('n002','tony',90);
insert into score(class_no,student_name,score) values('n002','lisa',99);
insert into score(class_no,student_name,score) values('n002','linda',67);
insert into score(class_no,student_name,score) values('n003','versy',84);
insert into score(class_no,student_name,score) values('n003','peter',97);
insert into score(class_no,student_name,score) values('n003','train',83);
insert into score(class_no,student_name,score) values('n003','rain',80);

例如 : 1 张三 100

3.用不同班级学生仍分数降序排列

           2 张三 90

select *
  from (select class_no,
               student_name,
               score,
               rank() over(partition by class_no order by score desc)
          from score) t;

          查询出来的结果

4.执结果

          两修信息都会合输出。

manbet手机客户端3.0 1

避这种情形,可以行使开窗函数。

5.别分析函数

村办了然就是是,开窗函数和聚合函数功用是倒转的。

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

聚合函数,将大半执数据统一成为一行数;而开窗函数则是用一行数拆分成多举办。

 

开窗函数可以满足上述问题,同事也可满意其他问题。例如:求每个班最高战表学生的消息。

剖析:每个人学号一定是见仁见智的,名字或出重名,最深复杂的情事是,每个班最高战表或者不断一个。

        如若后续拔取起来之点子,那么是未克满意要求的。

        使用开窗函数就会很好之化解这题目。

–每个班级之实绩率先的学习者
–学生表中新闻如下
a 1 80
b 1 78
c 1 95
d 2 74
e 2 92
f 3 99
g 3 99
h 3 45
i 3 55
j 3 78

查询结果如下:
c 1 95 1
e 2 92 1
f 3 99 1
g 3 99 1

SQL查询语句如下:
select *
from
(
select name,class,s,rank()over(partition by class order by s desc) mm
from t2

) as t
where t.mm=1

 

心得:
rank()跳跃排序,有个别个次誉为时后止跟着的是第四名叫
dense_rank() 连续排序,有些许个伯仲叫作时依然跟着第三叫作

over()开窗函数: 在运用聚合函数后,会将大半尽变成一行,
苟开窗函数是拿一行成为多行;
而于以聚合函数后,就算只要出示任何的排必须以列在到group by中,
假若拔取开窗函数后,可以不选取group by,直接用有着消息显示出。

开窗函数适用于在各种一行的末梢一排上加聚合函数的结果。

常用开窗函数:
1.为各条数据显示聚合信息.(聚合函数() over())
2.呢各级条数提供分组的聚合函数结果(聚合函数() over(partition by 字段) as
别名) –遵照字段分组,分组后展开测算
3.及排行函数一起利用(row number() over(order by 字段) as 别名)

常用分析函数:(最常用的该是1.2.3 的排序)
1、row_number() over(partition by … order by …)
2、rank() over(partition by … order by …)
3、dense_rank() over(partition by … order by …)
4、count() over(partition by … order by …)
5、max() over(partition by … order by …)
6、min() over(partition by … order by …)
7、sum() over(partition by … order by …)
8、avg() over(partition by … order by …)
9、first_value() over(partition by … order by …)
10、last_value() over(partition by … order by …)
11、lag() over(partition by … order by …)
12、lead() over(partition by … order by …)
lag 和lead 可以获取结果集中,按自然排序所排列的近期实施之左右相邻若干offset
的有行的某部列(不用结果集的于干);
lag ,lead 分别是进,向后;
lag 和lead
有五只参数,第一只参数是列名,第二独参数是偏移的offset,第三单参数是
超出记录窗口时之默认值)

留下评论

网站地图xml地图