MySQL窗口函数（mysql窗口函数有哪些）

窗口限定一个范围，可以理解为满足某些条件的记录集合。窗口函数也就是在窗口范围内的执行的函数

窗口函数有 over 关键字，指定函数执行的范围。可分为三部分：分组字句 (partition by)，排序字句 (order by)，滑动窗口字句 (rows)

cid（班级id） sname （学生姓名） score（分数） 001 张三 78 001 李四 82 002 小明 90 001 王五 67 002 小红 85 002 小刚 90

1.4.1 例1：查询各班级总分

在这里插入图片描述

1.4.2 例2：查询各班级累计总分

在这里插入图片描述

1.4.3 分区子句（partition by）

① 不分区可以写成 partition by null，或者直接不写
② 后面可以跟多个列，例如 partition by cid, sname
partition by 和 group by的区别： partition by 不将行数去重，group by 会将行数去重

1.4.4 排序子句（order by）

① 不排序可以写成 order by null，或者直接不写
② 后面可以跟多个列，例如 order by cid, sname

1.4.5 窗口子句（rows）

语法概述：
① 起始行：N preceding / unbounded preceding
② 当前行：current row
③ 终止行：N following / unbounded following
举例：
① 从分区内的前面所有行到当前行：rows between unbounded preceding and current row
② 从分区内的前面 2 行到当前行：rows between 2 preceding and current row
③ 从分区内的当前行到后面所有行：rows between current row and unbounded following
④ 从分区内的当前行到后面 3 行：rows between current row and 3 following
注意：
① partition by 分区字句后，若 order 排序子句后缺少 rows 窗口子句，窗口规范默认为：rows between unbounded preceding and current row
② partition by 分区字句后，若 order 排序子句和 rows 窗口子句都缺少，窗口规范默认为：rows between unbounded preceding and unbounded following

1.4.6 总体执行流程

① 通过 partition by 和 order by 确定大窗口（定义出上界和下界）
② 通过 rows 字句针对每一行数据，确定小窗口（即滑动窗口）
③ 针对每行的小窗口内的数据，执行函数并生成新的列

1.5.1 排序类函数

① row_number：序号不重复，且序号连续（即连续排名，不考虑并列）。例如：1，2，3 …
② rank：序号可重复，且序号不连续（即跳跃排名，考虑并列）。例如：1，2，2，4 …
③ dense_rank：序号可重复，且序号连续（即连续排名，考虑并列）。例如：1，2，2，3 …

在这里插入图片描述

1.5.2 聚合类函数

sum，avg，count，max，min

1.5.3 跨行类函数

在这里插入图片描述

cid（班级id） sname （学生姓名） course（学科） score（分数） 001 张三语文 78 002 小刚语文 71 001 李四数学 56 001 王五数学 97 002 小明数学 54 … … … …

题目 1：查询每个学生成绩最高的三个学科（分组内 Top N 问题）

在这里插入图片描述

题目 2：查询每个学科都高于班级学科平均分的学生（汇总分析问题）

在这里插入图片描述

empno ename hire_date salary dept_no 001 Adam 2018-03-01 1000 A 002 Bill 2021-03-01 1200 A 003 Cindy 2016-03-01 1500 A 004 Danney 2020-03-01 5000 A 005 Eason 2020-03-01 4000 B 006 Fred 2018-03-01 3500 B 007 Gary 2017-03-01 1800 B 008 Hugo 2020-03-01 4500 B

题目 1：查询每个部门工资最高的前三个员工信息（分组内 Top N 问题）

在这里插入图片描述

题目 2：查询员工工资占所属部门总工资的百分比（汇总分析问题）

在这里插入图片描述

题目 3：对各部门员工的工资进行升序排列，排名前 30% 为底层，30% - 80% 为中层，80% 以上为高层，并打上标签

在这里插入图片描述

题目 4：查询每年入职总数以及截至本年累计入职总人数（本年总入职人数 + 本年之前所有年的入职人数之和）

在这里插入图片描述

① 本文用窗口函数解决的 2 个主要问题：分区内 Top N 问题，汇总分析问题

② 分区内 Top N 公式

③ 窗口函数 → 生成辅助列（用于计算百分比等）

④ 重要：with 子句 → 生成临时表，把复杂的问题拆分成多个子问题，再用临时表表达

今天的文章 MySQL窗口函数（mysql窗口函数有哪些）分享到此就结束了，感谢您的阅读。

MySQL窗口函数（mysql窗口函数有哪些）

1.4.1 例1：查询各班级总分

1.4.2 例2：查询各班级累计总分

1.4.3 分区子句（partition by）

1.4.4 排序子句（order by）

1.4.5 窗口子句（rows）

1.4.6 总体执行流程

1.5.1 排序类函数

1.5.2 聚合类函数

1.5.3 跨行类函数

相关推荐