我在b站学数据库 （九）：函数

上一篇：我在b站学数据库 （八）：多表操作练习

函数分类
聚合函数、数学函数、字符串函数、日期函数、控制流函数、窗口函数

一、聚合函数
1、介绍
聚合函数主要由：count,sum,min,max,avg,这些聚合函数（之前有），不再重复。另外一个函数:group_concat(），该函数用户实现行的合并
2、格式
group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator ‘分隔符’])
说明：
（1）distinct可以排除重复值。
（2）如果需要对结果中的值进行排序，可以使用order by子句。
（3）separator是一个字符串值，默认为逗号。

3、例
（1）数据准备

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,'销售部'),
('刘云鹏',7800,'销售部');

（2）操作

-- 将所有员工的名字合并成一行 
select group_concat(emp_name) 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;

二、数学函数
1、abs()：返回绝对值

select abs(-10);   #10
select abs(-10);   #10

2、ceil():返回大于或等于的最小整数（向上取整）

select ceil(1.1);   #2
select ceil(1.0);   #1

3、floor（）：返回小于或等于的最大整数（向下取整）

select floor(1.1);   #1
select floor(1.9);   #1

4、greatest（expr1,expr2…）：取列表最大值

select greatest(1,2,3);   #3

5、least（expr1,expr2…）：取列表最小值

select least(1,2,3);   #1

6、mod(x，y)：返回x除以y以后的余数（取模）

select mod(5,2);   #1

7、pow（x，y）：返回x的y次方

select pow(2,3)   #8

8、rand（）：返回0到1的随机数

select rand()     #0.93099

9、round(x)：返回离x最近的整数（遵循四舍五入）

select round(1.23456)   #1

10、round（x，y）：返回指定位数的小数（遵循四舍五入）

select round(1.23456,3)   #1.235

11、truncate（x，y）：返回数值x保留到小数点后y位的值

select truncate(1.23456,3)   #1.234

注意：与round最大的区别是不会进行四舍五入

三、字符串函数
1、char_length(s)：返回字符串s的字符数

select char_length('hello');   #5
select char_length('你好吗');  #3
#注意：---length取长度，返回的单位是字节
select length('hello');   #5
select length('你好吗');  #9  一个汉字3个字节

2、character——length（s）：返回字符串s的字符数
同上
3、concat（s1，s2…sn）：字符串s1，s2等多个字符串合并为一个字符串

select concat ('hello','world');

4、concat_ws(x,s1,s2…sn):同上函数，但是每个字符串之间要加上x，x可以是分隔符

select concat_ws ('-','hello','world');

5、field（s,s1,s2…）:返回第一个字符串s
在字符串列表（s1,s2…）中的第一次位置

select field('a','a','b','c');   #1
select field('b','a','b','c');   #2

6、ltrim（s）：去掉字符串s开始处的空格

select ltrim('     aaaa');   #去除左边空格
select rtrim('     aaaa     ');   #去除右边空格
select trim('     aaaa     ');   #去除两边空格

7、mid(s,n,len):从字符串s的n位置截取长度为len的子字符串，同substring（s,n,len）

select mid("helloworld",2,3);  #从第二个字符开始截取，截取长度为3

8、position(s1 in s):从字符串s中获取s1的开始位置

select position('abc'in 'habcelloworld');   #2

9、replace（s,s1,s2）:将字符串s2代替字符串s中的字符串s1

select replace('helloaaaworld','aaa','bbb');

10、reverse（s）:将字符串s的顺序反过来

select reverse('hello');

11、right（s,n）:返回字符串s的后n个字符

select right('hello',3);   #返回最后三个字符

12、strcmp（s1,s2）:比较字符串s1和s2，如果s1
与s2相等返回0，如果s1>s2返回1，如果s1<s2返回-1。

select strcmp('hello','world');   #-1

13、substr（s,start,length）:从字符串s的start位置截取长度为length的子字符串

select substr('hello',2,3);   # 从第二个字符开始截取，截取三个字符

14、substring（s,start,length）:从字符串s的start位置截取长度为length的子字符串
同上
15、ucase(s):将字符串转换为大写
16、upper（s）:将字符串转换为大写

select ucase("helloworld");
select upper("helloworld");

17、lcase（s）:将字符串s的所有字母变成小写字母
18、lower（s）:将字符串s的所有字母变成小写字母

select lcase("HELLOWORLD");
select lower("HELLOWORLD");

四、日期函数
1、unix_timestamp():返回从1970-01-01-00:00:00到当前的毫秒值

select unix_timestamp();

2、unix_timestamp(DATE)：将指定日期转换为毫秒值时间

select unix_timestamp('2022-01-01 08:08:08');

3、from_unixtime(bigint unixtime[,stringformat]):将毫秒值时间转为指定格式日期

select from_unixtime(88888888,'%Y-%m-%d %H:%i:%s');

4、curdate（）：返回当前日期

select curdate();`

5、current_date():返回当前日期

```python
select current_date();

6、current_time():返回当前时间

select current_time();

7、curtime():返回当前时间

select curtime();

8、current_timestamp():返回当前日期和时间

select current_timestamp();

9、date()：从日期或日期时间表达式中提取日期值（年月日）

select date('2022-01-01 08:08:08');

10、datediff(d1,d2):计算日期d1到d2之间相隔的天数

select datediff('2022-01-01','2000-08-08');

11、timediff（time1,time2）:计算时间差值（秒级）

select timediff('08:08:08','06:06:06');

12、date_format(d,f):按表达式f的要求显示日期d

select date_format('2022-1-1 1:1:1','%Y-%m-%d %H:%i:%s');

13、str_to_date(string,format_mask):将字符串转变为日期

