MySQL学习十：常用函数与常见题型总结（连续更新中） ...

本文基于前段时间在牛客网找的 MySQL 标题举行的训练，总结出了一些较为常用的函数，供大家举行一定的参考


一、常用函数

1.1 left 和 right 函数

作用 1：根据名字的前几位或者末了几位举行排序

1. # 根据名字的最后两位进行排序
2. select first_name
3. from employees
4. order by right(first_name, 2)

复制代码

作用 2：根据日期的年份或月份筛选数据

1. # 筛选出入职年份为 2020 年的员工信息
2. select *
3. from employees
4. where left(hire_date, 4) = 2020

复制代码

1.2 first_value 函数

作用：窗口函数中，取分区的第一行数据（通常是最小日期等数据）

1. select *,
2. first_value(date) over(partition by user_id order by date) as first_buy_date
3. from order_info

复制代码

1.3 timestampdiff 函数

作用：计算两个时间的间隔（如第一个参数可以写年，月，日，小时，秒）
注意：小的时间放在第二个参数，大的时间放在第三个参数

1. select timestampdiff(day, '2024-01-06', '2024-03-28') result

复制代码

1.4 datediff 函数

作用：返回两个日期相隔的天数
注意：大的时间放在第一个参数，小的时间放在第二个参数

1. select datediff('2022-04-29','2022-04-17') as 相隔天数

复制代码

1.4 date_sub 和 date_add 函数

作用：将日期淘汰指定 day / month / year 等

1. select date_sub('2024-4-8', interval 3 day) as date1,
2. date_add('2024-4-8', interval 3 day) as date2

复制代码

二、常见题型

2.1 查询新登任命户总体的越日留存率（第一天新登录的总体用户，第二天再次登录的总体用户）

① 建立两个表，表1 为每个用户的注册日期表，表2 为用户登岸表
② 以表1 为主表，与表2 举行 left join 的利用
③ 连接条件有 2 个：表1 中的 user_id = 表2 中的 user_id，且表1 中的用户注册日期 = date_sub (表2 中的用户登录日期，interval 1 day)
注意： 计算这种题型时，每个用户的注册日期表要放在最左侧作为主表，再左连接用户登岸表
例1：查询牛客网新登任命户总体的越日留存率

1. with reg as (
2.         select user_id, min(date) as reg_date
3.         from login
4.         group by 1
5. ), -- 每个用户的注册日期表
6. log as(
7.         select user_id, date as login_date
8.     from login
9.     group by 1, 2
10. ) -- 每个用户的登录日期表
12. select round(count(l.user_id)/count(r.user_id), 3) as p
13. from reg r
14. left join log l
15. on r.user_id = l.user_id
16. and r.reg_date = date_sub(l.login_date, interval 1 day)

复制代码

2.2 查询每个日期登岸的新用户数量

① 建立两个表，表1 为所有用户登岸过的日期表（即所有日期），表2 为每个用户的注册日期表
② 以表1 为主表，与表2 举行 left join 的利用，连接条件：表1 中的日期 = 表2 中的每个用户的注册日期
③ 按照表1 中的日期举行分组，对 user_id 举行 count 利用（注意：按照日期和 user_id 一起举行分组得到的是每一个 user_id 在同一个日期的计数，都是 1，null 值计数为0）
注意： 计算这种题型时，所有用户登岸过的日期表（即所有出现过的日期）要放在最左侧作为主表，再左连接用户注册日期表
例2：查询牛客网每个日期登录新用户个数

1. with all_login_date as(
2.         select date
3.     from login
4.     group by 1
5. ), -- 所有用户登陆过的日期表
6. reg as(
7.         select user_id,
8.     min(date) as reg_date
9.     from login
10.     group by 1
11. ) -- 每个用户的注册日期表
13. select a.date as date, count(user_id) as new_user
14. from all_login_date a
15. left join reg b
16. on a.date = b.reg_date
17. group by 1

复制代码

若查询所有信息，同时按照日期和 user_id 一起举行分组，结果如下：

2.3 查询每个日期新用户的越日留存率

