开窗函数入门
- 开窗函数形式:聚合函数 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
- 一些常用的开窗函数组合
- 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
- 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行数据。