练习2
题目1：
假设云音乐数据库里面现在有几张如下简化的数据表:
关注follow表,第一列是关注人的id,第二列是被关注人的id,这2列的id组成主键
|
user_id |
follower_id |
|
1 |
2 |
|
1 |
4 |
|
2 |
3 |
这张表的第一行代表着用户id为1的关注着id为2的用户
这张表的第二行代表着用户id为1的关注着id为4的用户
这张表的第三行代表着用户id为2的关注着id为3的用户
个人的喜欢的音乐music_likes表,第一列是用户id,第二列是喜欢的音乐id,这2列的id组成主键
|
user_id |
music_id |
|
1 |
17 |
|
2 |
18 |
|
2 |
19 |
|
3 |
20 |
|
4 |
17 |
这张表的第一行代表着用户id为1的喜欢music_id为17的音乐
....
这张表的第五行代表着用户id为4的喜欢music_id为17的音乐
音乐music表，第一列是音乐id，第二列是音乐name,id是主键
|
id |
music_name |
|
17 |
yueyawang |
|
18 |
kong |
|
19 |
MOM |
|
20 |
Sold Out |
请你编写一个SQL，查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。
不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。你返回的结果中不应当包含重复项
上面的查询结果如下:
|
music_name |
|
kong |
|
MOM |
#方法一
select distinct m.music_name
from follow f join music_likes ml on ml.user_id=f.follower_id
join music m on ml.music_id=m.id
where music_id not in (
select music_id
from music_likes ml2
where user_id=1
)
and f.user_id=1
order by m.id
#方法二
SELECT distinct m.music_name
FROM follow f, music_likes ml, music m
WHERE f.follower_id=ml.user_id and ml.music_id=m.id
and f.user_id=1
and m.id NOT IN (select music_id
from music_likes
where user_id=1)
ORDER BY m.id;
分析思路：
第一步：将三个表进行连接，找到关注人喜欢的音乐
第二步：筛选用户id为1
第三步：去除用户喜欢过的电影
第四步：对关注人喜欢的音乐去重（可能存在关注人同时喜欢某一首音乐）
题目2：
现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下:
|
id |
send_id |
receive_id |
type |
date |
|
1 2 3 4 5 6 |
2 1 1 3 3 4 |
3 3 4 1 4 1 |
completed completed no_completed completed completed completed |
2020-01-11 2020-01-11 2020-01-11 2020-01-12 2020-01-12 2020-01-12 |
第1行表示为id为2的用户在2020-01-11成功发送了一封邮件给了id为3的用户;
第3行表示为id为1的用户在2020-01-11没有成功发送一封邮件给了id为4的用户;
第6行表示为id为4的用户在2020-01-12成功发送了一封邮件给了id为1的用户;
下面是一个用户(user)表，id为主键（注意这里id代表用户编号），is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户，简况如下:
|
id |
is_blacklist |
|
1 2 3 4 |
0 1 0 0 |
第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户，是黑名单用户，如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户
...
第4行表示id为4的是正常用户
现在让你写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:
|
date |
p |
|
2020-01-11 2020-01-12 |
0.500 0.000 |
结果表示:
2020-01-11失败的概率为0.500，因为email的第1条数据，发送的用户id为2是黑名单用户，所以不计入统计，正常用户发正常用户总共2次，但是失败了1次，所以概率是0.500;
2020-01-12没有失败的情况，所以概率为0.000.
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)
方法一：
#select e.date
# ,round(sum(case type when 'no_completed' then 1 else 0 end)/count(id),3) as p
#from email e
#where send_id in(select id from user where is_blacklist=0)
#and receive_id in(select id from user where is_blacklist=0)
#group by e.date
#order by e.date;
方法二：
select date,round(avg(case when type='completed' then 0 else 1 end ),3)
from email
where send_id in
(select send_id
from email,user
where send_id=user.id
and is_blacklist=0
)
and receive_id in
(select receive_id
from email,user
where receive_id=user.id
and is_blacklist=0
)
group by date
分析思路：
第一步：找到正常发送的用户
第二步：找到正常接收的用户
第三步：发送失败为1，成功为0，直接avg
题目3：
有一个薪水表salaries简况如下:
|
emp_no |
salary |
from_date |
to_date |
|
10001 |
88958 |
2002-06-22 |
9999-01-01 |
|
10002 |
72527 |
2001-08-02 |
9999-01-01 |
|
10003 |
43311 |
2001-12-01 |
9999-01-01 |
请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，
若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。
|
emp_no |
salary |
|
10002 |
72527 |
方法一：
select t.emp_no,t.salary from
(
select emp_no,salary,dense_rank() over (order by salary desc) ranking
from salaries) t
where t.ranking = 2
注意：不用加partition by 加了是输出每组第二，不加是输出全部的第二
方法二：
SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT salary
FROM salaries
WHERE to_date = '9999-01-01'
GROUP BY salary
ORDER BY salary DESC LIMIT 1,1 )
错误解法：
如果有两个并列第一，两个并列第二：rank()是跳跃排序，结果是1133；dense_rank()是连续排序，结果是1122；row_number连续且唯一，结果是1234。所以dense_rank()最合适。
经典错误一：row_number开窗函数(当薪水第二的人有多个时只会输出一个)
|
1 2 3 4 5 6 |
select emp_no,salary from (select emp_no,salary,row_number() over(order by salary desc) t from salaries where to_date='9999-01-01') where t=2 |
经典错误二：rank开窗函数(当薪水为1133情况时，输出为空)
|
1 2 3 4 5 6 |
select emp_no,salary from (select emp_no,salary,rank() over(order by salary desc) t from salaries where to_date='9999-01-01') where t=2 |
题目四：
用户行为日志表tb_user_log
|
id |
uid |
artical_id |
in_time |
out_time |
sign_cin |
|
1 |
101 |
9001 |
2021-10-31 10:00:00 |
2021-10-31 10:00:09 |
0 |
|
2 |
102 |
9001 |
2021-10-31 10:00:00 |
2021-10-31 10:00:09 |
0 |
|
3 |
101 |
0 |
2021-11-01 10:00:00 |
2021-11-01 10:00:42 |
1 |
|
4 |
102 |
9001 |
2021-11-01 10:00:00 |
2021-11-01 10:00:09 |
0 |
|
5 |
108 |
9001 |
2021-11-01 10:00:01 |
2021-11-01 10:00:50 |
0 |
|
6 |
108 |
9001 |
2021-11-02 10:00:01 |
2021-11-02 10:00:50 |
0 |
|
7 |
104 |
9001 |
2021-11-02 10:00:28 |
2021-11-02 10:00:50 |
0 |
|
8 |
106 |
9001 |
2021-11-02 10:00:28 |
2021-11-02 10:00:50 |
0 |
|
9 |
108 |
9001 |
2021-11-03 10:00:01 |
2021-11-03 10:00:50 |
0 |
|
10 |
109 |
9002 |
2021-11-03 11:00:55 |
2021-11-03 11:00:59 |
0 |
|
11 |
104 |
9003 |
2021-11-03 11:00:45 |
2021-11-03 11:00:55 |
0 |
|
12 |
105 |
9003 |
2021-11-03 11:00:53 |
2021-11-03 11:00:59 |
0 |
|
13 |
106 |
9003 |
2021-11-03 11:00:45 |
2021-11-03 11:00:55 |
0 |
（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）
问题：统计每天的日活数及新用户占比
注：
- 新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
- 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。
- 新用户占比保留2位小数，结果按日期升序排序。
方法一：
select 日期,
count(distinct uid),
round(sum(case when ranking=1 then 1 else 0 end)/count(distinct uid),2)
from
(select uid,
date_format(time,'%Y-%m-%d') as '日期',
dense_rank() over(partition by uid order by time) as ranking
from
(
select uid,in_time as time from tb_user_log a
union
select uid,out_time as time from tb_user_log b
) t1
) t2
group by 日期
分析思路：
第一步：in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。所以需要把in_time和out_time 当成共同的时间来分析，用union
第二步：新增用户的窗口函数解法，对用户进行日期排序，排名为1对应的日期就是最早登陆日期
第三步：计算去重的用户数就是日活数量，当天排名为1的用户数就是新增用户数
第四步：日期分组，小数点位数，日期排序等等。
方法二：同样的道理
select 日期,
count(distinct uid),
round(sum(case when 最早登陆=日期 then 1 else 0 end)/count(distinct uid),2)
from
(select uid,日期,
min(time) over(partition by uid ) as '最早登陆'
from
(
select uid,date_format(in_time,'%Y-%m-%d') as '日期' from tb_user_log a
union
select uid,date_format(out_time,'%Y-%m-%d') as '日期' from tb_user_log b
) t1
) t2
group by 日期
方法三：左连接的方法但是当数据量大的时候可能效率低
select dt, count(t1.uid) dau, round(sum(if(new_time=dt,1,0))/count(t1.uid),2)
from
(select uid, date(in_time) dt from tb_user_log
union
select uid,date(out_time) dt from tb_user_log
) t1
join
(
select uid,min(date(in_time)) new_time from tb_user_log
group by uid
) t2
on t1.uid = t2.uid
group by dt
order by dt
浙公网安备 33010602011771号