前言

本文为作者学习SQL的个人笔记，旨在记录自己学习的常用语法，整体学习顺序为先学基本框架语法再慢慢补充零碎知识点。运行环境为MySQL，故使用所有语法均使用MySQL版本。

基础结构

SQL查询语句实现的基本功能，从一张表中，取出一或多个列，这些列需要符合一些条件，最终取出的数据要根据一或多个列的大小进行排序。上面加粗的便是我们需要提供给SQL的参数，具体语句如下（所有SQL语句都需要以分号结束）：

1
2
3
4
select 列名
from 表名
where 条件
order by 列名（用于排序）;

select

select语句可以查询一个或多个列，或者查询所有列，具体的输入如下：

1
2
3
select 列名  # 查询一个列
select 列名1, 列名2  # 查询多个列
select *  # 查询所有列

where

where子句类似于一般编程语言中的if-else语句，即给定筛选条件，一般会包含运算符，常见的运算符如下表

image-20240127165705147

大部分运算符与常见编程语言相同，需要额外注意的为null的运算。

当然，where子句也支持条件的组合，使用and与or来连接多个条件语句（需要注意的是and的优先级更高，必要时需要使用括号来改变优先级）。还有in操作符，用于表示筛选条件为数据位于一个集合中。还有not操作符，用于取反。还是用上文的表studentscore来举例说明

1
2
3
4
5
6
where id = 1  # 筛选id为1的记录
where id = 1 and lesson = 1  # 筛选id为1且lesson为1的记录
where lesson = 1 or lesson = 2  # 筛选lesson为1或lesson为2的记录
where lesson in (1, 2)  # 筛选lesson在集合(1, 2)中的记录
where lesson not in (3, 4)  # 筛选lesson不在集合(3, 4)中的记录
where score is not null # 筛选score不为空的记录

where子句还可以配合很多函数使用，这些我们后文再说。

order by

order by子句非常简单，就是对最终输出的结果按照指定列进行排序，默认为升序，即字符串从A到Z，数值从小到大，时间从早到晚，使用desc关键字可改为降序，常见输入如下

1
2
3
order by 列名  # 按给定列排序
order by 列名1, 列名2  # 先按列1进行排序，列1取值相同的记录再按照列2排序
order by 列名 desc # 按给定列降序排序

还需要注意的是，order by子句必须写在SQL语句的最后。

示例

现在，让我们结合上述的基本结构，使用表studentscore来举例。表studentscore（id为学生学号，lesson为课程编号，score为成绩）

id lesson score
1 1 90
1 2 80
2 1 50
2 1 70

现在需要取出课程编号为1，2的所有及格的成绩，同时score可能存在空值，需要剔除score为空的数据，最后按学号升序排序。

1
2
3
4
select *
from studentscore
where lesson in (1, 2) and score is not null and score >= 60
order by id;

补充

distinct关键字

在实际使用过程中，可能会遇到需要查看一个列所有取值的情况，这时候就可以使用distinct关键字，这可以返回给定列中不同的值。我们来看一些例子，使用上文的表studentscore来举例

进行查询

1
2
select id
from studentscore;

其输出为

1
2
3
4
5
6
id
------
1
1
2
2

若使用distinct关键字

1
2
select distinct id
from studentscore;

则输出为

1
2
3
4
id
------
1
2

需要注意的是，distinct关键字会作用于所有输入的列，这会输出所有不同的多个列的组合，例如

1
2
select distinct id, lesson
from studentscore;

则输出为

id lesson
1 1
1 2
2 1

该表中存在两条id为2，lesson为1的记录，故只会输出一次。

从上述例子也可以看出，只要使用distinct关键字，则所有列都会被去重，故不存在对一部分列使用distinct关键字，对另一部分不使用的用法。

模糊匹配

模糊匹配多用于字符数据，如筛选以大学结尾的学校这类需求，就可以使用模糊匹配来进行筛选，一般配合where子句使用，这里介绍两种模糊匹配的关键字like与rlike，其语法为

1
2
列名 like 通配符表达式
列名 rlike 正则表达式

首先是like，like有三种支持的通配符，分别是%，_与[]，但是MySQL不支持[]通配符（rlike中可以用），故这里只讲前两种。%用于代表任意多个字符，_通配符用于代表一个字符，写模糊筛选语句的过程就是将需求转换为通配符表达式的过程，下面来看例子。

假如，现在有一个列name，我要筛选出所有姓李的人，那就是第一个字符为李，而后面可以跟上若干个字符，所以通配符表达式为'李%'，写为where子句为

1
where name like '李%'

再比如，我们要筛选出姓李且姓名为两个字的人，则就是第一个字符为李，而后面只能跟上一个字符，所以通配符表达式为'李_'，写为where子句为

1
where name like '李_'

当然like也支持not关键字，也就是反向筛选，例如筛选出不姓李的人，就是

1
where name not like '李%'

然后是rlike，可以看到like只能解决一下比较简单的模糊匹配问题，复杂的模糊匹配问题就要使用到rlike。

rlike使用的是正则表达式，这与like的逻辑很不一样，例如like '张伟'筛选出来的结果只有“张伟”，而rlike '张伟'会筛选出所有包含“张伟”的结果，如“张伟伟”等，而如果只要筛选出“张伟”，则是在现有表达式上用通配符做限制，如rlike '^张伟$'，表示以张伟开头，以张伟结尾，这样就能只筛选出“张伟”了。