① 建立三个表，表1 为所有用户登岸过的日期表，表2 为每个用户的注册日期表，表3 为每个用户的登录日期表
② 以表1 为主表，依次与表2，表3 举行 left join 的利用
③ 表2 与表3 的连接条件有 2 个：表2 中的 user_id = 表3 中的 user_id，且表2 中的用户注册日期 = date_sub (表3 中的用户登录日期，interval 1 day)
④ 按表1 中的日期举行分组
注意： 计算这种题型时，所有用户登岸过的日期表（即所有出现过的日期）要放在最左侧作为主表，再左连接用户注册日期表，末了左连接用户登录日期表
例3：查询牛客每个日期新用户的越日留存率

1. with all_login_date as(
2.         select date
3.     from login
4.     group by 1
5. ),-- 所有用户登陆过的日期表
6. reg as(
7.         select user_id, min(date) as reg_date
8.     from login
9.     group by 1
10. ), -- 每个用户的注册日期表
11. log as(
12.         select
13.     user_id, date as login_date
14.     from login
15.     group by 1, 2
16. ) -- 每个用户的登陆日期表
18. # 查询所有信息
19. select *
20. # 查询要求信息
21. select a.date as date,
22. round(ifnull(count(distinct c.user_id) / count(distinct b.user_id), 0), 3) as p
23. from all_login_date a
24. left join reg b
25. on a.date = b.reg_date
26. left join log c
27. on b.user_id = c.user_id
28. and b.reg_date = date_sub(c.login_date, interval 1 day)
29. group by 1
30. order by 1

复制代码

查询所有信息中的第一个 left join 结果（可求每个日期新用户的数量）：

查询所有信息中的第二个 left join 结果：

查询要求信息的终极结果：

2.4 统计每天的日活数及新用户占比

① 建立两个表，表1 为每个用户的登录日期表，表2 为每个用户的注册日期表
② 以表1 为主表，与表2 举行 left join 的利用（注意，与计算越日留存率的两个表的位置是相互交换过的）
③ 表1 与表2 的连接条件有 2 个：表1 中的 user_id = 表2 中的 user_id，且表1 中的用户登录日期 = 表2 中的用户注册日期
④ 按照表1 中的用户登录日期举行分组
注意： 计算这种题型时，用户登录日期表要放在最左侧作为主表，再左连接用户注册日期表
例4：查询牛客网每天的日活数及新用户占比

1. with reg as(
2.     select
3.     uid,
4.     date(min(in_time)) as reg_date
5.     from tb_user_log
6.     group by 1
7. ), -- 用户注册表
8. log as(
9.     select
10.     uid,
11.     date(in_time) as login_date
12.     from tb_user_log
13.     union
14.     select
15.     uid,
16.     date(out_time) as login_date
17.     from tb_user_log
18. ) -- 用户登陆表
20. select
21. *,
22. login_date,
23. count(distinct l.uid) as dau,
24. round(count(distinct r.uid) / count(distinct l.uid), 2) as uv_new_ratio
25. from log l
26. left join reg r
27. on l.uid = r.uid
28. and l.login_date = r.reg_date
29. group by 1
30. order by 1

复制代码

查询要求信息的终极结果（无 group by，order by）：

2.5 查询文章 / 直播间同一时刻最大浏览人数 / 最大观看人数

① 建立一个表，以统计每篇文章 / 每个直播间的差异时刻的人数增长和人数淘汰的环境，要用到 union all 语法（注意：要新增一列，每个时刻若人数增长则设为 1，若人数淘汰则设为 -1）
② 使用 sum 的窗口函数统计每篇文章 / 每个直播间同一时刻的浏览人数 / 观看人数（注意：以文章 ID / 直播间 ID 作为分区即可，并按照时刻升序分列，① 中新增的那一列降序分列）
例3：查询每篇文章同一时刻最大在看人数

1. with a as(
2.         select uid, artical_id, in_time as dt, 1 as is_in
3.     from tb_user_log
4.     union all
5.         select uid, artical_id, out_time as dt, -1 as is_in
6.     from tb_user_log
7. )
9. select artical_id, max(uv) as max_uv
10. from(
11.         select artical_id, dt,
12.         sum(is_in) over(partition by artical_id order by dt, is_in desc) as uv
13.         from a
14.     where artical_id <> 0
15. ) b
16. group by 1
17. order by 2 desc

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。