【MySQL】函数

1. DQL执行顺序

2. 函数

2.1 字符串函数

函数	功能
concat(s1,s2,…sn)	字符串拼接，将s1,s2…sn拼接成一个字符串
lower(str)	将字符串str全部转为小写
upper(str)	将字符串str全部转为大写
lpad(str, n, pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str, n, pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str, start, len)	返回字符串str从start位置起len个长度的字符串

2.2 数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x/y)	返回x/y的模
rand()	返回0~1的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数

2.3 日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date年份
month(date)	获取指定date月份
day(date)	获取指定date日期
date_add(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
date_sub(date, INTERVAL expr type)	返回一个日期/时间值减去一个时间间隔expr后的时间值
datediff(date1, date2)	返回起始时间date1和结束时间date2之间的天数

2.4 流程函数

函数	功能
if(value, t, f)	如果value为true，则返回t，否则返回f
ifnull(value1, value2)	如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1] … else [default] end	如果val1为true，返回res1，…否则返回default默认值
case [expr] when [val1] then [res1] … else[default] end	如果expr的值等于val1，返回res1，…否则返回default默认值

2.5 窗口函数

2.5.1 介绍

窗口函数可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同指出之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个结果。

窗口函数定义：

窗口函数名称 ([可选的分析字段或表达式]) over(# 分区partition by ...# 排序order by ...# 窗口大小frame_clause
)

• over 子句用于指定一个数据分析窗口。

• partition by 类似于 group by，会分别针对每个分区单独进行分析。

• order by 用于指定分区内数据的排序方式

• frame_clause 用于指定一个移动的分析窗口，窗口总是位于分区的范围之内，是分区的一个子集。在制定了分析窗口之后，窗口函数不再基于分区进行分析，而是基于窗口内的数据进行分析。

• 指定窗口函数大小的具体选项如下：

  {rows | range} frame_start
  {rows | range} between frame_start and frame_end
  

  row 表示以数据行为单位计算窗口的偏移量，range 表示以数值（10天、5千米等）为单位计算窗口的偏移量

• frame_start 用于定义窗口的起始位置，包括以下内容：

  • unbounded preceding：表示窗口从分区的第一行开始
  • n preceding：表示窗口从当前行之前的第n行开始
  • current row：表示窗口从当前行开始

• frame_end 用于定义窗口的结束位置，包括以下内容：

  • current row：表示窗口从当前行结束
  • n following：表示窗口从当前行之后的第n行结束
  • unbounded following：表示窗口到分区的最后一行结束

2.5.2 聚合窗口函数

avg、sum… over()

2.5.3 排名窗口函数

排名窗口函数可以用来获取数据的分类排名，常见的排名窗口函数如下：

排名窗口函数	描述
row_unmber()	为分区中的每行数据分配一个系列号，序列号从1开始
rank()	返回当前行在分区中的名次，如果存在名次相同的数据，后续的排名将会产生跳跃
dense_rank()	返回当前行在分区中的名次，即使存在名次相同的数据，后续的排名也是连续的
percent_rank()	以百分比的形式返回当前行在分区中的名次。如果存在名词相同的数据，后续的排名将会产生跳跃
cume_dist()	计算当前行在分区内的累积分布
ntile(n )	将分区内的数据分为n等分，并返回当前行所在的分区位置

排名窗口函数不支持动态的窗口大小选项，而是以整个分区作为分析的窗口。

2.5.4 取值窗口函数

取值窗口函数可以用来返回窗口内指定位置的行数据，常见的取值窗口函数如下：

取值窗口函数	描述
lag()	返回窗口内当前行之前的第n行数据
lead()	返回窗口内当前行之后的第n行数据
first_value()	返回窗口内第一行数据
last_value()	返回窗口内最后一行数据
nth_value()	返回窗口内第n行数据