作者:whisper
链接:http://proprogrammar.com:443/article/635
声明:请尊重原作者的劳动,如需转载请注明出处
在 Facebook 或者 Twitter 这样的社交应用中,人们经常会发好友申请也会收到其他人的好友申请。现在给如下两个表:
表: friend_request
| sender_id | send_to_id |request_date| |-----------|------------|------------| | 1 | 2 | 2016_06-01 | | 1 | 3 | 2016_06-01 | | 1 | 4 | 2016_06-01 | | 2 | 3 | 2016_06-02 | | 3 | 4 | 2016-06-09 |
表:
request_accepted
| requester_id | accepter_id |accept_date | |--------------|-------------|------------| | 1 | 2 | 2016_06-03 | | 1 | 3 | 2016-06-08 | | 2 | 3 | 2016-06-08 | | 3 | 4 | 2016-06-09 | | 3 | 4 | 2016-06-10 |
写一个查询语句,求出好友申请的通过率,用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。
对于上面的样例数据,你的查询语句应该返回如下结果。
|accept_rate| |-----------| | 0.80|
注意:
通过的好友申请不一定都在表 friend_request 中。在这种情况下,你只需要统计总的被通过的申请数(不管它们在不在原来的申请中),并将它除以申请总数,得到通过率
一个好友申请发送者有可能会给接受者发几条好友申请,也有可能一个好友申请会被通过好几次。这种情况下,重复的好友申请只统计一次。
如果一个好友申请都没有,通过率为 0.00 。解释: 总共有 5 个申请,其中 4 个是不重复且被通过的好友申请,所以成功率是 0.80 。
进阶:
你能写一个查询语句得到每个月的通过率吗?
你能求出每一天的累计通过率吗?
select case re.re_num when 0 then 0.00 else round(ac.ac_num / re.re_num, 2) end accept_rate
from (
select count(distinct requester_id, accepter_id) ac_num
from request_accepted
) ac,
(
select count(distinct sender_id, send_to_id) re_num
from friend_request
) re
接受的一定来自请求的,学习case when else end的用法,和多列distinct(类似还有多列in)
看另一种解法
select
round(
ifnull(
(select count(*) from (select distinct requester_id, accepter_id from request_accepted) as A)
/
(select count(*) from (select distinct sender_id, send_to_id from friend_request) as B),
0)
, 2) as accept_rate;
用ifnull代替了case when else end,学一下ifnull(if)的用法
在 Facebook 或者 Twitter 这样的社交应用中,人们经常会发好友申请也会收到其他人的好友申请。
表 request_accepted 存储了所有好友申请通过的数据记录,其中, requester_id 和 accepter_id 都是用户的编号。
| requester_id | accepter_id | accept_date| |--------------|-------------|------------| | 1 | 2 | 2016_06-03 | | 1 | 3 | 2016-06-08 | | 2 | 3 | 2016-06-08 | | 3 | 4 | 2016-06-09 |
写一个查询语句,求出谁拥有最多的好友和他拥有的好友数目。对于上面的样例数据,结果为:
| id | num | |----|-----| | 3 | 3 |
注意:
保证拥有最多好友数目的只有 1 个人。
好友申请只会被接受一次,所以不会有 requester_id 和 accepter_id 值都相同的重复记录。解释:
编号为 '3' 的人是编号为 '1','2' 和 '4' 的好友,所以他总共有 3 个好友,比其他人都多。
进阶:
在真实世界里,可能会有多个人拥有好友数相同且最多,你能找到所有这些人吗?
#官方题解,成为好友是相互的,一条记录会增加两个好友
#模板:两列是同性质的数据,如学生id,值可能相同,求每个值出现的次数
select friend2.friend_id id, friend2.friend_count num
from(
select friend.friend_id, count(1) friend_count
from(
#这里union all获取到原始数据
select requester_id friend_id
from request_accepted
union all
select accepter_id
from request_accepted
) friend
group by friend.friend_id
) friend2
order by friend2.friend_count desc
limit 1
一条记录多一对好友,出现一个id(requester_id或accepter_id),多一个好友,学一下union all的用法(不同于union),union all中的第一部分定义列名就可以了,后面部分列名以第一部分为准,count(1)(还有count(id), count(*),count(col))的用法
看另一种解法
select distinct r.requester_id as id,count(r.requester_id)+ifnull(r2.num,0) as num
from request_accepted as r left join (select distinct accepter_id ,count(accepter_id) as num
from request_accepted
group by accepter_id
) as r2
on r.requester_id=r2.accepter_id
group by r.requester_id
order by num desc
limit 1
思想是一样的,就是一个人不论是做为请求者还是接受者都要计算在内,但好像是有问题的,如果一个人只作为接受者而不作为请求者,就查不出来,是不是应该用full join
亲爱的读者:有时间可以点赞评论一下
全部评论