MySQL （六）函数(字符串、数值、日期、流程空值)、窗口函数（聚合、排名、取值）

一、字符串函数

select lpad("bo",5,"*")
select substring("boxiansi",3) #索引从1开始

二、数值函数

三、日期函数

select date_add(now(),interval 70 day) #今天往后推70天

四、流程控制函数

select ifnull('','boxiansi')
select ifnull(null,'boxiansi')

use test;
select
	dept,
    university,
    (case university #加括号、加列名 
    when 'CUMT' then '中国矿业大学'  #将'CUMT'改为'中国矿业大学'
    when 'JNU' then '暨南大学' 
    else '其他' 
    end ) #加括号
    as u2 #取别名
from test.total

运行结果：

use test;
select
	dept,
    case #不加列名
    when (math >= 80) then '优秀' 
    when (math between 60 and 80) then '合格' #数学成绩小于80大于60为合格
    else '不合格'
    end #case体结束
    as math #取别名
from test.section

运行结果：

when条件中用到列名，用第二种；when条件中用不到列名，用第一种

（个人理解，有误戳我改正）

五、窗口函数

1.聚合窗口函数

#数据准备
create database test;
use test;
create table total  (
`product` varchar(20), 
`ym` int,
`amount` int
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

insert into `total` values("苹果",202301,10297);
insert into `total` values("苹果",202302,12337);
insert into `total` values("苹果",202303,15660);
insert into `total` values("苹果",202304,12456);

insert into `total` values("香蕉",202301,34972);
insert into `total` values("香蕉",202302,29476);
insert into `total` values("香蕉",202303,49827);
insert into `total` values("香蕉",202304,76342);

insert into `total` values("菠萝",202301,48322);
insert into `total` values("菠萝",202302,32346);
insert into `total` values("菠萝",202303,34827);
insert into `total` values("菠萝",202304,36229);

a.partition by

use test;
select 
	product,
    ym,
    amount,
    sum(amount) over(
    partition by product #分组，类似于gruop by，每一行都有结果
    ) as s_amount  #取别名
from test.total

b.order by

use test;
select 
	product,
    ym,
    amount,
    sum(amount) over(
    partition by product #没有,
    order by ym #排序，结果为到这一行的累计值
    ) as s_amount  #取别名
from test.total

c.指定窗口

unbounded preceding	首行
n preceding	前n行
current row	当前行
m following	后m行
unbounded following	尾行

use test;
select 
	product,
    ym,
    amount,
    sum(amount) over(
    partition by product #没有,
    order by ym #没有,
    rows between 2 preceding and current row #从前面两行到现在这行
    ) as s_amount  #取别名
from test.total

use test;
select 
	product,
    ym,
    amount,
    sum(amount) over(
    partition by product #没有,
    order by ym #没有,
    rows between 1 preceding and 1 following #从前面一行到后面一行
    ) as s_amount  #取别名
from test.total

use test;
select 
	product,
    ym,
    amount,
    sum(amount) over(
    partition by product #没有,
    order by ym #没有,
    rows between unbounded preceding and current row #从首行到当前行，破坏了order by的窗口
    ) as s_amount  #取别名
from test.total

range

use test;
select 
	product,
    ym,
    amount,
    sum(amount) over(
    partition by product
    order by ym
    range 4 preceding #前四个数据
    ) as s_amount  #取别名
from test.total

时间range（一定要加interval 和 day 等）

#数据准备
create database test;
use test;
create table total  (
`product` varchar(20), 
`ymd` date,
`amount` int
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

insert into `total` values("苹果",'2023-01-03',10297);
insert into `total` values("菠萝",'2023-01-05',12337);
insert into `total` values("苹果",'2023-01-06',15660);
insert into `total` values("苹果",'2023-01-10',12456);
insert into `total` values("菠萝",'2023-01-11',34972);
insert into `total` values("苹果",'2023-01-12',29476);
insert into `total` values("苹果",'2023-01-23',49827);
insert into `total` values("菠萝",'2023-01-29',76342);

use test;
select 
	product,
    ymd,
    amount,
    sum(amount) over(
    partition by product
    order by ymd #一定要按时间排序
    range interval 4 day preceding #前四天所有数据（没有数据即忽略）
-- 	range between interval 4 day preceding and interval 1 day following #前四天到后一天所有数据
    ) as s_amount  #取别名
from test.total

2.排名窗口函数

row_number	为每行数据分配序列号，从1开始
rank	返回当前行在分区中的排名，存在名次相同的数据，后续排名跳跃
dense_rank	返回当前行在分区中的排名，存在名次相同的数据，后续排名不跳跃
percent_rank	返回当前行在分区中的排名，存在名次相同的数据，后续排名跳跃（百分比形式）
cume_dist	计算当前行在分区内的累积分布 分组内小于、等于当前rank值的行数 / 分组内总行数
ntile	将分区数据分为N等份，返回当前行所在片区位置

use test;
select 
	product,
    ym,
    amount,
    row_number() over(
    partition by product
    order by ym #按照列ym排名
    ) as ym_rank  #取别名

use test;
select 
	product,
    ym,
    amount,
    row_number() over(
    partition by product
    order by amount
    ) as amount_rank  #取别名
from test.total

3.取值窗口函数

lag	返回当前行之前的第n行数据
lead	返回当前行之后的第n行数据
first_value	返回第一行数据
last_value	返回最后一行数据
nth_value	返回第n行数据

use test;
select 
	product,
    ym,
    amount,
    lag(amount,1) over(partition by product order by ym) as lag_amount #滞后一期 
from test.total