下面来看一些正则表达式的常用通配符

^: 匹配开头，如^s匹配以s开头的字符串。

$：匹配结尾，如s$匹配以s结尾的字符串。

[]：匹配任意一个[]内的字符，如[abc],[012]，也可以使用-字符来指定范围，如[a-c],[0-9]，还可以使用^，表示非，如[^abc]表示不为abc。

*：匹配前面的子表达式零次或多次，如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。

+：匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。

{n}：n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。

{n, m}：m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。m可以不填，表示无上限。

用上面的通配符，就可以做到很多，例如筛选QQ号为10位的数据，那就是任意数字重复10次，且不能有任何其他字符，正则表达式为'^[0-9]{10}$'。

limlt

limlt写于SQL语句的最末端，用于限制输出数量，也可与order by子句配合输出指定排名数据，其语法如下

1
2
limit n  # 输出前n条记录
limit n, m  # 从第n条记录开始，返回m条记录

用表studentscore举例，例如输出前2条数据

1
2
select *
from studentscore limit 2;

例如输出分数前三高的三条记录

1
2
3
select *
from studentscore 
order by score desc limit 3;

有时候还会遇到一些需求，例如以3条记录为一页，输出第三页，这就可以转化为，跳过前两页，也就是6条记录，输出一页数量的记录，也就是3条记录，因此可以写为limit 6, 3

聚集函数

聚集函数就是常见的统计函数，如sum(), avg(), count(), max(), min()等，大多数情况下，聚集函数还需要配合group by子句使用，还是使用上文的表studentscore来举例

1
2
3
4
5
select 
	sum(score) as sum_score, 
	avg(score) as avg_score, 
	count(*) as cc
from studentscore;

输出为

sum_score avg_score cc
290 72.5 4

sum(score)会输出整个表score的和，avg(score)会输出整个表score的平均值，count(*)会输出整个表的记录条数

注：

  1. as为重命名语法，可以重命名列名，还有表名（后文会讲）
  2. 由于count()函数起计数功能，在没有null的情况下，填任何列结果都是一样的，所以一般写count(*)

可以注意到，使用聚集函数后整个表将会改变形状，这时候在select中写入无关列名会报错，如select id

group by

group by子句的作用是分组，比如现在我们要统计各课程的平均成绩，那就可以使用group by子句，按照课程编号分组，再使用聚集函数进行统计计算，代码如下

1
2
3
select lesson, avg(score) as avg_score
from studentscore
group by lesson;

输出为

lesson avg_score
1 70
2 80

与上一个例子相同，这时候在select中写入无关列名会报错（只有用于分组的列是相关的）

补充

count()与null

将表studentscore的数据改为

id lesson score
1 1 90
1 2 80
2 1 50
2 1 null

这时，count(*)，count(lesson)，count(score)分别会输出4,4,3。也就是说，count()会不统计空值。

注：如果有全空的列，则count()会输出0，而不是null

count()与distinct

distinct关键字的作用是去重，这也可以与count()配合使用，就拿表studentscore来说，假设现在我们想要统计学生数量，就可以这么写

1
2
select count(distinct id)
from studentscore;

这会计数所有不重复的id。

当然，还有更复杂的用法，比如现在我们要统计所有学生说选修的课程总数，可以看到在上述表格中，是存在两条id与lesson均相同的记录的，这可能是补考或者重修之类的原因，所以，我们就可以这么写

1
2
select count(distinct id, lesson)
from studentscore;

这会计数所有不重复的id与lesson的组合，这跟在select中使用是相同的。

在更加现实的场景中，类似的需求并不少见，比如用户活跃量。假设现在每次用户访问，都会在数据库中留下一条记录，一个用户每天可能会访问多次，留下多条记录，但是在计数时我们只能统计为一个活跃日，现在我们要这个月所有用户的活跃天数之和，那就可以写为count(distinct id, day)。（id为用户账号，day为访问时的日期，这里默认该数据表是一个月的数据，没有做月份的筛选）

concat()家族

concat()家族包括concat()，concat_ws()与group_concat()，用于连接数值，字符串。首先是concat()函数，其语法如下

1
concat(字符串1, 字符串2, ...)

用表表studentscore举例，现在我们要给分数写上单位分，就可以这么写

1
2
select id, lesson, concat(score, '分') as score
from studentscore;

输出如下

id lesson score
1 1 90分
1 2 80分
2 1 50分
2 1 70分

concat_ws()函数在此基础上增加了选择分隔符的功能，其语法如下

1
concat_ws(分隔符, 字符串1, 字符串2, ...)  # 默认分隔符为逗号

例如，我们要在一列中包含课程编号与分数，用-做分隔符，代码如下

1
2
select id, concat_ws('-', lesson, score) as lesson_score
from studentscore;

输出如下

id lesson_score
1 1-90
1 2-80
2 1-50
2 1-70

group_concat()函数用于配合group by子句做输出，用于将同一个分组的数据整合到一起。其语法如下

1
group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'])  # []内为可选参数