select str_to_date('2022-1-1 1:1:1','%Y-%m-%d %H:%i:%s');

14、date_sub(date,interval expr type):函数从日期减去指定的时间间隔

select date_sub('2022-01-01',interval 2 day);
#将日期进行加法
select date_add('2022-01-01',interval 2 day);
#将日期进行加法

15、从日期中获取

select extract(hour from '2021-12-12 08:08:08');  #获取小时
select extract(year from '2021-12-12 08:08:08');  #获取年
select extract(month from '2021-12-12 08:08:08');  #获取月

16、获取指定日期的最后一天

select last_day('2022-01-01');

17、获取指定年份和天数的日期

select makedate('2022',88);

18、根据日期获取信息

select monthname('2020-02-02 08:08:08');   #获取月份的英文
select dayname(''2020-02-02 08:08:08'')   #获取周几
select dayofmonth(''2020-02-02 08:08:08'')   #获取当月的第几天

五、控制流函数
1、if（expr,v1,v2）:如果表达式expr成立，返回结果v1，否则返回结果v2。

select if(5>3,'大于','小于');   #大于

2、ifnull(v1,v2):如果v1的值不为null，则返回v1，否则返回v2。

select ifnull(5,0);   #5
select ifnull(null,0);   #0

3、isnull（expression）：判断表达式是否为null。

select isnull(5);   #0
select isnull(null);   #1

4、nullif（expr1，expr2）：比较两个字符串，如果字符串expr1与expr2相等，返回null，否则返回expr1。

select nullif(12,12)   #null
select nullif(12,13)   #12

5、case when语句
格式：
case expression
when condition 1 then result 1
when condition 2 then result 2
…
else result
end
解释：case表示函数开始，end表示函数结束。如果condition1成立，则返回result1，如果condition2成立则返回result2，当全部不成立，则返回result，而当有一个成立之后，后面的就不执行了。
例：

-- 创建订单表
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);

-- 方式1
select 
*  ,
case 
  when payType=1 then '微信支付' 
    when payType=2 then '支付宝支付' 
    when payType=3 then '银行卡支付' 
    else '其他支付方式' 
end  as payTypeStr
from orders;
-- 方式2
select 
*  ,
case payType
  when 1 then '微信支付' 
    when 2 then '支付宝支付' 
    when 3 then '银行卡支付' 
    else '其他支付方式' 
end  as payTypeStr
from orders;

六、窗口函数（8.0新增）
1、概念
非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

2、序号函数
（1）序号函数有三个：row_number（）、rank()、dense_rank()
(2)格式：
row_number()|rank()|dense_rank() over (
partition by …
order by …
)
（3）例

#数据准备
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;

-- 对每个部门的员工按照薪资排序，并给出排名 rank
select 
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn 
from employee;

-- 对每个部门的员工按照薪资排序，并给出排名 dense-rank
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
    from employee
)t
where t.rn <= 3

-- 对所有员工进行全局排序（不分组）
-- 不加partition by表示全局排序
select 
     dname,
     ename,
     salary,
     dense_rank() over( order by salary desc)  as rn
from employee;

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

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

注意：结果为第一行salary加到当前行

select 
dname,
ename,
hiredate,
salary
sum(pv) over(partition by dname) as c1
from employee;  
-- 如果没有order  by排序语句  默认把分组内的所有数据进行sum操作

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,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and current row) as c1 
from employee;
#从当前行的前三行加到当前行

#同理 从当前的前三行加到当前的后一行（包括本行）
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and 1 following) as c1 
from employee;

#从当前行加到最后一行
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between current row and unbounded following) as c1 
from employee;

4、分布函数（CUME_DIST）
（1）用途：分组内小于、等于当前rank值的行数 / 分组内总行数
（2）应用场景：查询小于等于当前薪资（salary）的比例
（3）例

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

解释：
rn1 以第一行0.25为例，因为没有分组，所以在所有数据中小于等于3000的行数除以总行数。即3/12=0.25。
rn2以第一行0.166666666…为例，因为以dname分组的，所以在一组数据中小于等于3000的行数除以总行数。即1/6=0.1666…

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

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;

解释：
rn2:
第一行: (1 - 1) / (6 - 1) = 0
第二行: (1 - 1) / (6 - 1) = 0
第三行: (3 - 1) / (6 - 1) = 0.4

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

-- lag的用法
select 
 dname,
 ename,
 hiredate,
 salary,
 lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
 #前一行的值放到当前行，没有的话有默认值'2000-01-01'
 lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
 #前两行的值放到当前行，默认值为null
from employee;

解释：
last_1_time: 指定了往上第1行的值，default为’2000-01-01’ 。第一行，往上1行为null,因此取默认值 ‘2000-01-01’

last_2_time: 指定了往上第2行的值，为指定默认值
第一行，往上2行为null

-- 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;

解释：lead同作用lag，只是lead是向下。

7、头尾函数
（1）用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
（2）应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资
（3）例

-- 注意,  如果不指定ORDER BY，则进行排序混乱，会出现错误的结果
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;

解释：
first1：返回截至到当前，按照日期排序查询第一个入职的员工薪资
last1：返回截至到当前，按照日期排序查询第一个入职的员工薪资

8、其他函数（nth_value（expr，n））
（1）用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
（2）应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资
（3）例

-- 查询每个部门截止目前薪资排在第二和第三的员工信息
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

解释：
返回截至当前，按入职日期排第二和第三的员工薪资

9、其他函数（ntile）
（1）用途：将分区中的有序数据分为n个等级，记录等级数
（2）应用场景：将每个部门员工按照入职日期分成3组
（3）例

-- 根据入职日期将每个部门的员工分成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;

下一篇：我在b站学数据库 （十）：视图