开窗函数over()的妙用

天code行空

于 2024-09-03 08:15:00 发布

阅读量332

点赞数 14

分类专栏： SQL 文章标签：数据库 linux 运维 sql

本文链接：https://blog.csdn.net/weixin_40104407/article/details/141812794

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

前言

开窗函数是对一组值进行操作，不需要使用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;

结果展示

解释说明：

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

结果展示：

解释说明：

当天的销售额：这里很好统计，使用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 ;

结果展示：

说明解释：

销售员为【小王】，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;

结果展示：

说明解释：

首先我们来看:rows between 1 preceding and 1 following，表示在当前记录的前一条、后一条范围内查找并统计，比如销售员余蓝这一行，当前日期为2024-08-22 ，那么他的上一行就是2024-08-21的销售金额（25），下一行2024-08-23的销售金额（60）；这样我们就可以找出相邻的数据了。