例如，现在我们要输出参加了每个课程考试的学生id，并用逗号分隔，则可以这么写

1
2
3
select lesson, group_concat(distinct id separator ',') as lesson_id
from studentscore
group by lesson;

输出如下

lesson lesson_id
1 1,2
2 1

连接表

连接表用于需要查询或用于判断分组的数据分别存在不同的表中，这时候可以使用连接表，将多个表连接起来，再进行查询。在实际应用场景中，由于数据库设计的需要，经常会出现数据分散在多个表中的情况，故这也是一个很常用的语法。具体的语法

1
2
3
from 表1
连接语句 表2
on 连接键

连接语句常用的有3个，分别为

1
2
3
inner join  # 对应连接，只保留两个表中都有的部分，不会出现null
left join  # 左连接，保留左表的所有信息，会出现null
right join  # 同上，改为右表

接下来我们通过例子来具体理解这4种连接方式，假设现在有三个表，表studentscore，这里我们把该表的数据再放上来，便于观看

id lesson score
1 1 90
1 2 80
2 1 50
2 1 70

再增加一个表lessonname，用于储存课程名称（lesson为课程编号，name为课程名称）

lesson name
1 语文
2 数学
3 物理
4 化学

表student，用于储存学生信息（id为学生学号，age为学生年龄）

id age
1 17
2 18
3 18
4 19

inner join

现在我们来看具体需求，假设现在我要查询所有的成绩记录，要包括学号，课程名，成绩，那么这就要涉及表studentscore与表lessonname，具体查询语句如下

1
2
3
4
select ss.id, ln.name, ss.score
from studentscore ss
inner join lessonname ln
on ss.lesson = ln.lesson;

这里，表名后面的ss与ln为重命名，中间可以写一个as，也可以省略（同列的重命名），重命名是为了方便写下面的连接条件以及列名。还有一个需要注意的点，因为在两个表连接起来之后，会有一些同名的列，比如上述语句就有两个lesson，所以，我们需要在列名前加上表名，以准确的告诉SQL具体的列。（这里建议，在涉及连接的语句中，将用到的列名前全部加上表名，不管其是否真的需要，这会减少很多奇怪的bug）。最后inner join也可以写为join。

现在，让我们来看看上述语句的输出，其输出如下

id name score
1 语文 90
1 数学 80
2 语文 50
2 语文 70

可以看到，只有两个表的lesson能对上的记录才会被取出

接下来，如果我们要在上述基础上补充各学生的年龄，那么我们就需要连接三个表，其查询语句如下

1
2
3
4
5
6
select ss.id, ln.name, ss.score, s.age
from studentscore ss
join lessonname ln
on ss.lesson = ln.lesson
join student s
on ss.id = s.id;

其输出为

id name score age
1 语文 90 17
1 数学 80 17
2 语文 50 18
2 语文 70 18

left/right join

left join与right join是相同的，就是调换了一下表的位置而已，故这里只讲left join。

假设现在有这样的需求，我们要查询所有学生的考试信息，包括学号，课程编号与成绩，但是要求保留所有学生的信息，也就是，就算这个学生一条考试成绩的记录都没有，你也要给我报空，告诉我这个学生啥都没考。这就需要用到left join来保留左表的所有信息（先写的那个是左表），具体查询语句如下

1
2
3
4
select s.id, ss.lesson, ss.score
from student s
left join studentscore ss
on s.id = ss.id;

其输出为

id lesson score
1 1 90
1 2 80
2 1 50
2 1 70
3 null null
4 null null

可以看到，左表中的id取3,4的记录，在右表中是没有对应记录的，但是由于这是左连接，所以左表的所有信息都会被保留，就算右表没有对应记录，也需要返回null

补充

多个连接键

在连接表的过程中，可能会遇到需要指定多个连接键的情况，这时候可以用and连接多个连接键，例如，为了防止分数出错，一次考试中分别有两个老师打分，其分数分别记录在表studentscore1与表studentscore2中，数据如下

id lesson score
1 1 90
1 2 80
2 1 50
2 1 70
id lesson score
1 1 90
1 2 80
2 1 55
2 1 70

现在我们需要对比两个老师的打分，那么就需要将两个表连接起来，并且连接键为id与lesson均相等，代码为

1
2
3
4
select s1.id, s1.lesson, s1.score as score1, s2.score as score2
from studentscore1 s1
join studentscore2 s2
on s1.id = s2.id and s1.lesson=s2.lesson;

输出为

id lesson score1 score2
1 1 90 90
1 2 80 80
2 1 50 55
2 1 70 70

组合查询

如果说连接表是在列方向上的连接，那么组合查询就是行方向上的连接，用关键字union实现。用表studentscore与表student举例，例如，现在我们要查找有一门考试分数超过70分，或者年龄为18岁学生，那么我们可以对这两个条件分别查找，再组合成一个表，代码如下

1
2
3
4
5
6
7
8
select distinct id
from studentscore
where score > 70
union
select uid
from student
where age = 18
order by id;

输出为

id
1
2
3

很容易想到，组合的两个表的列需要完全一样。再者，如果需要排序，那么order by子句需要写在整个语句的最后，并且只能有一个order by子句。最后，union关键字会对结果进行去重，例如上述代码，两个查询的结果分别为1, 2与2, 3，但是最后的输出仅有一个2。

