SQL 音乐推荐：基于相似口味用户的协同过滤（网易面试题）
一、题目背景
这道题来自网易云音乐的数据分析岗面试。网易云音乐的推荐系统是其核心竞争力之一——"每日推荐"和"私人FM"背后的核心算法就是协同过滤。协同过滤的基本思路是"找到和你口味相似的用户，把他们的歌推荐给你"，而"共同偏好的音乐风格数量"就是衡量用户相似度的最直接指标。
业务场景：推荐系统团队定期产出一份"用户相似度矩阵"，为每个用户找到 Top N 相似用户作为协同过滤候选池。这道题的共同风格统计就是该矩阵的基础 SQL 查询。
二、题目
现有一张用户听歌偏好表 t7_user_genre，记录了每个用户偏好听哪些音乐风格（多对多关系）。请通过计算用户之间共同偏好风格的数量，找出与每个用户共同偏好风格数 >= 2 的"相似用户"，并输出用户对及共同风格数，按共同风格数降序排列。
用户偏好表 t7_user_genre：
+----------+--------+
| user_id | genre |
+----------+--------+
| U01 | 流行 |
| U01 | 摇滚 |
| U01 | 电子 |
| U02 | 摇滚 |
| U02 | 电子 |
| U02 | 民谣 |
| U03 | 流行 |
| U03 | 摇滚 |
| U03 | 电子 |
| U03 | 爵士 |
| U04 | 民谣 |
| U04 | 古典 |
| U04 | 爵士 |
| U05 | 流行 |
| U05 | 电子 |
+----------+--------+
三、思路分析
本题考察自连接（SELF JOIN）在推荐系统场景下的应用。通过对偏好表做自连接，找出不同用户之间共有的音乐风格。
解题步骤：
- 将
t7_user_genre表自连接，关联条件为genre相同且user_id不同（避免自己匹配自己）； - 按用户对分组统计共同风格数
common_genres； - 筛选取
common_genres >= 2的用户对，按共同风格数降序排列。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 自连接找出共同偏好
执行SQL
select a.user_id as user_a,
b.user_id as user_b,
a.genre
from t7_user_genre a
join t7_user_genre b
on a.genre = b.genre
and a.user_id < b.user_id
order by user_a, user_b
执行结果
+---------+---------+--------+
| user_a | user_b | genre |
+---------+---------+--------+
| U01 | U02 | 摇滚 |
| U01 | U02 | 电子 |
| U01 | U03 | 流行 |
| U01 | U03 | 摇滚 |
| U01 | U03 | 电子 |
| U01 | U05 | 流行 |
| U01 | U05 | 电子 |
| U02 | U03 | 摇滚 |
| U02 | U03 | 电子 |
| U02 | U04 | 民谣 |
| U02 | U05 | 电子 |
| U03 | U04 | 爵士 |
| U03 | U05 | 流行 |
| U03 | U05 | 电子 |
+---------+---------+--------+
14 rows selected (1.005 seconds)(https://www.dwsql.com)
2. 统计共同风格数并筛选
执行SQL
select user_a,
user_b,
count(1) as common_genres
from (
select a.user_id as user_a,
b.user_id as user_b,
a.genre
from t7_user_genre a
join t7_user_genre b
on a.genre = b.genre
and a.user_id < b.user_id
) t
group by user_a, user_b
having count(1) >= 2
order by common_genres desc
执行结果
+---------+---------+----------------+
| user_a | user_b | common_genres |
+---------+---------+----------------+
| U01 | U03 | 3 |
| U01 | U02 | 2 |
| U01 | U05 | 2 |
| U02 | U03 | 2 |
| U03 | U05 | 2 |
+---------+---------+----------------+
5 rows selected (0.885 seconds)(https://www.dwsql.com)
五、常见坑点
坑1：自连接数据膨胀
偏好表是用户-风格的"多对多"关系，自连接 ON 相同风格会产生大量中间结果。如果有 10 万用户 × 平均 5 个风格 = 50 万条记录，自连接后可能膨胀到百万级。实际业务中需限定活跃用户范围或提前按风格聚合后再 JOIN。
坑2：用户自身匹配同风格
t1.genre = t2.genre 会匹配到 t1.user_id = t2.user_id 的情况——用户自己的风格当然和自己相同。必须加 t1.user_id != t2.user_id，否则每个用户的"最相似用户"永远是自己，推荐毫无意义。
坑3：重复用户对
自连接会产生 (U01, U02) 和 (U02, U01) 两行重复对。协同过滤中相似度是对称的，建议加 t1.user_id < t2.user_id 去重，不仅结果更清晰，计算量也直接减半。
坑4：COUNT vs COUNT DISTINCT
如果同一风格在偏好表中出现多次（如用户A在不同时间多次标记"摇滚"），直接 COUNT(*) 会把同一风格算多次。应用 COUNT(DISTINCT t1.genre) 确保每个共同风格只计一次。
六、举一反三
-
Jaccard 相似度系数：用
共同风格数 / 两人合并去重风格数替代绝对数量，消除"什么都听的大众用户"被推荐概率偏高的问题。实现方式：分子为共同风格数，分母为两用户 UNION 后的去重风格总数 -
加权相似度：如果偏好表有
preference_score（偏好程度权重），用SUM(LEAST(t1.score, t2.score))替代 COUNT，高偏好匹配权重更高，推荐更精准 -
Top N 相似用户推荐：在结果上叠加
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY common_genres DESC)，取每个用户相似度最高的 Top 5，作为"你可能感兴趣的人" -
基于相似用户的歌曲推荐：找到相似用户后，再 JOIN 他们的播放记录表，排除目标用户已听过的歌，按相似用户的播放次数加权推荐候选歌曲——这就是完整的协同过滤推荐链路
七、知识点总结
| 考点 | 说明 |
|---|---|
| 自连接 (Self JOIN) | 同一张表通过 genre 关联，找出偏好风格相同的用户对 |
| t1.user_id < t2.user_id | 避免 (A,B) 和 (B,A) 重复对，同时排除自身匹配 |
| COUNT(DISTINCT) | 对共同风格去重计数，防止同一风格因多条记录被重复计算 |
| HAVING 阈值筛选 | HAVING COUNT(DISTINCT ...) >= 2 过滤低相似度噪声，只保留有意义的推荐 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t7_user_genre (
user_id string COMMENT '用户ID',
genre string COMMENT '偏好音乐风格'
) COMMENT '用户听歌偏好表';
-- 数据插入
INSERT INTO t7_user_genre VALUES
('U01', '流行'),
('U01', '摇滚'),
('U01', '电子'),
('U02', '摇滚'),
('U02', '电子'),
('U02', '民谣'),
('U03', '流行'),
('U03', '摇滚'),
('U03', '电子'),
('U03', '爵士'),
('U04', '民谣'),
('U04', '古典'),
('U04', '爵士'),
('U05', '流行'),
('U05', '电子');
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
