【SQL】MySQL中窗口函数和单行函数的使用

vlambda
2022-04-13

【SQL】MySQL中窗口函数和单行函数的使用

今天接着昨天的内容讲解一下 SQL 中函数的使用。其中窗口函数是考察的重点。需要注意的是 MySQL 需要 8.0 的版本才能使用窗口函数。可以点击文章最下方的阅读原文到 CSDN 中阅读体验更佳。

1.窗口函数

窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。窗口函数是面试中考察的重点。窗口函数通常用来解决统计汇总、排名、TopN、连续登录天数等问题。

语法：函数名(字段名) over(partition by <要分列的组> order by <要排序的列> rows between <数据范围>)

数据范围：通过下面的案例来讲解数据范围如何使用。

# 取本行和前面两行
rows between 2 preceding and current row

# 取本行和之前所有的行 
rows between unbounded preceding and current row

# 取本行和之后所有的行
rows between current row and unbounded following

# 从前面三行和下面一行，总共五行
rows between 3 preceding and 1 following 

# 当order by后面没有rows between时，窗口规范默认是取本行和之前所有的行
# 当order by和rows between都没有时，窗口规范默认是分组下所有行（rows between unbounded preceding and unbounded following）

分类：按照窗口函数的意义大概可以分为下面5类，其中排序函数最为常用。

排序函数：row_number()、rank()、dense_rank()
分布函数：percent_rank()、cume_dist()
相对位置函数：lag(expr,n)、lead(expr,n)，用于返回某字段的前 n 行或后 n 行的值。expr 既可以是表达式也可以是列名。
绝对位置函数：first_value(expr)、last_value(expr)、nth_value(expr,n)，返回第一个或最后一个或第 n 个 expr的值。
分桶函数：ntile(x)

另外，聚合函数也可以作为窗口函数使用：

聚合函数：avg()，sum()，min()，max()

1.1 排序函数

row_number()：对每一行分配一个序号，序号连续加1，不会重复。常用于排序。
rank()：给每行分配一个序号，相同值的序号相同，序号不连续。常用于排序。
dense_rank()：给每行分配一个序号，相同值的序号相同，序号不连续。常用于排序。

应用场景举例：对日期进行排序：

select
    log_date,
    row_number() over(order by log_date) as rn,
    rank() over(order by log_date) as r,
    dense_rank() over(order by log_date) as dr
from tb
order by log_date;

结果如下：

log_date	rn	r	dr
20220401	1	1	1
20220401	2	1	1
20220403	3	3	2
20220406	4	4	3
20220406	5	4	3
20220408	6	6	4

1.2 分布函数

percent_rank()：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。
cume_dist()：分组内小于、等于当前rank值的行数 / 分组内总行数

应用场景举例：

select
    score,
    rank() over(order by score desc) as rank,
    percent_rank() over(order by score desc) as pr,
    cume_dist() over(order by score desc) as cd
from tb
order by score desc;

结果如下：

score	rank	pr	cd
100	1	0	0.33333
100	1	0	0.33333
94	3	0.4	0.66666
94	3	0.4	0.66666
80	5	0.8	1
80	5	0.8	1

1.3 相对位置函数

lag(expr,n)：返回位于当前行的前 n 行的值
lead(expr,n)：返回位于当前行的后 n 行的值

应用场景举例：求下一次销售时间

select
    name,
    log_date,
    lead(log_date,1) over(partition by name order by log_date) next_ld
from tb

结果如下：

name	log_date	next_ld
aa	2019-10-01	2020-04-18
aa	2020-04-18	2021-11-06
aa	2021-11-06	NULL
bb	2020-05-08	2020-07-12
bb	2020-07-12	NULL

1.4 绝对位置函数

first_value(expr)：返回第一个 expr 的值。
last_value(expr)：返回最后一个 expr 的值。
nth_value(expr,n)：返回窗口中第 n 个 expr 的值。