如果想要不去重的结果，则可以使用union all关键字，例如将上述代码改为

1
2
3
4
5
6
7
8
select distinct id
from studentscore
where score > 70
union all
select uid
from student
where age = 18
order by id;

则输出为

id
1
2
2
3

子查询

子查询本质就是查询的嵌套，其语法就是用括号括住一个查询，将其看做一个新的表。用表studentscore与表student举例，例如，现在我们要查看成年学生的考试信息，就可以用子查询与in关键字配合

1
2
3
select *
from studentscore
where id in (select id from student where age >= 18);

需要注意的是，与in关键字配合时子查询只能有一个查询列名。

再比如，我们想要输出最高的学生总分，那我们需要先用一个表计算出总分，再在这个表里求最大值，可以这么写

1
2
select max(sum_score) as max_sum_score
from (select id, sum(score) as sum_score from studentscore group by id);

上述代码在子查询的括号后可以加上as 表名用于重命名子查询，在需要多表连接的时候需要用到

这个需求也可以这么写

1
2
3
4
5
6
7
8
with sc as (
    select id, sum(score) as sum_score 
    from studentscore 
    group by id
)

select max(sum_score) as max_sum_score
from sc;

就是在最前面定义一个表，其语法如下

1
2
3
4
5
6
with 表名1 as (
    表内容1
),
表名2 as (
	表内容2
)

条件判断函数

条件判断语句也就类似于一般编程语言的if-else结构，用起来很方便，接下来就来看看SQL里的条件语句

case when

case when语句是最类似与if-else的，接下来我们来看看case when语句的具体语法，其有两种语法

1
2
3
4
# 用法1
case 列名 when 预期值1 then 对应结果1 when 预期值2 then 对应结果1 else 对应结果3 end
# 用法2
case when 条件表达式1 then 对应结果1 when 条件表达式2 then 对应结果2 else 对应结果3 end

可以看到，这其实就是if-else的翻版，这可以用于各种子句中，例如select子句，以表studentscore举例，假设现在我们要输出学号，课程名字，成绩，并且我们知道课程编号对应的课程名字，就可以这么写

1
2
select id, (case lesson when 1 then "语文" else "数学" end) as name, score
from studentscore;

其输出如下

id name score
1 语文 90
1 数学 80
2 语文 50
2 语文 70

再比如，现在我们给学生的分数划分等级，大于等于90为优秀，80-90为良好，60-80为及格，小于60为不及格，则可以这么写

1
2
3
4
5
6
7
8
9
10
select 
	id, 
	lesson, 
	(CASE 
     WHEN score>=90 THEN '优秀' 
     WHEN score>=80 THEN '良好' 
     WHEN score>=60 THEN '及格' 
     ELSE '不及格' 
	END) as 'level'
from studentscore;

其输出如下

id lesson level
1 1 优秀
1 2 良好
2 1 不及格
2 1 及格

还可以配合聚合函数使用，用表student，例如，现在我们要计算大于等于18岁的学生数量，可以这么写

1
2
3
4
5
6
# 写法1
select sum(case when age>=18 then 1 else 0 end) as cnt
from student;
# 写法2
select count(case when age>=18 then age else null end) as cnt
from student;

则输出为

1
2
3
cnt
------
3

if

if语句的作用与case when语句基本相同，但是其只能填入一个条件，语法为

1
if(条件, 条件为真对应结果, 条件不为真对应结果)

使用if语句重写一下上述例子

1
2
3
4
5
6
7
# 输出课程名字
select id, if(lesson = 1, "语文", "数学") as name, score
from studentscore;

# 成年学生数量
select count(if(age>=18, age, null)) as cnt
from student;

ifnull

ifnull语句是用于处理空值的，其语法为

1
ifnull(列名, 若该列数值为空代替使用的值)

比如这个带空值的表studentscore

id lesson score
1 1 90
1 2 80
2 1 50
2 1 null

假设现在要计算各学生所有分数的和，空值记为0分则可以这么写

1
2
3
select id, sum(ifnull(score, 0)) as sum_score
from studentscore
group by id;

窗口函数

在使用group by子句时，会改变数据表的形状，而窗口函数就可以在不改变数据表形状下，使用统计函数，计算统计指标，并将其作为新的一列加入数据表，其具体语法如下

1
窗口函数 over(partition by 分组列名 order by 排序列名 框架)  # over中的参数均可选

用求各课程平均分为例，之前使用group by子句的写法如下

1
2
3
select lesson, avg(score) as avg_score
from studentscore
group by lesson;

用窗口函数的写法如下

1
2
select *, avg(score) over(partition by lesson) as avg_score
from studentscore;

最终输出如下

id lesson score avg_score
1 1 90 70
1 2 80 80
2 1 50 70
2 1 70 70

可以看到，课程的平均分以新列的形式出现在了表的最后，并且表原本的信息均可以得到保留。

聚合函数

接下来我们来看看聚合函数作为窗口函数的用法，这与group by子句类似，其效果上述例子已经看过了。具体的来说，将原本写在group by子句后的列名写在partition by后即可，同理partition by可以为空，例如

