MySQL的函数

最新推荐文章于 2023-07-07 10:48:23 发布
最新推荐文章于 2023-07-07 10:48:23 发布
阅读量203 收藏 1
点赞数
分类专栏： 数据库 文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/JAX_fire/article/details/125097598
版权

概念：
MySQL中没了提高代码的重用性和隐藏实现细节，MySQL提供了很多函数。函数可以理解为别人封装号的模板代码

分类：

  • 聚合函数
  • 数学函数
  • 字符串函数
  • 日期函数
  • 控制流函数
  • 窗口函数

聚合函数

概念：
在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.00, '财务部'),( '赵刚', 6200.00, '财务部'),
( '刘小贝', 5700.00, '人事部'),( '王大鹏', 6700.00, '人事部'),
( '张小斐', 5200.00, '人事部'),( '刘云云', 7500.00, '销售部'),
( '刘云鹏', 7200.00, '销售部'),( '刘云鹏', 7800.00, '销售部');

-- 将所有员工的名字合并成一行
SELECT GROUP_CONCAT(emp_name) FROM emp;

-- 指定分隔符合并
SELECT GROUP_CONCAT(emp_name SEPARATOR ';') FROM emp;


-- 指定排序方式和分隔符
SELECT department,GROUP_CONCAT(emp_name SEPARATOR ';')FROM emp GROUP BY department;
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的最大整数小于或等于1.5的整数：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的最大值：ELECT MAX(Price) AS MinPrice 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

例如：

 SELECT abs(-10);
 SELECT abs(10);
 
--  向上取整
 SELECT CEIL(1.1);
 SELECT CEIL(1.0);
 
--  向下取整
SELECT FLOOR(1.1);
SELECT FLOOR(1.9);

-- 取列表最大值
SELECT GREATEST(1,2,3);

-- 取列表最小值
SELECT LEAST(1,2,3);

-- 取模
SELECT MOD(5,2);

-- 取x的y次方
SELECT POWER(2,3);

-- 取随机数3
SELECT RAND();
SELECT FLOOR(RAND()*100);

-- 将小数的四舍五入取整
SELECT ROUND(3.1415);
SELECT ROUND(3.5415);

-- 将小数的四舍五入取指定位数小数
SELECT ROUND(3.5415,3);

-- 将小数直接截取到指定位数
SELECT TRUNCATE(3.1515,3);

字符串函数

