MySQL的函数

已于 2023-02-14 15:45:05 修改
阅读量139 收藏
点赞数
文章标签： mysql 数据库 java
于 2023-02-13 20:48:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_75278009/article/details/128953922
版权

文章目录

MySQL的函数

概述：

  • 在MySQL中，为了提高代码重用性和隐藏实现细节，MySQL提供了很多函数

  • 函数可以理解为别人封装好的模板代码（相当于java中的方法）

在MySQL中，函数非常多，主要可以分为以下几类

  1. 聚合函数

  2. 数学函数

  3. 字符串函数

  4. 日期函数

  5. 控制流函数

  6. 窗口函数

聚合函数——group_concat()

概述：

  • 在MySQL中，聚合函数主要有：count，sum，min，max，avg，这些我以前说过，就不说了

  • 我说另外一个函数:group_concat(），该函数用于实现行的合并

  • group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果

  • 如果不分组，查询结果将展示在一行上

格式：

group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator
‘分隔符’])

说明：
　　（1）使用distinct可以排除重复值
　　（2）如果需要对结果中的值进行排序，可以使用order by子句
　　（3）separator是一个字符串值，默认为逗号

我们准备一些数据便于接下来的操作

create table emp(
    emp_id int primary key auto_increment comment '编号',
    emp_name char(20) not null default '' comment '姓名',
    salary decimal(10,2) not null default 0 comment '工资',
    department char(20) not null default '' comment '部门'
);
 
insert into emp(emp_name,salary,department) 
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部');

这里说一个小的知识点：

  • 上述的COMMENT 是备注、注释的意思，写上COMMENT ‘注释信息后’ 之后，在建表信息里可以看到添加的备注信息

  • COMMENT ‘注释信息’ 删除不会影响数据操作作只是没有字段注释说明，通常字段或列名都需要加注释，以方便自己和其他同事阅读数据库表字段信息。

操作：

  • 目标：指定排序方式和分隔符
SELECT 
department,GROUP_CONCAT(emp_name ORDER BY salary DESC SEPARATOR ';')
FROM emp
GROUP BY department;

查询结果为在这里插入图片描述

数学函数

