牛客题库复盘

1.查找用户最近一次登录的日期

错解：select user.name as u_n,client.name as c_n,max(login.date)
from login
left join user 
on login.id=user.id
left join client 
on login.id=client.id
group by login.user_id 
order by user.name

错因分析：select是基于group by 得到的数据进行查询，select只能包含group by后的项与聚合函数，group by的数据范围>=select的数据范围

正解：select user.name as u_n,client.name as c_n,login.date
from login
left join user 
on login.user_id=user.id
left join client 
on login.client_id=client.id
where (login.user_id,login.date) in (select user_id,max(date) from login group by login.user_id) 
order by user.name

2.统计每个日期登录的新用户个数

  法一：select date,count(b.user_id)as new
 from (select distinct date from login)a
 left join (select user_id,min(date)first_date from login group by user_id)b
 on a.date=b.first_date
 group by a.date
 order by a.date

先对login表中的日期去重，

再左联接新用户登录日期表，筛选条件为日期

法二：select t.date,sum(case when t.r_rank=1 then 1 else 0 end)as new
from 
(select date,row_number()over(partition by user_id order by date)as r_rank
from login)t 
group by date

提取出日期并利用窗口函数对日期排序，利用case when条件筛选出新用户个数

3.计算新用户的次日留存率

select round(count(b.user_id)/count(a.user_id),3)as p
from 
(select user_id,min(date)as first_date from login group by user_id) a
left join
(select user_id,date from login group by user_id) b 
on a.user_id=b.user_id and b.date=date_add(a.first_date,interval 1 day)

思路分析：先查询新用户放入表格1中，
再将表格1与原表联接，筛选条件为日期，查询得第二天登录的新用户

4.删除重复行

错解：delete from sale 
where user_id not in 
(select max(user_id)as user
from sale
group by user_id,order_id,event_time,product_id
having count(user_id)>1)t;

分析：更新这个表的同时又查询了这个表，查询这个表的同时又去更新了这个表，可以理解为死锁。mysql不支持这种更新查询同一张表的操作

解决办法：把要更新的几列数据查询出来做为一个第三方表，然后筛选更新。

delete from sale 
where user_id not in 
(select t.user 
from
(select max(user_id)as user
from sale
group by user_id,order_id,event_time,product_id
having count(user_id)>1)t );

5.已知每个用户每天的刷题数量，计算“截止到某日期，每个用户的刷题数量”

用窗口函数按姓名分区日期排序计算累计数

sum(number)over(partition by user_id order by date)