函数名描述实例
CHAR_LENGTH(25)返回字符串s的字符数返回字符串RUNOOB的字符数SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString;
CHARACTER_LENGTH(s)返回字符串s的字符数返回字符串RUNOOB的字符数 SELECT CHARACTER_LENGTH(“RUNOOB”) AS LengthOfString;
CONCAT(s1,s2,…sn)字符串s1,s2等多个字符串合并为一个字符串合并多个字符串SELECT CONCAT(“SQL”,“Runoob”,“Google”,“Facebook”) AS ConcatenatedString;
CONCAT_WS(x,s1,s2…sn)同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上想，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”);
LTRIM(s)去掉字符串s开始出的空格去掉字符串RUNOOB开始出的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;
MID(s,n,len)从字符串s的n位置截取长度为len的子字符串，同SUBSTRING(s,n,len)从字符串RUNOOB中的第2个位置截取3个字符：SELECT MID(“RUNOOB”,2,3) AS ExtractString;
POSITION(s1 IN s)从字符串s中获取s1的开始位置返回字符串abc中b的位置：SELECT POSITION(‘b’ in ‘abc’)
REPLACE(s,s1,s2)将字符串s2替代字符串s中的字符串s1将字符串abc中的字符a替换为字符x：SELECT REPLACE(‘abc’)
RIGHT(s,n)返回字符串s的后n个字符返回字符串runnoob的后两个字符：SELECT RIGHT(‘runoob’,2)
RTRIM(s)去掉字符串s结尾处的空格去点字符串RUNOOB的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString;
STRCMP(s1,s2)比较字符串s1和s2，如果s1与s2相等返回0，如果s1>s2返回1，如果s1<s2返回-1比较字符串：SELECT STRCMP(“runoob”,“runoob”);
SUBSTR(s,start,length)从字符串s的start位置截取长度为length的子字符串从字符串RUNOOB中的第2个位置截取3个字符：SELECT SUBSTR(“RUNOOB”,2,3) AS ExtractString;
SUBSTRING(s,start,length)从字符串s的start位置截取长度为length的子字符串从字符串RUNOOB中的第2个位置截取3个字符：SELECT SUBSTRING(“RUNOOB”,2,3) AS ExtractString;
TRIM(s)去掉字符串s开始和结尾处的空格去掉字符串RUNOOB的首尾空格：SELECT TRIM(’ RUNOOB ') AS TrimmedString;
UCASE(s)将字符串转化为大写将字符串runnob转换为大写：SELECT UCASE(“runoob”);
UPPER(s)将字符串转化为大写将字符串runnob转换为大写：SELECT UPPER(“runoob”);
LCASE(s)将字符串s的所有字母变成小写字母字符串RUNOOB转换为小写：SELECT LCASE(‘RUNOOB’)
LOWER(s)将字符串s的所有字母变成小写字母字符串RUNOOB转换为小写：SELECT LOWER(‘RUNOOB’)

例如：

-- 获取字符串字符个数
SELECT CHAR_LENGTH('hello');
SELECT CHAR_LENGTH('你好吗');

-- length取长度，返回的单位是字节
SELECT LENGTH('hello');
SELECT LENGTH('你好吗');

-- 字符串合并
SELECT CONCAT('hello','world');

-- 指定分隔符进行字符串合并
SELECT CONCAT_WS('-','hello','world');

-- 返回字符串在列表中的位置
SELECT FIELD('aaa','aaa','bbb','ccc');

-- 去除字符串左边的空格
SELECT LTRIM('    aaaaa');
SELECT LTRIM('    aaaaa        ');
SELECT RTRIM('    aaaaa        ');
SELECT RTRIM('    aaaaa');


-- 字符串截取
SELECT MID("helloworld",2,3);

-- 获取字符串A在字符串中出现的位置
SELECT POSITION('abc' IN 'helloabcworld');
SELECT POSITION('abc' IN 'habcelloabcworld');

-- 字符串替换
SELECT REPLACE('aaahelloworld','aaa','bbb');

-- 字符串翻转
SELECT REVERSE('hello');

-- 返回字符串的后几个字符
SELECT RIGHT('hello',3);

-- 字符串比较
SELECT STRCMP("hello","world");

-- 字符串截取
SELECT SUBSTR('hello',2,3);
SELECT SUBSTRING('hello',2,3);

-- 将小写转大写
SELECT UCASE("helloWorld");
SELECT UPPER("helloWorld");

-- 将大写转小写
SELECT LCASE("HELLOWORLD");
SELECT LOWER("HELLOWORLD");

日期函数

函数名描述实例
UNIX_TIMESTAMP()返回从1970-01-01 00:00:00到当前毫秒值select UNIX_TIMESTAMP()->1632729059
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’);(1598079966,‘%Y-%m-%d %H:%i:%s’); ->2020-02-22 15-06-06
CURDATE()返回当前日期SELECT CURDATE(); ->2018-09-19
CURRENT_DATE()返回当前日期SELECT CURRENT_DATE();->2018-09-19
CURRENT_TIME返回当前时间SELECT CURRENT_TIME();
CURTIME()返回当前时间SELECT CURTIME();
CURRENT_TIMESTAMP()返回当前日期和时间SELCET CURRENT_TIMESTAMP()
DATE()从日期或日期时间表达式中提取日期值SELECT DATE(“2017-06-15”);
DATEDIFF(d1,d2)计算日期d1->d2之间相隔的天数SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’)
TIMEDIFF(time1,time2)计算时间差值SELECT TIMEDIFF(‘13:10:11’,‘13:10:10’);
DATE_FORMAT(d,f)按表达式f的要求显示日期dSELECT DATE_FORMAT(‘2011-11-11 11:11:11’,‘%M%d%Y’);
STR_TO_DATE(string,format_mask)将字符串转化为日期SELCET STR_TO_DATE(“August 10 2017”,“%M%d%Y”);
DATE_SUB(date,INTERVAL expr type)函数从日期减去指定的时间间隔Orders表中OrderDate字段减去2天：SELECT Orderid,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
ADDDATE/DATE_ADD(d,INTERVAL expr type)计算起始日期d加上一个时间段后的日期，type值可以是：1.MICROSECOND 2.SECOND 3.MINUTE 4.HOUR 5.DAY 6.WEEK 7.MONTH 8.QUARTER 9.YEAR 10.DAY_MINUTE 11.DAY_HOUR 12.YEAR_MONTHSELECT DATE_ADD(“2017-06-15”，IN TERVAL 10 DAY) ; SELECT DATE_ADD(“2017-06-15 09:34:21”,INTERVAL -3 HOUR); SELECT DATE_ADD(“2017-06-15 09:34:24”,INTERVAL )
EXTRACT(type FROM d)从日期d中获取指定的值，type指定返回的值。type可取值与上述一致SELECT EXTRACT(MINUTER FROM ‘2011-11-11 11:11:11’)
LAST_DAY(d)返回给给定日期的那一月份的最后一天SELECT LAST_DAY(‘2017-06-20’);
MAKEDATE(year,day-ok-year)基于给定参数年份year和所在年中的天数序号day-of-year返回一个日期SELECT MAKEDATE(2017,3)
YEAR(d)返回年份SELECT YEAR(‘2017-06-15’);
MONTH(d)返回日期d中的月份值，1到12SELECT MONTH(‘2011-11-11 11:11:11’);
DAY(d)返回日期d的日期部分SELECT DAY(‘2017-06-15’);
HOUR(t)返回t中的小时值SELECT HOUR(1:2:3);
MINUTE(t)返回t中的分钟值SELECT MINUTE(‘1:2:3’)
SECOND(t)返回t中的秒钟值SELECRT SECOND(‘1:2:3’)
QUARTER(d)返回日期d是第几个季节，返回1到4SELECT QUARTER(‘2011-11-11 11:11:11’)
MONTHNAME(d)返回日期当中的月份名称，如NovemberSELECT MONTHNAME(‘2022-12-22 22:22:22’);
DAYNAME(d)返回日期d是星期几，如Monday，TuesdaySELECT DAYNAME(‘2022-12-22 22:22:22’);
DAYOFMONTH(d)计算日期d是本月的第几天SELECT DAYOF MONTH(‘2022-12-22 22:22:22’);
DAYOFWEEK(d)日期d见天是星期几，1星期日，2星期一，以此类推SELECT DAYOFWEEK(‘2022-12-22 22:22:22’);
DAYOFYEAR(d)计算日期d是本年的第几天SELECT DAYOFYEAR(‘2022-12-22 22:22:22’);
WEEK(d)计算日期d是本年的第几个星期，范围是0到53SELECT WEEK(‘2022-12-22 22:22:22’);
WEEKDAY(d)日期d是星期几，0表示星期一，1表示星期二SELECT WEEKDAY(‘2022-06-17’);
WEEKOFYEAY(d)就散日期d是本年的第几个星期，范围是0到53SELECT WEEKOFYEAR(‘2022-06-17 18:14’);
YEARWEEK(date,mode)返回年份及第几周（0到53），mode中0表示周天，1表示周一，以及类推SELECT YEARWEEK(‘2022-06-17’);
NOW()返回当前日期和时间SELECT NOW()

