当前位置：首页 > news >正文

SQL面试题4：相互关注问题

news 2025/6/8 13:04:18

引言

在社交媒体和各类社区平台蓬勃发展的当下，用户之间的关系网络成为了平台运营和数据分析的关键部分。相互关注作为一种重要的社交关系，不仅反映了用户之间的紧密程度，还对平台的社交生态、内容传播等方面有着深远影响。本文将聚焦于 SQL 在处理相互关注问题上的应用，帮助大家深入理解如何通过 SQL 语言来分析和挖掘这类社交关系数据，为相关从业者应对面试以及实际工作中的数据处理需求提供有力支持。

场景介绍

（一）社交平台的发展与相互关注的重要性

如今，社交平台已经渗透到人们生活的方方面面。从以分享生活点滴为主的朋友圈，到专注于知识交流的知乎，再到以兴趣爱好为纽带的抖音等平台，用户数量数以亿计。在这些平台中，相互关注的关系构建起了一个个复杂的社交圈子。对于平台运营者而言，了解用户之间的相互关注情况，可以更好地进行用户画像分析、个性化推荐内容，提升用户体验和平台活跃度。例如，在电商社交平台上，相互关注的用户之间可能有着相似的购物偏好，平台可以根据这一特点向他们推荐相关商品，促进交易转化。对于内容创作者来说，粉丝之间的相互关注关系有助于内容的快速传播，扩大影响力。

（二）相互关注问题在数据分析中的价值

从数据分析的角度来看，相互关注数据蕴含着丰富的信息。通过分析相互关注的用户群体特征，可以发现不同兴趣群体的聚集模式，为市场细分提供依据。同时，研究相互关注关系的动态变化，如新增相互关注的趋势、某些用户群体之间相互关注的增长速度等，能够及时洞察平台社交生态的演变，为平台的战略决策提供数据支撑。

题目描述

现有一份用户关注关系的数据表，记录了用户之间的关注行为信息，包含以下字段：
follower_id：表示关注者的用户编号，用于唯一标识发起关注行为的用户。
followed_id：表示被关注者的用户编号，用于唯一标识受到关注的用户。
根据上述信息，需要完成以下任务：

统计平台上相互关注的用户对数。
分析每个用户拥有的相互关注好友数量，即有多少其他用户与该用户相互关注。

数据准备与代码实现

数据准备

CREATE TABLE follow_relation_tb (follower_id INT,followed_id INT
);INSERT INTO follow_relation_tb VALUES
(1, 2),
(2, 1),
(1, 3),
(3, 4),
(4, 3),
(5, 6);

1. 统计平台上相互关注的用户对数

思路一：自连接（常规思路）

思路：通过自连接，将原表与自身关联，匹配互为关注关系的记录。
注意：去重，确保每对相互关注用户数只出现一次。

SELECT COUNT(DISTINCT a.follower_id, a.followed_id) AS mutual_follow_count
FROM follow_relation_tb a
JOIN follow_relation_tb b ON a.follower_id = b.followed_id 
AND a.followed_id = b.follower_id
# 去重，确保每对相互关注用户数只出现一次
WHERE a.follower_id < a.followed_id;

思路二：UNION ALL + 分组统计法

思路：将原表与反转后的表合并，统计每组用户对出现的次数为2的记录。

SELECT follower_id,followed_id
FROM (SELECT follower_id, followed_id, COUNT(*) AS cntFROM (SELECT follower_id, followed_id FROM follow_relation_tbUNION ALLSELECT followed_id, follower_id FROM follow_relation_tb) tGROUP BY follower_id, followed_idHAVING cnt = 2
) tmp
WHERE follower_id < followed_id;

思路三：排序拼接法（优化思路）

思路：将用户对按字典序拼接为统一格式，统计出现次数为2的记录。

SELECT distinct CASE WHEN user1 < user2 THEN user1 ELSE user2 END AS user1,CASE WHEN user1 > user2 THEN user1 ELSE user2 END AS user2
FROM (SELECT followed_id AS user1, follower_id AS user2,COUNT(*) OVER (PARTITION BY CONCAT_WS('-',CAST(LEAST(followed_id, follower_id) AS STRING),CAST(GREATEST(followed_id, follower_id) AS STRING))) AS cntFROM follow_relation_tb
) t
WHERE cnt >= 2;

思路四：哈希函数与窗口函数结合（高阶优化）

思路：通过哈希函数生成唯一标识，结合窗口函数判断是否存在互相关注。
具体步骤：先将用户对进行统一的哈希处理，然后统计每个用户对出现的次数，标记出相互关注的用户对，最后筛选出处于相互关注关系中的被关注者的 ID。

SELECT DISTINCT follower_id,followed_id
FROM (SELECT follower_id,followed_id, IF(COUNT(fan_pair) OVER (PARTITION BY fan_pair) >= 2, 1, 0) AS is_mutualFROM (SELECT followed_id,follower_id,IF(followed_id < follower_id, HASH(followed_id, follower_id), HASH(follower_id, followed_id)) AS fan_pairFROM follow_relation_tb) t
) t2
WHERE is_mutual = 1;

四种思路对比

	时间复杂度	空间复杂度	适用场景	优缺点
