开窗函数

发布时间:2019-02-02  栏目:NoSQL  评论:0 Comments

SQL查询语句如下:
select *
from
(
select name,class,s,rank()over(partition by class order by s desc) mm
from t2

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

        使用开窗函数就能很好的缓解这么些题材。

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

) as t
where t.mm=1

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

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

1.创立测试表score

下面那种气象只适用id 和name是各样对应的,否则查询出来的数码是不科学的。

4.履行结果

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

5.别样分析函数

开窗函数可以满意上述难点,同事也可以满足其余题材。例如:求每个班最高战绩学生的音信。

3.将分歧班级学生按分数降序排列

例如 : 1 张三 100

 

           2 张三 90

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

查询结果如下:
c 1 95 1
e 2 92 1
f 3 99 1
g 3 99 1

图片 1

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

2.初阶化数据

over()开窗函数: 在使用聚合函数后,会将多行变成一行,
而开窗函数是将一行成为多行;
再就是在行使聚合函数后,借使要来得别的的列必须将列参与到group by中,
而利用开窗函数后,可以不利用group by,直接将享有音讯展现出来。

          查询出来的结果

防止那种景观,可以选择开窗函数。

          两条音讯都会输出。

剖析:每个人学号一定是见仁见智的,名字可能有重名,最大复杂的处境是,每个班最高成绩或者不断一个。

常用开窗函数:
1.为每条数据突显聚合音讯.(聚合函数() over())
2.为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as
别名) –依据字段分组,分组后展开计算
3.与名次函数一起使用(row number() over(order by 字段) as 别名)

一个学习性职分:每个人有两样次数的成绩,总结出各种人的最高战表。

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

        假设后续应用起来的方法,那么是无法满足须求的。

以此标题应当如故相对简便易行,其实就用聚合函数就好了。

村办知道就是,开窗函数和聚合函数功用是倒转的。

 

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

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

留下评论

网站地图xml地图