例如：


-- 获取的时间戳（毫秒值）
SELECT UNIX_TIMESTAMP();


-- 将一个日期字符串转化为毫秒值
SELECT UNIX_TIMESTAMP('2022-06-17 17:09:00');

-- 将时间戳毫秒值转化为指定格式的日期
SELECT FROM_UNIXTIME(1655456940,'%Y-%m-%d %H:%i:%s');

-- 获取当前的年月日
SELECT CURDATE();
SELECT CURRENT_DATE();

-- 获取当前时间的时分秒
SELECT CURRENT_TIME;
SELECT CURTIME();

-- 获取年月日和时分秒
SELECT CURRENT_TIMESTAMP();

-- 从日期字符串中获取年月日
SELECT DATE('2022-12-10 12:35:55');

-- 获取日期之间的差值
SELECT DATEDIFF('2022-06-17','2021-12-01');

-- 获取事件的差值（秒级）
SELECT TIMEDIFF('17:34:00','12:12:34');

-- 日期格式化
SELECT DATE_FORMAT('2022-06-17 17:35:0','%Y.%m.%d %H:%i:%s');

-- 将字符串转为日期
SELECT STR_TO_DATE('2022-06-17 11:11:11','%Y-%m-%d %H:%I:%s');

-- 将日期进行减法
SELECT DATE_SUB('2022-06-17',INTERVAL 2 DAY);
SELECT DATE_SUB('2022-06-17',INTERVAL 2 MONTH);


