HiveSQL-面试题031 计算每个用户的受欢迎程度

数据仓库晨曦

发布于 2024-01-08 15:46:45

1480

发布于 2024-01-08 15:46:45

一、题目

有好友关系表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。

select 
user1_id,
user2_id
from t_friend_031
union all
select 
user2_id,
user1_id
from t_friend_031

查询结果

2)计算全部用户数

with tmp as
(select 
user1_id,
user2_id
from t_friend_031
union all
select 
user2_id,
user1_id
from t_friend_031)
select count(distinct user1_id)
from tmp

查询结果

3.计算每个用户的好友数

with tmp as
(select 
user1_id,
user2_id
from t_friend_031
union all
select 
user2_id,
user1_id
from t_friend_031)
select user1_id,count(user2_id) as friend_cnt
from tmp
group by user1_id

查询结果

4)关联查询，用户好友数除以总用户数计算最终结果

with tmp as
(select 
user1_id,
user2_id
from t_friend_031
union all
select 
user2_id,
user1_id
from t_friend_031)
select
user1_id,
friend_cnt/total_cnt as res
from
(
--每个用户好友数量
select
user1_id,
count(user2_id) as friend_cnt
from tmp
group by user1_id
) t1
join
(
--总用户数
select
count(distinct user1_id) total_cnt
from 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)

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-08-22，如有侵权请联系 cloudcommunity@tencent.com 删除

friend