应用场景 1 举例：求首次登录和末次登录时间

select
    id,
    log_dt,
    first_value(log_dt) over(partition by id order by log_dt) f_dt,
    last_value(log_dt) over(partition by id order by log_dt) l_dt
from tb;

结果如下：

id	log_dt	f_dt	l_dt
1	2020-11-10	2020-11-10	2020-11-10
1	2021-01-20	2020-11-10	2021-01-20
1	2021-08-12	2020-11-10	2021-08-12
2	2021-12-05	2021-12-05	2021-12-05
2	2021-12-29	2021-12-05	2021-12-29

应用场景 2 举例：求部门中工资第二的员工

SELECT
    id,
    dept_id did,
    salary s,
    NTH_VALUE(salary,2) over(PARTITION BY dept_id ORDER BY salary DESC) s2
FROM employee;

结果如下：

id	did	s	s2
2	1	200	100
1	1	100	100
4	2	400	200
3	2	300	200
6	3	550	500
5	3	500	500

1.5 分桶函数

ntile(n)：对每个分区继续分成 n 组，每组的行数为：分区的总行数 / n。不常用。

1.6 窗口的定义

对于反复使用的窗口，可以单独提取出来简化代码，例如：

select
    id,
    log_dt,
    first_value(log_dt) over(partition by id order by log_dt) f_dt,
    last_value(log_dt) over(partition by id order by log_dt) l_dt
from tb;

select
    id,
    log_dt,
    first_value(log_dt) over win\
    f_dt,
    last_value(log_dt) over win l_dt
from tb
window win as (partition by id order by log_dt);

2.日期时间函数

日期时间函数较多，经常考察的用加粗字体标出。
CURDATE() 或 CURRENT_DATE() 返回当前日期
CURTIME() 或 CURRENT_TIME() 返回当前时间
NOW() 返回当前系统日期时间
SYSDATE() 同上
CURRENT_TIMESTAMP() 同上
LOCALTIME() 同上
LOCALTIMESTAMP() 同上
YEAR(date) 返回年
MONTH(date) 返回月
DAY(date) 返回日
HOUR(time) 返回时
MINUTE(time) 返回分
SECOND(time) 返回秒
WEEK(date) 返回一年中的第几周
WEEKOFYEAR(date) 同上
DAYOFWEEK() 返回周几，注意：周日是1，周一是2，...周六是7
WEEKDAY(date) 返回周几，注意，周1是0，周2是1，...周日是6
DAYNAME(date) 返回星期：MONDAY,TUESDAY,...SUNDAY
MONTHNAME(date) 返回月份：January，...
DATEDIFF(date1,date2) 返回date1 - date2的日期间隔
TIMEDIFF(time1, time2) 返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVAL expr type) 返回与给定日期时间相差 INTERVAL 时间段的日期时间。表达式类型：

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #需要单引号

#YEAR DAY_HOUR YEAR_MONTH
#MONTH DAY_MINUTE
#DAY DAY_SECOND
#HOUR HOUR_MINUTE
#MINUTE HOUR_SECOND
#SECOND MINUTE_SECOND

DATE_FORMAT(datetime ,fmt) 按照字符串 fmt 格式化日期 datetime 值。fmt的格式见下方，常用的格式化用加粗表示。