自连接法	O(n²)	高	小数据量，逻辑简单	优点：逻辑直观，适合数据量较小的场景缺点：自连接可能导致数据膨胀，尤其在大数据量下性能较差。
UNION ALL + 分组	O(nlogn)	中	大数据量，避免JOIN	优点：避免JOIN操作，适合大数据场景缺点：UNION ALL导致数据量翻倍，可能增加计算成本
排序拼接法	O(n)	低	超大数据量，需快速响应	仅需一次表扫描，利用窗口函数减少计算量
哈希与窗口函数结合	O(n)	低	海量数据，高性能要求	利用哈希函数减少字符串拼接开销，性能更优

2. 分析每个用户拥有的相互关注好友数量

步骤与思路：

先通过自连接找到所有相互关注的关系，
然后使用GROUP BY对用户进行分组，再利用COUNT(DISTINCT)函数统计每个用户对应的相互关注好友数量。
注意：由于相互关注关系会在连接结果中出现两次（如用户 1 和用户 2 相互关注，会有(1, 2)和(2, 1)两条记录），所以在统计时要使用DISTINCT避免重复计算。

SELECT a.follower_id AS user_id, COUNT(DISTINCT b.follower_id) AS mutual_follow_friends_count
FROM follow_relation_tb a
JOIN follow_relation_tb b ON a.follower_id = b.followed_id AND a.followed_id = b.follower_id
GROUP BY a.follower_id;

针对用户量较大的表处理相互关注问题的效率优化：索引优化和分区表

索引优化
在 follower_id 和 followed_id 列上创建复合索引。索引可以加快查询时的查找速度，因为数据库可以直接通过索引定位到符合条件的记录，而不需要全表扫描。

CREATE INDEX idx_follow_relation ON follow_relation_tb (follower_id, followed_id);

分区表
如果数据量非常大，可以考虑使用分区表。例如按照 follower_id 的范围进行分区，这样在查询时可以只扫描相关分区，减少扫描的数据量。

-- 创建分区表
CREATE TABLE follow_relation_tb (follower_id INT,followed_id INT
)
PARTITION BY RANGE (follower_id) (PARTITION p0 VALUES LESS THAN (1000),PARTITION p1 VALUES LESS THAN (2000),-- 可以根据实际情况添加更多分区PARTITION pn VALUES LESS THAN MAXVALUE
);

延伸问题

延伸问题1：如何避免重复记录（如(A,B)和(B,A)视为同一对）？

问题场景：查询结果中需要确保每对用户只出现一次（按字典序排列）。
优化点：
- 使用LEAST和GREATEST标准化用户对顺序，避免重复。
- 通过GROUP BY聚合减少数据量，结合HAVING筛选互关对。

SELECTLEAST(followed_id, follower_id) AS user1,GREATEST(followed_id, follower_id) AS user2
FROM follow_relation_tb
GROUP BY user1, user2
HAVING COUNT(DISTINCT CASEWHEN followed_id = user1 THEN follower_idELSE followed_id
END) = 2;

延伸问题2：如何快速判断某个用户（如用户A）的互关用户列表

问题场景：给定用户A，高效查询与其互相关注的用户。
优化点：
- 为(from_user, to_user)建立联合索引，避免全表扫描。
- 使用IN子查询将操作转换为索引覆盖查询。

SELECTfollower_id AS mutual_user
FROM follow_relation_tb
WHERE followed_id = 'A'
AND follower_id IN (SELECT followed_idFROM follow_relation_tbWHERE follower_id = 'A'
);

其他

延伸问题3：如何统计全平台用户的平均互关率？
- 问题场景：计算所有用户中，存在互相关注的用户占比。
延伸问题4：如何在大数据量下分页查询互关用户对？
- 问题场景：分页查询互关用户列表（如每页1000条）。
- 优化点：
  - 用ROW_NUMBER()生成游标替代OFFSET，避免深度分页的性能问题。
  - 预先聚合互关对减少计算量。
延伸问题5：如何实时监控新产生的互关对？
- 问题场景：实时检测新产生的互关关系（如用于消息推送）。
- 优化点：
  - 触发器确保实时性，但需注意高并发下的性能问题。
  - 替代方案：通过消息队列异步处理，降低数据库压力。

高频优化技巧总结

索引设计：
必建索引：(from_user, to_user)的联合索引。
可选优化：为(LEAST(from_user, to_user), GREATEST(from_user, to_user))建立生成列索引。
避免全表扫描：
使用EXISTS替代IN子查询；通过覆盖索引减少回表操作。
分治策略：
按用户ID哈希分桶，并行处理不同桶的数据；使用分区表（如按时间或用户范围分区）。
内存优化：
调整数据库的sort_buffer_size和join_buffer_size；使用临时表存储中间结果。
业务妥协：
异步计算：非实时场景可将结果写入缓存表定期更新。
概率统计：使用APPROX_COUNT_DISTINCT等近似函数加速计算。

面试回答技巧

强调场景适配：如“如果数据量在千万级，我会优先选择分桶+哈希的方式”。
结合执行计划：提到EXPLAIN分析索引使用情况。
容错设计：如处理重复数据、事务隔离级别的影响。
扩展思考：提及NoSQL方案（如Redis的集合操作）作为对比，体现技术广度。

引言