题目来源:meta。
1 题目
有好友关系表t_friend,记录了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 |
+-----------+-----------+
2 建表语句
--建表语句
CREATE TABLE t_friend(
user1_id bigint COMMENT '用户1ID',
user2_id bigint COMMENT '用户2ID'
) COMMENT '好友关系表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
-- 插入数据
insert into t_friend(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)
3 题解
(1)user1_id,user2_id互换,然后进行union all。
select user1_id,
user2_id
from t_friend
union all
select user2_id,
user1_id
from t_friend
执行结果
+---------------+---------------+
| _u1.user1_id | _u1.user2_id |
+---------------+---------------+
| 1 | 2 |
| 2 | 1 |
| 1 | 3 |
| 3 | 1 |
| 1 | 4 |
| 4 | 1 |
| 1 | 5 |
| 5 | 1 |
| 2 | 3 |
| 3 | 2 |
| 2 | 4 |
| 4 | 2 |
| 3 | 4 |
| 4 | 3 |
| 4 | 5 |
| 5 | 4 |
| 5 | 6 |
| 6 | 5 |
| 5 | 7 |
| 7 | 5 |
| 7 | 8 |
| 8 | 7 |
| 9 | 10 |
| 10 | 9 |
+---------------+---------------+
(2)计算每个用户的好友数,开窗计算出总用户数
with tmp as
(select user1_id,
user2_id
from t_friend
union all
select user2_id,
user1_id
from t_friend)
select user1_id,
count(user2_id) as friend_cnt,
count(distinct user1_id) over () as total_cnt
from tmp
group by user1_id
执行结果
+-----------+-------------+------------+
| user1_id | friend_cnt | total_cnt |
+-----------+-------------+------------+
| 10 | 1 | 10 |
| 9 | 1 | 10 |
| 8 | 1 | 10 |
| 7 | 2 | 10 |
| 6 | 1 | 10 |
| 5 | 4 | 10 |
| 4 | 4 | 10 |
| 3 | 3 | 10 |
| 2 | 3 | 10 |
| 1 | 4 | 10 |
+-----------+-------------+------------+
注意:
COUNT(DISTINCT user1_id) OVER () AS total_cnt
:这是一种窗口函数用法,它会计算整个结果集中 user1_id 的不同值的数量,并且将这个数量作为一个列值添加到每一行。COUNT(DISTINCT user1_id) AS total_cnt
:这是一个聚合函数用法,它只会计算分组后的每组中 user1_id 的不同值的数量。
(3)用户好友数除以总用户数计算最终结果
with tmp as
(select user1_id,
user2_id
from t_friend
union all
select user2_id,
user1_id
from t_friend)
select user1_id,
count(user2_id) /
count(distinct user1_id) over () res
from tmp
group by user1_id
执行结果
+-----------+------+
| user1_id | res |
+-----------+------+
| 10 | 0.1 |
| 9 | 0.1 |
| 8 | 0.1 |
| 7 | 0.2 |
| 6 | 0.1 |
| 5 | 0.4 |
| 4 | 0.4 |
| 3 | 0.3 |
| 2 | 0.3 |
| 1 | 0.4 |
+-----------+------+