十.MySQL函数

1.概述

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

2.分类

(1).聚合函数

(2).数学函数

(3).字符串函数

(4).日期函数

(5).控制流函数

(6).窗口函数

3.MySQL函数-聚合函数

(1).概述

在MySQL中，聚合函数主要由: count, sum, min, max, avg group_concat()

group_concat函数作用实现行合并

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

(2).格式

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

说明:
[1].使用distinct可以排除重复值

​ [2].如果需要对结果中的值进行排序，可以使用order by子句

​ [3].separator是一个字符串值，默认为逗号

(3).示例

-- 创建表
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, '财务部');
insert into emp(emp_name, salary, department) values ('王飞飞', 5800, '财务部');
insert into emp(emp_name, salary, department) values ('赵刚', 6200, '财务部');
insert into emp(emp_name, salary, department) values ('刘小贝', 5700, '人事部');
insert into emp(emp_name, salary, department) values ('王大鹏', 6700, '人事部');
insert into emp(emp_name, salary, department) values ('张小斐', 5200, '人事部');
insert into emp(emp_name, salary, department) values ('刘云云', 7500, '销售部');
insert into emp(emp_name, salary, department) values ('刘云鹏', 7200, '销售部');
insert into emp(emp_name, salary, department) values ('刘云鹏', 7800, '销售部');

-- 1.将所有员工的名字合并成一行
select group_concat(emp_name) from emp;

-- 2.指定分隔符合并
select group_concat(emp_name separator ';') from emp;

-- 3.指定排序方式和分隔符
select department, group_concat(emp_name separator ';') from emp group by department;
select department, group_concat(emp_name order by salary separator ';') from emp group by department;

4.MySQL函数-数学函数

(1).分类

函数名	描述	示例
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, …)	返回列表中的最大值	返回以下数字列表中的最大值:select GREATEST(3, 12, 34, 8, 25); – 返回34返回以下字符串列表中的最大值:select GREATEST(‘Geogle’, ‘Runoob’, ‘Apple’); – 返回Runoob
LEAST(expr1, expr2, …)	返回列表中的最小值	返回以下数字列表中的最小值:select LEAST(3, 12, 34, 8, 25); – 返回3返回以下字符串列表中的最小值:select LEAST(‘Geogle’, ‘Runoob’, ‘Apple’); – 返回Apple
MAX(expression)	返回字段expression中的最大值	返回数据表Products中字段Price的最大值:select MAX(Price) as LargestPrice from Products
MIN(expression)	返回字段expression中的最小值	返回数据表Products中字段Price的最小值:select MIN(Price) as LargestPrice 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.3089158770711309
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

(2).示例

-- abs 求绝对值
select abs(-10); -- 10
select abs(10); -- 10
select abs(表示式或者字段) from 表

-- ceil 向上取整
select CEIL(1.1); -- 2
select CEIL(1.0); -- 1

-- floor 向下取整
select floor(1.1); -- 1
select floor(1.0); -- 1

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

-- 取列表最小值
select least(1, 2, 3); -- 1

-- 取模
select mod(5, 2); -- 1

-- 乘方
select pow(2, 3); --8

-- 取随机值
select rand(); -- 0.125484 
-- 获取0-100随机数
select floor(rand() * 100); -- 14
 
-- 将小数的四舍五入取整
select round(3.5415) -- 4
-- 对小数的某一位取整
select round(3.5415, 3); -- 3.542

-- 将小数直接截取到指定位数
select truncate(3.1415, 3); -- 3.141

5.MySQL的函数 - 字符串函数

(1).分类

