手机网站后台,Sensei wordpress插件,做网站专用图标,简单微信小程序制作引言
在社交媒体和各类社区平台蓬勃发展的当下#xff0c;用户之间的关系网络成为了平台运营和数据分析的关键部分。相互关注作为一种重要的社交关系#xff0c;不仅反映了用户之间的紧密程度#xff0c;还对平台的社交生态、内容传播等方面有着深远影响。本文将聚焦于 SQL…引言
在社交媒体和各类社区平台蓬勃发展的当下用户之间的关系网络成为了平台运营和数据分析的关键部分。相互关注作为一种重要的社交关系不仅反映了用户之间的紧密程度还对平台的社交生态、内容传播等方面有着深远影响。本文将聚焦于 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的集合操作作为对比体现技术广度。
