mysql的窗口函数

一、窗口函数

1.1 什么是窗口函数

窗口函数，也叫OLAP函数（Online Analytical Processing，联机分析处理），可以对数据库进行实时分析处理，窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数:
专用窗口函数：rank，dense_rank，row_number等；
聚合函数：sum，avg，count，max，min等。
因为窗口函数是对where或者group by子句处理后的结果进行操作，原则上窗口函数只能出现在select子句中。

1.2 窗口函数作用

在数据库应用中，经常会遇到分组排名的数据分析需求，例如下面的业务需求：
排名问题：每个部门按业绩来排名；
topN问题：找出每个部门排名前N的员工信息；

二、数据准备

2.1 表结构

CREATE TABLE `stu_score` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(10) NOT NULL COMMENT '学号',
  `stu_name` varchar(10) NOT NULL COMMENT '姓名',
  `cour_no` varchar(10) NOT NULL COMMENT '课程号',
  `cour_name` varchar(20) NOT NULL COMMENT '课程名',
  `score` int NOT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) COMMENT='学生课程成绩表';

CREATE TABLE `order_form` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `order_no` varchar(10) DEFAULT NULL COMMENT '订单编号',
  `order_comment` varchar(10) DEFAULT NULL COMMENT '订单内容',
  `order_dt` varchar(10) DEFAULT NULL COMMENT '订单日期',
  PRIMARY KEY (`order_id`)
) COMMENT='订单表';

2.2 测试数据

INSERT INTO stu_score
(stu_no, stu_name, cour_no, cour_name, score) 
VALUES
('lihua', '张三', 'match', '数学', 99),
('lihua', '张三', 'English', '英语', 99),
('lihua', '张三', 'physics', '物理', 99),
('sunlei', '李四', 'match', '数学', 95),
('sunlei', '李四', 'English', '英语', 40),
('sunlei', '李四', 'physics', '物理', 88),
('wangping', '王五', 'match', '数学', 67),
('wangping', '王五', 'English', '英语', 78),
('wangping', '王五', 'physics', '物理', 61),
('zhangfu', '张大强', 'match', '数学', 95),
('zhangfu', '张大强', 'English', '英语', 87),
('zhangfu', '张大强', 'physics', '物理', 61),
('liuyishou', '刘阳', 'match', '数学', 91),
('liuyishou', '刘阳', 'English', '英语', 68),
('liuyishou', '刘阳', 'physics', '物理', 70),
('chenyang', '白展堂','match', '数学', 77),
('chenyang', '白展堂', 'English', '英语', 78),
('chenyang', '白展堂', 'physics', '物理', 99);

INSERT INTO order_form(order_no, order_comment, order_dt)
VALUES 
('001', '买人', '2024-03-01'),
('002', '买砖', '2024-03-01'),
('003', '买电', '2024-03-01'),
('004', '买水', '2024-03-02'),
('005', '买线', '2024-03-03'),
('006', '买酒', '2024-03-03'),
('007', '买衣', '2024-03-04'),
('008', '买帽', '2024-03-04'),
('009', '买裤', '2024-03-05'),
('010', '买房', '2024-03-05'),
('011', '买车', '2024-03-06'),
('012', '买肉', '2024-03-07'),
('013', '买蛋', '2024-03-08'),
('014', '买吃', '2024-03-08'),
('011', '买鱼', '2024-03-08'),
('012', '买人', '2024-03-08'),
('013', '买茶', '2024-03-08'),
('014', '买琴', '2024-03-09'),
('015', '买棋', '2024-03-09'),
('016', '买书', '2024-03-09'),
('017', '买画', '2024-03-09'),
('018', '买笔', '2024-03-10'),
('019', '买猪', '2024-03-10'),
('020', '买羊', '2024-03-10'),
('021', '买牛', '2024-03-10'),
('022', '买鹅', '2024-03-10'),
('023', '买鸭', '2024-03-10'),
('024', '买鸡', '2024-03-10');

三、函数详解

3.1 序号函数

3.1.1 rank()：返回数据集中每个值的排名，排名是根据当前行之前的行数加1，不包含当前行，该函数排序的关联值可能产生顺序上的空隙。例如，查看各科成绩的排名信息，如下：

SELECT
	cour_no,cour_name,
	RANK() OVER(PARTITION BY cour_no ORDER BY score DESC) AS paiming,
	stu_no,stu_name,score
FROM stu_score

结果如下图所示：
在这里插入图片描述
如上图，顺序间隙是指在出现相同分数时，相同分数的排名相同，但是下一个名次的计数会越过排名相同造成的数量。

3.1.2 dense_rank()：返回一组数值中每个数值的排名，该函数排序时不会产生顺序上的空隙。如上例换成使用dense_rank()，如下：

SELECT 
    cour_no,cour_name,
    DENSE_RANK()  OVER(PARTITION BY cour_no ORDER BY score DESC) AS paiming,
    stu_no ,stu_name ,score 
FROM stu_score

在这里插入图片描述
排名之间是没有数据间隔的。

3.1.3 row_number()：为每行数据返回一个唯一的顺序的行号，从1开始，根据行在窗口分区内的顺序。如下：

SELECT
cour_no,cour_name,
row_number() OVER(PARTITION BY cour_no ORDER BY score DESC) AS paiming,
stu_no,stu_name,score
FROM stu_score

在这里插入图片描述

3.2 分布函数

cume_dist()：表示当前行及小于当前行在窗口分区总行数中的占比。例如在订单数据中，分别统计每一天的累计订单总数占历史订单的百分比，则可以使用cume_dist()函数，如下分析：
由测试数据可知，订单总数共28个，历史订单累计数量从2024-03-01到2024-03-10分别为：3，4，6，8，10，11，12，17，21，28，我们最终需要得到的是这10个数字与28的比。

SELECT 
    order_id ,order_no ,order_comment ,order_dt ,
    CUME_DIST() OVER(ORDER BY order_dt ASC) AS per
FROM order_form ;

在这里插入图片描述
以上是全量累计数据的比，所以在窗口函数中不需要按字段值进行分区，直接排序即可，per的列值给出了order_dt的值对应的百分比，所以只需要在该结果集上进行加工，就能获取累计订单占总订单的百分比，使用如下SQL：

SELECT
order_dt,per
FROM (
	SELECT
	order_id,order_no,order_comment,order_dt
	,CUME_DIST() OVER(ORDER BY order_dt ASC) AS per
	FROM order_form ) AS tmp
GROUP BY order_dt, per;

在这里插入图片描述

3.3 偏移函数

lag()：向上偏移，返回当前字段前n行的数据；
lead()：向下偏移，返回当前字段后n行的数据；
偏移函数通常用于取时间间隔、做记录差值、取某数据前后N行等形式的数据处理需求，该函数可接受三个参数，第一个参数是表达式或者字段（即填充的值），第二个参数是偏移量，第三个参数是控制赋值（即当第一个参数按照第二个参数的偏移量无法确定填充值时，按何规则填充）。

查询每一笔订单的前第1笔订单的内容，没有前第2比订单的用汉字“无”填充，如下SQL：

select *, lag(order_comment, 1, "无") over() as per
from order_form

select *, lead(order_comment, 1, "无") over() as per
from order_form

在这里插入图片描述

3.4 头尾函数

头尾函数包含first_value()和last_value()，只选择分组排序中的第一条数据和最后一条数据，求每个学生的课程成绩与最高成绩之间的差距，使用SQL如下：

select *,
first_value(score) over(partition by cour_no order by score desc) as max_score,
(first_value(score) over(partition by cour_no order by score desc) - score) as first_diff
from stu_score

在这里插入图片描述