PostgreSQL中基于用户购买记录计算物品Jaccard相似度(IoU)方法

首先明确Jaccard相似度（交并比）的计算规则：

物品X与物品Y的相似度 = 同时购买X和Y的用户数 / (购买X的用户数 + 购买Y的用户数 - 同时购买X和Y的用户数)

完整SQL语句 #

WITH -- 用CTE拆分逻辑，可读性更高
-- 步骤1：获取所有购买过输入物品X的用户，以及购买X的总用户数
buy_x_users AS (
    SELECT "user"
    FROM UserItems
    WHERE item = ${input_item} -- 替换为你的输入物品参数，比如'item1'
),
cnt_x AS (
    SELECT COUNT(*) AS total
    FROM buy_x_users
),
-- 步骤2：统计每个候选物品Y的两个核心指标：同时买X和Y的用户数、买Y的总用户数
item_stats AS (
    SELECT
        ui.item AS y_item,
        COUNT(DISTINCT ui."user") AS cnt_intersect, -- 交集：同时买X和Y的用户数
        (SELECT COUNT(DISTINCT "user") FROM UserItems WHERE item = ui.item) AS cnt_y -- 全局购买Y的总用户数
    FROM UserItems ui
    INNER JOIN buy_x_users bux ON ui."user" = bux."user"
    WHERE ui.item != ${input_item} -- 排除输入物品X本身
    GROUP BY ui.item
)
-- 步骤3：计算相似度，关联物品表取特征，输出结果
SELECT
    ist.y_item AS item,
    it.feature1,
    it.feature2,
    it.feature3,
    -- 计算Jaccard，保留4位小数，不需要可去掉ROUND函数
    ROUND(
        ist.cnt_intersect::NUMERIC / (cx.total + ist.cnt_y - ist.cnt_intersect),
        4
    ) AS jaccard_similarity
FROM item_stats ist
CROSS JOIN cnt_x cx
INNER JOIN Items it ON ist.y_item = it.item
ORDER BY jaccard_similarity DESC;

关键说明 #

• 修复了原SQL中ON t1.item<>t1.item的无效关联逻辑，通过拆分步骤逐个获取计算所需的指标
• 对用户id加了DISTINCT去重，避免同一个用户多次购买同一个物品导致计数错误
• PostgreSQL中user是系统关键字，所以查询时需要用双引号包裹避免语法报错
• 输出结果默认按相似度从高到低排序，可根据需求调整排序规则

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