菜单

开窗函数

2019年1月28日 - 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地图