澳门太阳娱乐集团官网-太阳集团太阳娱乐登录

开窗函数
分类:数据库

一个学习性任务:每个人有不同次数的成绩,统计出每个人的最高成绩。

1.创建测试表score

oracle称分析函数。

这个问题应该还是相对简单,其实就用聚合函数就好了。

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

与聚合函数相同,都是对行集合进行计算!但,由于group by的原因,每组只返回一个值。前者每组可以返回多个值。

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

2.初始化数据

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

上边这种情况只适用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);

当over参数为null,即对所有行进行聚合计算

例如 : 1 张三 100

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

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

           2 张三 90

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

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

          查询出来的结果

4.执行结果

  FROM dbo.tbEmps

          两条信息都会输出。

图片 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,第三个参数是 超出记录窗口时的默认值)

本文由澳门太阳娱乐集团官网发布于数据库,转载请注明出处:开窗函数

上一篇:没有了 下一篇:php连接微软MSSQL(sql server)完全计谋
猜你喜欢
热门排行
精彩图文