-- 将日期进行加法
SELECT DATE_ADD('2022-06-17',INTERVAL 2 DAY);
SELECT DATE_ADD('2022-06-17',INTERVAL 2 MONTH);

-- 从日期中获取小时
SELECT EXTRACT(HOUR FROM '2022-6-17 17:50:00');
SELECT EXTRACT(DAY FROM '2022-6-17 17:50:00');
SELECT EXTRACT(MONTH FROM '2022-6-17 17:50:00');

-- 获取给定日期的最后一天
SELECT LAST_DAY('2022-02-1');

-- 获取指定年份和天数的日期
SELECT MAKEDATE('2022',100);

-- 根据日期获取年月日，时分秒
SELECT YEAR('2021-12-13 11:56:00');
SELECT MONTH('2021-12-13 11:56:00');
SELECT MINUTE('2021-12-13 11:56:00');
SELECT QUARTER('2021-12-13 11:56:00');

-- 根据日期获取信息
SELECT MONTHNAME('2022-12-22 22:22:22');
SELECT DAYNAME('2022-12-22 22:22:22');
SELECT DAYOFMONTH('2022-12-22 22:22:22');
SELECT DAYOFWEEK('2022-12-22 22:22:22');
SELECT DAYOFYEAR('2022-12-22 22:22:22');

SELECT WEEK('2022-6-17');

SELECT YEARWEEK('2022-06-17');

SELECT NOW();

控制流函数

if逻辑判断语句

函数名描述实例
IF(expr,v1,v2)如果表达式expr成立，返回结果v1；否则，返回结果v2。SELECT IF(1>0,‘正确’,‘错误’)
IFNULL(v1,v2)如果v1的值不为NULL,则返回v1，否则返回v2。SELECT IFNULL(nulll,‘Hello Word’)
ISNULL(expression)判断表达式是否为NULLSELECT ISNULL(NULL);
NULLIF(expr1,expr2)比较两个字符串，如果字符串expr1与expr2相等，则返回NULL，否则返回expr1SELCT NULLIF(25,25);

例如：


SELECT IF(5>3,'大于','小于');

SELECT IFNULL(5,0);
SELECT IFNULL(NULL,0);

SELECT ISNULL(5);
SELECT ISNULL(NULL);

SELECT NULLIF(12,12);
SELECT NULLIF(12,20);

case when 语句

函数名描述实例
CASE expression WHEN condition1 THEN result1 … WHEN conditionN THEN resultN ELSE result ENDCASE表示函数开始，END表示函数结束。如果condition1成立，则返回result1，…，当全部不成立则返回result，而当有一个成立后，后面就不执行了。SELECT CASE 100 WHEN 50 THEN ‘TOM’ WHEN 100 THEN 'MARY ELSE ‘TIM’ END;

例如：

-- CASE WEHN语句

SELECT
CASE 1
	WHEN 1 THEN
		'你好'
	WHEN 2 THEN
	'hello'
	WHEN 5 THEN
	'正确'
	ELSE
		'其他'
