SQL之常用函数

一、窗口函数

窗口函数，可以对数据库数据进行实时分析处理：

• 同时具有分组（partition by）和排序（order by）的功能
• 不减少原表的行数，所以经常用来在每组内排名
• 窗口函数原则上只能写在select子句中

语法

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：

静态窗口函数

• 排名函数，如rank，dense_rank，row_number等
• 分组函数，如ntitle

动态窗口函数

• 聚合函数，如sum，avg，count，max，min等
• 取值函数，如first_value，last_value，nth_value等
• 滑动函数，如lead，lag等

1.静态窗口函数

（1）排名函数

【每个班级内按成绩排名】:

• rank()：并列跳跃排名
• dense_rank()：并列连续排名
• row_number()：连续排名
  

SELECT *,
	RANK() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking,
	DENSE_RANK() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS dense_rank,
	ROW_NUMBER() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num
FROM 班级表

得到结果：

（2）分组函数

[ntitle()函数]：

NTITLE(4)：通过c_id分区后，再将这区域分成4组

SELECT *,
	NTITLE(4) OVER(PARTITION BY c_id)
FROM sc

1-2行属于第一组，3-4行属于第二组，5行属于第三组，6行属于第四组

2.动态窗口函数

（1）聚合函数

• sum()：求和
• avg()：求平均
• percent_rank()：累计百分比
• cume_dist()：累计分布值

【avg()函数】

over（）什么都不加，对整个数据的成绩求平均，窗口数据集是整个数据集

SELECT *, 
	AVG(score) OVER() 
FROM sc

over（）只加排序的参数，按成绩升序排列后对整个数据集成绩求移动平均

SELECT *, 
	AVG(score) OVER(ORDER BY score) 
FROM sc

over（）加分区参数再加上排序参数参数，对学生的成绩分课程求移动平均

SELECT *,
	AVG(score) OVER(PARTITION BY c_id ORDER BY score)
FROM sc

【案例1】：按每个学生平均分数降序排列成绩信息

SELECT *,
	AVG(score) OVER(PARTITION BY s_id) AS 平均成绩
FROM sc
ORDER BY 平均成绩 DESC

【案例2】：按每个学生总成绩降序排列成绩信息

SELECT *,
	SUM(score) OVER(PATITION BY s_id) AS 总成绩
FROM sc
ORDER BY 总成绩 DESC

【案例3】：查找单科成绩高于该科目平均成绩的学生名单

SELECT *
FROM (
	SELECT *,
		AVG(成绩) OVER(PARTITION BY 课程号) AS avg_score
	FROM score
	) AS b
WHERE 成绩 > avg_score

（2）取值函数

• first_value()：返回分组内第一个值
• last_value()：返回分组内最后一个值
• nth_value(n)：返回分组内第n个值

【first_value()函数】

FIRST_VALUE(score)：取所选数据区域的第一个值

SELECT *,
	FIRST_VALUE(score) OVER(PARTITION BY c_id)
FROM sc

【last_value()函数】

FIRST_VALUE(score)：取所选数据区域的最后一个值

SELECT *,
	LAST_VALUE(score) OVER(PARTITION BY c_id)
FROM sc

【nth_value()函数】

NTH_VALUE(score,2)：取分区后，所有区域的第二个值

SELECT *,
	NTH_VALUE(score,2) OVER(PARTITION BY c_id) 
FROM sc

（3）滑动函数

• lead函数，用于统计窗口内往下第n行值
• lag函数，用于统计窗口内往上第n行值

【lead()函数】

LEAD(score, 2)：score这列当前行，往下数2行，出现空值则为Null

SELECT *,
	LEAD(score,2) OVER(PARTITION BY c_id ORDER BY score DESC)
FROM sc

【lag()函数】

LAG(score,1,0)：score这列当前行往上数1行，如果出现空值则为0

SELECT *,
	LAG(score,1,0) OVER(PARTITION BY c_id ORDER BY score DESC)
FROM sc

（4）框架参数

