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

  今天接着昨天的内容讲解一下 SQL 中函数的使用。其中窗口函数是考察的重点。需要注意的是 MySQL 需要 8.0 的版本才能使用窗口函数。

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 开始

欢迎关注公众号。