END;

窗口函数(MySQL 8.0以下版本可以不看)

概念：

  • MySQL 8.0 新增窗口函数，窗口函数又被称为开窗函数，与Oracle函数类似，属于MySQL的一大特点。
  • 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变函数。

方法：WINDOW_FUNCTION(expr) OVER(
OARTITION BY …
ORDERR 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选项用于在当前分区内指定一个计算窗口，也就是一个与当前相关的数据子集

序号函数

序号函数有三个: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 DESC) AS rn1
RANK() OVER(PARTITION BY dname ORDER BY salary DESC) AS rn2
DENSE_RANK() OVER (PARTITION BY dname ORDER BY salary DESC) AS rn3
FROM employee;

-- 求出每个部门薪资排在前三名的员工-分组求TOPN
SELECT * FROM(
SELECT
dname,
ename,
salary,
DENSE_RANK() OVER (PARTITION BY dname ORDER BY salary DESC) AS rn
FROM employee
)t
WHERE t.rn <= 3

-- 对所有员工进行全部排序（不分组）
SELECT
dname,
ename,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rn
FROM employee;

开窗聚合函数-SUM,AVG,MIN,MAX

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

例如：

SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION  BY dname ORDER BY hiredate) AS c1
FROM employee;

SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (ORDER BY hiredate) AS c1
FROM employee;

SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION  BY dname ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c1
FROM employee;

SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION  BY dname ORDER BY hiredate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS c1
FROM employee;

SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION  BY dname ORDER BY hiredate ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS c1
FROM employee;

SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION  BY dname ORDER BY hiredate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS c1
FROM employee;

分布函数-CUME_DIST和PERCENT_RANK

概念：
CUME_DIST

  • 用途：分组内小于、等于当前rank值的行数/分组内总行数
  • 应用场景：查询小于等于当前薪资（salary）的比例

PERCENT_RANK

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

例如：

SELECT
dname,
ename,
salary,
-- 3/12=1/4=0.25
CUME_DIST() OVER (ORDER BY salary) AS rn1,
-- 1/6=0.1667
CUME_DIST() OVER (PARTITION  BY dname ORDER BY salary) AS rn2
FROM employee;

SELECT
dname,
ename,
salary,
RANK() OVER (PARTITION  BY dname ORDER BY salary DESC) AS rn,
-- 第一行：(1-1)/(6-1)=0 第二行与第一行相同
-- 第三行：（3-1）/（6-1）=0.4
PERCENT_RANK() OVER (PARTITION  BY dname ORDER BY salary DESC) AS rn2
FROM employee;

前后函数-LAG和LEAD

概念：

  • 用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
  • 应用场景：查询前1名同学的成绩和当前同学成绩的差值

例如：

SELECT
dname,
ename,
salary,
LAG(hirdate,1,'2000-01-01') OVER (PARTITION  BY dname ORDER BY hirdate) AS time1,
LAG(hirdate,2) OVER (PARTITION  BY dname ORDER BY hirdate) AS time2
FROM employee;

头尾函数-FIRST_VALUE和LAST_VALUE

概念：

  • 用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
  • 场景：截止到当前，按照日期排序查询第一个入职和最后一个入职员工的薪资

例如：

SELECT
dname,
ename,
salary,
hiredate,
FIRST_VALUE(salary) OVER (PARTITION  BY dname ORDER BY hirdate) AS first,
LAST_VALUE(salary) OVER (PARTITION  BY dname ORDER BY hirdate) AS last
FROM employee;

其他函数-NTH_VALUE(expr,n)、NTILE(n)

概念：

NTH_VALUE(expr,n)

  • 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
  • 应用场景：截止到当前薪资，显示每个员工的薪资中排名第二或第三的薪资

NTLE(n)

  • 用途：将分区中的有序数据分为n个等级，记录等级数
  • 应用场景：将每个部门员工按照入职日期分为3个组

例如：

