sql题解及笔记257-268

257 dense_rank排序

select id, number,
  dense_rank() over (order by number desc) t_rank
from passing_number

rank是mysql8.0的关键字，所以不能直接使用rank，要用" ` "号包裹，或者使用t_rank

排序函数row_number、rank、dense_rank的区别

• ROW_NUMBER()：12345
• RANK()：11345
• DENSE_RANK()：11234

258 left join

select p.id,p.name,t.content
from person p
left join task t on p.id = t.person_id
order by p.id asc;

259 异常的邮件概率

正常用户发送邮件失败的数目/发送邮件总数目

select date, round(sum(type = "no_completed") / count(*), 3) as p
from email e
    join user u1 on e.send_id = u1.id
    --u1 表示发件人用户
    join user u2 on e.receive_id = u2.id
    --u2 表示收件人用户
where u1.is_blacklist = 0 and u2.is_blacklist = 0
--确保发件人和收件人都不是黑名单用户
group by date
order by date;

260 每个用户的最后登录时间

select user_id,MAX(date) as d
from login 
group by user_id 
order by user_id;

261 每个用户的最后登录时间和设备

SELECT u.name AS u_n ,c.name AS c_n ,l.date
FROM login l
JOIN user u ON l.user_id = u.id
JOIN client c ON l.client_id=c.id
WHERE (l.user_id,l.date)IN( --作用类似于临时表
  SELECT user_id, MAX(date) FROM login
  GROUP BY user_id)
ORDER BY u_n;

262 新用户的次日留存率

SELECT
ROUND(COUNT(DISTINCT user_id)*1.0 / (SELECT COUNT(DISTINCT user_id) FROM login), 3)
--COUNT(DISTINCT user_id)子查询：还登录的新用户
--* 1.0：将结果转换为浮点数
--SELECT COUNT(DISTINCT user_id) FROM login整个用户群体的大小
--ROUND(..., 3)：四舍五入到小数点后三位
FROM login
WHERE (user_id, date) IN
(SELECT user_id, DATE_ADD(MIN(date),INTERVAL 1 DAY) FROM login GROUP BY user_id);
--还登录的新用户

263 每个日期的新用户个数

select date,
    count(distinct case when (user_id,date) in
    (select user_id,min(date)from login group by user_id)
    then user_id else null end)
    --COUNT(DISTINCT ...)所有符合 CASE 条件的 user_id 数量
    --CASE WHEN (user_id, date) IN (...) THEN user_id ELSE NULL END:是否是这个用户的首次登录日期
    --(SELECT user_id, MIN(date) FROM login GROUP BY user_id)用户的首次登录日期
from login
group by date
order by date;

264 每个日期新用户的次日留存率

SELECT date,IFNULL(ROUND(SUM(CASE WHEN (user_id,date) IN
(SELECT user_id,DATE_ADD(date,INTERVAL -1 DAY) FROM login) --子查询找到用户的前一天是否登录
AND (user_id,date) IN (SELECT user_id,MIN(date) FROM login GROUP BY user_id) --子查询找到每个 user_id 的首次登录日期
THEN 1 ELSE 0 END)/ --两个条件同时满足时返回 1
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
--ROUND(SUM(条件1)/SUM(条件2), 3)
--（首次登录后第二天登录的用户）占满足条件2（当天首次登录的用户）的比例，并将结果四舍五入到小数点后三位。
FROM login
GROUP BY date
ORDER BY date;

此题太复杂了可跳过

265 每个用户刷题信息

SELECT name AS u_n,date,
    SUM(number) OVER (PARTITION BY user_id ORDER BY date) AS ps_num
    --每个用户在该日期及之前所有 number 值的和
FROM passing_number  p
INNER JOIN user ON user.id=p.user_id --没刷题不用统计
GROUP BY date,u_n
ORDER BY date,name

内连接+窗口

266 各个岗位分数的平均数

SELECT job, 
ROUND(AVG(score),3) AS avg 
FROM grade 
GROUP BY job 
ORDER BY avg DESC;

267 查询大于（分组后平均值）

SELECT g.id,g.job,g.score
FROM grade g
JOIN (SELECT job,AVG(score) AS avg_sco
            FROM grade 
            GROUP BY job) t
ON g.job = t.job --临时表t部门平均分
WHERE g.score > t.avg_sco;

窗口函数

SELECT t.id,t.job,t.score
FROM (SELECT *,AVG(score) OVER(PARTITION BY job) AS avg_sco 
      FROM grade) t
WHERE t.score > t.avg_sco
ORDER BY t.id ASC;

268 每个岗位分数排名前2的用户

SELECT a.id, l.name, a.score
FROM language l
JOIN (SELECT id, language_id, score, dense_rank()over(PARTITION BY language_id ORDER BY score DESC) AS rank_num FROM grade) a
--临时表a：每个 language_id 分组内的 score 排名
--dense_rank()over(PARTITION BY language_id ORDER BY score DESC) AS rank_num
--每个language_id分组内，score列降序排名，且没有排名跳跃，记为rank_num
ON l.id = a.language_id
WHERE rank_num <=2--保留排名前 2 的记录
ORDER BY l.name, a.score DESC, a.id;