函数	描述	实例
CHAR_LENGTH(s)	返回字符串s的字符数	返回字符串RUNOOB的字符数:select CHAR_LENGTH(‘RUNOOB’) as lengOfString; – 6
CHARACTER_LENGTH(s)	返回字符串s的字符数	返回字符串RUNOOB的字符数:select CHARACTER_LENGTH(‘RUNOOB’) as lengOfString; – 6
CONCAT(s1, s2,…)	字符串s1,s2等多个字符串合并为一个字符串	合并多个字符串:select CONCAT(‘a’, ‘b’, ‘c’); – abc
CONCAT_WS(x, s1, s2, …)	同concat(s1, s2,…)函数，但是每个字符之间要加上x, x可以是分隔符	合并多个字符串，并添加分隔符：select CONCAT_WS('', ‘a’, ‘b’, ‘c’); – ab*c
FLELD(s,s1,s2,…)	返回第一个字符串在字符串列表(s1, s2, …)中的位置	返回字符串abc在列表值中的位置:select FIELD(‘abc’, ‘abc’, ‘bcd’, ‘edf’); – 1
LTRIM(s)	去掉字符串s开始处的空格	去掉字符串RUNOOB开始处的空格:select LTRIM(’ RUNOOB’); – RUNOOB
MID(s, n, len)	从字符串s的n位置截取长度为len的子字符串，同SUBSTRING(s, n, len)	从字符串RUNOOB中的第2个位置截取3个字符:select mid(‘runoob’, 2, 3); – 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结尾处的空格	去掉字符串aaaaa 的末尾空格:select RTRIM('aaaaa '); – aaaaa
STRCMP(s1, s2)	比较字符串s1和s2， 如果s1与s2相等返回0. 如果s1>s2,返回1. 如果s1	比较字符串:select STRCMP(‘abc’, ‘abc’); – 0
SUBSTR(s, start, length)	从字符串s的start位置截取长度为length的子字符串	从字符串RUNOOB中的第2个位置截取3个字符:select substr(‘RUNOOB’, 2, 3); – UNO
SUBSTRING(s, start, length)	从字符串s的start位置截取长度为length的子字符串	从字符串RUNOOB中的第2个位置截取3个字符:select substring(‘RUNOOB’, 2, 3); – UNO
TRIM(s)	去掉字符串s开始和结尾处的空格	去掉字符串RUNOOB的首尾空格:select trim(’ RUNOOB '); – RUNOOB
UCASE(s)	将字符串转换为大写	将字符串runoob转换为大写:select ucase(‘runoob’); – RUNOOB
UPPER(s)	将字符串转换为大写	将字符串runoob转换为大写:select upper’runoob’); – RUNOOB
LCASE(s)	将字符串转换为小写	将字符串RUNOOB转换为小写:select ucase(‘RUNOOB’); – runoob
LOWER(s)	将字符串转换为小写	将字符串RUNOOB转换为小写:select ucase(‘RUNOOB’); – runoob

(2).示例

-- 1.获取字符串字符个数
select char_length('hello'); -- 5
select char_length('你好吗'); -- 3

-- 2.获取字符串字节长度
-- length取长度，返回的单位是字节
select length('hello'); -- 5
select length('你好吗'); -- 9

-- 3.字符串合并
select concat('hello', 'wrold'); -- helloworld
select concat(c1, c2) from tablename;

-- 4.指定分隔符进行字符串合并
select concat_ws('-', 'hello', 'world'); -- hello-world

-- 5.返回字符串在列表中第一次出现的位置
select field('aaa', 'aaa', 'bbb', 'ccc'); -- 1

-- 6.去除字符串空格
-- 去除字符串左边空格
select ltrim('    aaa'); -- aaa
-- 去除字符串右边空格
select rtrim('aaa   '); -- aaa
-- 去除左右边空格
select trim('  aaa  '); -- aaa

-- 7.字符串截取
select mid('helloworld', 2, 3); -- ell

-- 8.获取字符串abc在字符串habcelloabcworld中出现的位置
select position('abc' in 'habcelloabcworld'); -- 2

-- 9.字符串替换
select replace('helloaaaworld', 'aaa', 'bbb'); -- hellobbbworld

-- 10.字符串反转
select reverse('hello'); -- olleh

-- 11.返回字符串的后几个字符
select right('hello', 3); -- llo

-- 12.字符串比较
select strcmp('hello', 'world'); -- -1

-- 13.字符串截取
-- 从第二个字符开始截取，截取三个字符
select substr('hello', 2, 3); -- llo
-- 从第二个字符开始截取，截取三个字符
select substring('hello', 2, 3); -- llo

