数据库MYSQL笔记8——函数

原创已于 2023-04-17 07:02:19 修改 · 1.9k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #字符串

于 2021-07-14 01:13:39 首次发布

mysql 专栏收录该内容

10 篇文章

订阅专栏

这篇博客详细介绍了MYSQL数据库中各类函数的使用，包括数值型数学计算（如ABS, EXP）、数值型聚合计算（如COUNT, SUM）、日期时间型函数（如NOW, CURDATE）、字符串操作（如CONCAT, SUBSTRING_INDEX）以及窗口函数的应用。还提到了一些特殊函数，如随机数RAND、日期偏移DATE_ADD以及百分位计算等。" 90485591,8160100,生化危机：安全路径判断,"['算法', '图论', '数据结构', '路径查找']

数值型数学计算

绝对值ABS
指数函数EXP
四舍五入ROUND( , 保留位数)
圆周率PI
余数MOD
- MOD(5,2)=1
向上取整CEIL
- CEIL(5.1)=6
向下取整FLOOR
- FLOOR(5.1)=5
0-1之间随机函数RAND
幂运算POWER
- POWER(2,3)=8
平方根：SQRT

数值型聚合计算

计数
COUNT(*)/(任意常数)——计算表的行数，包括NULL
COUNT(col)——计算某列，跳空
COUNT(DISTINCT col1,col2…)——(多列)去重计数，类比group by 多列分组
求和
SUM(col)
不跳空：四则运算a+b（NULL）+c=NULL;
跳空：SUM(a,b,c)=SUM(a,c)
均值
AVG(col)
与SUM跳空
最大值
MAX
可排序的都可计算MAX；忽略NULL
最小值
MIN
可排序的都可计算MIN；忽略NULL

日期时间型

当前日期+时间：NOW
当前日期+时间：SYSDATE
当前年月日：CURDATE
当前时分秒：CURTIME、CURRENT_TIME
等待时间（秒）：SLEEP()
日期格式
- 【年】%Y，4位；%y，2位；
- 【月】%M 英文；%m 数值；
- 【日】D% 带英文后缀的顺序数，如1st，2nd；d% 仅数值
文本格式转年月日格式： str_to_date
- select str_to_date(‘08/09/2008’, ‘%m/%d/%Y’);
日期转自定义日期格式 date_format
- select date_format(now(),‘%Y-%M-%d %H’) ; – 2020-May-23 17
提取时间
- 年月日的日期：DATE
- 时分秒的日期：TIME
- 年月季度周日：YEAR、MONTH、QUARTER、 WEEK、DAY
- 时分秒：HOUR、MINUTES、SECOND
- EXTRACT(YEAR FROM 2020-09-08)
偏移日期
- 时间增加/ 减少：DATE_ADD / SUB(时间, INTERVAL [间隔] [偏移单位]
时间差
- 天数差：DATEDIFF(‘DAY’,‘BEGIN’,‘END’) 遵循后-前
  示例： DATEDIFF(DAY,‘2008-12-29’,‘2008-12-30’)
- 时分秒差：TIMEDIFF(‘BEGIN’,‘END’) 遵循前-后支持time、datetime格式
- 返回指定格式的时间差：TIMESTAMPDIFF(‘时间单位’,‘BEGIN’,‘END’) 遵循后-前

字符间拼接

CONCAT(str1,str2,…)
CONCAT_WS(x, s1,s2…sn)
GROUP_CONCAT(x, s1,s2…sn) --分组后的对某字段的拼接
每个字符串之间要加上 x，x 可以是分隔符

字符切分

SUBSTRING、MID(s, start, length)
从字符串 s 的 start 位置截取长度为 length 的子字符串
SUBSTRING_INDEX(s, delimiter, number)
取第N个隔符左/右边的字符。如果 N是正数，取左边字符。如负数，返回右边字符
例子：SUBSTRING_INDEX(SUBSTRING_INDEX(‘a_b_c_d_e’,‘‘,3),’’,-1) 结果是c
LEFT(s,n)
返回字符串 s 的前 n 个字符，从左边数
RIGHT(s,n)
返回字符串 s 的后 n 个字符，也就是从右边数

字符串变换

字符长度：CHARLENGTH
字节长度：LENGTH
去除左/右/两边空值：LTRIM / RTRIM/TRIM
大小写字母转换：UPPER / LOWER
REPLACE(str,from_str,to_str)指定字符串替换新的字符串
INSERT(str,pos,len,newstr)
返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr替换。
颠倒字符顺序REVERSE(str)
复制n次REPEAT(str,count)
返回由重复count次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。
从左边填充：LPAD(s1,len,s2)
：在字符串 s1 的左边开始处填充字符串 s2，使字符串长度达到 len。如果s2不够则重复填充多个s2，如果s2过长则优先取左边的字符使得达到len长度。
从右边填充： RPAD

窗口函数

排序

RANK OVER (partition by … order by…)
- 有相同记录时跳过之后的位次
DENSE_RANK OVER (partition by … order by…)
- 有相同记录时不跳过之后的位次
ROW_NUMBER OVER (partition by … order by…)
- 赋予唯一的连续位次

聚合

SUM、AVG、COUNT、MAX、MIN （ …） OVER（partion by …order by …）****

  select v1,v2,sum(v2) over(**order by** v2) as sum     --仅排序，累计当前行及上面的行
  select v1,v2,sum(v2) over(**partition by** v1) as sum    --仅填充相同聚合值
  select v1,v2,sum(v2) over(**partition by** v1 **order by** v2) as sum     --先分组，后排序，再累加
  select v1,v2,sum(v2) over(**partition by v1 order by v1)** as sum   ---分组后，组内顺序按原表不变，累加
  select v1,v2,sum(v2) over(**partition by v1 order by v1 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)** as sum   ---分组后，组内顺序按原表不变，向上移动平均7行的聚合计算

在这里插入图片描述

偏移

LAG / LEAD (exp_str, n ,default) over(partition by …order by …)
取出当前行前 / 后面第n行数据
default是超出记录窗口时的默认值；不填，默认为NULL
例子：

在这里插入图片描述

百分位（0-1）

PERCENT_RANK() OVER (
[PARTITION BY partition_expression, … ]
ORDER BY sort_expression [ASC | DESC], …)
例子：
在这里插入图片描述
求百分位的其他两个方法见https://blog.csdn.net/weixin_33329305/article/details/113472520

2.percentile(字段,百分位)
返回某个字段从小到大排序后的百分比排位

例子：
求某主体小程序dau从小到大的百分位在80%的dau（dau从小到大）
在这里插入图片描述
结果：
验证：从小到大排，45名的dau是92

举一反三：
（1）如果是想求dau排位在top10%的那个dau，
则应该是1-10%=90%，percentile（dau，0.9）
即第 57*0.9=50.4 取整后第51名。（从小到大）

（2）PERCENT_RANK 与percentile 的区别

	PERCENT_RANK	percentile
功能1	可间接求某个百分位的数值（要加where rank=百分位%）	直接返回某个百分位的数值
功能2	可直接返回每行的所在百分位（升/降序均可）