SQL面试题练习 —— 计算每个用户的受欢迎程度

最新推荐文章于 2024-08-14 11:10:56 发布

夏木夕

最新推荐文章于 2024-08-14 11:10:56 发布

阅读量163

点赞数 5

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/hu_wei123/article/details/140018126

版权

SQL 专栏收录该内容

41 篇文章 2 订阅

订阅专栏

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  |
+-----------+------+