本文介绍了MySQL数据库中的一些高级函数，如数据类型转换、null处理、表达式判断，以及窗口函数如排名、分桶、聚合等。通过实例展示了如何在实际场景中运用这些功能，包括行级排名、分组分析和动态聚合。
掌握 MySQL 基本用法（如select ... from ... where.... group by ....等基本用法）后，可以通过学习高级函数和窗口函数来优化自己的 SQL 技能。
本文面向有 SQL 基础的群众，特别是窗口函数部分。
目录
1.7 得到最近生成的自增 ID：last_insert_id()
1.8 表达式分支函数：case when ... then ... end（重点！）
一、MySQL 高级函数
下文主要介绍这几个高级函数的用法：
1.1 数据类型转换函数 cast()
用法：cast(x as type) 表示将数据 x 转换成 “type” 类型。
例：将字符串“20240325” 转换成日期类型
select cast("20240325" as date);输出为：2024-03-25
1.2 得到第一个非空表达式 coalesce()
用法：coalesce(expr1, expr2, ... ,exprn)，表示返回 n 个表达式 {expr1, expr2, ... ,exprn} 中第一个非空表达式。
例：返回数据 {null，null，“clay”，null，“clay2”} 中第一个非空数据
select coalesce(null,null,"clay",null,"clay2");返回：clay
1.3 表达式判断函数 if()
用法：if(expr,v1,v2)，表示如若表达式 expr 成立，返回结果 v1，否则返回结果 v2。
相当于其他程序的 if...else....语句（例 if expr: v1; else: v2）。
例：返回1>0的结果
select if(1>0,"yes","no");返回： yes
1.4 null替换函数 ifnull()
用法：ifnull(v1,v2), 表示若 v1 为 null，返回 v2，即用 v2 替换为 null 值的 v1。
例：第一表达式为null，返回 “空值”
select ifnull(null,"空值");返回：空值
1.5 判断表达式是否为null：isnull(）
用法：isnull(expr)，表示当 expr 为 null 时，返回 1，否则返回 0。
例：表达式为 “clay” 时
select isnull("clay");返回：0
1.6 判断两个字符串是否相等：nullif(）
用法：nullif(expr1,expr2)，表示如果字符串 expr1 = expr2，返回 null，否则返回第一个表达式expr1。
例1：比较字符串 "11" 和 "2"
select nullif("11","2");返回：11
例1：比较字符串 "a" 和 "a"
select nullif("a","a");返回：null
1.7 得到最近生成的自增 ID：last_insert_id()
用法：last_insert_id(), 表示最近生成的，自己在表中增加的 ID。
例：在 userinfo 表中增加 ID，并查找最近增加的 ID。
-- 删除同名表格，避免后面创建表时出错
drop table if exist userinfo;
-- 创建表格userinfo
create table userinfo (id int(10) NOT null auto_increment primary key,
name varchar(20),
age int(2));
-- 插入数据，id自动累计生成
insert into userinfo (name,age) values ('zhangsan',25);
insert into userinfo (name,age) values ('lisi',27);
-- 查找最近生成的id
select last_insert_id();
返回：2
1.8 表达式分支函数：case when ... then ... end（重点！）
常用用法：case when condition1 then result1 when condition2 then result2 ... when conditionN then resultN else result end
例：在 employee_tb1 表中，记录姓名 name 和签到 signin 情况，将签到次数划分为小于5次，大于5小于10次，大于10次，并返回不同用户的签到等级情况。
select name, (case when signin_count<5 then '<5' when signin_count<10 then '<10' else '>10' end) as level
from (select name, sum(signin) as signin_count from employee_tb1 group by name)t;二、MySQL 窗口函数
窗口函数类似于可以返回聚合值的函数，例如sum()、count()、max()，但是窗口函数又与普通的聚合函数不同，它不会对结果进行分组，因此输出的行数与输入中的函数相同。函数函数语法为：
- 窗口函数的关键字为over。over() 用来指定函数执行的窗口范围，如果 over 后面括号中什么都不写，则意味着窗口包含满足 where 条件的所有数据行，窗口函数基于所有的数据行进行计算；如果不为空，则支持以下两种语法设置窗口：
- partition by 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；
- order by 子句：窗口按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。
- rows between $n$ preceding and $m$ following: 表示当前行的前n行到后m行的窗口范围
- range between $n$ preceding and $m$ following: 表示当前行的值-n到当前行的值+m的窗口范围，即记当前行指定字段值为a，窗口范围该字段值在 [a-n, a+m] 区间内的范围（闭区间）。
- current now & unbounded: 在前面的 “rows...” 和 "range..." 中代替整数值 n 使用，current now 表示当前行（的指定字段值），unbounded preceding 表示分组内的第一行（的指定字段值），unbounded following 表示分组内的最后一行（的指定字段值）。
学习窗口函数前，创建一张学生成绩表用于具体函数的实践应用与理解。
create table student_score(
stu_no int(10), -- 学生编号
course_no varchar(50), -- 科目编号
score int -- 科目成绩
);插入数据
insert into student_score (stu_no,course_no,score) values (1,'L0001',90);
insert into student_score (stu_no,course_no,score) values (1,'L0002',98);
insert into student_score (stu_no,course_no,score) values (1,'L0003',98);
insert into student_score (stu_no,course_no,score) values (1,'L0004',95);
insert into student_score (stu_no,course_no,score) values (1,'L0005',95);
insert into student_score (stu_no,course_no,score) values (2,'L0001',90);
insert into student_score (stu_no,course_no,score) values (2,'L0002',98);
insert into student_score (stu_no,course_no,score) values (2,'L0003',98);
insert into student_score (stu_no,course_no,score) values (2,'L0004',95);
insert into student_score (stu_no,course_no,score) values (2,'L0005',96);
insert into student_score (stu_no,course_no,score) values (3,'L0001',98);
insert into student_score (stu_no,course_no,score) values (3,'L0002',96);
insert into student_score (stu_no,course_no,score) values (3,'L0003',90);
insert into student_score (stu_no,course_no,score) values (3,'L0004',98);
insert into student_score (stu_no,course_no,score) values (3,'L0005',97);
2.1 顺序排名函数 row_number()
用法：row_number(字段），表示对提供的字段进行排序，返回排名序号。
例1：对学生编号是1的学生的各科成绩进行排序，并返回排名。
select *
from (
select stu_no, row_number() over (partition by stu_no order by score desc) as score_order, course_no, score
from student_score
) t
where stu_no = 1;注：即使分数相同时，使用 row_number(）得到的排名序号不相同。排名序号会从1开始不断叠加。
通过 row_number() 得到排名序号后，可以用来得到各个学生最高分的科目。
例2：查询每个学生的分数最高的科目
select *
from (
select stu_no, row_number() over (partition by stu_no order by score desc) as score_order, course_no, score
from student_score
) t
where score_order <= 12.2 跳级排名函数 rank()
用法：与 row_number() 用法相同，最终得到的也是提供字段的排名序号，与 row_number() 函数的区别在于对相同数值，rank() 函数可以得到相同的排名序号，且若存在相同数值，排名序号不连续，例如当降序排序数值为（20，10，10，5）时，排名序号为（1，2，2，4）。
为了更直观地从实例中看到两个排名函数的区别，rank() 示例同样通过查询编号为1的学生的各科成绩排名、查询每个学生的最高分科目展开。
例1：使用 rank() 函数对学生编号是1的学生的各科成绩进行排序，并返回排名。
select *
from (
select stu_no, rank() over (partition by stu_no order by score desc) as score_order, course_no, score
from student_score
) t
where stu_no = 1;例2：使用 rank() 函数查询每个学生的分数最高的科目
select *
from (
select stu_no, rank() over (partition by stu_no order by score desc) as score_order, course_no, score
from student_score
) t
where score_order <= 1运行两个示例的结果便可看出两个函数的区别，比如例 2 使用 rank() 函数时，每个学生都会得到两个科目的成绩，因为分数相同，且排名序号都为1；而 row_number() 函数每个学生只有唯一的排名序号为 1 的科目 ，所以使用 row_number() 时，查询得到的每个学生最高分科目都只有一个。
2.3 不跳级排名函数 dense_rank()
用法：与 rank() 用法相同，最终得到的也是提供字段的排名序号，与 rank() 的相同之处在于相同数值的排名序号相同，与 rank() 区别在于即使存在相同数值，排名序号也连续，例如当降序排序数值为（20，10，10，5）时，排名序号为（1，2，2，3）。
例：对学生编号为2的学生的各科目成绩进行排名，排名序号为连续整数序号，且相同分数为相同序号。
select *
from(
select stu_no, dense_rank() over (partition by stu_no order by score dec) as score_order,
course_no, score
from student_score
) t
where stu_no = 2;2.4 前分析函数 lag()
用法：lag(a,n), 返回窗口中当前字段前n行的值
例：查询科目L0001的成绩，根据成绩排名，查看前一名和当前学生的成绩之差。
select stu_no, course_no, score, pre_score, score-pre_score as diff
from(
select stu_no, course_no, score, lag(score,1) over w as pre_score
from student_score
where course_no in ('l0001')
window w as (partition by course_no order by score)
) t;2.5 后分析函数 lead()
用法：lead(a,n), 返回窗口中当前字段后n行的值
例：查询科目L0001的成绩，根据分数排名，查看后一名和当前学生的成绩之差。
select stu_no, course_no, score, later_score, later_score-score as diff
from(
select stu_no, course_no, score, lead(score,1) over w as later_score
from student_score
where course_no in ('l0001')
window w as (partition by course_no order by score)
) t;2.6 头尾函数 first_value()
用法：first_value(a), 返回窗口中当前字段第一行的值
例1：根据科目分组对成绩排序，查询科目L0005的成绩中第一个（最低分）成绩值。
select stu_no, course_no, score,
first_value(expr) over (partition by course_no order by score) as lowest_score
from student_score
where course_no in ('l0005');例2：根据科目分组对成绩排名，查询每科的成绩中最低分成绩值
select stu_no, course_no, score,
first_value(expr) over (partition by course_no order by score) as lowest_score
from student_score;注：使用窗口函数最终会返回所有记录条，因此示例中的查询返回结果是每个数据条后添加最低分
2.7 头尾函数 last_value()
用法：last_value(a), 返回窗口中当前字段最后一行的值
last_value() 函数与 first_value() 用法一致，如前面例 2 中，如果要查询每科成绩中的最高分，只需要将 first_value() 替换成 last_value() 即可，即
select stu_no, course_no, score,
last_value(expr) over (partition by course_no order by score
range between unbounded preceding and unbounded following) as highest_score
from student_score;例：根据科目分组对成绩排序，查询每个科目的成绩中最高分和最低分的成绩值
select stu_no, course_no, score,
first_value(score) over (partition by course_no order by score) as lowest_score,
last_value(score) over (partition by course_no order by score
range between unbounded preceding and unbounded following) as highest_score
from student_score;注：使用最后一行取值或特定行取值最好划分范围，防止出错，所以示例增加了range between ... and ...语句。
2.8 特定行取值函数 nth_value()
用法：nth_value(a), 返回窗口中当前字段第n行的值
例：查询学生编号为1和2的学生的成绩中各自排名第1和第2的成绩分数
select stu_no, course_no, score,
nth_value(score, 1) over w as score_1,
nth_value(scoer, 2) over w as score_2,
from student_score
where stu_no in (1,2)
windows w as (partition by stu_no order by score desc
range between unbounded preceding and unbounded following);
2.9 数据集分桶函数 ntile()
用法：ntile(n), 将有序数据分为 n 个等级，返回等级数。使用 ntile() 可以较好平均分配等级，但如果记录数不被 n 整除，那么分配结果不完全平均，但当数据量比较大时，分配比例依旧较平均。
例：根据科目目录分组，分成两组。
select ntile(2) over w as nf,
stu_no, course_no, score
from student_score
window w as (partition by course_no order by score);2.10 聚合求和函数 sum() over()
sum(a) over(window) , 表示对字段a在特定的窗口内进行动态的聚合求和。
例：查询学生编号为2的学生的累计分数
select stu_no, course_no, score,
sum(score) over (partition by stu_no order by course_no) as score_sum
from student_score
where stu_no = 2;返回结果是逐行累加，若用range或rows可实现所有记录条结果都为所有科目总和成绩。
2.11 聚合求平均函数 avg() over()
avg(a) over(window) , 表示对字段a在特定的窗口内进行动态的聚合求平均值。
例：查询学生编号为2的平均成绩
select stu_no, course_no, score,
avg(score) over (partition by stu_no order by course_no
range between unbounded preceding and unbounded following) as score_avg
from student_score
where stu_no = 2;2.12 聚合求最大值函数 max() over()
max(a) over(window) , 表示对字段a在特定的窗口内进行动态的聚合求最大值。
例：查询学生编号为2的最高成绩
select stu_no, course_no, score,
max(score) over (partition by stu_no order by course_no
range between unbounded preceding and unbounded following) as score_max
from student_score
where stu_no = 2;2.13 聚合求最小值函数 min() over()
min(a) over(window) , 表示对字段a在特定的窗口内进行动态的聚合求最小值。
例：根据学生编号分组并对科目编号进行排序，查询所有学生的最低分数。
select stu_no, course_no, score,
min(score) over (partition by stu_no order by course_no
range between unbounded preceding and unbounded following) as score_min
from student_score;2.14 聚合求总条数函数 count() over()
min(a) over(window) , 表示对字段a在特定的窗口内进行动态的聚合求分组的总条数。
例：计算每个学生参加考试的总科目数。
select stu_no, course_no, score
count(score_no) over (partition by stu_no order by course_no
range between unbounded preceding and unbounded following) as course_count
from student_score;主要参考资料：《MySQL的应用实战与性能调优》 张文亮编著（该书籍很适合查找函数）
一点优化想法
使用窗口函数会返回原来数据一致的记录条数，但有些时候需求不需要返回全部记录条，所以一些直接的想法就是使用连结 join 函数来去除一些多余的记录条。
(作者目前求职中....... 要是有sql和大模型下游任务需求的工作介绍可以私聊~）
扫一扫
956
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