函数描述实例
ABS(x)返回 x 的绝对值返回 -1 的绝对值：SELECT ABS(-1) – 返回1
CEIL(x)返回大于或等于 x 的最小整数（向上取整）SELECT CEIL(1.5) – 返回2
FLOOR(x)返回小于或等于 x 的最大整数（向上取整）SELECT FLOOR(1.5) – 返回1
GREATEST(expr1, expr2, expr3, …)返回列表中的最大值返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); – 返回34；返回以下字符串列表中的最大值：SELECT GREATEST(“Google”, “Runoob”, “Apple”); – 返回Runoob（英文单词按出现在字典中的先后顺序查找）
LEAST(expr1, expr2, expr3, …)返回列表中的最小值返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); – 返回3；返回以下字符串列表中的最小值：SELECT LEAST(“Google”, “Runoob”, “Apple”); – 返回Apple
MAX(expression)返回字段 expression 中的最大值返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)返回字段 expression 中的最小值返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)返回 x 除以 y 以后的余数5 除于 2 的余数：SELECT MOD(5,2) – 1
PI()返回圆周率(3.141593）SELECT PI() --3.141593
POW(x,y)返回 x 的 y 次方2 的 3 次方：SELECT POW(2,3) – 8
RAND()返回 0 到 1 的随机数SELECT RAND() --0.93099315644334
ROUND(x)返回离 x 最近的整数（遵循四舍五入）SELECT ROUND(1.23456) --1
ROUND(x,y)返回指定位数的小数（遵循四舍五入）SELECT ROUND(1.23456,3) –1.235
TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）SELECT TRUNCATE(1.23456,3) – 1.234

字符串函数

函数描述实例
CHAR_LENGTH(s)返回字符串 s 的字符数返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString;
LENGTH(s)返回字符串 s 的字节数返回字符串 你好 的字节数SELECT LENGTH(‘你好’) ->6
CHARACTER_LENGTH(s)返回字符串 s 的字符数[等同于char_length(s)]返回字符串 RUNOOB 的字符数SELECT CHARACTER_LENGTH(“RUNOOB”) AS LengthOfString;
CONCAT(s1,s2…sn)字符串 s1,s2 等多个字符串合并为一个字符串合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", “Facebook”) AS ConcatenatedString;
CONCAT_WS(x, s1,s2…sn)同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x，x 可以是分隔符（分隔符要放在第一位）合并多个字符串，并添加分隔符：SELECT CONCAT_WS(“-”, “SQL”, “Tutorial”, “is”, “fun!”)AS ConcatenatedString;
FIELD(s,s1,s2…)返回第一个字符串 s 在字符串列表(s1,s2…)中的位置返回字符串 c 在列表值中的位置：SELECT FIELD(“c”, “a”, “b”, “c”, “d”, “e”);
left(s,n)返回字符串 s 的前 n 个字符返回字符串 runoob 的前两个字符：SELECT LEFT(‘runoob’,2) – ru
LTRIM(s)去掉字符串 s 开始处的空格去掉字符串 RUNOOB开始处的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
MID(s,n,len)从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT MID(“RUNOOB”, 2, 3) AS ExtractString; – UNO
POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置返回字符串 abc 中 b 的位置：SELECT POSITION(‘b’ in ‘abc’) – 2
REPLACE(s,s1,s2)将字符串 s2 替代字符串 s 中的字符串 s1将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE(‘abc’,‘a’,‘x’) --xbc
REVERSE(s)将字符串s的顺序反过来将字符串 abc 的顺序反过来：SELECT REVERSE(‘abc’) – cba
RIGHT(s,n)返回字符串 s 的后 n 个字符返回字符串 runoob 的后两个字符：SELECT RIGHT(‘runoob’,2) – ob
RTRIM(s)去掉字符串 s 结尾处的空格去掉字符串 RUNOOB 的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString; – RUNOOB
STRCMP(s1,s2)比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1比较字符串：SELECT STRCMP(“runoob”, “runoob”); – 0
SUBSTR(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTR(“RUNOOB”, 2, 3) AS ExtractString; – UNO
SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTRING(“RUNOOB”, 2, 3) AS ExtractString; – UNO
TRIM(s)去掉字符串 s 开始和结尾处的空格去掉字符串 RUNOOB 的首尾空格：SELECT TRIM(’ RUNOOB ') AS TrimmedString;
UCASE(s)将字符串转换为大写将字符串 runoob 转换为大写：SELECT UCASE(“runoob”); – RUNOOB
UPPER(s)将字符串转换为大写将字符串 runoob 转换为大写：SELECT UPPER(“runoob”); – RUNOOB
LCASE(s)将字符串 s 的所有字母变成小写字母字符串 RUNOOB 转换为小写：SELECT LCASE(‘RUNOOB’) – runoob
LOWER(s)将字符串 s 的所有字母变成小写字母字符串 RUNOOB 转换为小写：SELECT LOWER(‘RUNOOB’) – runoob

日期函数

下方提到的相关日期格式类型可看 程序小达人 博主的该篇博客
链接: MySql时间日期函数及格式化详解

函数描述实例
UNIX_TIMESTAMP()返回从1970-01-01 00:00:00到当前毫秒值select UNIX_TIMESTAMP() ->1675941963
UNIX_TIMESTAMP(DATE_STRING)将制定日期转为毫秒值时间戳SELECT UNIX_TIMESTAMP(‘2011-12-07 13:01:03’);
FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])将毫秒值时间戳转为指定格式日期SELECT FROM_UNIXTIME(1598079966,‘%Y-%m-%d %H:%i:%s’); -> 2020-08-22 15-06-06
CURDATE()返回当前日期（年月日）SELECT CURDATE();-> 2023-02-09
CURRENT_DATE()返回当前日期（年月日）SELECT CURRENT_DATE();-> 2023-02-09
CURTIME()返回当前时间（时分秒）SELECT CURTIME();-> 19:37:04
CURRENT_TIME()返回当前时间（时分秒）SELECT CURRENT_TIME();-> 19:37:04
CURRENT_TIMESTAMP()返回当前日期和时间（年月日 时分秒）SELECT CURRENT_TIMESTAMP()-> 2023-02-09 19:37:04
DATE()从日期或日期时间表达式中提取日期值SELECT DATE(“2017-06-15”); -> 2017-06-15
DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’)-> -32
TIMEDIFF(time1, time2)计算时间差值SELECT TIMEDIFF(“13:10:11”, “13:10:10”);-> 00:00:01
DATE_FORMAT(d,f)按表达式 f的要求显示日期 dSELECT DATE_FORMAT(‘2011-11-11 11:11:11’,‘%Y年%m月%d日 %r’)-> 2011年11月11日 11:11:11 AM
STR_TO_DATE(string, format_mask)将字符串转变为日期SELECT STR_TO_DATE(“August 10 2017”, “%M %d %Y”);-> 2017-08-10
DATE_SUB(date,INTERVAL expr type)函数从日期减去指定的时间间隔Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY)
DATE_ADD(d，INTERVAL expr type)计算起始日期 d 加上一个时间段后的日期加上10天：SELECT DATE_ADD(“2017-06-15”, INTERVAL 10 DAY); -> 2017-06-25

