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

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

一、常用函数

1.1 left 和 right 函数

作用 1：根据名字的前几位或者最后几位进行排序

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

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

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

1.2 first_value 函数

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

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

1.3 timestampdiff 函数

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

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

1.4 datediff 函数

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

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

1.4 date_sub 和 date_add 函数

作用：将日期减少指定 day / month / year 等

select date_sub('2024-4-8', interval 3 day) as date1,
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：查询牛客网新登录用户总体的次日留存率

with reg as (
	select user_id, min(date) as reg_date
	from login
	group by 1
), -- 每个用户的注册日期表
log as(
	select user_id, date as login_date
    from login
    group by 1, 2
) -- 每个用户的登录日期表

select round(count(l.user_id)/count(r.user_id), 3) as p
from reg r
left join log l
on r.user_id = l.user_id
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：查询牛客网每个日期登录新用户个数

with all_login_date as(
	select date
    from login
    group by 1
), -- 所有用户登陆过的日期表
reg as(
	select user_id,
    min(date) as reg_date
    from login
    group by 1
) -- 每个用户的注册日期表

select a.date as date, count(user_id) as new_user
from all_login_date a
left join reg b
on a.date = b.reg_date
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：查询牛客每个日期新用户的次日留存率

with all_login_date as(
	select date
    from login
    group by 1
),-- 所有用户登陆过的日期表
reg as(
	select user_id, min(date) as reg_date
    from login
    group by 1
), -- 每个用户的注册日期表
log as(
	select
    user_id, date as login_date
    from login
    group by 1, 2
) -- 每个用户的登陆日期表

# 查询所有信息
select *
# 查询要求信息
select a.date as date,
round(ifnull(count(distinct c.user_id) / count(distinct b.user_id), 0), 3) as p
from all_login_date a
left join reg b
on a.date = b.reg_date
left join log c
on b.user_id = c.user_id
and b.reg_date = date_sub(c.login_date, interval 1 day)
group by 1
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：查询牛客网每天的日活数及新用户占比

with reg as(
    select 
    uid, 
    date(min(in_time)) as reg_date
    from tb_user_log
    group by 1
), -- 用户注册表
log as(
    select
    uid,
    date(in_time) as login_date
    from tb_user_log
    union
    select
    uid,
    date(out_time) as login_date
    from tb_user_log
) -- 用户登陆表

select
*,
login_date,
count(distinct l.uid) as dau,
round(count(distinct r.uid) / count(distinct l.uid), 2) as uv_new_ratio
from log l
left join reg r
on l.uid = r.uid
and l.login_date = r.reg_date
group by 1
order by 1

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

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

① 建立一个表，以统计每篇文章 / 每个直播间的不同时刻的人数增加和人数减少的情况，要用到 union all 语法（注意：要新增一列，每个时刻若人数增加则设为 1，若人数减少则设为 -1）
② 使用 sum 的窗口函数统计每篇文章 / 每个直播间同一时刻的浏览人数 / 观看人数（注意：以文章 ID / 直播间 ID 作为分区即可，并按照时刻升序排列，① 中新增的那一列降序排列）

例3：查询每篇文章同一时刻最大在看人数

with a as(
	select uid, artical_id, in_time as dt, 1 as is_in
    from tb_user_log
    union all
	select uid, artical_id, out_time as dt, -1 as is_in
    from tb_user_log
)

select artical_id, max(uv) as max_uv
from(
	select artical_id, dt,
	sum(is_in) over(partition by artical_id order by dt, is_in desc) as uv
	from a
    where artical_id <> 0
) b
group by 1
order by 2 desc