目录
一、什么是窗口函数
窗口函数的定义
窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
窗口的概念可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,窗口大小都是固定的,这种属于静态窗口;不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。
其中,over是关键字,用来指定函数执行的窗口范围,包含三个分析子句:分组(partition by)子 句,排序(order by)子句,窗口(rows)子句
窗口函数的语法格式
<窗口函数> OVER (PARTITION BY <用于分组的列名>
ORDER BY <用于排序的列名>)
二、窗口函数的分类
专用窗口函数
RANK()
DENSE_RANK()
ROW_NUMBER()
三者的区别:
成绩 | rank() 排名结果考虑并列排名但不连续 | dense_rank() 排名结果考虑并列排名且连续 | row_number() 排名结果不考虑并列排名 |
100 | 1 | 1 | 1 |
100 | 1 | 1 | 2 |
100 | 1 | 1 | 3 |
90 | 4 | 2 | 4 |
聚合函数作为窗口函数
最值
MAX(exp) 、MIN(exp)
计算
COUNT(exp) 、SUM(exp)、AVG(exp)
偏移分析函数
Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)
作为 独立的列。
LAG(exp)
LEAD(exp)
三、窗口函数的功能
在不减少原表的行数的基础上同时具有分组和排序的功能
GROUP BY 和 PARTITION BY 的区别
GROUP BY
GROUP BY用于将数据按照某个或多个列的值进行分组,然后对每个分组进行聚合操作。GROUP BY通常与聚合函数(如SUM、COUNT、AVG等)一起使用,以计算每个分组的统计结果。GROUP BY生成的结果集中,每个分组都有唯一的键值,并且可以使用HAVING子句对结果进行进一步过滤。
PARTITION BY
PARTITION BY用于将表或索引的数据划分为多个分区,每个分区可以单独进行管理和操作。PARTITION BY通常用于优化大型表的查询性能,通过将数据分散存储在不同的分区中,可以减少查询的范围。PARTITION BY可以按照列的值范围、列表或哈希值等方式进行分区,提供了灵活的分区策略选择。
区别分析
-
功能不同:GROUP BY用于对数据进行分组和聚合,得到每个分组的统计结果。PARTITION BY用于将表或索引的数据划分为多个分区,以提高查询性能。
-
数据操作层面不同:GROUP BY操作在查询结果集上进行,不会改变数据表的物理存储结构。PARTITION BY操作在数据表或索引的存储层面进行,会改变数据的物理分布。
-
使用场景不同:GROUP BY适用于对查询结果进行分组和聚合操作,常用于统计分析、报表生成等场景。PARTITION BY适用于大表的数据管理和查询优化,常用于分布式存储、数据仓库等场景。
-
group by是分组函数,partition by是分区函数
-
执行顺序不同:
常用sql关键字的执行顺序:
FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT
partition by作为窗口函数的一部分只能写于SELECT子句中,所以顺序要在GROUP BY之后
使用注意
-
在使用GROUP BY时,注意选择适当的聚合函数和列进行分组,并合理使用HAVING子句进行结果过滤。
-
在使用PARTITION BY时,考虑表的大小、查询频率和数据分布等因素,选择合适的分区策略。
-
注意对分区表进行维护和管理,及时调整分区策略以适应数据的变化。
总结
GROUP BY和PARTITION BY是MySQL中常用的关键字,用于数据的分组和分区操作。尽管它们在功能上有一定的相似性,但在实际应用中存在重要的区别。GROUP BY适用于对查询结果进行分组和聚合,常用于统计分析和报表生成等场景。PARTITION BY适用于大表的数据管理和查询优化,常用于分布式存储和数据仓库等场景。在实际使用中,我们应该根据具体需求选择合适的关键字,并结合最佳实践进行正确的数据处理和分析操作,以提高查询性能和数据管理效率。
* 此段内容均来源于网络搜索引用
四、窗口函数的应用
排名问题
e.g.1 学生排名
# 创建表及插入数据
CREATE TABLE 成绩表(
学号 CHAR(4),
课程号 CHAR(4),
成绩 INT(2));
INSERT INTO 成绩表 VALUES
('0001','0001',80),
('0001','0002',90),
('0001','0003',99),
('0002','0002',60),
('0002','0003',80),
('0003','0001',80),
('0003','0002',80),
('0003','0003',80);
# 查询
SELECT *,
ROW_NUMBER() OVER (PARTITION BY 学号 ORDER BY 成绩) AS 排名
FROM 成绩表;
e.g.2 雇员排名
# 建表与插入数据
CREATE TABLE 雇员表(
雇员编号 CHAR(5),
出生日期 DATE,
名字 VARCHAR(10),
姓 VARCHAR(10),
性别 CHAR(1),
雇用日期 DATE);
INSERT INTO 雇员表 VALUES
('10001','1953-9-2','Georgi','Facello','M','1986-6-26'),
('10002','1964-6-2','Bezalel','Simmel','F','1985-11-21'),
('10003','1955-1-21','Kyoichi','Maliniak','M','1989-9-12'),
('10004','1953-4-20','Anneke','Preusig','F','1989-6-2');
# 查询
SELECT 名字
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY 名字) AS 序号, 名字
FROM 雇员表) AS 字母表
WHERE MOD(序号,2) = 1;
e.g.3 去除最大值、最小值后求平均值
# 建表与插入数据
CREATE TABLE 薪水表(
雇员编号 CHAR(5),
部门编号 CHAR(1),
薪水 INT(5));
INSERT INTO 薪水表 VALUES
('10001','1',60117),
('10002','2',92102),
('10003','2',86074),
('10004','1',66596),
('10005','1',66961),
('10006','2',81046),
('10007','2',94333),
('10008','1',75286),
('10009','2',85994),
('10010','1',76884);
# 查询
SELECT a.部门编号,ROUND(AVG(a.薪水)) AS 平均薪资
FROM(
SELECT *,
RANK() OVER (PARTITION BY 部门编号 ORDER BY 薪水 DESC) AS 降序排列,
RANK() OVER (PARTITION BY 部门编号 ORDER BY 薪水 ASC) AS 升序排列
FROM 薪水表
) AS a
WHERE a.降序排列>1 AND a.升序排列>1
GROUP BY a.部门编号;
e.g.4 去除最大值、最小值后求平均值(变式)
# 建表与插入数据
CREATE TABLE 成绩表(
学号 CHAR(1),
成绩 INT(2));
INSERT INTO 成绩表 VALUES
('1',80),
('2',75),
('3',88),
('4',76),
('5',91),
('6',99);
# 查询
SELECT ROUND(AVG(a.成绩),2) AS 平均成绩
FROM (
SELECT *,
RANK() OVER (ORDER BY 成绩 DESC) AS 降序排列,
RANK() OVER (ORDER BY 成绩 ASC) AS 升序排列
FROM 成绩表
) AS a
WHERE a.降序排列>1 and a.升序排列>1;
TOP N问题
在对数据分组之后,取每组里面的最大值、最小值
e.g.1查询前三名的成绩
# 建表与插入数据
CREATE TABLE 成绩表(
课程号 CHAR(4),
学号 CHAR(4),
成绩 INT(2));
INSERT INTO 成绩表 VALUES
('0001','0001',80),
('0001','0003',80),
('0002','0001',90),
('0002','0003',80),
('0002','0002',60),
('0002','0004',55),
('0003','0001',99),
('0003','0002',80),
('0003','0003',80);
# 查询
SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (PARTITION BY 课程号 ORDER BY 成绩 DESC) AS 排名
FROM 成绩表
) AS a
WHERE 排名<=3;
e.g.2 查询排在前两名的工资
# 建表与插入数据
CREATE TABLE 雇员表(
工号 INT(1),
姓名 CHAR(2),
工资 INT(5),
部门编号 INT(1));
INSERT INTO 雇员表 VALUES
(1,'张三',85000,1),
(2,'李四',80000,2),
(3,'王朝',60000,2),
(4,'马汉',90000,1),
(5,'猴子',69000,2),
(6,'扎扎',85000,1),
(7,'赵五',70000,1);
# 查询
SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (PARTITION BY 部门编号 ORDER BY 工资 DESC) AS 排名
FROM 雇员表
) AS a
WHERE 排名<=2;
前百分之N问题
e.g.1 成绩前40%的学生信息
# 建表与插入数据
CREATE TABLE 成绩表(
学号 CHAR(4),
班级 INT(1),
成绩 INT(2));
INSERT INTO 成绩表 VALUES
('0001',1,86),
('0002',1,95),
('0003',2,89),
('0004',1,83),
('0005',2,86),
('0006',3,92),
('0007',3,86),
('0008',1,88);
# 查询
SELECT 学号,班级,成绩
FROM (
SELECT *,
PERCENT_RANK() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS 百分位排名
FROM 成绩表
) AS a
WHERE 百分位排名<=0.4;
e.g.2 用户访问次数
# 建表与插入数据
CREATE TABLE 用户访问次数表(
用户编号 INT(2),
用户类型 CHAR(1),
访问次数 INT(3));
INSERT INTO 用户访问次数表 VALUES
(10,'A',352),
(6,'C',209),
(7,'C',110),
(4,'E',101),
(2,'B',53),
(20,'A',53),
(11,'C',33),
(1,'A',30),
(9,'E',29),
(8,'B',6);
# 查询
#方法一
SELECT 用户类型,ROUND(AVG(访问次数),1) AS 平均访问次数
FROM
(SELECT *
FROM
(SELECT *,
row_number() OVER (ORDER BY 访问次数 DESC) AS 排名
FROM 用户访问次数表) AS a
WHERE 排名 >
(SELECT COUNT(*)
FROM 用户访问次数表)*0.2) AS b
GROUP BY 用户类型
ORDER BY 用户类型;
#方法二
SELECT 用户类型,ROUND(AVG(访问次数),1) AS 平均访问次数
FROM
(SELECT *
FROM
(SELECT *,
PERCENT_RANK() OVER (ORDER BY 访问次数 DESC) AS 百分位排名
FROM 用户访问次数表)AS a
WHERE 百分位排名 > 0.2) AS b
GROUP BY 用户类型
ORDER BY 用户类型;
累计问题
e.g.1 学生成绩累计求和
# 建表与插入数据
CREATE TABLE 学生成绩表(
学号 CHAR(4),
课程号 CHAR(4),
成绩 INT(2));
INSERT INTO 学生成绩表 VALUES
('0001','0003',99),
('0001','0002',90),
('0001','0001',80),
('0002','0003',80),
('0003','0001',80),
('0003','0002',80),
('0003','0003',80),
('0002','0002',60);
# 查询
SELECT *,
sum(成绩) OVER (ORDER BY 成绩 DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累计
FROM 学生成绩表
每组内比较问题
e.g.1 每组大于平均值
# 建表与插入数据
CREATE TABLE 成绩表(
姓名 CHAR(2),
科目 CHAR(2),
成绩 INT(2));
INSERT INTO 成绩表 VALUES
('张三','语文',90),
('李四','语文',81),
('王朝','语文',79),
('马汉','语文',88),
('张三','数学',85),
('李四','数学',86),
('王朝','数学',92),
('马汉','数学',83),
('张三','英语',87),
('李四','英语',98),
('王朝','英语',93),
('马汉','英语',95);
# 查询
SELECT 科目,姓名
FROM
(SELECT *,
AVG(成绩) OVER (PARTITION BY 科目) AS 平均成绩
FROM 成绩表) AS a
WHERE 成绩 > 平均成绩;
e.g.2 低于平均薪水的雇员信息
# 建表与插入数据
CREATE TABLE 雇员表(
雇员编号 CHAR(5),
部门编号 CHAR(1),
薪水 INT(5));
INSERT INTO 雇员表 VALUES
('10001','1',60117),
('10002','2',92102),
('10003','2',86074),
('10004','1',66596),
('10005','1',66961),
('10006','2',81046),
('10007','2',94333),
('10008','1',75286),
('10009','2',85994),
('10010','1',76884);
# 查询
SELECT *
FROM
(SELECT *,
ROUND(AVG(薪水) OVER (PARTITION BY 部门编号)) AS 平均薪水
FROM 雇员表) AS a
WHERE 薪水 < 平均薪水;
连续出现N次问题
e.g.1 连续3次为球队得分的球员名单
# 查询
SELECT DISTINCT 球员姓名
FROM
(SELECT 球员姓名,
LAG(球员姓名,1) OVER (PARTITION BY 球队 ORDER BY 得分时间) AS 姓名1,
LAG(球员姓名,2) OVER (PARTITION BY 球队 ORDER BY 得分时间) AS 姓名2
FROM 分数表) AS a
WHERE 球员姓名 = 姓名1 AND 球员姓名 = 姓名2;
e.g.2 连续出现N次的问题(举一反三)
# 建表及插入数据
CREATE TABLE 成绩表(
学号 CHAR(4),
成绩 INT(2));
INSERT INTO 成绩表 VALUES
('0001',89),
('0002',76),
('0003',76),
('0004',84),
('0005',84),
('0006',84),
('0007',76),
('0008',91),
('0009',88),
('0010',86);
# 查询
SELECT DISTINCT 成绩
FROM
(SELECT
成绩,
LAG(成绩,1) OVER (ORDER BY 学号) AS 成绩1,
LAG(成绩,2) OVER (ORDER BY 学号) AS 成绩2
FROM 成绩表) AS a
WHERE 成绩 = 成绩1 AND 成绩 = 成绩2;
e.g.3 连续访问记录
# 建表及插入数据
CREATE TABLE 访问记录表(
用户ID CHAR(4),
访问的页面 INT(1),
访问页面的时间 TIME);
INSERT INTO 访问记录表 VALUES
('1001',1,'3:01:01'),
('1001',1,'3:04:00'),
('1001',1,'3:05:43'),
('1001',1,'3:07:20'),
('1001',2,'3:10:00'),
('1001',2,'3:13:00'),
('1001',1,'3:15:15'),
('1001',2,'4:00:08'),
('1001',3,'5:15:29'),
('1001',3,'6:15:10'),
('1001',3,'7:56:10'),
('1001',3,'8:08:00');
# 查询
SELECT 用户ID,访问的页面,访问页面的时间
FROM
(SELECT *,
LAG(访问的页面,1) OVER (PARTITION BY 用户ID ORDER BY 访问页面的时间 ASC) AS 上一次访问的页面
FROM 访问记录表) AS a
WHERE 上一次访问的页面 IS NULL OR 上一次访问的页面 != 访问的页面;
五、注意事项
1.partition子句可以省略,省略就是不指定分组,只执行排序子句不分组
2.窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中
声明:以上涉及的题目和相关知识点均为作者完成 猴子 《SQL面试宝典》中例题练习的记录与总结,仅作日常参考使用。