如何用SQL查询获取与目标用户口味最匹配的用户的指定商品评分

需求概述 #

• 核心目标：在商品页面展示与访问用户口味最匹配的其他用户对该商品的评分
• 筛选规则：
  • 排除未对当前目标商品评分的用户
  • 排除仅提交1-50分低分、或仅提交51-100分高分的用户（需同时拥有两类评分记录）
  • 相似度判定：基于双方共同评分的非目标商品，计算评分差值的平均值，差值越小则口味越相似
  • 无符合条件用户时，返回n/a
• 示例场景：访问用户user_id=1查看product_id=3的商品页面，最终应返回用户2的评分55

实现SQL查询 #

WITH valid_users AS (
    -- 筛选同时具备高低分记录、且对目标商品有评分的用户
    SELECT user_id
    FROM ratings
    GROUP BY user_id
    HAVING 
        SUM(CASE WHEN rating BETWEEN 1 AND 50 THEN 1 ELSE 0 END) > 0
        AND SUM(CASE WHEN rating BETWEEN 51 AND 100 THEN 1 ELSE 0 END) > 0
        AND EXISTS (SELECT 1 FROM ratings r2 WHERE r2.user_id = ratings.user_id AND r2.product_id = 3)
),
user_similarity AS (
    -- 计算有效用户与目标用户的平均评分差值（仅统计共同的非目标商品）
    SELECT 
        r_other.user_id,
        AVG(ABS(r_target.rating - r_other.rating)) AS avg_diff
    FROM ratings r_target
    JOIN ratings r_other 
        ON r_target.product_id = r_other.product_id 
        AND r_target.user_id = 1 
        AND r_other.user_id IN (SELECT user_id FROM valid_users)
        AND r_other.product_id != 3
    GROUP BY r_other.user_id
),
top_similar_user AS (
    -- 选出口味最匹配的用户（平均差值最小）
    SELECT user_id
    FROM user_similarity
    ORDER BY avg_diff ASC
    LIMIT 1
)
-- 获取匹配用户的目标商品评分，无符合用户则返回n/a
SELECT COALESCE(
    (SELECT rating FROM ratings WHERE user_id = (SELECT user_id FROM top_similar_user) AND product_id = 3),
    'n/a'
) AS matched_rating;

关键逻辑说明 #

• valid_users：先锁定符合基础条件的用户，确保用户评分行为有区分度，且对当前商品有评价。
• user_similarity：仅对比双方共同评价过的非目标商品，用评分差值的平均值量化口味相似度，避免当前商品评分干扰相似度计算。
• top_similar_user：通过排序取差值最小的用户，保证选出最匹配的对象。
• 最终查询用COALESCE处理空值场景，确保无符合用户时返回指定的n/a。

内容的提问来源于stack exchange，提问作者ChuckyFE