mysql牛客网大厂面试刷题深度解析篇，每日一更（持续更新）

SQL158：
SQL158 每类视频近一个月的转发量/率

select tag,concat(round(sum(if_retweet)/count(1),2)*100,'%')
from tb_user_video_log ul
left join tb_video_info tvi on ul.video_id = tvi.video_id
group by tag

1
2
3
4
5

WHERE DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(ul.start_time)) <= 29
近一个月内的时间 此时将他建成一张表然后进行

#interval作为一个关键字时，表示为时间间隔，
#  常用在date_add()、date_sub()函数中，常用于时间的加减法。 interval ：间隔
#   start_time,interval 30 DAY :开始之前的
# SELECT DATE_SUB(MAX(DATE(start_time)) ：表中最新的一天
#subData ：日期减法
#  subdate(max(start_time,interval 30 DAY))  过去三十天
select DATE(start_time) >  subdate(max(start_time,interval 30 DAY)) FROM tb_user_video_log

select tag,concat(round(sum(if_retweet)/count(1),2)*100,'%') retweet_rate
from tb_user_video_log ul
         left join tb_video_info tvi on ul.video_id = tvi.video_id
WHERE DATE(start_time) > (
    SELECT DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY)
    FROM tb_user_video_log
)
group by tag
order by retweet_rate desc ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SQL159 每个创作者每月的涨粉率及截止当前的总粉丝量

涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。
if_follow-是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注。

前置知识 ： over（）函数的使用

1. 每个月的涨粉率 （加粉- 掉粉）/ 总粉丝量
   肯定需要按月份进行分组 ，然后再计算他的 涨粉率
2. 当前总粉丝量就是count（*）

// 按照月份进行分组
where year(start_time)=2021
group by author,month

// 涨粉率
 round( // 保留三位小数 
       sum( if(log.if_follow=2,-1,if_follow))/count(author),3) fans_growth_rate

// 总粉丝量(方案 )
//方案1：
sum(
      sum(// 分区函数的累加规则
         case when if_follow=1 then 1
               when if_follow=2 then -1
                else 0 end))
                // 
           over(partition by author order by date_format(start_time,'%Y-%m')) total_fans

// 方案2：
 先对作者进行分区 ， 然后对月份进行排序
SELECT author,
       month,
       ROUND(fans_add_count / pv_count,3) AS fans_growth_rate,
       SUM(fans_add_count) over(partition by author order by month) AS total_fans
       FROM
(
SELECT b.author, 
       DATE_FORMAT(a.start_time,'%Y-%m') AS month,
       SUM(IF(a.if_follow = 2,-1,a.if_follow)) AS fans_add_count,
       COUNT(a.video_id) AS pv_count
FROM tb_user_video_log AS a 
     JOIN 
     tb_video_info b
	 USING(video_id)
    WHERE YEAR(a.start_time) = 2021
GROUP BY b.author,month
) AS author_monthly_fans_play_cnt
ORDER BY author,total_fans;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

SQL162 2021年11月每天的人均浏览文章时长

统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序。

分析： 人均 浏览文章时长（秒数）：总时长/ 人头数
2021年11月每天的人均浏览文章时长（秒数），需要按照天数进行分组
并按时长由短到长排序。普通的排序字段

select  round(sum(timestampdiff(second,in_time,out_time)) / count(distinct uid),1) avg_lensec,
      date(in_time) day
from tb_user_log
where date_format(in_time,'%Y-%m') = '2021-11' and artical_id != 0
group by day
order by avg_lensec;

1
2
3
4
5
6
7

SQL168 计算商城中2021年每月的GMV

超连接

1. 按照月份进行分组
2. 订单状态不等于 2

 select DATE_FORMAT(event_time,'%Y-%m') month, sum(total_amount) as GMV
 from tb_order_overall
      # 按照月份进行分组
 where year(event_time) = 2021 and status != 2
 group by month ;
1
2
3
4
5

完整代码

select DATE_FORMAT(event_time, "%Y-%m") as `month`,
    ROUND(sum(total_amount), 0) as GMV
from tb_order_overall
where status != 2 and YEAR(event_time) = 2021
group by `month`
having GMV > 100000
order by GMV;
1
2
3
4
5
6
7

SQL169 统计2021年10月每个退货率不大于0.5的商品各项指标

商品点展比=点击数÷展示数；
加购率=加购数÷点击数；
成单率=付款数÷加购数；退货率=退款数÷付款数，
当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。

先简单提纯 计算商品点展比

select product_id, round(temp.clickNum/temp.show_cnt,2) as clirate
from (select  product_id,
              COUNT(1) as show_cnt,
          sum(if_click)   as clickNum,
          sum(if_cart)    as cartNum,
          sum(if_payment) as payNum,
          sum(if_refund)  as refundNum
      from tb_user_event
     group by product_id
    ) as  temp;

1
2
3
4
5
6
7
8
9
10
11

再加上第二个条件

select product_id, round(temp.clickNum/temp.show_cnt,2) as clirate,
       round(if(cartNum>0,cartNum/show_cnt,0),2) as carate
from (select  product_id,
              COUNT(1) as show_cnt,
          sum(if_click)   as clickNum,
          sum(if_cart)    as cartNum,
          sum(if_payment) as payNum,
          sum(if_refund)  as refundNum
      from tb_user_event
     group by product_id
    ) as  temp;


1
2
3
4
5
6
7
8
9
10
11
12
13

select product_id, round(click_cnt/show_cnt, 3) as ctr,
    round(IF(click_cnt>0, cart_cnt/click_cnt, 0), 3) as cart_rate,
    round(IF(cart_cnt>0, payment_cnt/cart_cnt, 0), 3) as payment_rate,
    round(IF(payment_cnt>0, refund_cnt/payment_cnt, 0), 3) as refund_rate
from (
    select product_id, COUNT(1) as show_cnt,
        sum(if_click) as click_cnt,
        sum(if_cart) as cart_cnt,
        sum(if_payment) as payment_cnt,
        sum(if_refund) as refund_cnt
    from tb_user_event
    where DATE_FORMAT(event_time, '%Y%m') = '202110'
    group by product_id
) as t_product_index_cnt
where payment_cnt = 0 or refund_cnt/payment_cnt <= 0.5
order by product_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SQL164 2021年11月每天新用户的次日留存率

刷题地址

1. 先按照天数进行分组

select *
from (select uid
           ,min(date(in_time)) dt
      from tb_user_log
      group by uid) as t1  -- 每天新用户表
         left join (select uid , date(in_time) dt
                    from tb_user_log
                    union
                    select uid , date(out_time)
                    from tb_user_log ) as t2 -- 用户活跃表
 on t1.uid = t2.uid
     and t1.dt=date_sub(t2.dt,INTERVAL 1 day);
1
2
3
4
5
6
7
8
9
10
11
12