黑群晖的做网站文件,做网站要准备,天眼查在线查询企业信息查询,杭州建设网杭州造价平台文章目录 概念#xff1a;语法#xff1a;常用的窗口函数及示例#xff1a;求平均值#xff1a;AVG() #xff1a;求和#xff1a;SUM():求排名#xff1a;移动平均计数COUNT():求最大MXA()/小MIN()值求分区内的最大/最小值求当前行的前/后一个值 概念#xff1a;
开窗… 文章目录 概念语法常用的窗口函数及示例求平均值AVG() 求和SUM():求排名移动平均计数COUNT():求最大MXA()/小MIN()值求分区内的最大/最小值求当前行的前/后一个值 概念
开窗函数是对于每条记录 都要在此窗口内执行函数它对数据的每一行 都使用与该行相关的行进行计算并返回计算结果。开窗函数的本质还是聚合运算只不过它更具灵活性。 开窗函数和普通聚合函数的区别
聚合函数是将多条记录聚合为一条而开窗函数是每条记录都会执行有几条记录执行完还是几条。聚合函数也可以用于开窗函数中。
应用 窗口函数提供了在查询结果中进行排序、排名、聚合和分析的灵活性。窗口函数在数据分析和报表生成中非常有用可以实现更复杂的计算和分析需求。
语法 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 | 500 | 9000 | 11705.55556 |
| 销售2部 | 1002 | 2024/5/24 | 刘阳 | 24 | 500 | 12000 | 11705.55556 |
| 销售2部 | 1002 | 2024/5/19 | 刘阳 | 8 | 500 | 4000 | 11705.55556 |
| 销售2部 | 1003 | 2024/5/22 | 刘阳 | 7 | 1000 | 7000 | 11705.55556 |
| 销售2部 | 1003 | 2024/5/23 | 刘阳 | 20 | 1000 | 20000 | 11705.55556 |
| 销售2部 | 1002 | 2024/5/3 | 刘阳 | 19 | 500 | 9500 | 11705.55556 |
| 销售2部 | 1003 | 2024/5/25 | 刘阳 | 25 | 1000 | 25000 | 11705.55556 |
| 销售2部 | 1003 | 2024/5/9 | 刘阳 | 50 | 1000 | 50000 | 11705.55556 |
| 销售3部 | 1001 | 2024/5/20 | 纪风 | 18 | 200 | 3600 | 13186.66667 |
| 销售3部 | 1003 | 2024/5/12 | 付华 | 17 | 1000 | 17000 | 13186.66667 |
| 销售3部 | 1003 | 2024/5/22 | 纪风 | 19 | 1000 | 19000 | 13186.66667 |
| 销售3部 | 1003 | 2024/5/27 | 付华 | 16 | 1000 | 16000 | 13186.66667 |
| 销售3部 | 1003 | 2024/5/17 | 纪风 | 24 | 1000 | 24000 | 13186.66667 |
| 销售3部 | 1001 | 2024/5/9 | 付华 | 13 | 200 | 2600 | 13186.66667 |
| 销售3部 | 1003 | 2024/5/24 | 付华 | 12 | 1000 | 12000 | 13186.66667 |
| 销售3部 | 1003 | 2024/5/21 | 纪风 | 23 | 1000 | 23000 | 13186.66667 |
| 销售3部 | 1001 | 2024/5/1 | 付华 | 10 | 200 | 2000 | 13186.66667 |
| 销售3部 | 1003 | 2024/5/23 | 付华 | 18 | 1000 | 18000 | 13186.66667 |
| 销售3部 | 1003 | 2024/5/26 | 付华 | 30 | 1000 | 30000 | 13186.66667 |
| 销售3部 | 1001 | 2024/5/2 | 付华 | 18 | 200 | 3600 | 13186.66667 |
| 销售3部 | 1003 | 2024/5/20 | 付华 | 21 | 1000 | 21000 | 13186.66667 |
| 销售3部 | 1002 | 2024/5/18 | 纪风 | 10 | 500 | 5000 | 13186.66667 |
| 销售3部 | 1001 | 2024/5/10 | 付华 | 5 | 200 | 1000 | 13186.66667 |
----------------------------------------------------------------求和SUM():
查询每个产品的销售总额:
SELECT product, SUM(quantity*unit_price) AS sale FROM sales GROUP BY product;
----------------------------------------------------------------
| product | sale |
| 1001 | 56600 |
| 1002 | 142000 |
| 1003 | 290000 |
----------------------------------------------------------------求排名
row_number(): 显示分区中不重复不间断的序号dense_rank(): 显示分区中重复不间断的序号rank() 显示分区中重复间断的序号
计算每个产品在每个日期的销售量排名:
SELECT product,quantity,unit_price,sale_date,RANK() OVER (PARTITION BY sale_date, product ORDER BY quantity DESC) AS sale_rank FROM sales ORDER BY sale_date, product, quantity DESC;
-----------------------------------------------------------------------
-----------
| product | quantity | unit_price | sale_date | sales_rank |
| 1001 | 10 | 200 | 2024/5/1 | 1 |
| 1002 | 14 | 500 | 2024/5/1 | 1 |
| 1001 | 20 | 200 | 2024/5/2 | 1 |
| 1001 | 18 | 200 | 2024/5/2 | 2 |
| 1001 | 15 | 200 | 2024/5/3 | 1 |
| 1002 | 19 | 500 | 2024/5/3 | 1 |
| 1001 | 17 | 200 | 2024/5/4 | 1 |
| 1001 | 30 | 200 | 2024/5/5 | 1 |
| 1002 | 23 | 500 | 2024/5/5 | 1 |
| 1002 | 13 | 500 | 2024/5/6 | 1 |
| 1001 | 21 | 200 | 2024/5/7 | 1 |
| 1001 | 11 | 200 | 2024/5/7 | 2 |
| 1001 | 12 | 200 | 2024/5/8 | 1 |
| 1001 | 13 | 200 | 2024/5/9 | 1 |
| 1002 | 10 | 500 | 2024/5/9 | 1 |
| 1003 | 50 | 1000 | 2024/5/9 | 1 |
| 1001 | 5 | 200 | 2024/5/10 | 1 |
| 1002 | 20 | 500 | 2024/5/10 | 1 |
| 1001 | 22 | 200 | 2024/5/11 | 1 |
| 1002 | 10 | 500 | 2024/5/11 | 1 |
| 1002 | 25 | 500 | 2024/5/12 | 1 |
| 1003 | 17 | 1000 | 2024/5/12 | 1 |
| 1002 | 9 | 500 | 2024/5/13 | 1 |
| 1001 | 25 | 200 | 2024/5/14 | 1 |
| 1003 | 8 | 1000 | 2024/5/15 | 1 |
| 1001 | 14 | 200 | 2024/5/16 | 1 |
| 1002 | 18 | 500 | 2024/5/16 | 1 |
| 1003 | 24 | 1000 | 2024/5/17 | 1 |
| 1002 | 30 | 500 | 2024/5/18 | 1 |
| 1002 | 10 | 500 | 2024/5/18 | 2 |
| 1002 | 8 | 500 | 2024/5/19 | 1 |
| 1001 | 18 | 200 | 2024/5/20 | 1 |
| 1003 | 21 | 1000 | 2024/5/20 | 1 |
| 1003 | 23 | 1000 | 2024/5/21 | 1 |
| 1003 | 19 | 1000 | 2024/5/22 | 1 |
| 1003 | 7 | 1000 | 2024/5/22 | 2 |
| 1003 | 20 | 1000 | 2024/5/23 | 1 |
| 1003 | 18 | 1000 | 2024/5/23 | 2 |
| 1002 | 24 | 500 | 2024/5/24 | 1 |
| 1003 | 12 | 1000 | 2024/5/24 | 1 |
| 1002 | 22 | 500 | 2024/5/25 | 1 |
| 1003 | 25 | 1000 | 2024/5/25 | 1 |
| 1003 | 30 | 1000 | 2024/5/26 | 1 |
| 1003 | 16 | 1000 | 2024/5/27 | 1 |
| 1002 | 11 | 500 | 2024/5/28 | 1 |
| 1001 | 9 | 200 | 2024/5/29 | 1 |
| 1001 | 7 | 200 | 2024/5/29 | 2 |
| 1001 | 16 | 200 | 2024/5/30 | 1 |
| 1002 | 18 | 500 | 2024/5/31 | 1 |
-----------------------------------------------------------------------
-----------移动平均
计算每个产品的移动平均销售额(最近3个订单):
SELECTproduct,quantity*unit_price sale,sale_date,AVG(quantity*unit_price) OVER (PARTITION BY product ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_sale
FROM sales
ORDER BY product, sale_date;
-----------------------------------------------------------------------
-----------
| product | sale | sale_date | moving_avg_sale |
| 1001 | 2000 | 2024/5/1 | 2000 |
| 1001 | 3600 | 2024/5/2 | 2800 |
| 1001 | 4000 | 2024/5/2 | 3200 |
| 1001 | 3000 | 2024/5/3 | 3533.333333 |
| 1001 | 3400 | 2024/5/4 | 3466.666667 |
| 1001 | 6000 | 2024/5/5 | 4133.333333 |
| 1001 | 2200 | 2024/5/7 | 3866.666667 |
| 1001 | 4200 | 2024/5/7 | 4133.333333 |
| 1001 | 2400 | 2024/5/8 | 2933.333333 |
| 1001 | 2600 | 2024/5/9 | 3066.666667 |
| 1001 | 1000 | 2024/5/10 | 2000 |
| 1001 | 4400 | 2024/5/11 | 2666.666667 |
| 1001 | 5000 | 2024/5/14 | 3466.666667 |
| 1001 | 2800 | 2024/5/16 | 4066.666667 |
| 1001 | 3600 | 2024/5/20 | 3800 |
| 1001 | 1400 | 2024/5/29 | 2600 |
| 1001 | 1800 | 2024/5/29 | 2266.666667 |
| 1001 | 3200 | 2024/5/30 | 2133.333333 |
| 1002 | 7000 | 2024/5/1 | 7000 |
| 1002 | 9500 | 2024/5/3 | 8250 |
| 1002 | 11500 | 2024/5/5 | 9333.333333 |
| 1002 | 6500 | 2024/5/6 | 9166.666667 |
| 1002 | 5000 | 2024/5/9 | 7666.666667 |
| 1002 | 10000 | 2024/5/10 | 7166.666667 |
| 1002 | 5000 | 2024/5/11 | 6666.666667 |
| 1002 | 12500 | 2024/5/12 | 9166.666667 |
| 1002 | 4500 | 2024/5/13 | 7333.333333 |
| 1002 | 9000 | 2024/5/16 | 8666.666667 |
| 1002 | 5000 | 2024/5/18 | 6166.666667 |
| 1002 | 15000 | 2024/5/18 | 9666.666667 |
| 1002 | 4000 | 2024/5/19 | 8000 |
| 1002 | 12000 | 2024/5/24 | 10333.33333 |
| 1002 | 11000 | 2024/5/25 | 9000 |
| 1002 | 5500 | 2024/5/28 | 9500 |
| 1002 | 9000 | 2024/5/31 | 8500 |
| 1003 | 50000 | 2024/5/9 | 50000 |
| 1003 | 17000 | 2024/5/12 | 33500 |
| 1003 | 8000 | 2024/5/15 | 25000 |
| 1003 | 24000 | 2024/5/17 | 16333.33333 |
| 1003 | 21000 | 2024/5/20 | 17666.66667 |
| 1003 | 23000 | 2024/5/21 | 22666.66667 |
| 1003 | 19000 | 2024/5/22 | 21000 |
| 1003 | 7000 | 2024/5/22 | 16333.33333 |
| 1003 | 18000 | 2024/5/23 | 14666.66667 |
| 1003 | 20000 | 2024/5/23 | 15000 |
| 1003 | 12000 | 2024/5/24 | 16666.66667 |
| 1003 | 25000 | 2024/5/25 | 19000 |
| 1003 | 30000 | 2024/5/26 | 22333.33333 |
| 1003 | 16000 | 2024/5/27 | 23666.66667 |
-----------------------------------------------------------------------
-----------计数COUNT():
示例 计算每个部门的销售记录总和
SELECTdepartment,COUNT(1) OVER (PARTITION BY department) AS total_sales_count
FROM sales;
-----------------------------------------------------------------------
-----------
| department | total_sales_count |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售1部 | 16 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售2部 | 18 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
| 销售3部 | 15 |
-----------------------------------------------------------------------
-----------求最大MXA()/小MIN()值
示例 查找每个部门在销售日期的最大销售数量
SELECTdepartment,sale_date,quantity,MAX(quantity) OVER (PARTITION BY department, sale_date) AS max_quantity_on_date
FROM sales;
-----------------------------------------------------------------------
-----------
| department | sale_date | quantity | max_quantity_on_date |
| 销售1部 | 2024/5/2 | 20 | 20 |
| 销售1部 | 2024/5/3 | 15 | 15 |
| 销售1部 | 2024/5/4 | 17 | 17 |
| 销售1部 | 2024/5/5 | 30 | 30 |
| 销售1部 | 2024/5/5 | 23 | 30 |
| 销售1部 | 2024/5/7 | 11 | 11 |
| 销售1部 | 2024/5/8 | 12 | 12 |
| 销售1部 | 2024/5/11 | 22 | 22 |
| 销售1部 | 2024/5/11 | 10 | 22 |
| 销售1部 | 2024/5/14 | 25 | 25 |
| 销售1部 | 2024/5/16 | 14 | 18 |
| 销售1部 | 2024/5/16 | 18 | 18 |
| 销售1部 | 2024/5/18 | 30 | 30 |
| 销售1部 | 2024/5/29 | 7 | 9 |
| 销售1部 | 2024/5/29 | 9 | 9 |
| 销售1部 | 2024/5/30 | 16 | 16 |
| 销售2部 | 2024/5/1 | 14 | 14 |
| 销售2部 | 2024/5/3 | 19 | 19 |
| 销售2部 | 2024/5/6 | 13 | 13 |
| 销售2部 | 2024/5/7 | 21 | 21 |
| 销售2部 | 2024/5/9 | 10 | 50 |
| 销售2部 | 2024/5/9 | 50 | 50 |
| 销售2部 | 2024/5/10 | 20 | 20 |
| 销售2部 | 2024/5/12 | 25 | 25 |
| 销售2部 | 2024/5/13 | 9 | 9 |
| 销售2部 | 2024/5/15 | 8 | 8 |
| 销售2部 | 2024/5/19 | 8 | 8 |
| 销售2部 | 2024/5/22 | 7 | 7 |
| 销售2部 | 2024/5/23 | 20 | 20 |
| 销售2部 | 2024/5/24 | 24 | 24 |
| 销售2部 | 2024/5/25 | 22 | 25 |
| 销售2部 | 2024/5/25 | 25 | 25 |
| 销售2部 | 2024/5/28 | 11 | 11 |
| 销售2部 | 2024/5/31 | 18 | 18 |
| 销售3部 | 2024/5/1 | 10 | 10 |
| 销售3部 | 2024/5/2 | 18 | 18 |
| 销售3部 | 2024/5/9 | 13 | 13 |
| 销售3部 | 2024/5/10 | 5 | 5 |
| 销售3部 | 2024/5/12 | 17 | 17 |
| 销售3部 | 2024/5/17 | 24 | 24 |
| 销售3部 | 2024/5/18 | 10 | 10 |
| 销售3部 | 2024/5/20 | 18 | 21 |
| 销售3部 | 2024/5/20 | 21 | 21 |
| 销售3部 | 2024/5/21 | 23 | 23 |
| 销售3部 | 2024/5/22 | 19 | 19 |
| 销售3部 | 2024/5/23 | 18 | 18 |
| 销售3部 | 2024/5/24 | 12 | 12 |
| 销售3部 | 2024/5/26 | 30 | 30 |
| 销售3部 | 2024/5/27 | 16 | 16 |
-----------------------------------------------------------------------
-----------示例 查找每个部门在销售日期的最小销售数量
SELECTdepartment,sale_date,quantity,MAX(quantity) OVER (PARTITION BY department, sale_date) AS max_quantity_on_date
FROM sales;
-----------------------------------------------------------------------
-----------
| department | sale_date | quantity | min_quantity_on_date |
| 销售1部 | 2024/5/2 | 20 | 20 |
| 销售1部 | 2024/5/3 | 15 | 15 |
| 销售1部 | 2024/5/4 | 17 | 17 |
| 销售1部 | 2024/5/5 | 30 | 23 |
| 销售1部 | 2024/5/5 | 23 | 23 |
| 销售1部 | 2024/5/7 | 11 | 11 |
| 销售1部 | 2024/5/8 | 12 | 12 |
| 销售1部 | 2024/5/11 | 22 | 10 |
| 销售1部 | 2024/5/11 | 10 | 10 |
| 销售1部 | 2024/5/14 | 25 | 25 |
| 销售1部 | 2024/5/16 | 14 | 14 |
| 销售1部 | 2024/5/16 | 18 | 14 |
| 销售1部 | 2024/5/18 | 30 | 30 |
| 销售1部 | 2024/5/29 | 7 | 7 |
| 销售1部 | 2024/5/29 | 9 | 7 |
| 销售1部 | 2024/5/30 | 16 | 16 |
| 销售2部 | 2024/5/1 | 14 | 14 |
| 销售2部 | 2024/5/3 | 19 | 19 |
| 销售2部 | 2024/5/6 | 13 | 13 |
| 销售2部 | 2024/5/7 | 21 | 21 |
| 销售2部 | 2024/5/9 | 10 | 10 |
| 销售2部 | 2024/5/9 | 50 | 10 |
| 销售2部 | 2024/5/10 | 20 | 20 |
| 销售2部 | 2024/5/12 | 25 | 25 |
| 销售2部 | 2024/5/13 | 9 | 9 |
| 销售2部 | 2024/5/15 | 8 | 8 |
| 销售2部 | 2024/5/19 | 8 | 8 |
| 销售2部 | 2024/5/22 | 7 | 7 |
| 销售2部 | 2024/5/23 | 20 | 20 |
| 销售2部 | 2024/5/24 | 24 | 24 |
| 销售2部 | 2024/5/25 | 22 | 22 |
| 销售2部 | 2024/5/25 | 25 | 22 |
| 销售2部 | 2024/5/28 | 11 | 11 |
| 销售2部 | 2024/5/31 | 18 | 18 |
| 销售3部 | 2024/5/1 | 10 | 10 |
| 销售3部 | 2024/5/2 | 18 | 18 |
| 销售3部 | 2024/5/9 | 13 | 13 |
| 销售3部 | 2024/5/10 | 5 | 5 |
| 销售3部 | 2024/5/12 | 17 | 17 |
| 销售3部 | 2024/5/17 | 24 | 24 |
| 销售3部 | 2024/5/18 | 10 | 10 |
| 销售3部 | 2024/5/20 | 18 | 18 |
| 销售3部 | 2024/5/20 | 21 | 18 |
| 销售3部 | 2024/5/21 | 23 | 23 |
| 销售3部 | 2024/5/22 | 19 | 19 |
| 销售3部 | 2024/5/23 | 18 | 18 |
| 销售3部 | 2024/5/24 | 12 | 12 |
| 销售3部 | 2024/5/26 | 30 | 30 |
| 销售3部 | 2024/5/27 | 16 | 16 |
-----------------------------------------------------------------------
-----------求分区内的最大/最小值
FIRST_VALUE() OVER(PARTITION BY … ORDER BY …):
作用 返回在指定分区内按指定排序顺序的第一个值。 应用 常用于找出每个分组内的第一个值。
示例 找出每个部门的最早销售日期
SELECTdepartment,sale_date,FIRST_VALUE(sale_date) OVER (PARTITION BY department ORDER BY sale_date) AS first_sale_date
FROM sales;
----------------------------------------------------------------
| department | sale_date | first_sale_date |
| 销售1部 | 2024/5/2 | 2024/5/2 |
| 销售1部 | 2024/5/3 | 2024/5/2 |
| 销售1部 | 2024/5/4 | 2024/5/2 |
| 销售1部 | 2024/5/5 | 2024/5/2 |
| 销售1部 | 2024/5/5 | 2024/5/2 |
| 销售1部 | 2024/5/7 | 2024/5/2 |
| 销售1部 | 2024/5/8 | 2024/5/2 |
| 销售1部 | 2024/5/11 | 2024/5/2 |
| 销售1部 | 2024/5/11 | 2024/5/2 |
| 销售1部 | 2024/5/14 | 2024/5/2 |
| 销售1部 | 2024/5/16 | 2024/5/2 |
| 销售1部 | 2024/5/16 | 2024/5/2 |
| 销售1部 | 2024/5/18 | 2024/5/2 |
| 销售1部 | 2024/5/29 | 2024/5/2 |
| 销售1部 | 2024/5/29 | 2024/5/2 |
| 销售1部 | 2024/5/30 | 2024/5/2 |
| 销售2部 | 2024/5/1 | 2024/5/1 |
| 销售2部 | 2024/5/3 | 2024/5/1 |
| 销售2部 | 2024/5/6 | 2024/5/1 |
| 销售2部 | 2024/5/7 | 2024/5/1 |
| 销售2部 | 2024/5/9 | 2024/5/1 |
| 销售2部 | 2024/5/9 | 2024/5/1 |
| 销售2部 | 2024/5/10 | 2024/5/1 |
| 销售2部 | 2024/5/12 | 2024/5/1 |
| 销售2部 | 2024/5/13 | 2024/5/1 |
| 销售2部 | 2024/5/15 | 2024/5/1 |
| 销售2部 | 2024/5/19 | 2024/5/1 |
| 销售2部 | 2024/5/22 | 2024/5/1 |
| 销售2部 | 2024/5/23 | 2024/5/1 |
| 销售2部 | 2024/5/24 | 2024/5/1 |
| 销售2部 | 2024/5/25 | 2024/5/1 |
| 销售2部 | 2024/5/25 | 2024/5/1 |
| 销售2部 | 2024/5/28 | 2024/5/1 |
| 销售2部 | 2024/5/31 | 2024/5/1 |
| 销售3部 | 2024/5/1 | 2024/5/1 |
| 销售3部 | 2024/5/2 | 2024/5/1 |
| 销售3部 | 2024/5/9 | 2024/5/1 |
| 销售3部 | 2024/5/10 | 2024/5/1 |
| 销售3部 | 2024/5/12 | 2024/5/1 |
| 销售3部 | 2024/5/17 | 2024/5/1 |
| 销售3部 | 2024/5/18 | 2024/5/1 |
| 销售3部 | 2024/5/20 | 2024/5/1 |
| 销售3部 | 2024/5/20 | 2024/5/1 |
| 销售3部 | 2024/5/21 | 2024/5/1 |
| 销售3部 | 2024/5/22 | 2024/5/1 |
| 销售3部 | 2024/5/23 | 2024/5/1 |
| 销售3部 | 2024/5/24 | 2024/5/1 |
| 销售3部 | 2024/5/26 | 2024/5/1 |
| 销售3部 | 2024/5/27 | 2024/5/1 |
----------------------------------------------------------------LAST_VALUE() OVER(PARTITION BY … ORDER BY …):
作用 返回在指定分区内按指定排序顺序的最后一个值。 应用 由于 MySQL 中并没有内置的 LAST_VALUE 函数可以通过 ROW_NUMBER 窗口函数先给每行分配一个序号然后利用 MAX() 函数结合 CASE 表达式来实现类似功能。
示例 找出每个部门的最晚销售日期
SELECTdepartment,sale_date,LAST_VALUE(sale_date) OVER (PARTITION BY department ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM sales;
----------------------------------------------------------------
| department | sale_date | last_sale_date |
| 销售1部 | 2024/5/2 | 2024/5/30 |
| 销售1部 | 2024/5/3 | 2024/5/30 |
| 销售1部 | 2024/5/4 | 2024/5/30 |
| 销售1部 | 2024/5/5 | 2024/5/30 |
| 销售1部 | 2024/5/5 | 2024/5/30 |
| 销售1部 | 2024/5/7 | 2024/5/30 |
| 销售1部 | 2024/5/8 | 2024/5/30 |
| 销售1部 | 2024/5/11 | 2024/5/30 |
| 销售1部 | 2024/5/11 | 2024/5/30 |
| 销售1部 | 2024/5/14 | 2024/5/30 |
| 销售1部 | 2024/5/16 | 2024/5/30 |
| 销售1部 | 2024/5/16 | 2024/5/30 |
| 销售1部 | 2024/5/18 | 2024/5/30 |
| 销售1部 | 2024/5/29 | 2024/5/30 |
| 销售1部 | 2024/5/29 | 2024/5/30 |
| 销售1部 | 2024/5/30 | 2024/5/30 |
| 销售2部 | 2024/5/1 | 2024/5/31 |
| 销售2部 | 2024/5/3 | 2024/5/31 |
| 销售2部 | 2024/5/6 | 2024/5/31 |
| 销售2部 | 2024/5/7 | 2024/5/31 |
| 销售2部 | 2024/5/9 | 2024/5/31 |
| 销售2部 | 2024/5/9 | 2024/5/31 |
| 销售2部 | 2024/5/10 | 2024/5/31 |
| 销售2部 | 2024/5/12 | 2024/5/31 |
| 销售2部 | 2024/5/13 | 2024/5/31 |
| 销售2部 | 2024/5/15 | 2024/5/31 |
| 销售2部 | 2024/5/19 | 2024/5/31 |
| 销售2部 | 2024/5/22 | 2024/5/31 |
| 销售2部 | 2024/5/23 | 2024/5/31 |
| 销售2部 | 2024/5/24 | 2024/5/31 |
| 销售2部 | 2024/5/25 | 2024/5/31 |
| 销售2部 | 2024/5/25 | 2024/5/31 |
| 销售2部 | 2024/5/28 | 2024/5/31 |
| 销售2部 | 2024/5/31 | 2024/5/31 |
| 销售3部 | 2024/5/1 | 2024/5/27 |
| 销售3部 | 2024/5/2 | 2024/5/27 |
| 销售3部 | 2024/5/9 | 2024/5/27 |
| 销售3部 | 2024/5/10 | 2024/5/27 |
| 销售3部 | 2024/5/12 | 2024/5/27 |
| 销售3部 | 2024/5/17 | 2024/5/27 |
| 销售3部 | 2024/5/18 | 2024/5/27 |
| 销售3部 | 2024/5/20 | 2024/5/27 |
| 销售3部 | 2024/5/20 | 2024/5/27 |
| 销售3部 | 2024/5/21 | 2024/5/27 |
| 销售3部 | 2024/5/22 | 2024/5/27 |
| 销售3部 | 2024/5/23 | 2024/5/27 |
| 销售3部 | 2024/5/24 | 2024/5/27 |
| 销售3部 | 2024/5/26 | 2024/5/27 |
| 销售3部 | 2024/5/27 | 2024/5/27 |
----------------------------------------------------------------求当前行的前/后一个值
LAG() OVER(PARTITION BY … ORDER BY …):
作用 用于获取指定列在指定排序顺序下的前一个值。 应用 常用于比较相邻行的值。 示例 找出销售量比上一次销售量增加的产品
select * from (
select department,product,sale_date,quantity,LAG(quantity) OVER (PARTITION BY department, product ORDER BY sale_date) AS previous_quantity
FROM sales ) t1
where quantity previous_quantity;
----------------------------------------------------------------
| department | product | sale_date | quantity | previous_quantity |
| 销售1部 | 1001 | 2024/5/4 | 17 | 15 |
| 销售1部 | 1001 | 2024/5/5 | 30 | 17 |
| 销售1部 | 1001 | 2024/5/8 | 12 | 11 |
| 销售1部 | 1001 | 2024/5/11 | 22 | 12 |
| 销售1部 | 1001 | 2024/5/14 | 25 | 22 |
| 销售1部 | 1001 | 2024/5/30 | 16 | 7 |
| 销售1部 | 1002 | 2024/5/16 | 18 | 10 |
| 销售1部 | 1002 | 2024/5/18 | 30 | 18 |
| 销售2部 | 1002 | 2024/5/3 | 19 | 14 |
| 销售2部 | 1002 | 2024/5/10 | 20 | 10 |
| 销售2部 | 1002 | 2024/5/12 | 25 | 20 |
| 销售2部 | 1002 | 2024/5/24 | 24 | 8 |
| 销售2部 | 1002 | 2024/5/31 | 18 | 11 |
| 销售2部 | 1003 | 2024/5/23 | 20 | 7 |
| 销售2部 | 1003 | 2024/5/25 | 25 | 20 |
| 销售3部 | 1001 | 2024/5/2 | 18 | 10 |
| 销售3部 | 1001 | 2024/5/20 | 18 | 5 |
| 销售3部 | 1003 | 2024/5/17 | 24 | 17 |
| 销售3部 | 1003 | 2024/5/21 | 23 | 21 |
| 销售3部 | 1003 | 2024/5/26 | 30 | 12 |
----------------------------------------------------------------LEAD() OVER(PARTITION BY … ORDER BY …):
作用 用于获取指定列在指定排序顺序下的后一个值。 应用 常用于比较相邻行的值。 示例 找出下一次销售量较本次销量减少的产品
select * from (
selectdepartment,product,sale_date,quantity,LEAD(quantity) OVER (PARTITION BY department, product ORDER BY sale_date) AS next_quantity
FROM sales
) t1
where quantity next_quantity;
----------------------------------------------------------------
| department | product | sale_date | quantity | next_quantity |
| 销售1部 | 1001 | 2024/5/2 | 20 | 15 |
| 销售1部 | 1001 | 2024/5/5 | 30 | 11 |
| 销售1部 | 1001 | 2024/5/14 | 25 | 14 |
| 销售1部 | 1001 | 2024/5/16 | 14 | 9 |
| 销售1部 | 1001 | 2024/5/29 | 9 | 7 |
| 销售1部 | 1002 | 2024/5/5 | 23 | 10 |
| 销售2部 | 1002 | 2024/5/3 | 19 | 13 |
| 销售2部 | 1002 | 2024/5/6 | 13 | 10 |
| 销售2部 | 1002 | 2024/5/12 | 25 | 9 |
| 销售2部 | 1002 | 2024/5/13 | 9 | 8 |
| 销售2部 | 1002 | 2024/5/24 | 24 | 22 |
| 销售2部 | 1002 | 2024/5/25 | 22 | 11 |
| 销售2部 | 1003 | 2024/5/9 | 50 | 8 |
| 销售2部 | 1003 | 2024/5/15 | 8 | 7 |
| 销售3部 | 1001 | 2024/5/2 | 18 | 13 |
| 销售3部 | 1001 | 2024/5/9 | 13 | 5 |
| 销售3部 | 1003 | 2024/5/17 | 24 | 21 |
| 销售3部 | 1003 | 2024/5/21 | 23 | 19 |
| 销售3部 | 1003 | 2024/5/22 | 19 | 18 |
| 销售3部 | 1003 | 2024/5/23 | 18 | 12 |
| 销售3部 | 1003 | 2024/5/26 | 30 | 16 |
----------------------------------------------------------------
