一、题目
有好友关系表t_friend_031，记录了user1_id,user2_id的好友关系对。现定义用户受欢迎程度=用户拥有的朋友总数/平台上的用户总数,请计算出每个用户的受欢迎程度。
+-----------+-----------+| user1_id | user2_id |+-----------+-----------+| 1 | 2 || 1 | 3 || 1 | 4 || 1 | 5 || 2 | 3 || 2 | 4 || 3 | 4 || 4 | 5 || 5 | 6 || 5 | 7 || 7 | 8 || 9 | 10 |+-----------+-----------+
二、分析
1.题目中数据user1_id,user2_id为互为好友关系，为关系对，即1与2是好友关系，则1-2,2-1记录只会存在一条，为方便计算，我们需要有两条记录。所以将user2_id与user1_id 互换，然后与原表进行union all;
2.对union all后的user1_id进行去重，统计用户个数，即：平台上的用户总数；
3.对union all后的数据，按照user1_id分组，统计user2_id的个数，即user1_id 的好友数据；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1）user1_id,user2_id互换，然后进行union all。
selectuser1_id,user2_idfrom t_friend_031union allselectuser2_id,user1_idfrom t_friend_031
查询结果
2)计算全部用户数
with tmp as(selectuser1_id,user2_idfrom t_friend_031union allselectuser2_id,user1_idfrom t_friend_031)select count(distinct user1_id)from tmp
查询结果
3.计算每个用户的好友数
with tmp as(selectuser1_id,user2_idfrom t_friend_031union allselectuser2_id,user1_idfrom t_friend_031)select user1_id,count(user2_id) as friend_cntfrom tmpgroup by user1_id
查询结果
4)关联查询，用户好友数除以总用户数计算最终结果
with tmp as(selectuser1_id,user2_idfrom t_friend_031union allselectuser2_id,user1_idfrom t_friend_031)selectuser1_id,friend_cnt/total_cnt as resfrom(--每个用户好友数量selectuser1_id,count(user2_id) as friend_cntfrom tmpgroup by user1_id) t1join(--总用户数selectcount(distinct user1_id) total_cntfrom tmp)t2
查询结果
四、建表语句和数据插入
--建表语句CREATE TABLE t_friend_031 (user1_id bigint COMMENT '用户1ID',user2_id bigint COMMENT '用户2ID') COMMENT '好友关系表'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';-- 插入数据insert into t_friend_031(user1_id,user2_id)values(1,2),(1,3),(1,4),(1,5),(2,3),(2,4),(3,4),(4,5),(5,6),(5,7),(7,8),(9,10)
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
