mysql刷题思路记录 2021.04.03

MYSQL题解

牛客第70题：
查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序；

select date,ifnull(round
   ( (sum(case when (user_id,date)in(
            select user_id,date_add(date,interval -1 day)
            from login
            group by user_id
    ) then 1 else 0 end))/
    (sum(
        case when (user_id,date)in(
            select user_id,min(date)
            from login
            group by user_id
        ) then 1 else 0 end)),3),0) as p
from login
group by date
order by date asc;

思路：
IFNULL(次日留存率，0)，次日留存率中分子为在该date和date的后一天连续两天登录的用户数，date_add(date,interval -1 day)表示date的前一天，分母为date的新登录用户数；

IFNULL函数：
IFNULL(expression_1,expression_2)
如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果；

DATE_ADD函数：
DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式，expr 参数是希望添加的时间间隔，type 参数可以是day、week、hour等；

牛客第71题：
查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出；

select u.name u_n,l.date,p.nsum ps_num
from login l
join user u
on l.user_id = u.id
join (
    select p1.user_id,p1.date,sum(p2.number) nsum
    from passing_number p1
    join passing_number p2
    on p1.user_id = p2.user_id and p1.date >= p2.date
    group by user_id,date
) p
on l.user_id = p.user_id and l.date = p.date
group by l.date,u.name
order by l.date asc,u.name asc;

思路：
子查询中用两张passing_number表分别记为p1、p2来作连接，连接条件为p1表与p2表的user_id要相同，并且相同user_id的记录p1表相应的登陆日期要大于等于p2表，连接后表p1中的记录会与表p2中的记录存在一对多的关系，按p1表的user_id和date来分组，再将p2表中的number按分组作加总，得到每个用户不同登录日期下的累计通过数；

子查询中，p1表中id=4的记录会对应到p2表中id=2、4的两条记录

牛客第74题：
找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序；

select g.id,l.name,g.score
from grade g
join(
    select id,dense_rank()
    over(partition by language_id order by score desc) s_rank
    from grade
) s
on g.id = s.id and s.s_rank <=2
join language l
on l.id = g.language_id
order by l.name asc,g.score desc,g.id asc;

思路：
按language_id分组，按分数降序排列，利用窗口函数dense_rank()排序记为s_rank，与grade表作内连接，连接条件为两表id一致且相应分数的排序小于等于2，此时筛选出的表格就只包含每个类别排名前2的记录，再与language表作内连接，依次按name、score、id进行排序；

窗口函数：
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中；

语法：
函数名([参数]) over(partition by [分组字段] order by [排序字段] asc/desc rows/range between 起始位置 and 结束位置)

函数分为两个部分：
一、函数名称：开窗函数的数量较少，只有11个窗口函数+聚合函数（所有聚合函数都可以用作开窗函数）；
二、over语句：over()是必须要写的，里面有三个参数，都是非必须参数，根据需求选写：

• List item 第一个参数是 partition by +分组字段，将数据根据此字段分成多份，如果不加partition by参数，那会把整个数据当做一个窗口；
• 第二个参数是 order by +排序字段，每个窗口的数据要不要进行排序；
• 第三个参数 rows/range between 起始位置 and 结束位置，这个参数仅针对滑动窗口函数有用，是在当前窗口下分出更小的子窗口；

窗口函数rank()、dense_rank()、row_number()的区别：
1.rank()是排名函数，不需要参数，但需要指定按照那个字段进行排名，所以使用rank函数必须用order by参数(1,1,3,3,5)；
2.当出现名次并列时，使用dense_rank()函数可以让下一个人的名次是连续的(1,1,2,2,3)；
3.row_number连续排名(1,2,3,4,5)；