网易大数据面试SQL-音乐推荐：相似口味用户聚类

⚠️ 待修正

一、题目背景

这道题来自网易的数据分析岗面试。网易是游戏和音乐，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：数据分析相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

现有一张用户听歌偏好表 t1_user_genre，记录了每个用户偏好听哪些音乐风格（多对多关系）。请通过计算用户之间共同偏好风格的数量，找出与每个用户共同偏好风格数 >= 2 的"相似用户"，并输出用户对及共同风格数，按共同风格数降序排列。

用户偏好表 t1_user_genre：

+----------+-------------+
| user_id  | genre       |
+----------+-------------+
| U01      | 流行         |
| U01      | 摇滚         |
| U01      | 电子         |
| U02      | 摇滚         |
| U02      | 电子         |
| U02      | 民谣         |
| U03      | 流行         |
| U03      | 摇滚         |
| U03      | 电子         |
| U03      | 爵士         |
| U04      | 民谣         |
| U04      | 古典         |
| U04      | 爵士         |
| U05      | 流行         |
| U05      | 电子         |
+----------+-------------+

三、思路分析

本题考察自连接（SELF JOIN）在推荐系统场景下的应用。通过对偏好表做自连接，找出不同用户之间共有的音乐风格。

解题步骤：

1. 将 t1_user_genre 表自连接，关联条件为 genre 相同且 user_id 不同（避免自己匹配自己）；
2. 按用户对分组统计共同风格数 common_genres；
3. 筛选取 common_genres >= 2 的用户对，按共同风格数降序排列。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

四、逐步推导

1. 自连接找出共同偏好

执行SQL

select a.user_id  as user_a,
       b.user_id  as user_b,
       a.genre
from t1_user_genre a
join t1_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    | 电子    |
| ...    | ...    | ...    |
+--------+--------+--------+

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 t1_user_genre a
    join t1_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             |
+--------+--------+---------------+

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
CREATE TABLE t1_user_genre (
    user_id string COMMENT '用户ID',
    genre   string COMMENT '偏好音乐风格'
) COMMENT '用户听歌偏好表';

-- 数据插入
INSERT INTO t1_user_genre VALUES
('U01', '流行'),
('U01', '摇滚'),
('U01', '电子'),
('U02', '摇滚'),
('U02', '电子'),
('U02', '民谣'),
('U03', '流行'),
('U03', '摇滚'),
('U03', '电子'),
('U03', '爵士'),
('U04', '民谣'),
('U04', '古典'),
('U04', '爵士'),
('U05', '流行'),
('U05', '电子');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题