1
2
3
4
5
select *,
	   count(*) over() as cnt,  # 总记录数
	   sum(score) over(partition by id) as sum_score,  # 每个学生的总分
	   max(score) over(partition by lesson) as max_score  # 每个科目的最高分
from studentscore;

当然，聚合函数也可以配合order by使用，其主要在带时间的数据中会被用到，我们用一个例子来理解一下，现在修改表studentscore，给条记录加上一个时间，修改后如下

id lesson time score
1 1 2021-07-02 09:05:01 90
1 2 2021-07-03 09:05:01 80
2 1 2021-07-05 09:05:01 50
2 1 2021-09-10 09:05:01 70

现在我们的需求是输出全表及该学生在当前时间下所考到的最高分，代码如下

1
2
3
select *,
	   max(score) over(partition by id order by time) as mcnt
from studentscore;

输出如下

id lesson time score mcnt
1 1 2021-07-02 09:05:01 90 90
1 2 2021-07-03 09:05:01 80 90
2 1 2021-07-05 09:05:01 50 50
2 1 2021-09-10 09:05:01 70 70

排序函数

语法

然后是排序函数，常见的有rank(), dense_rank(), row_number()，这些函数都用于排序，一般配合order by使用，例如，现在要对表studentscore中的所有分数进行排序，写法如下

1
2
select *, rank() over(order by score desc) as score_rank
from studentscore;

输出为

id lesson score score_rank
1 1 90 1
1 2 80 2
2 1 70 3
2 1 50 4

这个写法就表名，根据列score降序（从大到小）进行排名。

当然，还可以配合partition by一起使用，比如说，现在我们要输出每一个科目的成绩排名，那就需要按照lesson进行分组，再按照score降序进行排名，写出来就是

1
2
3
select *, rank() over(partition by lesson order by score desc) as score_rank
from studentscore
order by lesson;

输出为

id lesson score score_rank
1 1 90 1
2 1 70 2
2 1 50 3
1 2 80 1

当然，还可以按照多个列进行排序，比如，对于相同的得分就按照id大小排序，那上述代码可以改为

1
2
3
select *, rank() over(partition by lesson order by score desc, id) as score_rank
from studentscore
order by lesson;

排序函数的区别

那么排序函数rank(), dense_rank(), row_number()之间有什么区别呢，主要区别在出现相同的排名时的处理函数，我们通过一个例子来看看

score rank() dense_rank() row_number()
100 1 1 1
90 2 2 2
90 2 2 3
80 4 3 4
80 4 3 5

可以看到，在出现相同的排名时，rank(), dense_rank()都会输出同样的排名，不同的是rank()输出的排名是间断的，而dense_rank()输出的排名是连续的。而row_number()则不会输出不同的排名，而是自己分配一个先后顺序。

补充

使用排序函数求中位数

在不支持使用聚合函数直接求中位数的SQL环境中，可以使用排序函数求中位数，以表studentscore为例，求出每个学生的分数中位数，代码如下

1
2
3
4
5
6
7
8
9
10
with rr as (
    select id, 
           row_number() over(partition by id order by score) as rs, 
           count(*) over(partition by id) as cnt
    from studentscore
)

select id, AVG(score) AS median
from rr
where rs in (floor((cnt + 1)/2), ceiling((cnt + 1)/2))

具体思路为，先计算出每条记录的排名以及总数，即上述定义的表rr，此处排名使用row_number()函数或rank()函数均可，然后将总数除以2，若为奇数，则会得到一个整数，该整数对应排名的记录即为中位数，若为偶数，则会得到一个小数，此时需要求最近的两个整数（如4.5就取4，5，用floor()函数与ceiling()函数就可以实现），取出两个整数对应排名的记录，再进行平均则得到中位数。

前后函数

前后函数包括lead()函数与lag()函数，其作用是将数据向前后位移，这样说有点抽象，我们用具体了例子来说明。用带时间的表studentscore来举例，数据如下

id lesson time score
1 1 2021-07-02 09:05:01 90
1 2 2021-07-03 09:05:01 80
2 1 2021-07-05 09:05:01 50
2 1 2021-09-10 09:05:01 70

首先我们来看看语法，lead()函数与lag()函数的语法都很简单

1
2
lead(列名, 位移长度, null值处理)  # 向上位移
lag(列名, 位移长度, null值处理)  # 向下位移

其中null值处理可以不填。

现在，我们的需求是求出每个学生相近的两次考试时间的时间差，也被称为时间窗，具体写法如下

1
2
select *, lead(time, 1) over(partition by uid order by time) as tt, timestampdiff(day, time, lead(time, 1) over(partition by uid order by time)) as time_window
from studentscore

上述代码中，timestampdiff()函数用于计算时间差，具体可见下文时间数据相关函数部分，代码输出如下

id lesson time tt time_window
1 1 2021-07-02 09:05:01 2021-07-03 09:05:01 1
1 2 2021-07-03 09:05:01 null null
2 1 2021-07-05 09:05:01 2021-09-10 09:05:01 67
2 1 2021-09-10 09:05:01 null null

可以看到，time列被整体向上位移了一个单位，并且由于没有填写null值的处理方式，所以缺失的值会直接为null（如果写的是lead(time, 1, 0)则缺失值为0）。还需要注意的是，窗口函数中根据time排序，这样才能确保是相邻的两次考试时间。

