目录
前言
开窗函数是对一组值进行操作,不需要使用group by 语句来进行分组,能够在同一行中同时返回基础行的列,和聚合的列;使用关键字over(),必须和聚合函数或排序函数等一起使用,不能单独存在。
1. 语法
over([partition by colnum] [order by colnum])
partition by : 该子句代表分组
order by : 子句代表排查
2. 用法
开窗函数必须与聚合,排序或者查找函数,要一起使用,不能单独存在,具体函数有如下:
聚合函数 | |
sum() | 组内求和 |
count() | 组内计数 |
min() | 组内最小值 |
max() | 组内最大值 |
avg() | 组内平均值 |
排序函数 | |
row_number() | 组内唯一性连续的排序 |
rank() | 组内不唯一且不连续,当并列的排名时,函数会为并列的列分配相同的排名数, |
dense_rank() | 组内不唯一且连续,当并列的排名时,函数会为并列的列分配不同的排名数 |
ntile() | 将组内的数据分为N个等级 |
查找函数 | |
first_value() | 查找向当前列的上一条记录 |
last_value() | 查找当前列的下一条记录 |
提示:开窗函数中,针对与开窗的程度不同得到的效果已是不同的,我们来一起看一下。
本次用到的测试数据如下:
# 创建一个员工表
CREATE TABLE Employee
(
ID INT PRIMARY KEY,
Name VARCHAR(20),
GroupName VARCHAR(20),
Salary INT
);
# 插入数据
INSERT INTO Employee
VALUES(1,'小明','开发部',8000),
(4,'小张','开发部',7600),
(5,'小白','开发部',7000),
(8,'小王','财务部',5000),
(9, null,'财务部',NULL),
(15,'小刘','财务部',6000),
(16,'小高','行政部',4500),
(18,'小王','行政部',4000),
(23,'小李','行政部',4500),
(29,'小吴','行政部',4700);
# 订单表
create table orders (
id VARCHAR() PRIMARY key,
time date,
name VARCHAR(200),
counts int,
price DOUBLE(5,2),
sale_je DOUBLE(5,2),
sale_name VARCHAR(200)
);
# 导入数据
insert into orders(id,time,name,counts,price,sale_name)
VALUES('101000',STR_TO_DATE('2024-08-27', '%Y-%m-%d'),'商品1',1,25,'小王'),
('101001',STR_TO_DATE('2024-08-21', '%Y-%m-%d'),'商品1',1,25,'小王'),
('101002',STR_TO_DATE('2024-08-22', '%Y-%m-%d'),'商品2',2,30,'小王'),
('101003',STR_TO_DATE('2024-08-22', '%Y-%m-%d'),'商品1',3,25,'小王'),
('101004',STR_TO_DATE('2024-08-24', '%Y-%m-%d'),'商品3',1,54,'小张'),
('101005',STR_TO_DATE('2024-08-25', '%Y-%m-%d'),'商品5',2,23,'小刘'),
('101006',STR_TO_DATE('2024-08-27', '%Y-%m-%d'),'商品4',5,89,'小贾'),
('101007',STR_TO_DATE('2024-08-28', '%Y-%m-%d'),'商品2',7,30,'小王'),
('101008',STR_TO_DATE('2024-08-22', '%Y-%m-%d'),'商品6',8,10,'小五'),
('101009',STR_TO_DATE('2024-08-23', '%Y-%m-%d'),'商品2',2,30,'老王'),
('1010010',STR_TO_DATE('2024-08-25', '%Y-%m-%d'),'商品1',3,25,'笑笑'),
('1010011',STR_TO_DATE('2024-08-26', '%Y-%m-%d'),'商品4',2,89,'小王'),
('1010012',STR_TO_DATE('2024-08-22', '%Y-%m-%d'),'商品3',1,54,'余蓝');
# 更新销售额
update orders set sale_je=counts*price;
2.1 聚合开窗
2.1.1 列汇总
示例:以sum()求和函数为例,求工资情况。
SELECT e.*,
SUM(Salary) OVER(PARTITION BY Groupname) as 每个组的总工资,
SUM(Salary) OVER(PARTITION BY Groupname ORDER BY ID) as 每个组的累计总工资,
SUM(Salary) OVER(ORDER BY ID) as 累计工资,
SUM(Salary) OVER() as 总工资
from Employee e;
结果展示
![](https://i-blog.csdnimg.cn/direct/81c26b73c8b747e0ae35b95f272dc83e.png)
解释说明:
SUM(Salary) OVER (PARTITION BY Groupname) # 求组内的工资之和
SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID) # 对Groupname进行分组,ID列进行升序 ,最后在组内进行累加求和
SUM(Salary) OVER (ORDER BY ID) #整个查询条件的累计工资,这里没有使用partition 关键字使用不是组内的
SUM(Salary) OVER () # 整个查询条件的汇总,这里over()里面不含有任何的子句。
注:其他的聚合函数以是类似的用法,主要是理解over() 里面的子句的用法和使用场景。
上面我们可以看出,是按照具体的列进行分组,对工资就行统计的,其实我们以可以对具体的范围进行统计,就来一起看一下吧!!!
2.2.2 范围汇总
范围汇总用到的是:range .. interval day
示例:
需求:统计当天销售额和五天内的商品销售额
-- 统计当天销售额和五天内的商品销售额
select
o.*,
sum(sale_je) over(partition by time) as 当天销售额,
sum(sale_je) over(order by time range between interval '2' day preceding and interval '2' day following) as 五天内销售额
from orders o
结果展示:
![](https://i-blog.csdnimg.cn/direct/bc2517b1e56e47b1b328c0bc3ff4eee1.png)
解释说明:
当天的销售额:这里很好统计,使用over()对time 分组计算即可
5天内的销售额:这里的5天内,指相对当前统计的time 日期列的为基础,向前推2天,向后推2天;那么刚好就是5天
sum(sale_je) over(order by time range between interval '2' day preceding and interval '2' day following)
这里over()里面的子句 between .. and ..代表区间范围;interval '2' day preceding 代表前2天;
interval '2' day following 代表后2天。所以第一行 354=21为基础(19~22的数据)之和。
2.2 排序开窗
排序函数 row_number() ,dense_rank() ,rank() 使用的场景比较多,一般做统计分析时常用。
示例:
# 给销售员,销售的商品进行排名
select
a.sale_name,name,
row_number () over(partition by sale_name order by name) as rn, #组内唯一排名,不并列
rank () over(partition by sale_name order by name) as rk, #组内不唯一并列不连续
dense_rank() over(partition by sale_name order by name) as drk #组内不唯一并列连续
from orders a ;
结果展示:
![](https://i-blog.csdnimg.cn/direct/ca712e7508fd4aff84e8a660b454ef82.png)
说明解释:
销售员为【小王】,r n 列排序都是唯一的数字不重合,rk 列非唯一的,并且相同商品并列排名,到出现第一次的不同商品时,按照已经排过的统计数量继续排名,并不是数字之间连续(组内不连续);drk 列,同样的我们可以看出,非唯一的排名,但到出现第一次的不同商品时,紧紧的继续按照连续的数字进行排序(组内连续)
2.3 查找开窗
这里的查找函数用到的是first_value() ,last_value();一般用来统计相邻数据项的值
示例:
# 查找当前列的上行和下一行的销售金额
select
sale_name,name,sale_je,time,
first_value(sale_je) over(order by time rows between 1 preceding and 1 following ) as prev_month,
last_value(sale_je) over(order by time rows between 1 preceding and 1 following ) as next_month
from orders;
结果展示:
![](https://i-blog.csdnimg.cn/direct/02c77c8201e84e87817124ab0cd635ce.png)
说明解释:
首先我们来看:rows between 1 preceding and 1 following,表示在当前记录的前一条、后一条范围内查找并统计,比如销售员余蓝这一行,当前日期为2024-08-22 ,那么他的上一行就是2024-08-21的销售金额(25),下一行2024-08-23的销售金额(60);这样我们就可以找出相邻的数据了。
3. 总结
首先今天就介绍到这里,后期介绍几个其他的场景;这里三类开窗函数中,除了查找函数不常用外,其他基本在统计分析系统中是非常常用的,得多多练习,先搞点数据自己动手测试每个函数的用法和场景,得出具体的效果这样才能灵活运用。