sql 中的开窗函数: 聚合函数 over(选项)

开窗函数入门

• 开窗函数形式：聚合函数 over(选项)
• 开窗函数的作用：与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是普通聚合函数每组只能返回一个值，而开窗函数可以每组返回多个值。
• 例子：
  比如我们想查询每个工资小于5000元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于5000元的员工个数，执行下面的SQL语句

select t.fcity,t.fage,count(*) from person t where t.fsalary<5000

这个语句显然是错误的，因为count()是聚合函数，然后fname和fage字段没有包含分组里面。
但是要是使用开窗函数就可以实现了

select t.fcity, t.fage, count(*) over()
from person t
where t.fsalary < 5000

• 从上面例子可见，over（）相当于over（partition by 1）表示开窗函数对所有结果集进行运算。 over关键字表示把函数当成开窗函数而不是聚合函数，SQL标准允许将所有聚合函数用做开窗函数，使用over关键字来区分这两种用法。

开窗函数进阶1

• 聚合函数 over(选项) 形式中 括号中的选项通常用来标识 开窗函数的开窗范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。
• 例子:
  1.建表

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

2.插入数据

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

3、查询每个人员的信息以及所在城市人数

select  FName, 
			FCity, 
			FAge INT, 
			FSalary，
			count(*) over(partition by FCity ) as 所在城市人数

发现开窗范围为FCity。

开窗函数进阶2

• 含有group by的开窗函数，一般在group聚合的基础上再次进行聚合。
  例子：

SELECT T.CHANNEL AS PATTERN,
       COUNT(T.TRANSACTIONKEY) AS T_COUNT,
       SUM(T.AMT) AS T_AMT,
       ROUND(100 * SUM(T.AMT) / SUM(SUM(T.AMT)) OVER(PARTITION BY 1), 2) AS AMT_PERCENT,
       ROUND(100 * COUNT(T.TRANSACTIONKEY) / SUM(COUNT(T.TRANSACTIONKEY)) OVER(PARTITION BY 1),2) AS COUNT_PERCENT
  FROM XX(表名) T
 WHERE T.PARTY_ID = '100579050'
 GROUP BY T.CHANNEL

以上表示在T.CHANNEL聚合sum的基础上再次在整个表的维度上进行sum聚合。

开窗函数进阶3

• 关于开窗范围（在over(选项) 选项中指定）
  例子：下面以sum举例，其他函数同理（avg、min、max）
  表中数据

	cookie1,2015-04-10,1
	cookie1,2015-04-11,5
	cookie1,2015-04-12,7
	cookie1,2015-04-13,3
	cookie1,2015-04-14,2
	cookie1,2015-04-15,4
	cookie1,2015-04-16,4

sql语句

SELECT cookieid,createtime,pv,
    SUM(pv) OVER (PARTITION BY cookieid ORDER BY createtime) AS pv1,          												-- 默认为从起点到当前行                                                                                                        	
    SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2,      --从起点到当前行，结果同pv1
    SUM(pv) OVER(PARTITION BY cookieid) AS pv3,      																		--分组内所有行                                                                                                                                              
    SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,               --当前行+往前3行
    SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,                    --当前行+往前3行+往后1行
    SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6     	--当前行+往后所有行
FROM cookie;

查询结果

cookieid 	createtime		pv		pv1		pv2		pv3		pv4		pv5		pv6
	cookie1 	2015-04-16      4       26      26      26      13      13      4
	cookie1 	2015-04-15      4       22      22      26      16      20      8
	cookie1 	2015-04-14      2       18      18      26      17      21      10
	cookie1 	2015-04-13      3       16      16      26      16      18      13
	cookie1 	2015-04-12      7       13      13      26      13      16      20
	cookie1 	2015-04-11      5       6       6       26      6       13      25
	cookie1 	2015-04-10      1       1       1       26      1       6       26

开窗函数进阶4

• 一些常用的开窗函数组合

1. over(partition by XX order by XX) partition by和order by 结合
   例子:查询每个班的第一名。

SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

得到的结果是:

dss        1        95        1
ffd        1        95        1
gds        2        92        1
gf         3        99        1
ddd        3        99        1

说明：rank()和dense_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来；rank()和dense_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。

2.sum()over（）的使用
例子：
根据班级进行分数求和

select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

运行结果：

dss        1        95        190  --由于两个95都是第一名，所以累加时是两个第一名的相加
ffd        1        95        190 
fda        1        80        270  --第一名加上第二名的
gds        2        92        92
cfe        2        74        166
gf         3        99        198
ddd        3        99        198
3dd        3        78        276
asdf       3        55        331
adf        3        45        376

3. first_value() over()和last_value() over()的使用
   如求出第一个的成绩

select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

4.其他聚合函数与over的搭配使用
　　count() over(partition by … order by …)：求分组后的总数。
　　max() over(partition by … order by …)：求分组后的最大值。
　　min() over(partition by … order by …)：求分组后的最小值。
　　avg() over(partition by … order by …)：求分组后的平均值。
　　lag() over(partition by … order by …)：取出前n行数据。　　
　　lead() over(partition by … order by …)：取出后n行数据。