框架

最后我们来讲前面一直没有用到的一个参数，框架。框架用于进一步限制窗口大小，其分为rows与range两种模式

rows模式

rows模式类似于滑动窗口，我们先从一个例子入手，现在有一份关于销售量的表sale，数据如下

sale_date sale_cnt
2021-08-30 09:05:01 40
2021-08-31 09:05:01 60
2021-09-01 09:05:01 70
2021-09-02 09:05:01 30
2021-09-03 09:05:01 50

我们要计算出从九月一号起，每一天的累计3天销量。这时候，我们就需要一个大小为3的滑动窗口，先计算从8-30到9-1的平均销量，然后往下滑动一格，再计算从8-31到9-2的平均销量，以此类推。这样的滑动窗口就可以用rows模式来实现。

rows模式是根据写好的规格，以当前行为参考，计算出限制窗口，其有两种写法

1
2
rows 指定行  # 从当前行开始到指定行或从指定行开始到当前行
rows between 开始行 and 结束行  # 从开始行开始到结束行

可填参数有以下几个

current row：当前行
unbounded preceding：当前行上侧所有行，也就是第一行
unbounded following：当前行下侧所有行，也就是最后一行
expr preceding：当前行上侧expr行(expr可以是数字，也可以是表达式)
expr following：当前行下侧expr行(expr可以是数字，也可以是表达式)

上述例子的需求，可以转化为计算从当前行上2行开始到当前行的平均销量，代码如下

1
2
select *, avg(sale_cnt) over(rows 2 preceding) as avg_sale_3
from sale;

上述代码的输出如下

sale_date sale_cnt avg_sale_3
2021-08-30 09:05:01 40 40
2021-08-31 09:05:01 60 100
2021-09-01 09:05:01 70 170
2021-09-02 09:05:01 30 160
2021-09-03 09:05:01 50 150

可以看到，由于窗口函数计算时还是从第一行开始计算的，所以也会以8-30与8-31为中心计算，而其前两行中有空行，所以会被忽视。所以，上述需求的完整代码其实要这么写

1
2
3
4
5
6
7
8
with avg_sale as (
    select *, avg(sale_cnt) over(rows 2 preceding) as avg_sale_3
	from sale
)

select sale_date, avg_sale_3
from avg_sale
where date_format(sale_date, "%Y%m%d") >= 20210701;

range模式

range模式是根据当前行的order by列值，来筛选符合所写规则的行。其语法为

1
2
range 指定值  # 筛选列值属于从当前值到指定值或从指定值到当前值的行
range between 开始值 and 结束值  # 筛选列值属于从开始值到结束值的行

其可填参数不变，但意义不同

current row：当前值
unbounded preceding：无下限，也就是小多少都可以
unbounded following：无上限，也就是大多少都可以
expr preceding：当前行的列值小expr(expr可以是数字，也可以是表达式)
expr following：当前行的列值大expr(expr可以是数字，也可以是表达式)

例如，当前值为nn，我要筛选出列值在[n−3,n+4][n-3, n+4]之间的行，那就是写为range between 3 preceding and 4 following

时间数据相关函数

时间类型的数据在实际的数据库中很常见，其一般格式为2021-07-02 09:05:01，下面介绍一些操作与处理时间数据的函数

date_format()

date_format()函数用于取出时间数据的各个部件，也就是可以根据实际要求取出年，月，日，小时，分钟，秒钟这些部分的一个或多个。以上述给的2021-07-02 09:05:01为例，介绍一下date_format()函数的语法

1
date_format(时间列名, '通配符与字符的组合')

假设时间数据所在的列名为start_time，其中的数据为2021-07-02 09:05:01，常用的通配符有

1
2
3
4
5
6
date_format(start_time, '%Y')  # 函数值为2021
date_format(start_time, '%m')  # 函数值为07
date_format(start_time, '%d')  # 函数值为02
date_format(start_time, '%H')  # 函数值为09
date_format(start_time, '%i')  # 函数值为05
date_format(start_time, '%s')  # 函数值为01

通配符也可以同时使用，以及与字符配合使用，比如

1
2
3
date_format(start_time, '%Y%m%d')  # 函数值为20210702
date_format(start_time, '%Y-%m-%d')  # 函数值为2021-07-02
date_format(start_time, '%Y-%m-%d %H:%i:%s')  # 函数值为2021-07-02 09:05:01

想要得到周几也可以通过date_format()函数取出，使用通配符"%w"，输出为数字0-7（0为星期天），如

1
date_format(start_time, '%w')

date_format()函数可以配合多个子句使用，比如

1
2
3
4
5
select date_format(start_time, '%Y%m%d')  # 打印日期

where date_format(start_time, '%Y') = 2021  # 筛选2021年的记录

group by date_format(start_time, '%Y%m')  # 根据月份分组

date()

date()函数用于提取时间数据的年月日部分，输出格式为%Y-%m-%d，例如

1
date(2021-07-02 09:05:01)  # 输出为2021-07-02

timestampdiff()

timestampdiff()函数用于计算两个时间数据之间的时间差，并且可以输出为各种单位，具体语法如下

1
timestampdiff(单位, 时间数据1, 时间数据2)  # 最终输出时间数据2-时间数据1