-- 14.将小写转大写
select ucase('helloworld'); -- HELLOWORLD
select upper('helloworld'); -- HELLOWORLD

-- 15.将大写转成小写
select lcase('HELLOWORLD'); -- helloworld
select lower('HELLOWORLD'); -- helloworld

6.MySQL函数–日期函数

(1).分类

函数名	描述	实例
UNIX_TIMESTAMP()	返回从1970-01-01 00:00:00到当前毫秒数	select UNIX_TIMESTAMP(); – 1642986095
UNIX_TIMESTAMP(DATE_STRING)	将指定日期转为毫秒值时间戳	select UNIX_TIMESTAMP(‘2020-12-14 12:15:14’); – 1607919314
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(); – 2022-01-24
CURRENT_DATE()	返回当前日期	select CURRENT_DATE(); – 2022-01-24
CURRENT_TIME	返回当前时间	select current_time(); – 12:45:08
CURTIME()	返回当前时间	select CURTIME(); – 12:46:06
CURRENT_TIMESTAMP()	返回当前日期和时间	select CURRENT_TIMESTAMP(); – 2022-01-24 12:46:54
DATE()	从日期或日期时间表达式中提取日期值	select date(‘2020-12-14’); – 2020-12-14
DATEDIFF(d1, d2)	计算日期d1>d2之间相隔的天数	select DATEDIFF(‘2022-1-1’, ‘2022-2-1’); – -31
TIMEDIFF(time1, time2)	计算时间差值	select TIMEDIFF(‘13:01:12’, ‘13:02:12’); – -00:01:00
DATE_FORMAT(d, f)	按表达式f的要求显示日期d	select date_format(‘2020-12-14 15:24:14’, ‘%Y-%m-%d’); – 2020-12-14
STR_TO_DATE(string, format_mask)	按字符串转变为日期	select STR_TO_DATE(‘August 10 2020’, ‘%M %d %Y’); – 2020-08-10
DATE_SUB(date, INTERVAL expr type)	函数从日期减去指定的时间间隔	给定日期减去2天:select DATE_SUB(‘2021-12-14 10:10:10’, INTERVAL 2 DAY); – 2021-12-12 10:10:10
ADDDATE/DATE_ADD(d, INTERVAL expr type)	计算起始日期d加上一个时间段后的日期，type值可以是:MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARDAY_MINUTEDAY_HOURYEAR_MONTH	select date_add(‘2017-06-15’, INTERVAL 10 DAY); – 2017-06-25select date_add(‘2017-06-15 09:34:21’, INTERVAL 15 MINUTE); – 2017-06-15 09:49:21select date_add(‘2017-06-15 09:34:21’, INTERVAL -3 HOUR); – 2017-06-15 06:34:21
EXTRACT(type FROM d)	从日期d中获取指定的值，type指定返回的值。type可取值为:MICROSECONDSECONDMINUTEHOUR…	select EXTRACT(MINUTE from ‘2021-12-14 15:16:17’); – 16
LAST_DAY(d)	返回给给定日期的那一月份的最后一天	select LAST_DAY(‘2022-02-10’); – 2022-02-28
MAKEDATE(year, day-of-year)	基于给定参数年份year和所在年中的天数序号 day-of-year返回一个日期	select MAKEDATE(2017, 32); – 2017-02-01
YEAR(d)	返回年份	select year(‘2017-06-15 12:12:12’); – 2017
MONTH(d)	返回日期d中的月份值，1到12	select month(‘2017-06-15 12:12:12’); – 6
DAY(d)	返回日期值d的日期部分	select day(‘2017-06-15 12:12:12’); – 15
HOUR(t)	返回t的小时值	select hour(‘14:12:16’); – 14
MINUTE(t)	返回t的分钟值	select minute(‘14:12:16’); – 12
SECOND(t)	返回t的分秒值	select second(‘14:12:16’); – 16
QUARTER(d)	返回日期d是第几季节，返回1到4	select QUARTER(‘2017-06-15’); – 2
MONTHNAME(d)	返回日期当中的月份名称, 如November	select MONTHNAME(‘2021-02-13 14:15:16’); – February
MONTH(d)	返回日期d中的月份值，1到12	select MONTH(‘2021-02-13 14:15:16’); – 2
DAYNAME(d)	返回日期d是星期几，如Monday, Tuesdays	select DAYNAME(‘2021-02-13 14:15:16’); – Saturday
DAYOFMONTH(d)	计算日期d是本月的第几天	select DAYOFMONTH(‘2021-02-13 14:15:16’); – 13
DAYOFWEEK(d)	日期d今天是星际几，1星期日，2星期一，以此类推	select DAYOFWEEK(‘2021-02-13 14:15:16’); – 7
DAYOFYEAR(d)	计算日期d是本年的第几天	select DAYOFYEAR(‘2021-02-13 14:15:16’); – 44
WEEK(d)	计算日期d是本年的第几个星期，范围是0~53	select week(‘2020-11-12 13:45:20’); – 45
WEEKDAY(d)	日期d是星期几，0表示星期一，1表示星期二	select weekday(‘2017-06-15’); – 3
WEEKOFYEAR(d)	计算日期d是本年的第几个星期，范围是0到53	select WEEKOFYEAR(‘2017-06-15’); – 24
YEARWEEK(date, mode)	返回年份及第几周,mode中0表示周日，1表示周一。以此类推	select YEARWEEK(‘2017-06-15’); – 201724
NOW()	返回当前日期和时间

