SQL开窗函数

已于 2024-05-21 21:10:38 修改
阅读量1.5k 收藏 27
点赞数 51
分类专栏： SQL-数据分析必备技能 文章标签： sql 数据库
于 2024-05-21 21:08:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/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	|	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个订单):

SELECT
    product,
    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():

示例： 计算每个部门的销售记录总和：

SELECT
    department,
    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()值

示例： 查找每个部门在销售日期的最大销售数量：

SELECT
    department,
    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	|
+-------+--------+-----------+------+------------+------+------+--------+-------
-----------+

示例： 查找每个部门在销售日期的最小销售数量：

SELECT
    department,
    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 …):

作用： 返回在指定分区内按指定排序顺序的第一个值。
应用： 常用于找出每个分组内的第一个值。

示例： 找出每个部门的最早销售日期：

SELECT
    department,
    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 表达式来实现类似功能。

示例： 找出每个部门的最晚销售日期：

SELECT
    department,
    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 (
select
    department,
    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	|
+-------+--------+-----------+------+------------+------+------+--------+
打工仔小任
关注
  • 51
    点赞
  • 踩
  • 27
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
SQL开窗函数总结
now
06-03 2306
分析函数中，窗口函数内的ROWS条件
SQL 开窗函数
sinat_41207450的博客
09-05 897
1、开窗函数/分析函数：over() 2、几个排序函数的差异 3、主要开窗函数
SQL开窗函数之排序函数
weixin_46599926的博客
12-11 2709
当我们需要进行一些比较复杂的子查询时，聚合函数就会非常的麻烦，因此可以使用开窗函数进行分组再运用函数查询。
hiveSQL开窗函数详解
jx的博客
02-28 2390
窗口函数也称OLAP函数，对数据库进行实时分析处理。
SQL开窗函数（窗口函数）详解
热门推荐
流水随清风的博客
06-29 5万+
一、什么是开窗函数 开窗函数/分析函数：over() 开窗函数也叫分析函数，有两类：一类是聚合开窗函数，一类是排序开窗函数。 开窗函数的调用格式为： 函数名(列名) OVER(partition by 列名 order by列名) 。 如果你没听说过开窗函数，看到上面开窗函数的调用方法，你可能还会有些疑惑。但只要你了解聚合函数，那么理解开窗函数就非常容易了。 我们知道聚合函数对一组值执行计算并返回单一的值，如sum()，count()，max()，min()， avg()等，这些函数常与grou
sql 开窗函数小结
vex123的专栏
04-15 720
1、相关函数说明 over() -- 指定窗口 窗口大小变化(partition by & order by ) current row -- 当前行 n preceding -- 往前1行 n following -- 往后1行 unbounded -- 起点 unbounded preceding -- 前面起点 unbounded followding -- 后面起点 lag(col,n,default) -- 往前n行数据 lead(col,n,default) -- 往
SQL开窗函数之基本用法和聚合函数
weixin_46599926的博客
12-10 1992
当我们需要进行一些比较复杂的子查询时，聚合函数就会非常的麻烦，因此可以使用开窗函数进行分组再运用函数查询
常见的sql开窗函数整理
yuqc1028的博客
09-15 285
RANK()：为每个分组分配一个排名，根据指定的排序规则进行排名。以上是一些常见的 SQL 开窗函数的示例，它们可以应用于不同的数据操作和分析场景，提供灵活的数据处理和分析能力。SUM()、AVG()、MAX()、MIN() 等聚合函数：可以在指定的窗口范围内对数据进行聚合计算。DENSE_RANK()：类似于 RANK()，但它在排名平级的情况下会分配下一个最大的排名值。ROW_NUMBER()：为每个分组分配一个唯一的行号，可以根据指定的排序规则确定行号顺序。
SQL开窗函数的具体实现详解
09-08
主要介绍了SQL开窗函数的具体实现详解，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
SQL开窗函数详解.pdf
03-24
SQL开窗函数（Window Function）是一种用于对查询结果集中的一组行进行计算或对比的函数。它可以在查询中创建类似于窗口（window）或范围（window）的概念，使得可以在分组级别上执行聚合、排序、排名等操作，而不...
SQL开窗函数介绍以及示例
03-24
sql开窗函数详解 SQL开窗函数（Window Functions）是一种用于在查询结果集中执行聚合、排序和分析操作的强大工具。它们可以在不破坏查询结果集的情况下，对每一行数据进行计算，比如计算行的排名、累计和、移动平均...
SQL开窗函数的基本概念、用法以及一些常见的开窗函数
03-24
SQL开窗函数（Window Functions）是SQL中用于在结果集的每一行上执行计算的一类函数。它们通常与OVER()子句一起使用，以定义窗口或分区，并在这些窗口或分区上执行计算。开窗函数为分析型查询提供了强大的工具，使您...
基础—SQL—DQL（数据查询语言）聚合函数
最新发布
m0_74363339的博客
05-30 487
这篇博客主要讲到了SQL分类中的DQL（数据查询语言）—聚合函数的讲解，聚合函数比较简单，但它主要配合着分组查询进行数据查询的操作，注意NULL的字段值不参与聚合函数的运算......
SQL Server定期收缩日志文件详细步骤——基于SQL Server 2012
lxcw_sir的博客
05-29 401
SQL Server定期收缩日志文件详细步骤——基于SQL Server 2012
PostgreSQL常用插件
PGCCC的博客
05-29 621
1.提供 PostgreSQL 实例的操作系统级别的资源使用情况，包括缓存命中率和磁盘 IO 等。通过使用这些插件，您可以大大增强 PostgreSQL 的功能，满足各种复杂的应用需求。1.提供三元组（trigram）相似度匹配和索引功能，常用于模糊搜索和文本相似度匹配。1.增加对地理空间数据的支持，使 PostgreSQL 成为一个功能强大的空间数据库。1.PostgreSQL 的过程语言，允许用户编写存储过程和触发器。1.基于逻辑复制的插件，提供更细粒度的数据复制控制和双向复制能力。
postgressql——Tuple学习（2）
Re_view的博客
05-27 354
postgressql——Tuple学习（2）
PostgreSQL发展史
PGCCC的博客
05-28 1025
PostgreSQL 的发展史反映了其从学术研究项目到全球领先的开源数据库系统的演变过程。通过不断的创新和社区支持，PostgreSQL 已经成为一个功能强大、性能优越的数据库管理系统，广泛应用于各个行业的关键业务场景中。#postgresql培训。
家庭理财管理系统（含源码+sql+视频导入教程+文档）
stopTheworld2022的博客
05-30 812
家庭理财管理系统拥有两种角色： 管理员：财务管理、财务类型管理、财务计划管理、家庭财务报表、用户管理、支出排行等 家庭成员：添加个人财务计划、查看家庭支出收入等信息
SQL开窗函数的使用场景
07-15
SQL开窗函数的使用场景有很多，以下是一些常见的场景： 1. 分组计算：开窗函数可以在每个分组内进行计算，例如计算每个销售人员的销售额排名。可以使用开窗函数将每个销售人员的销售额排序，并为每个销售人员分配一...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值