MySQL常用及自定义函数
计算函数
| 函数 | 参数 | 作用 |
|---|---|---|
| abs | a | 取绝对值 |
| round | a,b | 按照四舍五入取整，可以选择精确位数 |
| sqrt | a | 取平方根 |
| power | a,b | 指数运算 POWER(a,b)=a^b |
| mod | a,b | 取模运算 MOD(a,b)=a%b |
| sin、cos、tan | a | 三角函数 |
| ceiling | a,b | 向上取整 如：CEILING(1.14)=2 CEILING(-1.14)=-1 |
| floor | a,b | 向下取整 如：FLOOR(1.14)=1 FLOOR(-1.14)=-2 |
字符串函数
| 函数 | 参数 | 作用 |
|---|---|---|
| ascii | a | 求ascii码；空字符为0；null为null |
| concat | a,b,…,n | 以英文逗号连接各个子字符串 |
| length | a,b a=str ,b=length | 返回字符串的长度 |
| instr | a,b | 返回b在a中第一次出现的位置 |
| locate | a,b | 返回a在b中第一次出现的位置 |
| left、right | a,b | 取a 左边（或右）的b个字符 |
| substring | a,b a=str ,b=index | 从index位置开始截取子字符串 （还有多种入参，效果不同） |
| trim、ltrim、rtrim | a | 截取两边（左）（右）的空格字符 |
| replace | a,b,c | 替换字符串 a 中所有的 b 为 c |
| repeat | a,b a=str ,b=repeat times | 将字符串 a 重复 b次 |
| reverse | a | 反转输出 |
| insert | a,b,c,d a=str ,b=pos ,c=len, d=newstr | 将字符串 a 从 b 位置开始后的 c 个字符 全部替换成 newstr |
| lpad、rpad | a,b,c a=str ,b=endsize ,c=fillstr | 将字符串c 填充到 字符串 a 的左边（或右边），直到达到b长度 |
| - | - | 如果b<a.length a截取为b长度字符串如： LPAD(‘abc’,1,‘11’)=1 |
时间函数
| 函数 | 参数 | 作用 |
|---|---|---|
| now | - | 返回当前时间，默认以 YYYY-mm-dd HH:MM:SS 的格式 |
| date_add | date,INTERVAL expr type | 时间加 DATE_ADD(NOW(), INTERVAL 1 HOUR) |
| - | - | 当type为符合类型是需要用单引号括起来并空格， 如 DATE_ADD(NOW(), INTERVAL '1 33 22’ DAY_MINUTE) |
| date_sub | date,INTERVAL expr type | 时间减 type=(YEAR,DAY,HOUR,MINUTE,SECOND,DAY_HOUR,DAY_MINUTE,DAY_SECOND) |
| extract | unit from date | 抽取时间段的某个单元 如 EXTRACT(DAY FROM NOW()) EXTRACT(YEAR FROM NOW()) |
| year | date | 获取年份 |
| month | date | 获取月份 |
流程函数
1）IF
IF(expr1,expr2,expr3)
--如果表达式expr1 为真，则返回expr2，否则返回expr3
2）IFNULL
IF(v1,v2)
--如果v1不为null ，返回v1，否则返回v2
3）CASE WHEN
CASE 字段 WHEN r1 THEN c1 WHEN .. THEN .. ELSE cn
--直接case某个字段，当值为r1返回c1，所有都不满足，返回cn
CASE WHEN expr1 THEN oper1 WHEN .. THEN .. ELSE opern
--case 后 满足表达式expr1 执行oper1操作，所有都不满足，执行opern操作
窗口函数
窗口函数又称分析函数。Mysql8以后开始支持该功能，常用的oracle也支持窗口函数。
区分于分组函数。分组函数会聚合分组内容，而窗口函数可以针对每行进行操作。
window_function_name（expr）
over([PARTITION_clause] [ORDER_clause] [FRAME_clause])
关于窗口函数的使用，可以分为两类：静态窗口函数、滑动窗口函数。
- 静态窗口函数：不改变窗口大小，原本数据有多少行，使用函数后仍旧多少行
- 滑动窗口函数：改变窗口大小。
建表实测
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`age` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'zs', 11);
INSERT INTO `user` VALUES (2, 'ls', 15);
INSERT INTO `user` VALUES (3, 'zs', 22);
INSERT INTO `user` VALUES (4, 'ww', 15);
INSERT INTO `user` VALUES (5, 'ls', 18);
INSERT INTO `user` VALUES (6, 'ww', 16);
INSERT INTO `user` VALUES (7, 'zs', 11);
INSERT INTO `user` VALUES (8, 'ls', 19);
INSERT INTO `user` VALUES (9, 'ww', 16);
表数据
| id | name | age |
+----+------+------+
| 1 | zs | 11 |
| 2 | ls | 15 |
| 3 | zs | 22 |
| 4 | ww | 15 |
| 5 | ls | 18 |
| 6 | ww | 16 |
| 7 | zs | 11 |
| 8 | ls | 19 |
| 9 | ww | 16 |
+----+------+------+
window_function
1)排名函数
SELECT *,
RANK() over(PARTITION BY name order by age ASC ) sort1,
DENSE_RANK() over(PARTITION BY name order by age ASC ) sort2,
ROW_NUMBER() over(PARTITION BY name order by age ASC ) sort3
FROM `user`
+----+------+------+-------+-------+-------+
| id | name | age | sort1 | sort2 | sort3 |
+----+------+------+-------+-------+-------+
| 2 | ls | 15 | 1 | 1 | 1 |
| 5 | ls | 18 | 2 | 2 | 2 |
| 8 | ls | 19 | 3 | 3 | 3 |
| 4 | ww | 15 | 1 | 1 | 1 |
| 6 | ww | 16 | 2 | 2 | 2 |
| 9 | ww | 16 | 2 | 2 | 3 |
| 1 | zs | 11 | 1 | 1 | 1 |
| 7 | zs | 11 | 1 | 1 | 2 |
| 3 | zs | 22 | 3 | 2 | 3 |
+----+------+------+-------+-------+-------+
排名函数有3种：
占位：存在两个排名1时，下一个排名从3开始，没有排名为2的记录的情况。
- RANK()：相同值排名相同，会占位。
- DENSE_RANK()：相同值排名相同，不会站位。
- ROW_NUMBER()：相同值排名不同。
2)聚合函数
| 函数 | 作用 |
|---|---|
| sum | 窗口求和 |
| avg | 窗口求平均 |
| percent_rank | 累计百分比 |
| cume_dist | 累计分布值 |
3)取值函数
| 函数 | 参数 | 作用 |
|---|---|---|
| last_value，first_value | exper | 窗口最后一个（第一个）值 |
| nth_value | exper,n | 返回窗口第n行的值 |
| lag | exper,n | 返回前n行的值r |
| lead | exper,n | 返回后n行的值r |
| ntile | n | 分成n个桶 |
SELECT
u.*,
LAST_VALUE(u.name) over(PARTITION BY u.name ) 窗口最后一个值,
FIRST_VALUE(u.name) over(PARTITION BY u.name) 窗口第一个值,
LAG(u.age,1) over(PARTITION BY u.name) 前一行age,
LEAD(u.age,1) over(PARTITION BY u.name) 后一行age,
NTH_VALUE(u.age,2) over(PARTITION BY u.name)窗口第2行age,
NTILE(2) over(PARTITION BY u.name) 分成2个桶
FROM user u
+----+------+------+----------------+--------------+-----------+-----------+--------------+-----------+
| id | name | age | 窗口最后一个值 | 窗口第一个值 | 前一行age | 后一行age | 窗口第2行age | 分成2个桶 |
+----+------+------+----------------+--------------+-----------+-----------+--------------+-----------+
| 2 | ls | 15 | ls | ls | NULL | 18 | 18 | 1 |
| 5 | ls | 18 | ls | ls | 15 | 19 | 18 | 1 |
| 8 | ls | 19 | ls | ls | 18 | NULL | 18 | 2 |
| 4 | ww | 15 | ww | ww | NULL | 16 | 16 | 1 |
| 6 | ww | 16 | ww | ww | 15 | 16 | 16 | 1 |
| 9 | ww | 16 | ww | ww | 16 | NULL | 16 | 2 |
| 1 | zs | 11 | zs | zs | NULL | 22 | 22 | 1 |
| 3 | zs | 22 | zs | zs | 11 | 11 | 22 | 1 |
| 7 | zs | 11 | zs | zs | 22 | NULL | 22 | 2 |
+----+------+------+----------------+--------------+-----------+-----------+--------------+-----------+
使用lag函数计算差值
SELECT u.*, (LAG(u.age,1) OVER(ORDER BY u.age desc) -u.age) 年龄差值 FROM user u
+----+------+------+----------+
| id | name | age | 年龄差值 |
+----+------+------+----------+
| 3 | zs | 22 | NULL |
| 8 | ls | 19 | 3 |
| 5 | ls | 18 | 1 |
| 6 | ww | 16 | 2 |
| 9 | ww | 16 | 0 |
| 2 | ls | 15 | 1 |
| 4 | ww | 15 | 0 |
| 1 | zs | 11 | 4 |
| 7 | zs | 11 | 0 |
+----+------+------+----------+
partition、order
- partition： 分组关键字 partition by
- order by: 排序关键字 order by
- frame: 框架设置
测试内容
SELECT user.*,
sum(age) over() 不分组不排序,
sum(age) over(partition by name) 只分组,
sum(age) over(order by name) 只排序
FROM user
+----+------+------+--------------+--------+--------+
| id | name | age | 不分组不排序 | 只分组 | 只排序 |
+----+------+------+--------------+--------+--------+
| 2 | ls | 15 | 143 | 52 | 52 |
| 5 | ls | 18 | 143 | 52 | 52 |
| 8 | ls | 19 | 143 | 52 | 52 |
| 4 | ww | 15 | 143 | 47 | 99 |
| 6 | ww | 16 | 143 | 47 | 99 |
| 9 | ww | 16 | 143 | 47 | 99 |
| 1 | zs | 11 | 143 | 44 | 143 |
| 3 | zs | 22 | 143 | 44 | 143 |
| 7 | zs | 11 | 143 | 44 | 143 |
+----+------+------+--------------+--------+--------+
由上面的测试可知：
- 不分组、不排序时 ==>范围为所有行；
- 只分组时 ==>按照分组字段分为各个区；
- 只排序时 ==>范围为第1个区到当前区（相同排序字段值为1个区）；
frame框架
单位
frame字句 是在经过PARTITION BY 分组之后再度缩小的语句，相当于二次分组。
主要包含两个单位： ROWS、RANGE
- ROWS：以当前行为基准，前后取单位
- **RANGE：**以当前行值为基准，范围比对取值
范围
| 框架语句 | 描述 |
|---|---|
| CURRENT ROW | 当前扫描行 |
| UNBOUNDED PRECEDING | 上侧所有行到当前扫描行（分组范围内） |
| UNBOUNDED FOLLOW | 下侧所有行到当前扫描行（分组范围内） |
| expr PRECEDING | 当前扫描行上侧expr 行 |
| expr FOLLOWING | 当前扫描行下侧expr 行 |
-- 关于框架范围定义，主要有两种方式
OVER(PARTITION BY expr ORDER BY expr ROWS/RANGE ..(range_start))
OVER(PARTITION BY expr ORDER BY expr ROWS/RANGE BETWEEN ..(range_start) AND ..(range_end))
range_start和range_between
不使用BETWEEN来明确划分框架范围的情况下，默认是range_start 到当前扫描行。
ROWS测试
SELECT *,
SUM(age) OVER(PARTITION BY name ORDER BY id ROWS CURRENT ROW) no_between,
SUM(age) OVER(PARTITION BY name ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) use_between
FROM `user`
+----+------+------+------------+-------------+
| id | name | age | no_between | use_between |
+----+------+------+------------+-------------+
| 2 | ls | 15 | 15 | 33 |
| 5 | ls | 18 | 18 | 52 |
| 8 | ls | 19 | 19 | 37 |
| 4 | ww | 15 | 15 | 31 |
| 6 | ww | 16 | 16 | 47 |
| 9 | ww | 16 | 16 | 32 |
| 1 | zs | 11 | 11 | 33 |
| 3 | zs | 22 | 22 | 44 |
| 7 | zs | 11 | 11 | 33 |
+----+------+------+------------+-------------+
使用ROWS CURRENT ROW表示只扫描当前行。经过name第一次分组后，整张表被分成了3个组：(2,5,8)-(4,6,9)-(1,3,7)。由于sum的计算单位为CURRENT ROW （即当前行），所以每个sum都只有当前行记录。
使用ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING时，表示 [（当前行-1）行,（当前行+1）行]
如id=2时，取值为15+18=32；id=5时，取值为15+18+19=52；
RANGE 测试
SELECT *,
SUM(age) OVER(PARTITION BY name ORDER BY id RANGE BETWEEN 2 PRECEDING AND 3 FOLLOWING ) range_calc
FROM `user`
----+------+------+------------+
| id | name | age | range_calc |
+----+------+------+------------+
| 2 | ls | 15 | 33 |
| 5 | ls | 18 | 37 |
| 8 | ls | 19 | 19 |
| 4 | ww | 15 | 31 |
| 6 | ww | 16 | 47 |
| 9 | ww | 16 | 16 |
| 1 | zs | 11 | 33 |
| 3 | zs | 22 | 33 |
| 7 | zs | 11 | 11 |
+----+------+------+------------+
上图 id=1的计算规则是： 当前扫描行 id=2 所以范围为 [2-2,2+3]，即[0,5]，符合要求的有两行。最后为15+18=33。
上图 id=7的计算规则是： 当前扫描行 id=7 所以范围为 [7-2,7+3]，即[5,10]，符合要求的只有当前行。最后为11=11。
由上面我们可以总结出：
RANGE进行 值得范围比较，并且依照的是ORDER BY的内容，在本例中为id。
另外还有一点：从上面可以看出来，计算时需要ORDER内容。所以在使用ROWS/RANGE字句时，需要指定ORDER内容。
事实上，mysql8针对这边也加了验证。
SELECT *,
SUM(age) OVER(PARTITION BY name RANGE BETWEEN 2 PRECEDING AND 3 FOLLOWING ) range_calc
FROM `user`
-- error
Window '<unnamed window>' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type
扫一扫
311
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
