mysql 自定义函数 返回结果集 mysql自定义函数数学公式

MySQL常用及自定义函数

• 计算函数
• 字符串函数
• 时间函数
• 流程函数

• 1）IF
• 2）IFNULL
• 3）CASE WHEN

• 窗口函数

• window_function

• 1)排名函数
• 2)聚合函数
• 3)取值函数

• 使用lag函数计算差值

• partition、order
• frame框架

• 单位
• 范围

• range_start和range_between

计算函数

函数	参数	作用
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