DATE_ADD(d，INTERVAL expr type) 中type 值可以是：

  • MICROSECOND ——>微秒

  • SECOND ——>秒

  • MINUTE ——>分

  • HOUR ——>时

  • DAY ——>天

  • WEEK ——>周

  • MONTH ——>月

  • QUARTER ——>季度

  • YEAR ——>年

  • DAY_SECOUND ——>在当前时间上加秒数

  • DAY_MINUTE ——>在当前时间上加分数

  • DAY_HOUR ——>在当前时间上加小时数

  • YEAR_MONTH ——>在当前时间上加月数

  • SECOND_MICROSECOND

  • MINUTE_MICROSECOND

  • MINUTE_SECOND

  • HOUR_MICROSECOND

  • HOUR_SECOND

  • HOUR_MINUTE

  • DAY_MICROSECOND

  • DAY_SECOND

  • DAY_MINUTE

  • DAY_HOUR

  • YEAR_MONTH

函数描述实例
EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值SELECT EXTRACT(MINUTE FROM ‘2011-11-11 11:11:11’) -> 11
LAST_DAY(d)返回给给定日期的那一月份的最后一天SELECT LAST_DAY(“2017-06-20”);-> 2017-06-30
MAKEDATE(year, day-of-year)基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期SELECT MAKEDATE(2017,50);-> 2017-02-19
YEAR(d)返回年份SELECT YEAR(“2017-06-15”);-> 2017
MONTH(d)返回日期d中的月份值，1 到 12SELECT MONTH(‘2011-11-11 11:11:11’)->11
DAY(d)返回日期值 d 的日期部分SELECT DAY(“2017-06-15”); -> 15
HOUR(t)返回 t 中的小时值SELECT HOUR(‘1:2:3’)-> 1
MINUTE(t)返回 t 中的分钟值SELECT MINUTE(‘1:2:3’)-> 2
SECOND(t)SECOND(t)SELECT SECOND(‘1:2:3’)-> 3
QUARTER(d)返回日期d是第几季节，返回 1 到 4SELECT QUARTER(‘2011-11-11 11:11:11’)-> 4
MONTHNAME(d)返回日期当中的月份名称，如 NovemberSELECT MONTHNAME(‘2011-11-11 11:11:11’)-> November
MONTH(d)返回日期d中的月份值，1 到 12SELECT MONTH(‘2011-11-11 11:11:11’)->11
DAYNAME(d)返回日期 d 是星期几，如 Monday,TuesdaySELECT DAYNAME(‘2011-11-11 11:11:11’)->Friday
DAYOFMONTH(d)计算日期 d 是本月的第几天SELECT DAYOFMONTH(‘2011-11-11 11:11:11’)->11
DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推SELECT DAYOFWEEK(‘2011-11-11 11:11:11’)->6
DAYOFYEAR(d)计算日期 d 是本年的第几天SELECT DAYOFYEAR(‘2011-11-11 11:11:11’)->315
WEEK(d)计算日期 d 是本年的第几个星期，范围是 0 到 53SELECT WEEK(‘2011-11-11 11:11:11’)-> 45
WEEKDAY(d)日期 d 是星期几，0 表示星期一，1 表示星期二SELECT WEEKDAY(“2017-06-15”);-> 3
WEEKOFYEAR(d)计算日期 d 是本年的第几个星期，范围是 0 到 53SELECT WEEKOFYEAR(‘2011-11-11 11:11:11’)-> 45
YEARWEEK(date, mode)返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推SELECT YEARWEEK(“2017-06-15”);-> 201724
NOW()返回当前日期和时间SELECT NOW()-> 2018-09-19 20:57:43

