>

开窗函数

- 编辑:澳门新葡亰平台游戏 -

开窗函数

          查询出来的结果

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

当over参数为null,即对负有行进行联谊总结

常用开窗函数:
1.为每条数据显示聚合新闻.(聚合函数() over())
2.为每条数据提供分组的聚合函数结出(聚合函数() over(partition by 字段) as 别名) --根据字段分组,分组后进行计算
3.与排行函数一齐使用(row number() over(order by 字段) as 外号)

 

oracle称分析函数。

叁个学习性职责:每一种人有差别次数的大成,总结出每一种人的参天战表。

图片 1

  FROM dbo.tbEmps

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

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

语法:函数名(列名) over(sql项)

        假设后续运用起来的秘籍,那么是无法满足须求的。

2.初阶化数据

与聚合函数一样,都是对行集结进行总括!但,由于group by的原因,每组只回去五个值。前面三个每组能够回到多少个值。

 

5.别样剖析函数

例子:  SELECT EmpID,DeptID,PostID, sum(PostID) OVER(PARTITION BY DeptID,PostID)
  FROM dbo.tbEmps

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

4.执行结果

SELECT    ROW_NUMBER() OVER ( ORDER BY PassportID DESC ) AS rowNum

        使用开窗函数就能够很好的消除那几个题目。

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

制止这种状态,能够选拔开窗函数。

3.将不相同班级学生按分数降序排列

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

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

1.创建测量检验表score

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

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

其一难点应有依然相对轻易,其实就用聚合函数就好了。

心得:
rank()跳跃排序,有八个第二名时前边跟着的是第四名
dense_rank() 三回九转排序,有多个第二名时依然跟着第三名

上边这种场所只适用id 和name是各类对应的,不然查询出来的数码是不准确的。

浅析:种种人学号一定是见仁见智的,名字可能有重名,最大复杂的境况是,各种班最高战表或然不断二个。

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

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

over()开窗函数: 在动用聚合函数后,会将多行产生一行,
而开窗函数是将一行成为多行;
与此同一时候在运用聚合函数后,倘使要出示任何的列必需将列出席到group by中,
而使用开窗函数后,能够不接纳group by,直接将全部消息浮现出来。

           2 张三 90

--各个班级的成就率先的学员
--学生表中新闻如下
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

例如 : 1 张三 100

开窗函数能够满意上述难点,同事也能够满意其余标题。例如:求每一种班最高战表学生的音信。

          两条音信都会输出。

本文由数据库发布,转载请注明来源:开窗函数