开窗函数

发布时间:2019-07-28  栏目:NoSQL  评论: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地图