开窗函数 –over()

八个学习性职责:各类人有例外交部次官数的成就,总括出各类人的万丈战绩。

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
有多少个参数,第叁个参数是列名,第1个参数是偏移的offset,首个参数是
凌驾记录窗口时的私下认可值)

发表评论

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

网站地图xml地图