基于ChatGPT辅助优化协同过滤算法SQL实现的实践与思考

-- 原始实现：计算用户相似度
SELECT 
    u1.user_id as user1,
    u2.user_id as user2,
    COUNT(*) as common_products,
    SUM(u1.rating * u2.rating) as dot_product,
    SQRT(SUM(u1.rating * u1.rating)) * SQRT(SUM(u2.rating * u2.rating)) as magnitude
FROM user_ratings u1
INNER JOIN user_ratings u2 ON u1.product_id = u2.product_id
WHERE u1.user_id != u2.user_id
GROUP BY u1.user_id, u2.user_id
HAVING common_products > 5;

-- 创建预过滤的评分视图
CREATE MATERIALIZED VIEW user_ratings_filtered AS
SELECT user_id, product_id, rating
FROM user_ratings
WHERE rating >= 3; -- 只考虑3分及以上评分

-- 添加索引
CREATE INDEX idx_user_ratings_user ON user_ratings_filtered(user_id);
CREATE INDEX idx_user_ratings_product ON user_ratings_filtered(product_id);

-- 优化后的相似度计算
WITH user_norms AS (
    SELECT 
        user_id,
        SQRT(SUM(rating * rating)) as norm
    FROM user_ratings_filtered
    GROUP BY user_id
),
common_ratings AS (
    SELECT 
        u1.user_id as user1,
        u2.user_id as user2,
        u1.product_id,
        u1.rating as rating1,
        u2.rating as rating2
    FROM user_ratings_filtered u1
    INNER JOIN user_ratings_filtered u2 
        ON u1.product_id = u2.product_id
        AND u1.user_id < u2.user_id  -- 避免重复计算
    WHERE u1.user_id != u2.user_id
)
SELECT 
    user1,
    user2,
    COUNT(*) as common_products,
    SUM(rating1 * rating2) / (n1.norm * n2.norm) as cosine_similarity
FROM common_ratings cr
JOIN user_norms n1 ON cr.user1 = n1.user_id
JOIN user_norms n2 ON cr.user2 = n2.user_id
GROUP BY user1, user2, n1.norm, n2.norm
HAVING COUNT(*) > 5
ORDER BY cosine_similarity DESC;

-- 采用分区表优化
CREATE TABLE user_ratings_partitioned (
    user_id INT,
    product_id INT,
    rating DECIMAL(3,2),
    PARTITION BY HASH(user_id) PARTITIONS 16
);

-- 使用窗口函数优化范数计算
WITH user_products AS (
    SELECT 
        user_id,
        product_id,
        rating,
        COUNT(*) OVER (PARTITION BY user_id) as user_product_count
    FROM user_ratings_filtered
    WHERE user_product_count > 5 -- 只考虑有足够评分行为的用户
),
similarity_calculations AS (
    SELECT 
        u1.user_id as user1,
        u2.user_id as user2,
        u1.product_id,
        u1.rating * u2.rating as product_similarity
    FROM user_products u1
    INNER JOIN user_products u2 
        ON u1.product_id = u2.product_id
        AND u1.user_id < u2.user_id
)
SELECT 
    user1,
    user2,
    COUNT(*) as common_products,
    SUM(product_similarity) / (
        (SELECT SQRT(SUM(rating * rating)) FROM user_ratings WHERE user_id = user1) *
        (SELECT SQRT(SUM(rating * rating)) FROM user_ratings WHERE user_id = user2)
    ) as cosine_similarity
FROM similarity_calculations
GROUP BY user1, user2
HAVING common_products > 5;