用户喜好推荐系统

使用场景

推荐系统广泛应用于各类互联网平台和传统行业，通过分析用户行为、偏好和上下文信息，为用户提供个性化内容推荐。常见的场景有：

• 电子商务与零售：根据用户浏览、购买历史推荐相关商品（如“猜你喜欢”）。
• 流媒体与内容平台：基于观看记录、评分推荐内容（如电影、短视频等）。
• 旅游服务：基于用户偏好推荐目的地。
• 应用推荐：根据用户下载和使用应用习惯，推荐应用。

本文以电影为例，介绍如何设计推荐系统数据库。

前提条件

已安装pg_trgm插件。

设计实现

PostgreSQL的pg_trgm扩展提供了基于三元组的字符串相似度计算功能，可以结合该插件的相似度计算功能来搭建简易的用户推荐系统。

1. 连接RDS for PostgreSQL实例。
2. 在实例上创建测试表。

   CREATE TABLE movies (
       id INT,                     -- 电影ID
       title VARCHAR(255),         -- 电影名称
       description TEXT,           -- 电影描述
       genres VARCHAR(255)[],      -- 电影类型
       year INTEGER                -- 电影年份
   );

3. 查找与特定电影标题相似的电影。

   -- 创建GIN索引加速相似度查询
   CREATE INDEX movies_title_gin_idx ON movies USING gin(title gin_trgm_ops);
   
   SELECT 
       m2.id, 
       m2.title, 
       similarity(m1.title, m2.title) AS similarity_score
   FROM 
       movies m1, 
       movies m2
   WHERE 
       m1.id = 123 AND                       -- 目标电影ID
       m1.id != m2.id AND
       similarity(m1.title, m2.title) > 0.3  -- 相似度阈值
   ORDER BY 
       similarity_score DESC
   LIMIT 10;

4. 基于电影描述相似度搜索电影。

   -- 为描述创建GIN索引
   CREATE INDEX movies_description_gin_idx ON movies USING gin(description gin_trgm_ops);
   
   SELECT 
       m2.id, 
       m2.title, 
       similarity(m1.description, m2.description) AS similarity_score
   FROM 
       movies m1, 
       movies m2
   WHERE 
       m1.id = 123 AND
       m1.id != m2.id AND
       similarity(m1.description, m2.description) > 0.1  -- 描述相似度阈值可以设置较低
   ORDER BY 
       similarity_score DESC
   LIMIT 10;

5. 结合标题、描述以及类型等多个特征搜索电影。

   SELECT 
       m2.id, 
       m2.title,
       (
           0.5 * similarity(m1.title, m2.title) + 
           0.3 * similarity(m1.description, m2.description) +
           0.2 * (SELECT COUNT(*) FROM unnest(m1.genres) AS g1 
                   JOIN unnest(m2.genres) AS g2 ON g1 = g2)::FLOAT / 
                 GREATEST(array_length(m1.genres, 1), array_length(m2.genres, 1))
       ) AS combined_similarity
   FROM 
       movies m1, 
       movies m2
   WHERE 
       m1.id = 123 AND
       m1.id != m2.id
   ORDER BY 
       combined_similarity DESC
   LIMIT 10;

6. 可以根据用户配置来限制搜索范围。例如执行以下SQL只搜索同类型的电影。

   SELECT m2.id, m2.title, similarity(m1.title, m2.title) AS similarity_score
   FROM movies m1, movies m2
   WHERE m1.id = 123 AND
         m1.id != m2.id AND
         m1.genres && m2.genres AND  -- 至少有一个共同类型
         similarity(m1.title, m2.title) > 0.3
   ORDER BY similarity_score DESC
   LIMIT 10;