%Y 4位数字表示年份
%y 表示两位数字表示年份
%M 月名表示月份（January,...）
%m 两位数字表示月份（01,02,03,...）
%b 缩写的月名（Jan.，Feb.，...）
%c 数字表示月份（1,2,3,...）
%D 英文后缀表示月中的天数（1st,2nd,3rd,...）
%d 两位数字表示月中的天数(01,02...)
%e 数字形式表示月中的天数（1,2,3,4,5,...）
%H 两位数字表示小时，24小时制（01,02,...）
%h和%I 两位数字表示小时，12小时制（01,02,...）
%k 数字形式的小时，24小时制(1,2,3,...)
%l 数字形式表示小时，12小时制（1,2,3,4,...）
%i 两位数字表示分钟（00,01,02,...）
%W 一周中的星期名称（Sunday,...）
%a 一周中的星期缩写（Sun.,Mon.,Tues.,...）
%w 以数字表示周中的天数(0=Sunday,1=Monday....)
%j 以3位数字表示年中的天数(001,002,...)
%U 以数字表示年中的第几周，（1,2,3,...）其中Sunday为周中第一天
%u 以数字表示年中的第几周，（1,2,3,...）其中Monday为周中第一天
%T 24小时制
%r 12小时制
%p AM或PM
%% 表示%

DATE_FORMAT(datetime, '%Y-%m')    # ’2020-02‘
DATE_FORMAT(datetime, '%Y%m%d')    # ’20200201‘

STR_TO_DATE(str, fmt) 按照字符串fmt对str进行解析，解析为一个日期

3.流程函数

IF(value,t ,f) 如果value是真，返回t，否则返回f
IFNULL(value1, value2) 如果value1不为空，返回value1，否则返回value2
CASE WHEN

# 相当于 if else
CASE 
WHEN 条件1 THEN result1
WHEN 条件2 THEN result2
....
[ELSE resultn]
END

# 相当于 switch
CASE  expr 
WHEN 常量值1 THEN 值1
WHEN 常量值1 THEN 值1
....
[ELSE 值n]
END

4.数学函数

ABS(x) 返回 x 的绝对值
CEIL(x) 返回大于 x 的最小整数值
FLOOR(x) 返回小于 x 的最大整数值
MOD(x,y) 返回 x/y 的模
RAND(x) 返回0~1的随机值，x可以不写
ROUND(x,y) 返回参数 x 的四舍五入的有 y 位的小数的值
TRUNCATE(x,y) 返回数字 x 截断为 y 位小数的结果
SQRT(x) 返回 x 的平方根
POW(x,y) 返回 x 的 y 次方

5.字符串函数

字符串函数较多，经常考察的用加粗字体标出。

CONCAT(S1,S2,......,Sn) 连接 S1, S2, ......, Sn 为一个字符串
CONCAT_WS(s, S1, S2, ......, Sn) 同 CONCAT(s1, s2, ...)函数，但是每个字符串之间要加上 s
CHAR_LENGTH(s) 返回字符串s的字符数
LENGTH(s) 返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr) 将字符串 str 从第 index 位置开始，len 个字符长的子串替换为字符串 instr
UPPER(s) 或 UCASE(s) 将字符串 s 的所有字母转成大写字母
LOWER(s) 或 LCASE(s) 将字符串 s 的所有字母转成小写字母
LEFT(s,n) 返回字符串 s 最左边的 n 个字符
RIGHT(s,n) 返回字符串 s 最右边的 n 个字符
LPAD(str, len, pad) 用字符串 pad 对 str 最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad) 用字符串 pad 对 str 最右边进行填充，直到 str 的长度为 len 个字符
LTRIM(s) 去掉字符串 s 左侧的空格
RTRIM(s) 去掉字符串 s 右侧的空格
TRIM(s) 去掉字符串 s 开始与结尾的空格
TRIM(【BOTH 】s1 FROM s) 去掉字符串 s 开始与结尾的 s1
TRIM(LEADING s1 FROM s) 去掉字符串 s 开始处的 s1
TRIM(TRAILING s1 FROM s) 去掉字符串 s 结尾处的 s1
REPEAT(str, n) 返回 str 重复 n 次的结果
REPLACE（str, a, b）用字符串 b 替换字符串 str 中所有出现的字符串 a
STRCMP(s1,s2) 比较字符串 s1, s2
SUBSTRING(s,index,len) 返回从字符串 s 的 index 位置其 len 个字符。index 从 1 开始

vlambda博客
学习文章列表