菜单

开窗函数

2019年1月30日 - sqlite

开窗函数可以满意上述难题,同事也得以知足其他难点。例如:求每个班最高成绩学生的新闻。

5.其他分析函数

一个学习性任务:每个人有分裂次数的大成,计算出各类人的参天成绩。

2.早先化数据

) as t
where t.mm=1

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 ...)

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

4.推行结果

心得:
rank()跳跃排序,有多少个第二名时后面跟着的是第四名
dense_rank() 两次三番排序,有四个第二名时依旧跟着第三名

manbetx网页手机登录版,3.将差距班级学生按分数降序排列

        若是延续选拔起来的章程,那么是不可能满足要求的。

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

剖析:每个人学号一定是不一致的,名字或许有重名,最大复杂的场所是,每个班最高成绩或者不断一个。

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);

          查询出来的结果

manbetx网页手机登录版 1

 

1.成立测试表score

例如 : 1 张三 100

 

聚合函数,将多行数据统一成一行数据;而开窗函数则是将一行数据拆分成多行。

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

上边那种情景只适用id 和name是逐一对应的,否则查询出来的数码是不正确的。

           2 张三 90

常用开窗函数:
1.为每条数据展现聚合新闻.(聚合函数() over())
2.为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as
别名) –根据字段分组,分组后开展统计
3.与名次函数一起利用(row number() over(order by 字段) as 别名)

幸免那种场合,可以动用开窗函数。

          两条音信都会输出。

开窗函数适用于在每一行的尾声一列添加聚合函数的结果。

over()开窗函数: 在拔取聚合函数后,会将多行变成一行,
而开窗函数是将一行成为多行;
再就是在行使聚合函数后,假诺要来得其他的列必须将列到场到group by中,
而利用开窗函数后,可以不行使group by,直接将装有音信体现出来。

常用分析函数:(最常用的应有是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,第多少个参数是
超出记录窗口时的默许值)

–每个班级的成就率先的学生
–学生表中音讯如下
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

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图