其中，单位可以输入

frac_second：毫秒
second：秒
minute：分钟
hour：小时
day：天
week：星期
month：月
quarter：季度
year：年

例如

1
2
3
timestampdiff(day, 2021-07-02 09:05:01, 2021-07-03 09:05:01)  # 输出 1
timestampdiff(hour, 2021-07-02 09:05:01, 2021-07-02 17:05:01)  # 输出 8
timestampdiff(minute, 2021-07-02 09:05:01, 2021-07-02 11:25:01)  # 输出 140

datediff()

datediff()函数用于计算两个时间数据的日期差，语法如下

1
datediff(时间数据1, 时间数据2)  # 最终输出时间数据1-时间数据2

例如

1
2
datediff(2021-07-03 09:05:01, 2021-07-02 09:05:01)  # 输出1
datediff(2021-07-10 09:05:01, 2021-07-02 09:05:01)  # 输出8

timestampdiff()与datediff()的区别

timestampdiff()函数计算时会考虑时间数据的全部信息，再转换为具体单位，且转换时会直接丢弃小数。datediff()函数由于只用于计算日期差，所以是用时间数据的日期直接相减。例如

1
2
timestampdiff(day, 2021-09-04 12:00:00, 2021-09-05 11:00:00)  # 输出0
datediff(2021-09-05 12:00:00, 2021-09-04 11:00:00)  # 输出1

这两个时间数据相差23小时，timestampdiff()函数转化为day时发现不满一天，则直接返回0，datediff()函数用日期直接相减则返回1。

date_add()与date_sub()

date_add()与date_sub()函数用于加减时间，其语法如下

1
2
date_add(时间, interval 增加时间数 单位)
date_sub(时间, interval 减少时间数 单位)

其中，单位可填的有

microsecond：毫秒
second：秒
minute：分钟
hour：小时
day：天
week：星期
month：月
quarter：季度
year：年

例如要给时间增加5天，就是

1
date_add(2021-07-10 09:05:01, interval 5 day)  # 输出2021-07-15 09:05:01

还可以使用复合型的写法，例如要给时间增加一个月3天，可以这么写

1
date_add(2021-07-10 09:05:01, interval '1, 3' month_day)  # 输出2021-08-13 09:05:01

常用指标与场景

留存率

在互联网行业中，留存率是一个重要且常用的指标。其百科定义如下：留存率是用于反映网站、互联网应用或网络游戏的运营情况的统计指标，其具体含义为在统计周期（周/月）内，每日活跃用户数在第N日仍启动该App的用户数占比的平均值。其中N通常取2、4、8、15、31，分别对应次日留存率、三日留存率、周留存率、半月留存率和月留存率。

次日留存率

以次日留存率为例，示例表user_log，数据如下（uid为用户id，dt为登录日期）：

uid dt
101 2021-07-10
101 2021-07-11
101 2021-07-12
102 2021-07-11
102 2021-07-12
102 2021-07-14
103 2021-07-11
103 2021-07-13
103 2021-07-14
104 2021-07-12
104 2021-07-13
104 2021-07-15

这里使用的是经过去重的数据，实际场景中数据通常还需要经过distinct或union等处理

现在的需求是，求每天的新用户的次日留存率。首先是判断新用户，只需要计算其第一次登录的日期即可，这里我们使用窗口函数在原表最后添加一列第一次登录日期

1
2
select uid, dt, min(dt) over(partition by uid) as f_dt
from user_log

然后我们就可以根据f_dt进行分组，count(distinct uid)计算当天新用户人数，判断次日登录只需要判断dt与f_dt加一天是否相等即可，完整代码如下

1
2
3
4
5
6
7
8
with ul as (
    select uid, dt, min(dt) over(partition by uid) as f_dt
    from user_log
)

select f_dt, sum(if(dt = date_add(f_dt, interval 1 day), 1, 0))/count(distinct uid) as left_rate
from ul
group by f_dt;

如果现在的需求是求每天的次日留存率，注意需要计算所有用户，那就是要筛选出每天登录的人，再计算这些人中第二天还登录的有多少人。这里可以通过前后函数来简单实现

1
2
select uid, dt, lead(dt, 1) over(partition by uid order by dt) as dt2
from user_log

首先用lead()函数，根据用户id分组，登录日期排序，由于数据经过去重，故这可以得到每次登录记录的下一次登录日期。输出如下

uid dt dt2
101 2021-07-10 2021-07-11
101 2021-07-11 2021-07-12
101 2021-07-12 null
102 2021-07-11 2021-07-12
102 2021-07-12 2021-07-14
102 2021-07-14 null
103 2021-07-11 2021-07-13
103 2021-07-13 2021-07-14
103 2021-07-14 null
104 2021-07-12 2021-07-13
104 2021-07-13 2021-07-15
104 2021-07-15 null

我们要判断是否次日登录，只需要判断dt加一天是否等于dt2。完整代码如下

1
2
3
4
5
6
7
8
with ul as (
    select uid, dt, lead(dt, 1) over(partition by uid order by dt) as dt2
    from user_log
)

select dt, sum(if(dt2 = date_add(dt, interval 1 day), 1, 0))/count(*) as left_rate
from ul
group by dt;