rows或（range）子句往往来控制窗口边界范围，基本语法：

• CURRENT ROW：当前行；
• n PRECEDING：往前n行数据；
• n FOLLOWING：往后n行数据；
• UNBOUNDED PRECEDING： 表示从前面的起点
• UNBOUNDED FOLLOWING：表示到后面的终点

默认框架：

range BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

【例子】：

计算近7日点赞总量

SUM(like_cnt) OVER(PARTITION BY tag ORDER BY dt rows 6 PRECEDING)

包括了自身，所以往前数6个单位

二、正则表达式

1. LIKE模糊匹配

用法：

SELECT a
FROM table 
WHERE b LIKE 'pattern'

pattern：

• 百分号’%'匹配任意数目的字符
• 下划线’_'匹配任意单个字符
  
  比如，从Persons表中:

例子1：选取居住在以 “N” 开始的城市里的人：

SELECT * FROM Persons
WHERE City LIKE 'N%'

例子2：选取居住在以 “g” 结尾的城市里的人：

SELECT * FROM Persons
WHERE City LIKE '%g'

例子3：选取居住在包含 “lon” 的城市里的人：

SELECT * FROM Persons
WHERE City LIKE '%lon%'

2. REGEXP == RLIKE 正则匹配

用法：

SELECT a
FROM table 
WHERE b RLIKE(REGEXP) 'pattern'

常用正则表达式：

模式	表示
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
*	匹配前面的子表达式一次或多次，例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}
+	匹配前面的子表达式零次或多次，例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}
[0-9]	匹配数字0-9
[…]	匹配所包含的任意一个字符。例如， [abc]可以匹配 “plain” 中的 ‘a’
[^…]	匹配未包含的任意字符。例如， [^abc]可以匹配 “plain” 中的’p’

三、字符串函数

1. SUBSTRING_INDEX函数

字符串截断，用法：

SUBSTRING_INDEX(要处理的字符,'分隔符',计数)

这里有细节，例如str=[www.baidu.com]

• 当计数是正数，那么就是从左往右数，第N个分隔符左边的所有内容

SUBSTRAING_INDEX(str,'.',1)
结果是：www

SUBSTRAING_INDEX(str,'.',2)
结果是：www.baidu

• 当计数是负数，那么就是从右往左数，第N个分隔符右边的所有内容

SUBSTRAING_INDEX(str,'.',-2)
结果是：baidu.com

• 中间位置，从两个方向进行

SUBSTRAING_INDEX(SUBSTRAING_INDEX(str,'.',-2),'.',1)
结果是：baidu

2. GROUP_CONCAT函数

将group by产生的同一个分组中的值连接起来，返回一个字符串

用法：

SELECT 性别,GROUP_CONCAT(姓名 ORDER BY 性别) AS 组合字段
FROM student
GROUP BY 性别

结果：

3. CONCAT_WS函数

将多个字符串连接成一个字符串，在最开始的位置连接符，指定一次即可（concat需要多次指定）

用法：

SELECT *, CONCAT_WS('-',学号，姓名，出生日期)
FROM student

结果：

4. SUBSTR函数

用于从字段中提取出相应位置的字符

用法：

SELECT *, SUBSTR(出生日期,1,4) # 提取年份
FROM student

结果：

5. 其他函数

• CHAR_LENGTH：计算字符串长度
• CONCAT：将多个字符串连接成一个字符串

四、拼接

1 UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集，但是

• UNION 内部的 SELECT 语句必须拥有相同数量的列
• 列也必须拥有相似的数据类型
• 同时，每条 SELECT 语句中的列的顺序必须相同

比如，如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过

SELECT uid, DATE(in_time) AS dt 
FROM tb_user_log                            -- 相同数量的列：都为2列
UNION										-- 数据类型相同
SELECT uid, DATE(out_time) AS dt			-- 列的顺序相同：uid dt
FROM tb_user_log

【注】：默认地，UNION 操作符选取不同的值

2. UNION ALL

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值

参考

https://zhuanlan.zhihu.com/p/92654574