其中FROM_UNXITIME格式:

格式	描述
%a	编写星期名
%b	编写月名
%c	月，数值
%d	月的天，数值(00-31)
%e	月的天， 数值(0-31)
%f	微秒
%H	小时(00 - 23)
%h	小时(01 - 12)
%I	小时(01-12)
%i	分钟, 数值(00 - 59)
%j	年的天(001 - 366)
%k	小时(0 - 23)
%l	小时(1-12)
%M	月名
%m	月，数值(00 - 12)
%p	AM 或 PM
%r	时间，12-小时(hh:mm:ss AM 或 PM)
%S	秒(00 - 59)
%s	秒(00 - 59)
%T	时间, 24-小时(hh:mm:ss)
%U	周(00-53) 星期日是一周的第一天
%u	周(00-53)星期一是一周的第一天
%V	周(01-53)星期日是一周的第一天，与%X使用
%v	周(01-53)星期一是一周的第一天，与%x使用
%W	星期名
%w	周的天(0-星期日， 6-星期六)
%X	年，其中的星期日是周的第一天，4位，与%V使用
%x	年，其中的星期一是周的第一天，4位，与%v使用
%Y	年，4位
%y	年，2位

(2).示例

-- 1.获取时间戳(毫秒值)
select UNIX_TIMESTAMP(); -- 1642986095

-- 2.将一个日期字符串转为毫秒值
select UNIX_TIMESTAMP('2021-12-11 08:08:08'); -- 1639181288

-- 3.将时间戳毫秒值转为指定格式的日期
select FROM_UNIXTIME(1598079966, '%Y-%m-%d %H:%i:%s'); -- 2020-08-22 15:06:06

-- 4.获取当前的年月日
select CURDATE(); -- 2022-01-24
select CURRENT_DATE(); -- 2022-01-24

-- 5.获取当前的时分秒
SELECT current_time(); -- 12:52:14
select curtime(); -- 12:12:14

-- 6.获取年月日和时分秒
SELECT CURRENT_TIMESTAMP(); -- 2022-01-24 12:53:59

-- 7.从日期字符串中获取年月日
select date('2022-01-01 12:16:00'); -- 2022-01-01

-- 8.获取日期之间的差值
select DATEDIFF('2022-1-1', '2022-2-1'); -- -31
select DATEDIFF(CURRENT_DATE(), '2008-08-08'); -- 4917

-- 9.获取时间的差值(秒级)
select TIMEDIFF('13:01:12', '13:02:12'); -- -00:01:00

