开窗函数_2

开窗函数

与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，
开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。
ISO SQL 规定了这样的函数为开窗函数（在 Oracle 中则被称为分析函数，而在 DB2 中则被称为 OLAP 函数）

--要求：查询每个工资小于 5 000 元的员工信息（城市及年龄），并且在每行中都显示所有工资小于 5 000 元的员工个数
--          意味要列出3个字段：   城市、年龄、个数（薪资<5000）

/*
--开窗函数训练 ，《程序员的经典》
*/

--先建立一个表结构、例子数据
CREATE TABLE T_Person (
FName VARCHAR(20),
FCity VARCHAR(20), 
FAge INT,
FSalary INT) 


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

select count(*)  as [汇总人数]
from T_Person

运行结果：             -----------------因为窗口函数时建立在聚合函数之上的，所以此处只是显示一下
      

--每行中都显示所有员工薪资<5000的员工的个数
select FCITY,FAGE,
(
    SELECT COUNT(*) FROM T_Person   ----------------->>>这个部分，只是显示了一个字段，不划算              
    WHERE FSALARY < 5000
)
FROM T_Person
WHERE FSALARY < 5000

运行结果：
      

 

 

--使用开窗函数统计（每行中都显示所有员工薪资<5000的员工的个数）
--开窗函数在聚合函数后增加了一个over关键字
select FCity,FAge,count(*) over() [每行中都显示所有员工薪资<5000的员工的个数]            ------>此处OVER()括号中为空，表示对所有行进行聚合计算
from T_Person
where FSalary < 5000

运行结果：
      

 

 

--定义行的分区，从而进行聚合计算
--select * from T_Person
select FName,FCity,FAge,FSalary,
       count(*) over(partition by FCity) as [按FCity]
from T_Person

运行结果：
      

 

 

----定义行的分区，从而进行聚合计算
--select * from T_Person
select FName,FCity,FAge,FSalary,
          count(*) over(partition by FAge) as [按FAge]
from T_Person

运行结果：
      

----定义行的分区，从而进行聚合计算
--select * from T_Person
select FName,FCity,FAge,FSalary,
          count(*) over(partition by FAge) as [按FAge]
from T_Person

运行结果：