MySQL 经验集总结(更新ing)

1. 函数使用方法

1.1 时间差函数-timestampdiff()

语法：
timestampdiff（unit，begin，end）
参数：
begin和end可以为DATE或DATETIME类型，并且可允许参数为混合类型。
unit — 确定（end-begin）时间差的单位，MySQL支持以下单位。

作用：
根据单位返回时间差，对于传入的begin和end不需要相同的数据结构，可以存在一个为Date一个DateTime。

示例：

法一：用avg计算完播率
select a.video_id,
round(avg(case when timestampdiff(second,a.start_time,a.end_time) >= b.duration then 1 else 0 end),3) as avg_comp_play_rate
from tb_user_video_log a
left join tb_video_info b 
on a.video_id = b.video_id
where date_format(a.start_time,'%Y')=2021   
and date_format(a.end_time,'%Y')=2021    
group by a.video_id
order by avg_comp_play_rate desc

法二：用图片方法计算完播率
select a.video_id,
round(sum(if(end_time - start_time >= duration,1,0))/count(start_time),3) 
as avg_comp_play_rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
where year(a.start_time)=2021
and year(a.end_time)=2021
group by a.video_id
order by avg_comp_play_rate desc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

1.2 datediff()函数

语法：
DATEDIFF(date1,date2)
参数：
date1 和 date2 参数是合法的日期或日期/时间表达式。
注释：只有值的日期部分参与计算。
作用：
DATEDIFF() 函数返回两个日期之间的时间。datediff函数的结果为date1-date2的值。

1.3 date_format()函数-日期格式化

语法：
DATE_FORMAT(date,format)
参数：
date — 参数是合法的日期；
format — 规定日期/时间的输出格式
作用：
DATE_FORMAT()函数用于以不同的格式显示日期/时间数据

使用格式参考：
https://www.w3school.com.cn/sql/func_date_format.asp
具体用法参考‘数据会说话’兄的博客：
https://blog.csdn.net/xjh163/article/details/96312001

1.4 substring()函数-截取字符串

1.4.1 两个参数

语法：
substring(string ，index) | substring(string from index)
即substring(被截取的字符串 , 开始位置序号) | substring(被截取的字符串 from开始位置序号)
参数：
string：被截取的字符串，表示需要从该字符串截取目标字符。
index：开始截取的位置，为整数。当index为0时返回的是空值；当index为正数表示从左到右数的位置序数；当index为负数表示从右到左的位置序数。
作用：
从指定字符串的某序数开始获取字符。

1.4.2 三个参数

语法：
substring(string ，index，len) | substring(string from index for len)
即substring(被截取字符串 ，开始位置，长度) | substring(被截取字符串 from 开始位置 for长度)
参数：
string：被截取的字符串，表示需要从该字符串截取目标字符。
index：开始截取的位置，为整数。同上。
len：表示截取字符串的长度。

作用：
从指定字符串的某个位置开始获取指定长度的字符。