控制流函数

if逻辑判断语句

格式解释案例
IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2SELECT IF(1 > 0,‘正确’,‘错误’) ->正确
IFNULL(v1,v2)如果 v1 的值不为 NULL，则返回 v1，否则返回 v2SELECT IFNULL(null,‘Hello Word’)->Hello Word
ISNULL(expression)判断表达式是否为 NULL（为NULL返回1，否则返回0）SELECT ISNULL(NULL);->1
NULLIF(expr1, expr2)比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1SELECT NULLIF(25, 25);->NULL

case when语句

格式：

CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
WHEN conditionN THEN resultN
ELSE result
END
或
CASE
WHEN expression=condition1 THEN result1
WHEN expression=condition2 THEN result2
…
WHEN expression=conditionN THEN resultN
ELSE result
END

解释：

  • CASE 表示函数开始，END 表示函数结束。

  • 如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

操作

SELECT CASE 2+1
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 5 THEN 5
ELSE 6
END AS result; -- 3

SELECT CASE 
WHEN 2+1=1 THEN 1
WHEN 2+1=2 THEN 2
WHEN 2+1=3 THEN 3
WHEN 2+1=4 THEN 4
WHEN 2+1=5 THEN 5
ELSE 6
END AS result; -- 3

熟练掌握case when 语句

准备数据

create table orders(
 oid int primary key, -- 订单id
 price double, -- 订单价格
 payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他)
);
 
insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);

要求：查询orders表的信息，包含patType对应的支付类型

SELECT *,CASE payType
WHEN 1 THEN '微信支付'
WHEN 2 THEN '支付宝支付'
WHEN 3 THEN '银行卡支付'
ELSE '其他'
END AS readyPayType
FROM orders;

查询结果
在这里插入图片描述

窗口函数

概述：

  • MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点。

  • 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。

  • 窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

聚合函数与窗口函数的区别可以通过模型图简单了解

二者模型图
在这里插入图片描述

窗口函数的分类
在这里插入图片描述
另外还有开窗聚合函数: SUM，AVG，MIN，MAX

基本语法

window_function ( expr ) OVER (
PARTITION BY …
ORDER BY …
frame_clause
)

其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；
OVER子句包含三个选项：

  • 分区（PARTITION BY）
    PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算

  • 排序（ORDER BY）
    OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似

  • 以及窗口大小（frame_clause）
    frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集

窗口大小（frame_clause）

框架的计数单位分为两种：ROWS 和 RANGE，默认值为RANGE

  • ROWS：以一行为一个单位

  • RANGE(有排序)：以连续相同的值为一个单位

  • RANGE(无排序)：以分区为单位

框架范围(frame_extent)

  • CURRENT ROW ——>当前行
  • UNBOUNDED PRECEDING ——>当前行上侧所有行
  • UNBOUNDED FOLLOWING ——>当前行下侧所有行
  • expr PRECEDING——>当前行上侧expr行(expr可以是数字，也可以是表达式)
  • expr FOLLOWING——>当前行下侧expr行(expr可以是数字，也可以是表达式)

框架范围可由两种形式来定义：frame_start 和 frame_between，范围的定义基于基本单位

  • frame_start：仅指定开始行(或区域)，则结束范围为默认值，即当前行(或区域)
  • frame_between：指定开始行(或区域)与结束行(或区域)

序号函数

序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号

格式：

row_number()|rank()|dense_rank() over (
partition by …
order by … )

数据准备

create table employee( 
   dname varchar(20), -- 部门名 
   eid varchar(20), 
   ename varchar(20), 
   hiredate date, -- 入职日期 
   salary double -- 薪资
); 
-- 添加数据
insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

操作：

  • 对每个部门的员工按照薪资排序，并给出排名，显示出排名，部门，姓名和薪水