实例

最后我们来看一个比较现实的例子，用户行为日志表tb_user_log（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）。计算2021年11月每天新用户的次日留存率，结果按日期升序（如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过），数据如下

id uid artical_id in_time out_time sign_cin
1 101 0 2021-11-01 10:00:00 2021-11-01 10:00:42 1
2 102 9001 2021-11-01 10:00:00 2021-11-01 10:00:09 0
3 103 9001 2021-11-01 10:00:01 2021-11-01 10:01:50 0
4 101 9002 2021-11-02 10:00:09 2021-11-02 10:00:28 0
5 103 9002 2021-11-02 10:00:51 2021-11-02 10:00:59 0
6 104 9001 2021-11-02 11:00:28 2021-11-02 11:01:24 0
7 101 9003 2021-11-03 11:00:55 2021-11-03 11:01:24 0
8 104 9003 2021-11-03 11:00:45 2021-11-03 11:00:55 0
9 105 9003 2021-11-03 11:00:53 2021-11-03 11:00:59 0
10 101 9002 2021-11-04 11:00:55 2021-11-04 11:00:59 0

首先，我们要将数据往上述的示例数据那边靠，根据题意，进入时间与离开时间都算活跃，那就把这两列合并，变成一个uid与活跃时间的表，代码如下

1
2
3
4
5
select uid, date(in_time) as dt
from tb_user_log
union
select uid, date(out_time) as dt
from tb_user_log

用date()提取出我们关注的日期，用union做去重。

然后，就是求第一次登录时间，同样的使用窗口函数

1
2
3
4
5
6
7
8
9
10
with tul as (
    select uid, date(in_time) as dt
    from tb_user_log
    union
    select uid, date(out_time) as dt
    from tb_user_log
)

select *, min(dt) over(partition by uid) as f_dt
from tul

最后计算次日留存率

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
with tul as (
    select uid, date(in_time) as dt
    from tb_user_log
    union
    select uid, date(out_time) as dt
    from tb_user_log
),
ut as (
    select *, min(dt) over(partition by uid) as f_dt
    from tul
)

select f_dt as dt, round(sum(if(dt = date_add(f_dt, interval 1 day), 1, 0))/count(distinct uid), 2) as uv_left_rate
from ut
where date_format(f_dt, "%Y%m") = 202111
group by f_dt
order by dt;

注意，2021年11月这个限定条件要在最后进行限定，如果在表ut中提前限定的话，会导致错判部分用户的首次登录日期。

连续签到天数

在实际场景中，经常会有计算连续签到天数的需求，其难点在于如何判断日期的连续。示例表user_log，数据如下（uid为用户id，dt为登录日期）：

uid dt
101 2021-07-10
102 2021-07-10
101 2021-07-11
102 2021-07-12
102 2021-07-13
101 2021-07-13

这里用的是最方便的数据，实际情况下数据会比较复杂，需要计算连续签到时，可以将数据处理成类似于上述数据的结构，例如用date()函数提取时间的日期部分，有多个时间列的情况下，用union将所有时间列合并为一个等。

现在我们来看看判断连续签到的具体思路，一般的思路是，先对时间进行排序，再使用时间与排序相减，只要时间是连续的，那么时间与排序相减的值就是不变的，代码为

1
2
3
4
5
select 
	*, 
	row_number() over(partition by uid order by dt) as r1, 
	date_format(dt, "%Y%m%d") - row_number() over(partition by uid order by dt) as dt2
from user_log;

输出为

uid dt r1 dt2
101 2021-07-10 1 20210709
101 2021-07-11 2 20210709
101 2021-07-13 3 20210710
102 2021-07-10 1 20210709
102 2021-07-12 2 20210710
102 2021-07-13 3 20210710

可以看到，只需要在上述表的基础上，根据uid, dt2进行分组，那就可以保证每一个分组中都是连续的日期，只需要根据这个分组进行排序，就可以计算得到连续签到的天数了，代码如下

1
2
3
4
5
6
7
8
9
10
with r1 as (
    select 
		*, 
		row_number() over(partition by uid order by dt) as r1, 
		date_format(dt, "%Y%m%d") - row_number() over(partition by uid order by dt) as dt2
	from user_log
)

select *, row_number() over(partition by uid, dt2 order by dt) as cnt_day
from r1;

输出为

uid dt r1 dt2 cnt_day
101 2021-07-10 1 20210709 1
101 2021-07-11 2 20210709 2
101 2021-07-13 3 20210710 1
102 2021-07-10 1 20210709 1
102 2021-07-12 2 20210710 1
102 2021-07-13 3 20210710 2

可以看到cnt_day记录的就是当前时间下用户的连续签到天数。

补充

数据类型转化

在实际情况中，有时候会出现数据类型与我们的要求不符导致bug的情况（比如两列相减，但是列的类型为无符号整数，相减结果有负数机会溢出报错），这时候就要用到数据类型转化。数据类型转化有两种方法，分别是cast()与convert()，其语法分别为

1
2
cast(列名 as 类型)
convert(列名, 类型)

类型可填参数如下

二进制: binary

字符型: char

日期: date

时间: time

日期时间型: datetime

浮点数: decimal

整数: signed

无符号整数: unsigned