菜单

开窗函数 –over()

2019年8月23日 - MySQL

三个学习性义务:每一种人有两样次数的战表,总计出每一种人的最高战表。

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.执行结果

          两条新闻都会输出。

图片 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地图