SELECT dname,ename,salary,
row_number() over(
	PARTITION BY dname 
	ORDER BY salary 
) COUNT
FROM employee;

使用row_number() 排序为：正常排序，数据相同情况下排名依旧不相同
在这里插入图片描述

使用rank() 排序为：数据相同情况下排名相同
在这里插入图片描述
使用dense_rank() 排序为：数据相同情况下排名相同，但不会出现跳数字的情况，每个数字都保证存在
在这里插入图片描述

  • 求出每个部门薪资排在前三名的员工
SELECT dname,ename,salary
FROM
(SELECT dname,ename,salary,
rank() over(
	PARTITION BY dname 
	ORDER BY salary  DESC
) COUNT
FROM employee) t
WHERE t.count<=3;

查询结果
在这里插入图片描述

  • 对所有员工进行全局排序（不分组），不加partition by表示全局排序
SELECT dname,ename,salary,
rank() over(
	ORDER BY salary DESC
) COUNT
FROM employee;

查询结果
在这里插入图片描述

开窗聚合函数

概念

  • 在窗口中每条记录动态地应用聚合函数【SUM()、AVG()、MAX()、MIN()、COUNT()】，可以动态计算在指定的窗口内的各种聚合函数值。

  • 如果没有order by排序语句，默认把分组内的所有数据进行sum操作

select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate) as pv1 
from employee;

查询结果如下，salary的和是截止到当行数据的salary和，但如果我们排序按照的字段有重复的情况，会将重复的数据都加上。
在这里插入图片描述
去除order by 语句

SELECT dname,ename,salary,
SUM(salary) over(
	PARTITION BY dname
) sum_money FROM employee;

查询结果如下，将所有的数据进行相加
在这里插入图片描述

与窗口大小（frame_clause）相结合的查询

SELECT  
dname,
ename,
salary,
SUM(salary) 
over(PARTITION BY dname 
ORDER BY hiredate  
ROWS BETWEEN unbounded preceding AND current ROW) AS c1 
-- 表示从当前行开始到上面所有salary数据的和
FROM employee;

select  
dname,
ename,
salary,
sum(salary) 
over(partition by dname order by hiredate   
rows between 3 preceding and current row) as c1 
-- 表示从当前行开始和当前行上的3条salary数据的和
from employee;

表示当前行加上上三行和下一行的数据表示

ROWS BETWEEN 3 preceding AND 1 following

表示当前行和当前行下的所有行的数据和

rows between current row and unbounded following

分布函数

CUME_DIST

  • 用途：分组内小于、等于当前rank值的行数 / 分组内总行数

  • 应用场景：查询小于等于当前薪资（salary）的比例

  • 没有partition语句 所有的数据位于一组

SELECT  
 dname,
 ename,
 salary,
 cume_dist() over(PARTITION BY dname ORDER BY salary) AS count
FROM employee;

查询结果如下
在这里插入图片描述

我们以salary 进行排序，则该数据的计算：

  • 在研发部中，第一行，小于3000的只有本身，所以count=1/6=0.166666…
  • 第二行，小于等于4000的有3个人，所以count=3/6=0.5
  • …

PERCENT_RANK

  • 用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数

  • 应用场景：不常用

SELECT dname,ename,salary,
percent_rank() over(
	PARTITION BY dname
	ORDER BY salary 

) COUNT
FROM employee

查询结果为
在这里插入图片描述
我们以salary 进行排序，则该数据的计算：

  • 在研发部中，第一行，count=(1-1)/(6-1)=0
  • 第二行,所以count=(2-1)/(6-1)=0.2
  • …

由于这个使用的是rank()方式排序，rank()排序的特点为数据相同情况下排名相同，二第三行和第二行的salary数据相同，所以序号也相同，计算结果也相同

前后函数

  • 用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值

  • 应用场景：查询前1名同学的成绩和当前同学成绩的差值

lag的用法

lag() 的括号中可以填字段，行数和默认值

  • 字段：表示以哪个字段进行输入
  • 行数：表示填入前几行的数据
  • 默认值：表示前几行无数据时，该行的值为null，但我们不想让他为null时，所填充的默认值

填充hiredate的前两行数据

SELECT dname,ename,hiredate,
lag(hiredate,2) over(
	PARTITION BY dname
	ORDER BY hiredate
) TIME FROM employee

