一道非常贴近真实推荐系统的 SQL 题

本质是：基于用户行为的协同过滤（Item-Based CF 雏形）

一、题目描述

在音乐类产品中，经常会有这样一个需求：

如果用户 A 听过的歌曲集合，与歌曲 B 的“受众行为”高度相似，就可以把歌曲 B 推荐给用户 A。 

现在有一张用户听歌行为表，我们希望为用户推荐歌曲。

🎧 用户听歌行为表：user_listen_log

说明：

• 表中 没有主键
• 可能存在 重复记录
• 每一行表示：用户 user_id 在 listen_date 听过歌曲 song_id

🎯 歌曲推荐规则

将歌曲 S₂ 推荐给用户 U，需满足：

1. ❌ 用户 U 尚未听过歌曲 S₂
2. 📅 在同一天内
3. 👥 至少有 3 个不同用户
4. 🎶 同时听过 用户 U 听过的某首歌曲 S₁ 和 歌曲 S₂
5. 🚫 推荐结果中不允许重复 

二、DDL & 数据模拟

1️⃣ 建表语句

CREATETABLE user_listen_log (    user_id INTCOMMENT'用户ID',    song_id INTCOMMENT'歌曲ID',    listen_date DATECOMMENT'收听日期');

2️⃣ 模拟数据

INSERTINTO user_listen_log VALUES(1,101,'2026-03-15'),(1,102,'2026-03-15'),(1,103,'2026-03-15'),(2,101,'2026-03-15'),(2,102,'2026-03-15'),(2,103,'2026-03-15'),(3,101,'2026-03-15'),(3,102,'2026-03-15'),(3,103,'2026-03-15'),(4,101,'2026-03-15'),(4,102,'2026-03-15'),(4,104,'2026-03-15'),(2,105,'2026-03-16'),(3,105,'2026-03-16'),(3,106,'2026-03-16'),(4,106,'2026-03-16'),(5,105,'2026-03-16'),(5,106,'2026-03-16');

三、思路分析（⭐ 推荐系统 SQL 核心）

这道题可以拆成 4 个关键步骤。

Step 1️⃣ 行为去重（非常关键）

由于原始行为表可能存在重复记录， 第一步必须先对 (user_id, song_id, listen_date) 去重：

WITH base AS (SELECT user_id, song_id, listen_dateFROM user_listen_logGROUPBY user_id, song_id, listen_date)

Step 2️⃣ 找“同一天听过同一首歌”的用户集合

通过自连接，找出 听过同一首歌的用户对：

FROM base aJOIN base bON a.listen_date = b.listen_dateAND a.song_id = b.song_id

Step 3️⃣ 基于“用户重合度”做歌曲推荐候选

• a：用户 U 听过的歌曲 S₁
• b：其他用户也听过 S₁
• 再找这些用户 还听过哪些其他歌曲 S₂

统计 同时听过 S₁ 和 S₂ 的用户数 ≥ 3。

Step 4️⃣ 排除用户已经听过的歌曲

推荐前一定要过滤：用户已经听过的歌，不能再推荐

四、参考答案（完整 SQL）

WITH base AS (SELECT user_id, song_id, listen_dateFROM user_listen_logGROUPBY user_id, song_id, listen_date),song_pair AS (SELECT        a.song_id AS src_song,        b.song_id AS rec_song,        a.listen_date,COUNT(DISTINCT a.user_id) AS user_cntFROM base aJOIN base bON a.listen_date = b.listen_date AND a.user_id = b.user_id AND a.song_id <> b.song_idGROUPBY a.song_id, b.song_id, a.listen_dateHAVINGCOUNT(DISTINCT a.user_id) >= 3),user_song AS (SELECTDISTINCT user_id, song_idFROM base)SELECT    u.user_id,    s.rec_song AS recommended_songFROM user_song uJOIN song_pair sON u.song_id = s.src_songLEFTJOIN user_song eON u.user_id = e.user_id AND s.rec_song = e.song_idWHERE e.song_id ISNULLGROUPBY u.user_id, recommended_song;

写在最后