-- 10.日期格式化
select date_format('2020-12-14 15:24:14', '%Y-%m-%d'); -- 2020-12-14
select date_format('2020-12-16 15:26:16', '%Y-%m-%d'); -- 2020-12-16

-- 11.将字符串转为日期
select STR_TO_DATE('August 10 2020', '%M %d %Y'); -- 2020-08-10

-- 12.将日期进行减法
-- 日期向前跳
select DATE_SUB('2021-12-14 10:10:10', INTERVAL 2 DAY); -- 2021-12-12 10:10:10

-- 13.将日期进行加法
-- 日期向后跳
select date_add('2017-06-15 09:34:21', INTERVAL -3 HOUR); -- 2017-06-15 06:34:21

-- 14.从日期中获取小时
select EXTRACT(hour from '2021-12-14 15:16:17'); -- 15

-- 15.获取给定日期所在月的最后一天
select last_day('2022-01-14'); -- 2022-01-31

-- 16.获取指定年份和天数的日期
select makedate('2021', 32); -- '2021-02-01'

-- 17.根据日期获取年月日，时分秒
select year('2021-02-13 14:15:16'); -- 2021
select month('2021-02-13 14:15:16'); -- 02
select minute('2021-02-13 14:15:16'); -- 15
select quarter('2021-02-13 14:15:16'); -- 1

--18.根据日期获取信息
-- 获取月份的英文
select MONTHNAME('2021-02-13 14:15:16'); -- February
-- 获取周几
select DAYNAME('2021-02-13 14:15:16'); -- Saturday
-- 获取当月的第几天
select DAYOFMONTH('2021-02-13 14:15:16'); -- 13
-- 获取星期几 1:周日 2:周一
select DAYOFWEEK('2021-02-13 14:15:16'); -- 7
-- 获取一年的第几天
select DAYOFYEAR('2021-02-13 14:15:16'); -- 44

7.MySQL函数 - 控制流程函数

(1).if逻辑判断语句

[1].分类

格式	解释	示例
IF(expr, v1, v2)	如果表达式expr成立，返回结果v1；否则，返回结果v2	select if(1 > 0, ‘正确’, ‘错误’); – 正确
IFNULL(v1, v2)	如果v1的值不为NULL, 则返回v1, 否则返回v2	select IFNULL(null, ‘hello world’); – hello world
ISNULL(expression)	判断表达式是否为NULL	select ISNULL(null); – 1
NULLIF(expr1, expr2)	比较两个字符串，如果字符串expr1与expr2相等 返回NULL，否则返回expr1	select nullif(25, 25); – NULL

[2].示例

-- 控制流函数
select if(5 > 3, '大于', '小于'); -- 大于
select *, if(score >= 85, '优秀', '及格') flag from score;

select ifnull(5, 0); -- 5
select ifnull(null, 0); -- 0
select *, ifnull(conn, 0) conn_flag from emp;

select isnull(5); -- 1
select isnull(NULL); -- 0

select nullif(12, 12); -- null
select nullif(12, 13); -- 12

(2).case when语句

[1].分类:

格式	解释	示例
case expression WHEN condtion1 THEN result1 WHEN condtion2 THEN result2 … WHEN condtionN THEN resultN ELSE resultEND	CASE表示函数开始，END表示结束。如果condition1. 则返回result1，如果condition2. 则返回result2，当全部不成立则返回result, 而当有一个成立之后，后面的就不执行了。	select case 100 when 50 then ‘tom’ when 100 then ‘mary’ else ‘tin’ end; – maryselect case when 1=2 then ‘tom’ when 2=2 then ‘mary’ else ‘tin’ end; – mary

[2].示例:

select
   case 9
	 when 1 then '1'
	 when 2 then '2'
	 when 3 then '3'
	 when 4 then '4'
	 when 5 then '5'
	 else
	   'aa' 
	 end; -- 3

8.MySQL函数-窗口函数

(1).介绍

[1].MySQL8.0新增窗口函数，窗口函数又称为开窗函数，与Oracle窗口函数类似，属于MySQL的一大特点