查询结果
在这里插入图片描述
填入默认值 2001-1-1

SELECT dname,ename,hiredate,
lag(hiredate,2,'2001-1-1') over(
	PARTITION BY dname
	ORDER BY hiredate
) TIME FROM employee

查询结果
在这里插入图片描述
lead() 和lag() 用法一样，只不过lead() 是向下取数，也没必要再说了

头尾函数

  • 用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值

  • 应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

-- 截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资
SELECT
  dname,
  ename,
  hiredate,
  salary,
  first_value(salary) over(PARTITION BY dname ORDER BY hiredate) AS FIRST,
  last_value(salary) over(PARTITION BY dname ORDER BY  hiredate) AS LAST 
FROM  employee;

查询结果
在这里插入图片描述

其他函数

NTH_VALUE(expr,n)

  • 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
  • 应用场景：截止到当前薪资，显示每个员工的薪资中排名第3的薪资
-- 截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资
SELECT 
dname,ename,salary,
nth_value(salary,3) over(PARTITION BY dname ORDER BY salary) COUNT
FROM employee

查询结果
在这里插入图片描述
注意：我们选择第几个数据，就从第几个数据开始有值，如过我们选取的数据与前面的有重复的，则在重复的地方也会有值。

ntile()

  • 用途：将分区中的有序数据分为n个等级，记录等级数
  • 应用场景：将每个部门员工按照入职日期分成3组
-- 根据入职日期将每个部门的员工分成3组
SELECT 
  dname,
  ename,
  hiredate,
  salary,
ntile(3) over(PARTITION BY dname ORDER BY  hiredate  ) AS rn 
FROM employee;

查询结果
在这里插入图片描述
每组6个人，分成3组，刚好为11，22，33

那么分成四组情况
在这里插入图片描述
总会保持一种平衡状态

