开窗函数有浅入好详解(一)

发布时间:2018-12-18  栏目:sqlite  评论:0 Comments

以开窗函数出现前是在众多于是 SQL
语句很为难化解之题材,很多还要经复杂的相互关子查询或者存储过程来好。为了缓解这些题材,在2003年ISO 
SQL标准在了开窗函数,开窗函数的运用让这一个经典的难题可以给轻松的解决。

1.开立测试表score

即在 MSSQLServer、Oracle、DB2
等主流数据库被都提供了针对开窗函数的扶助,可是好不满之是 MYSQL
暂时还不对开窗函数给予扶助。

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

为更加明亮地通晓,我们来建表并进行有关的查询(截图也MSSQLServer中之结果)

2.起头化数据

        MYSQL,MSSQLServer,DB2:       

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);
CREATE TABLE T_Person  
( 
  FName VARCHAR(20), 
  FCity VARCHAR(20),  
  FAge INT, 
  FSalary INT 
)  

3.以不同班级学生仍分数降序排列

        Oracle:

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

      

4.履结果

复制代码 代码如下:

图片 1

 CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20), FAge
INT,FSalary INT)

5.其他分析函数

注:以下结果就当MSSQLServer中示范:

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

T_Person 表保存了人士信息,FName 字段为人口姓名,FCity
字段为人口所当的城市名,
FAge  字段为人口年纪,FSalary 字段为人口工资。

 

然后实施下的SQL语句向 T_Person表中插入一些示范数据:    

INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Tom','BeiJing',20,3000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Tim','ChengDu',21,4000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Jim','BeiJing',22,3500);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Lily','London',21,2000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('John','NewYork',22,1000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('YaoMing','BeiJing',20,3000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Swing','London',22,2000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Guo','NewYork',20,2800);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('YuQian','BeiJing',24,8000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Ketty','London',25,8500);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Kitty','ChengDu',25,3000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Merry','BeiJing',23,3500);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Smith','ChengDu',30,3000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Bill','BeiJing',25,2000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Jerry','NewYork',24,3300);  

翻表中的始末:

复制代码 代码如下:

select * from T_Person

图片 2

开窗函数简介

  与 聚
合函数一样,开窗函数也是指向行集组举行联谊统计,不过它不像普通聚合函数那么每组只回一个价,开窗函数可以为每组再次来到多独价值,因为开窗函数所实施聚合总计的行集组是窗口。

以ISO SQL规定了这般的函数为开窗函数,在
Oracle中即使被誉为分析函数,而于DB2中虽让称为OLAP函数。

比方总结有所人士的总额,大家可以推行下的 SQL语句:

复制代码 代码如下:

SELECT COUNT(*) FROM T_Person

        
除了那种比简单的利用形式,有时需要从不以聚合函数吃的行中访问这些聚集总结的值。比如我们想询问每个工资低于
5000第一的职工音信(城市跟年)
,并且于每行中还突显有工资低于5000首之员工个数,尝试编写下面的
SQL语句:

SELECT FCITY , FAGE , COUNT(*) 

FROM T_Person 

HERE FSALARY<5000 

  执行方的SQL将来大家会得下面的错误信息:

择列表中之列  ‘T_Person.FCity’
无效,因为该列没有包含在聚合函数或者 GROUP BY 子句被。

  这是为有不含在聚合函数着的排列必须申明在GROUP BY 子句被,
足展开如下修改:

SELECT FCITY, FAGE, COUNT(*) 

FROM T_Person 

WHERE FSALARY<5000 

GROUP BY FCITY , FAGE 

  执行了大家就能于输出结果丁视上边的行结果:       

图片 3

     这些执行结果及我们怀念像的凡了两样之,这是因GROUP 
BY子句针对结果集举行了分组,所以聚合函数举行测算的靶子不再是具的结果集,而是每一个分组。

好通过子查询来解决这些题材,SQL如下:

SELECT FCITY , FAGE , 
( 
 SELECT COUNT(* ) FROM T_Person 
 WHERE FSALARY<5000 
) 
FROM T_Person 
WHERE FSALARY<5000

  执行了我们虽可知以出口结果丁看出下面的履结果:

图片 4

 
尽管使用子查询可以缓解此题材,可是子查询的以分外累,使用开窗函数则好大大简化实现,下边的SQL语句展示了使以开窗函数来促成平等的效果:

SELECT FCITY , FAGE , COUNT(*) OVER() 
FROM T_Person 
WHERE FSALARY<5000 

 执行完毕我们不怕可以在输出结果受来看底的行结果:

图片 5

好望跟聚合函数不同之是,开窗函数在聚合函数后搭了一个OVER
关键字。

开窗函数的调用格式为:

函数名(列) OVER(选项)

    OVER   关键字表示把函数当成开窗函数而不是聚合函数。SQL 
标准允许将富有聚合函数用做开窗函数,使用OVER 关键字来分这简单种用法。

    在上头的事例中,开窗函数COUNT(*)
OVER()
对于查询结果的各一行都回来所有符合条件的举行的条数。OVER关键字后的括号中尚时不时补加选项用以改变举行联谊运算的窗口范围。

只要OVER关键字后的括号中的挑也空,则开窗函数会针对结果集中的具有执行举办联谊运算。   

小结:上述讲述的是开窗函数的为主用法,希望对我们具有助!

而可能感兴趣之篇章:

留下评论

网站地图xml地图