SQL开窗函数

已于 2024-06-20 14:59:59 修改
阅读量1.7k 收藏 27
点赞数 51
分类专栏： SQL-数据分析必备技能 文章标签： sql 数据库
于 2024-05-21 21:08:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/zwldn/article/details/139082315
版权

文章目录

概念：

开窗函数是对于每条记录 都要在此窗口内执行函数，它对数据的每一行 ，都使用与该行相关的行进行计算并返回计算结果。开窗函数的本质还是聚合运算，只不过它更具灵活性。
开窗函数和普通聚合函数的区别：

  • 聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几条。
  • 聚合函数也可以用于开窗函数中。

应用：
窗口函数提供了在查询结果中进行排序、排名、聚合和分析的灵活性。窗口函数在数据分析和报表生成中非常有用，可以实现更复杂的计算和分析需求。

语法：

window_function() OVER (
[PARTITION BY partition_expression]
[ORDER BY order_list]
[frame_clause] )

开窗函数的一个概念是当前行，当前行属于某个窗口，窗口由over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所行，开窗函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：

  • window_function(): 要使用的窗口函数，如：ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM(), AVG() 等。
  • PARTITION BY: 可选项，用于将结果集划分为分区，以便窗口函数在每个分区内计算。
  • ORDER BY: 可选项，用于指定结果集的排序顺序，窗口函数将根据指定的排序顺序进行计算。
  • frame_clause: 可选项，用于指定窗口中要考虑的行的范围。常见的 frame 类型包括 ROWS, RANGE 等，通常用来作为滑动窗口使用。

对于滑动窗口的范围指定，通常使用 between frame_start and frame_end 语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

current row 边界是当前行，一般和其他范围关键字一起使用
unbounded preceding 边界是分区中的第一行
unbounded following 边界是分区中的最后一行
expr preceding 边界是当前行减去expr的值
expr following 边界是当前行加上expr的值

示例：

rows between 1 preceding and 1 following 窗口范围是当前行、前一行、后一行一共三行记录。
rows unbounded preceding 窗口范围是当前行到分区中的最后一行。
rows between unbounded preceding and unbounded following 窗口范围是当前分区中所有行，等同于不写。

在这里插入图片描述

常用的窗口函数及示例：

以下是一些MySQL中常用的窗口函数：
示例数据： 销售表包含以下列：销售部门、销售产品、销售日期、销售员、销售数量、产品单价；(销售额=销售数量*产品单价)

CREATE TABLE sales (
    department VARCHAR(50),
    product VARCHAR(50),
    sale_date DATE,
    salesperson VARCHAR(50),
    quantity INT,
    unit_price DECIMAL(10,2)
);