奇见疯
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL之函数
qq_38523616的博客
08-28 597
聚合函数 统计指定列其值不为NULL的记录行数 SELECT COUNT (*) FROM 表名 计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算 SELECT MAX (字段名) FROM 表名; 计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算 SELECT MIN(字段名) FROM 表名; 计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0 ...
MySQL之——常用函数汇总
Sumuxi9797926的博客
10-30 2061
对平时经常用到的mysql中的函数归类了一下，希望自己持续学习，奥力给~
MySQL 4：MySQL函数
Amzmks的博客
02-11 397
对窗口中的每条记录动态应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），动态计算指定窗口内的各种聚合函数值。序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。在MySQL中，函数有很多，主要可以分为以下几类：聚合函数、数学函数、字符串函数、日期函数、控制流函数、窗口函数。用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值。用途：返回窗口中第n个expr的值。
midi接口和midi time code（MTC）
cenzmin的专栏
03-16 6657
Midi的硬件接口     MIDI 协议是一种特殊的串行通讯协议，是专门为数字音乐传输制订的，因此，它 有着自己的特点：每一条线路允许连接16个MIDI 设备，每个设备有各自的通道号，数据流是单向的，不进行应答，演奏设备只管发出MIDI命令而不检测接收设备是否准确收到，由于音乐自身的特点，接收设备判断收到正确的信号则响应，而错误的信号将被忽略－音乐演奏时，允许丢掉音符，但不能搞乱节奏。
NES APU
1bite 的专栏
07-13 1826
NES（小霸王、FC）模拟器开发中，各部分其实都不简单。而 APU 和声音系统的难点在于，我们可能缺乏相关的知识。desdev 上虽然有硬件各部分的详细说明，却不成系统，很零散。本文将围绕 “声音的产生”、“NES APU 的组成”、“播放 APU 声音” 三个知识点并结合代码的方式介绍如何实现 NES APU。.............................................
MySQL函数大全，MySQL常用函数汇总
洛卡JAVA架构师每日分享Java架构师知识
06-06 173
MySQL函数大全以及常用函数MySQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。 MySQL 包含了大量并且丰富的函数，这套 MySQL 函数大全只收集了几十个常用的，剩下的比较罕见的函数我们就不再整理了，读者可以到「MySQL 官网」查询。 可以对 MySQL 常用函数进行简单的分类，大概包括数值型函数、字符串型函数、日期时间函数、聚合函数等。 ...
Mysql函数手册.rar_MySQL函数手册_VZI_mysql 函数手册
09-15
MySQL函数手册是一部非常重要的参考资料，尤其对于数据库管理员和开发人员来说，它提供了全面而详细的MySQL数据库函数使用指南。这份手册涵盖了MySQL中各种内置函数的功能、语法以及使用示例，是学习和工作中不可或...
mysql函数
07-27
MySQL函数是数据库管理中不可或缺的一部分，它们用于执行各种计算、数据处理和逻辑操作。MySQL提供了丰富的内置函数，涵盖了数学、字符串、日期时间、条件判断等多个领域。以下将详细阐述MySQL函数的一些关键知识点...
MySQL函数
12-14
MySQL函数是预编译的SQL语句集合，它们提供了一种高效、简洁的方式来处理数据库中的数据。函数在数据库管理中扮演着重要的角色，主要体现在以下几个方面： 1. **提高代码重用性**：通过创建函数，你可以将常用的...
25.4 MySQL 函数
11-14
了解并掌握这些MySQL函数是每个数据库管理员和开发者的基础技能，通过熟练使用这些函数，可以编写出更高效、更简洁的SQL查询，从而优化数据库性能，提升工作效率。在实际工作中，还需要根据具体需求灵活运用这些函数...
mysql函数大全
11-29
MySQL 字符串函数大全 MySQL 中的字符串函数可以在数据库中进行各种字符串操作。下面是 MySQL 中的一些常用字符串函数： 1. ASCII(str) ASCII 函数返回字符串 str 的第一个字符的 ASCII 值。如果 str 是空串，则...
MySQL字段设置的varchar长度小于数据长度自动截取丢弃超出的长度而不是报错？
weixin_43831997的博客
07-23 283
当MySQL数据库设置为==严格模式（STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES）==时，如果尝试插入或更新超过设定长度的数据，将会导致错误，并阻止该事务进行。在默认情况下，或者在非严格模式（NO_STRICT）下，当插入长度超出 VARCHAR 字段定义的字符串时，MySQL会自动截断超出的部分，并发出一个警告，而不是错误。
测试开发面试题---MySQL
m0_53302824的博客
07-22 962
索引是数据库管理系统中用于提高数据检索速度的数据结构，它可以帮助快速定位数据不需要扫描全表。提高查询速度；唯一性约束，确保数据不会重复；辅助排序和分组；减少磁盘IO。
mysql定时备份
yandong634的专栏
07-25 170
注意：导出需要在数据库的my.ini文件中配置数据库用户和密码。右键【任务计划程序库】-》【创建基本任务】或【创建任务】在C盘新建文件夹【mysql-backup】和【bk】在【bk】下新建文件【backup.bat】在windows里面搜索【任务计划程序】
《MySQL DBA 修炼之道》第四章 开发进阶
最新发布
Trishia的博客
07-25 437
博主学习《MySQL DBA 修炼之道》结合自身理解的总结。主要讲解了范式、反范式、慢SQL查询、事务和锁等。
mysql中ONLY_FULL_GROUP_BY报错
zzzzzucc的博客
07-21 328
禁用会话级别的 ONLY_FULL_GROUP_BY SET SESSION sql_mode = ‘’;– 或者禁用全局级别的 ONLY_FULL_GROUP_BY SET GLOBAL sql_mode = ‘’;在 MySQL 中，sql_mode是一个服务器变量，用于定义 SQL 语句的运行模式。是sql_mode中的一个选项，它影响 SQL 查询中GROUP BY子句的行为。
python使用mysql，封装
qq_34631220的博客
07-25 261
【代码】python使用mysql，封装。
Windows版MySQL5.7解压直用（如何卸载更换位置重新安装）
xzzteach的博客
07-21 310
【代码】Windows版MySQL5.7解压直用（如何卸载更换位置重新安装）
MySQL函数详解：全面掌握基础到高级操作
MySQL函数大全提供了丰富的字符串处理、数字转换以及字符编码操作的工具，对于数据库管理和开发人员来说，掌握这些函数至关重要。以下是一些关键的函数及其详细介绍： 1. **ASCII(str)**: 这个函数用于获取字符串...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值