[2].非聚合窗口函数是相对于聚函数来说的。聚函数是对一组数据计算后返回单个值(即分组)，非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

(2).分类

(3).语法结构

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

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

[1].分区(PARTITION BY)

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

[2].分区(ORDER BY)

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

[3].以及窗口大小(frame_clause)

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

(4).序号函数

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

[1].格式

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

[2].示例

-- 创建employee表
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 rn 
from employee;
--------------------------------------------------------------------------------
select
dname,
ename,
salary,
rank() over (partition by dname order by salary desc) as rn
from employee;
---------------------------------------------------------------------------------
select
dname,
ename,
salary,
dense_rank() over (partition by dname order by salary desc) as rn
from employee;
-- 求出每个部门薪资排在前三名的员工 - 分组求TOPN
select * from(
    select
    dname,
    ename,
    salary,
    dense_rank() over (partition by dname order by salary desc) as rn
) as t where t.rn <= 3;
-- 对所有员工进行全局排序(不分组)
select
dname,
ename,
salary,
rank() over (order by salary desc) as rn
from employee;

(5).开窗聚合函数 - SUM, AVG, MIN, MAX

[1].概念

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

[2].示例

-- 开窗聚合函数
select 
dname,
ename,
salary,
sum(salary) over(PARTITION by dname order by hiredate) as pv1
from employee;

select cookieid, createtime, pv,
sum(pv) over(partition by cookieid) as pv3
from itcast_t1; -- 如果没有order by排序语句 默认把分组内的所有数据进行sum操作

(6).分布函数-CUME_DIST和PERCENT_RANK

[1].介绍-CUME_DIST()

[a].用途

分组内小于，等于当前rank值的行数/分组内总行数

[b].应用场景

查询小于等于当前薪资(salary)的比例

[c].示例

select
dname,
ename,
salary,
cume_dist() over (order by salary) as rn1, -- 没有partition语句 所有的数据位于一组
cume_dist() over (partition by dept ORDER BY salary) as rn2
from employee;

[2].介绍-PERCENT_RANK

[a].用途

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

[b].应用场景

不常用

[c].示例

select 
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn,
PERCENT_RANK() over(partition by dname order by salary desc) as rn2
from employee;

(7).前后函数-LAG和LEAD

[1].介绍:

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

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

[2].示例

-- lag用法
select 
dname,
ename,
hiredate,
salary,
lag(hiredate, 1, '2000-01-01') over(partition by dname order by hiredate) as last_1_time,
lag(hiredate, 2) over(partition by dname order by hiredate) as last_2_time
from employee;
-- lead用法
select 
dname,
ename,
hiredate,
salary,
lead(hiredate, 1, '2000-01-01') over(partition by dname order by hiredate) as last_1_time,
lead(hiredate, 2) over(partition by dname order by hiredate) as last_2_time
from employee;

(8).头尾函数-FIRST_VALUE和LAST_VALUE

[1].介绍

用途:返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr)) expr的值

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

注意:如果不指定order by, 则进行排序混乱，会出现错误

[2].示例

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;

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

[1].NTH_VALUE(expr, n)

[a].介绍

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

[b].示例

-- 查询每个部门截止目前薪资排在第二和第三的员工信息
select 
dname,
ename,
hiredate,
salary,
nth_value(salary, 2) over(partition by dname order by hiredate) as second_score,
nth_value(salary, 3) over(partition by dname order by hiredate) as third_score,
from employee;

[2].NTILE

[a].介绍

用途:将分区中的有序数据分为n个等级，记录等级数

应用场景:将每个部门员工按照入职日期分成3组

[b].示例

-- 根据入职日期将每个部门的员工分成3组
select 
dname,
ename,
hiredate,
salary,
ntile(3) over(partition by dname order by hiredate) as rn
from employee;

-- 取出每个部门的第一组员工
 select 
 *
 from 
 (
    select 
    dname,
    ename,
    hiredate,
    salary,
    ntile(3) over(partition by dname order by hiredate) as rn
    from employee;    
 ) t
 where t.rn = 1;