INSERT INTO sales (department, product, sale_date, salesperson, quantity, unit_price)
VALUES
('销售1部','1001','2024/5/3','王明','15','200'),
('销售2部','1002','2024/5/10','徐小小','20','500'),
('销售3部','1002','2024/5/18','纪风','10','500'),
('销售1部','1001','2024/5/5','王明','30','200'),
('销售2部','1002','2024/5/12','徐小小','25','500'),
('销售3部','1001','2024/5/20','纪风','18','200'),
('销售1部','1001','2024/5/8','王明','12','200'),
('销售2部','1002','2024/5/25','徐小小','22','500'),
('销售2部','1003','2024/5/15','徐小小','8','1000'),
('销售1部','1001','2024/5/30','王明','16','200'),
('销售2部','1002','2024/5/1','徐小小','14','500'),
('销售3部','1003','2024/5/22','纪风','19','1000'),
('销售2部','1001','2024/5/7','徐小小','21','200'),
('销售2部','1002','2024/5/28','刘阳','11','500'),
('销售3部','1003','2024/5/17','纪风','24','1000'),
('销售1部','1001','2024/5/4','王明','17','200'),
('销售2部','1002','2024/5/13','刘阳','9','500'),
('销售3部','1003','2024/5/21','纪风','23','1000'),
('销售1部','1001','2024/5/29','张一','7','200'),
('销售2部','1002','2024/5/6','刘阳','13','500'),
('销售3部','1003','2024/5/23','付华','18','1000'),
('销售1部','1001','2024/5/2','张一','20','200'),
('销售2部','1002','2024/5/9','刘阳','10','500'),
('销售3部','1003','2024/5/26','付华','30','1000'),
('销售1部','1001','2024/5/14','张一','25','200'),
('销售2部','1002','2024/5/31','刘阳','18','500'),
('销售3部','1003','2024/5/24','付华','12','1000'),
('销售1部','1001','2024/5/11','张一','22','200'),
('销售2部','1002','2024/5/19','刘阳','8','500'),
('销售3部','1003','2024/5/27','付华','16','1000'),
('销售1部','1001','2024/5/16','张一','14','200'),
('销售2部','1002','2024/5/3','刘阳','19','500'),
('销售3部','1003','2024/5/20','付华','21','1000'),
('销售1部','1001','2024/5/7','张一','11','200'),
('销售2部','1002','2024/5/24','刘阳','24','500'),
('销售3部','1003','2024/5/12','付华','17','1000'),
('销售1部','1001','2024/5/29','张一','9','200'),
('销售1部','1002','2024/5/5','张一','23','500'),
('销售2部','1003','2024/5/22','刘阳','7','1000'),
('销售3部','1001','2024/5/9','付华','13','200'),
('销售1部','1002','2024/5/16','张一','18','500'),
('销售2部','1003','2024/5/23','刘阳','20','1000'),
('销售3部','1001','2024/5/1','付华','10','200'),
('销售1部','1002','2024/5/18','张一','30','500'),
('销售2部','1003','2024/5/25','刘阳','25','1000'),
('销售3部','1001','2024/5/2','付华','18','200'),
('销售1部','1002','2024/5/11','张一','10','500'),
('销售2部','1003','2024/5/9','刘阳','50','1000'),
('销售3部','1001','2024/5/10','付华','5','200');

求平均值：AVG() ：

查询各部门的平均销售额（需保留全部行信息）