SELECT
dname,
ename,
salary,
hiredate,
NTH_VALUE(salary,2) OVER (PARTITION  BY dname ORDER BY hirdate) AS first_salary,
NTH_VALUE(salary,3) OVER (PARTITION  BY dname ORDER BY hirdate) AS second_salary
FROM employee;

SELECT
dname,
ename,
salary,
hiredate,
NTILE(3) OVER (PARTITION  BY dname ORDER BY hirdate) AS nt
FROM employee;
火眼猊
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
炸裂！MySQL 82 张图带你飞
weixin_70730532的博客
06-14 763
这篇文章我们来了解一下 MySQL 中的高级内容。其他文章：138 张图带你 MySQL 入门47 张图带你 MySQL 进阶！！！本文思维导图如下。我们知道，MyISAM 和 MEMORY 存储引擎支持，InnoDB 存储引擎支持，BDB 存储引擎支持。各个锁定级别的特点如下页级锁：销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般表级锁：表级锁是对整张表进行加锁，MyISAM 和 MEMORY 主要支持表级锁，表级锁加锁快，不会出现死锁，锁的粒度比较粗，并发度最低行级锁：
齐全且实用的MySQL函数使用大全
热门推荐
Sheenky的博客
06-06 2万+
目录一、MySQL函数介绍二、MySQL函数分类（一）单行函数①字符串函数②数学函数③日期函数④流程控制函数⑤系统信息函数⑥其他函数（二）聚合函数三、函数使用示例（一）字符函数①length(str)函数②concat(str1,str2,...)函数③upper(str)、lower(str)函数④substr(str,start,len)函数⑤instr(str,要查找的子串)函数③trim(str)函数⑧lpad(str,len,填充字符)、rpad(str,len,填充字符)函数⑨replace(s
mysql炸裂函数（lateral view是MySQL 8.0及以上版本支持）
qq_36332184的博客
02-17 3979
我们有一张表user_like_eat记录了用户喜欢吃的东西, select distinct like from user_like_eat;这个时候我们可以使用炸裂函数lateral view explode(split(字段,','))变成如下表是否好统计呢。
MySQL实现数据炸裂拆分(类似Hive的explode函数的拆分数组功能)
最新发布
wt334502157的博客
07-07 5218
在Hive中，"explode"函数用于将数组类型的列拆分为多行，以便对数组中的每个元素进行处理。然而，在MySQL中，并没有直接的类似功能。但是，我们可以使用一些技巧来模拟这个功能，实现在MySQL中拆分数组并进行查询的操作。本文将介绍如何在MySQL中实现类似Hive的"explode"函数的拆分数组功能。 ​ 场景模拟：假设我们有一个名为`wow_info`的表，其中包含一个包含竖线分隔的数字列表的列`tianfu`，我们希望将每一种天赋拆分为多行进行查询。
炸裂函数，MySQL 将数值2变为1并且展示为两行
feisanking的博客
09-06 1829
炸裂函数，MySQL 将数值2变为1并且展示为两行
mysql炸包_炸裂！MySQL 82 张图带你飞
weixin_29215969的博客
01-28 217
本文思维导图如下。image事务控制和锁定语句我们知道，MyISAM 和 MEMORY 存储引擎支持表级锁定(table-level locking)，InnoDB 存储引擎支持行级锁定(row-level locking)，BDB 存储引擎支持页级锁定(page-level locking)。各个锁定级别的特点如下页级锁：销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并...
Mysql函数手册.rar_MySQL函数手册_VZI_mysql 函数手册
09-15
MySQL函数手册是一部非常重要的参考资料，尤其对于数据库管理员和开发人员来说，它提供了全面而详细的MySQL数据库函数使用指南。这份手册涵盖了MySQL中各种内置函数的功能、语法以及使用示例，是学习和工作中不可或...
mysql函数
07-27
MySQL函数是数据库管理中不可或缺的一部分，它们用于执行各种计算、数据处理和逻辑操作。MySQL提供了丰富的内置函数，涵盖了数学、字符串、日期时间、条件判断等多个领域。以下将详细阐述MySQL函数的一些关键知识点...
MySQL函数
12-14
MySQL函数是预编译的SQL语句集合，它们提供了一种高效、简洁的方式来处理数据库中的数据。函数在数据库管理中扮演着重要的角色，主要体现在以下几个方面： 1. **提高代码重用性**：通过创建函数，你可以将常用的...
MySQL 函数索引的优化方案
01-19
很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。 1、 MySQL5.7 MySQL5.7版本中不...
mysql 高级分组函数,炸裂！82张图进阶MySQL的高级玩法！ - MySQL - dbaplus社群：围绕Data、Blockchain、AiOps的企业级专业社群。技术大咖、原创干货，每天精品...
weixin_34609222的博客
03-23 636
SQL Mode 可以解决下面这几种问题通过设置 SQL Mode，可以完成不同严格程度的数据校验，有效保障数据的准确性。设置 SQL Mode 为ANSI模式，来保证大多数 SQL 符合标准的 SQL 语法，这样应用在不同数据库的迁移中，不需要对 SQL 进行较大的改变数据在不同数据库的迁移中，通过改变 SQL Mode 能够更方便的进行迁移。下面我们就通过示例来演示一下 SQL Mode 用法...
mysql 几个好用的函数
yjw1007020425的博客
10-28 629
1.concat_group：超级好用，配合groupby使用 功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。 语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] ) 说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order ...
mysql分裂函数函数_分裂函数等价于T-SQL？
weixin_33542589的博客
01-28 364
这里有一些过时的解决方案：/*Splitsstringintopartsdelimiteredwithspecifiedcharacter.*/CREATEFUNCTION[dbo].[SDF_SplitString](@sStringnvarchar(2048),@cDelimiternchar(1))RETURNS@tPartsTABLE(partnvarcha...
mysql期末考试试卷_MySQL数据库考试试题及答案
weixin_39626237的博客
12-21 1852
精品文档.专业：移动通信科目：MySQL数据库一、单项选择题1．以下聚合函数求数据总和的是()A．MAXB．SUMC．COUNTD．AVG答案：B2．可以用()来声明游标A．CREATECURSORB．ALTERCURSORC．SETCURSORD．DECLARECURSOR答案：D3．SELECT语句的完整语法较复杂，但至少包括的部分是()A．仅SELECTB．SELECT，FROMC．...
「Hive」Hive与MySQL技术差异点（二）：HQL查询语句
满眼凄迷i
07-03 1447
主要内容 1.HQL查询语句与MySQL执行顺序的区别； 2.Hive复合类型的数据查询、正则查询以及Hive独有的排序方式； 3.Hive的表关联语法； 4.Hive子查询的限制； 5.Hive开窗函数包含偏移函数、统计函数和排序函数； 6.Hive抽样查询包含随机抽样、数据块抽样和分桶抽样； 7.Hive调优分为环境调优和HQL语句调优； 8.总结与explode函数和lateral 虚拟表用法。 9.regexp_extract提取函数用法 10.json数据解析get_json_object函数用法
炸裂函数explode
weixin_30375427的博客
05-14 1450
lateral viem 表的关联只能和explode使用 hive> select explode(info) from t_map where id=1; hive> create table watch(id int,name string) row format delimited fields terminated by " ";vim movie...
Hive之爆炸函数
张鑫的博客
08-13 2万+
本部分主要讲述了列转行函数，是最最最最常用的函数，爆炸函数 1、函数说明 1）explode(col)： explode 函数是UFTF函数，将hive一列中复杂的array或者map结构拆分成多行。Explode函数是不允许在select再有其他字段, explode(ARRAY)  列表中的每个元素生成一行 explode(MAP)     map中每个key-value对，生成一...
炸裂函数的使用
sunlang的博客
06-09 2290
explode(col) lateral view 业务场景： 一个工单被多个处理人处理过，那么这个工单会被算作每个人处理过的工单，进行每个人处理的工单量的topN时候，将处理人炸裂然后按人分组统计
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

火眼猊

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值