开窗函数 –over()

发布时间:2019-02-04  栏目:sqlite  评论: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.实施结果

          两条信息都会输出。

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