SELECT *,quantity*unit_price as sale,avg(quantity*unit_price) over(partition by department ) avg_sale  from sales;
+-------+--------+-----------+------+------------+------+------+--------+-------
-----------+
|  department	|	product	|	sale_date	|	salesperson	|	quantity	|	unit_price	|	sale	|	avg_sale	|
|  销售1部	|	1001	|	2024/5/3	|	王明	|	15	|	200	|	3000	|	5006.25	|
|  销售1部	|	1001	|	2024/5/7	|	张一	|	11	|	200	|	2200	|	5006.25	|
|  销售1部	|	1001	|	2024/5/29	|	张一	|	9	|	200	|	1800	|	5006.25	|
|  销售1部	|	1001	|	2024/5/5	|	王明	|	30	|	200	|	6000	|	5006.25	|
|  销售1部	|	1001	|	2024/5/29	|	张一	|	7	|	200	|	1400	|	5006.25	|
|  销售1部	|	1002	|	2024/5/5	|	张一	|	23	|	500	|	11500	|	5006.25	|
|  销售1部	|	1001	|	2024/5/8	|	王明	|	12	|	200	|	2400	|	5006.25	|
|  销售1部	|	1002	|	2024/5/16	|	张一	|	18	|	500	|	9000	|	5006.25	|
|  销售1部	|	1001	|	2024/5/2	|	张一	|	20	|	200	|	4000	|	5006.25	|
|  销售1部	|	1001	|	2024/5/30	|	王明	|	16	|	200	|	3200	|	5006.25	|
|  销售1部	|	1002	|	2024/5/18	|	张一	|	30	|	500	|	15000	|	5006.25	|
|  销售1部	|	1002	|	2024/5/11	|	张一	|	10	|	500	|	5000	|	5006.25	|
|  销售1部	|	1001	|	2024/5/14	|	张一	|	25	|	200	|	5000	|	5006.25	|
|  销售1部	|	1001	|	2024/5/11	|	张一	|	22	|	200	|	4400	|	5006.25	|
|  销售1部	|	1001	|	2024/5/16	|	张一	|	14	|	200	|	2800	|	5006.25	|
|  销售1部	|	1001	|	2024/5/4	|	王明	|	17	|	200	|	3400	|	5006.25	|
|  销售2部	|	1002	|	2024/5/10	|	徐小小	|	20	|	500	|	10000	|	11705.55556	|
|  销售2部	|	1002	|	2024/5/6	|	刘阳	|	13	|	500	|	6500	|	11705.55556	|
|  销售2部	|	1002	|	2024/5/12	|	徐小小	|	25	|	500	|	12500	|	11705.55556	|
|  销售2部	|	1002	|	2024/5/13	|	刘阳	|	9	|	500	|	4500	|	11705.55556	|
|  销售2部	|	1002	|	2024/5/25	|	徐小小	|	22	|	500	|	11000	|	11705.55556	|
|  销售2部	|	1003	|	2024/5/15	|	徐小小	|	8	|	1000	|	8000	|	11705.55556	|
|  销售2部	|	1002	|	2024/5/1	|	徐小小	|	14	|	500	|	7000	|	11705.55556	|
|  销售2部	|	1002	|	2024/5/9	|	刘阳	|	10	|	500	|	5000	|	11705.55556	|
|  销售2部	|	1001	|	2024/5/7	|	徐小小	|	21	|	200	|	4200	|	11705.55556	|
|  销售2部	|	1002	|	2024/5/28	|	刘阳	|	11	|	500	|	5500	|	11705.55556	|
|  销售2部	|	1002	|	2024/5/31	|	刘阳	|	18	|	<
最低0.47元/天 解锁文章
大数据Flink（七十四）：SQL的滑动窗口（HOP）
Lansonli（蓝深李）的博客
09-06 1612
与滚动窗口功能一样，也有窗口大小的概念。不一样的地方在于，滑动窗口有另一个参数控制窗口计算的频率（滑动窗口滑动的步长）。因此，如果滑动的步长小于窗口大小，则滑动窗口之间每个窗口是可以重叠。这样，每 5 分钟会划分一次窗口，这个窗口包含的数据是过去 10 分钟内的数据，如下图所示。：比如计算同时在线的数据，要求结果的输出频率是 1 分钟一次，每次计算的数据是过去 5 分钟的数据（有的场景下用户可能在线，但是可能会 2 分钟不活跃，但是这也要算在同时在线数据中，所以取最近 5 分钟的数据就能计算进去了）
SQL开窗函数的具体实现详解
09-08
SQL开窗函数是一种在SQL查询中执行复杂分析的高级技术，它允许你在同一查询中对一组相关行（称为“窗口”或“分组”）进行聚合操作，而不仅仅是整个结果集。这使得处理复杂的排序、分组和计算变得更加便捷。在2003年...
SQL滑动窗口
qq_45849148的博客
08-10 1606
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。
Flink-sql-整理-窗口函数
zengjun_csdn的博客
03-10 813
总结区别： 窗口表值函数 ： 可用根据选择窗口类型，对原数据基础上增加三列：“window_start”，“window_end”，“window_time” 用在from 之后，类似子查询一样，配合group by函数，选择分组字段（“window_start”，“window_end”，“window_time）配合聚合函数，进行一个窗口输出一行窗口分组函数： 一种淘汰的使用方式，放在group by 之后，进行分组,直接配合聚函数对一个窗口的结果进行输出一行。
关于对开窗函数的理解
m0_69761265的博客
03-06 282
对开窗函数有了更深的认识 开窗函数是用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），能够对所需要的值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。 例如：OVET()，PARTITION BY()，ORDER BY() 开窗函数格式： 函数名(列) OVER(选项) OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。 注：如果 OVER 关键
over 开窗函数的的理解
cuanshen0958的博客
08-05 257
over的功能： 一、 with Ta as(select 1 as Fa, 'a' as Fbunion allselect 2 as Fa, 'b' as Fbunion allselect 3 as Fa, 'c' a...
MYSQL窗口函数（Rows & Range）——滑动窗口函数用法
热门推荐
WHYbeHERE的博客
11-17 1万+
总结SQL开窗函数中range 和rows的用法，实现滑动窗口计算功能。
开窗函数的通俗理解（此文章基于sparkSql）：
G___T___G的博客
05-12 363
什么是窗口函数： 窗口函数解决了哪些问题： 假如有很多种不同类型的数据，如果我们想按照某个列进行分组，然后在对分组后的数据进行排序，在输出分组排序后的结果，那么依靠普通的函数是无法做到的，那么就应运而生了窗口函数，作用就是：先对数据分组，在对分组后的数据进行排序之类的操作，最后再把想要查询的列的结果查询出来。要注意窗口函数和聚合函数有着本质上的区别：聚合函数的最大的特点就是只能操作分组的字段，但是开窗函数可以操作所有的字段，并不受分区字段的限制 对sparkSql窗口函数的理解： 例子： def test(
重新理解开窗函数
代码写的越急，程序跑得越慢。
01-03 317
select deptno, count(*) from emp group by deptno; 输出结果： select deptno, count(*) over() from emp group by deptno; 输出结果： select deptno, count(*) over() from emp; 输出结果： 通过这个例子，体会开窗函数的强大： select ...
SQL开窗函数介绍以及示例
03-24
sql开窗函数详解 SQL开窗函数（Window Functions）是一种用于在查询结果集中执行聚合、排序和分析操作的强大工具。它们可以在不破坏查询结果集的情况下，对每一行数据进行计算，比如计算行的排名、累计和、移动平均...
SQL开窗函数详解.pdf
03-24
### SQL开窗函数详解 #### 一、开窗函数的基本概念和语法 开窗函数，又称分析函数或OLAP函数，是SQL中一种高级特性，它允许用户在保持与原始数据行关联的同时执行复杂的计算和聚合操作。这些功能极大地提高了SQL...
SQL开窗函数的基本概念、用法以及一些常见的开窗函数
03-24
### SQL开窗函数详解 #### 一、基本概念 ##### 窗口（Window） - **定义**: 在SQL中，开窗函数定义了一个窗口，这个窗口可以覆盖整个查询结果集或者仅是结果集的一部分（即分区）。 - **作用**: 在窗口上执行的...
MySQL开窗函数
zhao_guo_feng的博客
06-25 1241
sql必须掌握的技能
oracle中over()开窗函数的理解
eyejavaeye的博客
01-07 140
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数 over（partition by deptno）按照部门分区 over（order by salary range between 50 preceding and 150 follow...
开窗函数介绍
善守者
08-24 647
开窗函数又叫分析函数开窗函数用于定义一个窗口（这里的窗口是指运算将要操作的数据集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回对于一组值的处理结果。两者区别：聚合函数：将数据按照一定的规则分组，统一分析各组的某项情况，每个分组返回一行结果开窗函数：将数据按照一定的规则分组，统一分析各组的某项情况，每行数据返回一行结果。
SQL 窗口函数
hao606的博客
03-09 291
MySQL从8.0版本才开始支持窗口函数，因而很多MySQL教材并没有对窗口函数进行详细介绍，然而窗口函数功能强大，可以解决很多使用普通查询语句很难实现的复杂的单表查询问题。 一、相关概念 窗口：满足某种条件的记录集合，分为静态窗口（集合内记录数量固定）和滑动窗口（集合内数量不固定）； 窗口函数：在窗口的每条记录上执行的函数。对于每条记录，窗口函数均产生一个输出。 二、语法格式 SELECT 字段1, 字段2, 窗口函数() OVER (窗口设置语句) | 窗口别名 # FROM 表1 WHERE .
SQL 开窗函数
sinat_41207450的博客
09-05 987
1、开窗函数/分析函数：over() 2、几个排序函数的差异 3、主要开窗函数
SQL学习(基础) ——窗口函数
S1406793的博客
10-25 2438
窗口函数、滑动函数、
SQL窗口函数
最新发布
weixin_59342501的博客
03-11 930
通过row_number()生成连续行号，与区间列进行差值运算，得到的临时结果如果相同表示为同意了连续区间。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值