推荐系统：从数学原理到 MVP 实战

推荐系统是 AI 在工业界最广泛的落地场景之一——抖音的视频流、小红书的首页瀑布流、淘宝的“猜你喜欢”、Netflix 的影片排序、Spotify 的每日推荐歌单，背后都是推荐系统，贡献了这些平台超过 80% 的用户消费内容。核心数学方法包括：协同过滤利用用户-物品交互矩阵计算相似度（余弦、皮尔逊相关系数）；矩阵分解（SVD/ALS）将稀疏评分矩阵分解为用户向量 × 物品向量，是 Netflix Prize 冠军方案的核心；深度排序模型（Wide&Deep、DeepFM、DIN）用神经网络自动学习特征交叉；双塔模型分别编码用户和物品，内积打分后用 ANN 近似最近邻做召回；多目标优化（MMOE、PLE）同时优化点击率、完播率、分享率等多个目标。本文从数学基础出发，结合类似小红书的作品推荐场景，完整讲解协同过滤、矩阵分解、特征工程原理，并给出基于 PostgreSQL 的 MVP 数据库设计。

一、示例数据——类小红书作品平台

为了让所有算法都有具体数字可算，我们先建立一套完整的示例数据。

1.1 用户数据

1.2 作品数据

1.3 用户历史行为

二、推荐算法的数学基础

2.1 用户-物品评分矩阵（协同过滤的基石）

2.2 余弦相似度（衡量用户或物品的相似程度）

2.3 矩阵分解（Matrix Factorization）——填充评分矩阵的关键

2.4 标签匹配度（基于内容的推荐）

2.5 热度分计算

三、推荐系统核心架构：召回 → 粗排 → 精排 → 重排

3.1 多路召回详解

3.2 精排：LightGBM 打分模型

3.3 重排：业务规则调整

四、更多推荐场景

4.1 相关推荐（看完一篇后）

4.2 搜索推荐（搜索结果个性化排序）

4.3 新用户冷启动

4.4 负反馈处理

五、PostgreSQL MVP 数据库设计

5.1 核心表结构

-- ========== 用户表 ==========
CREATE TABLE users (
    user_id     VARCHAR(20) PRIMARY KEY,
    nickname    VARCHAR(50) NOT NULL,
    age         SMALLINT,
    gender      VARCHAR(4),            -- '男','女','未知'
    city        VARCHAR(50),
    tags        TEXT[],                 -- PostgreSQL 数组类型: {'美食','探店','穿搭'}
    register_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- ========== 作品表 ==========
CREATE TABLE items (
    item_id     VARCHAR(20) PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    category    VARCHAR(50) NOT NULL,
    author      VARCHAR(100) NOT NULL,
    tags        TEXT[],                 -- {'美食','火锅','成都','探店'}
    likes       INT DEFAULT 0,
    collects    INT DEFAULT 0,
    comments    INT DEFAULT 0,
    hot_score   DOUBLE PRECISION DEFAULT 0,  -- 热度分（定时计算）
    published_at TIMESTAMPTZ DEFAULT NOW(),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- ========== 行为日志表 ==========
CREATE TABLE behavior_logs (
    id          BIGSERIAL PRIMARY KEY,
    user_id     VARCHAR(20) NOT NULL,
    item_id     VARCHAR(20) NOT NULL,
    action      VARCHAR(20) NOT NULL,   -- 'expose','click','like','collect','comment','share','dislike'
    duration_ms INT DEFAULT 0,          -- 停留时长(毫秒)
    context     VARCHAR(20),            -- 'feed','search','related','category'
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 按时间分区（大数据量时性能关键）
CREATE INDEX idx_behavior_user_time ON behavior_logs (user_id, created_at DESC);
CREATE INDEX idx_behavior_item ON behavior_logs (item_id);

-- ========== 用户-物品评分表（预计算，定时更新） ==========
CREATE TABLE user_item_scores (
    user_id     VARCHAR(20) NOT NULL,
    item_id     VARCHAR(20) NOT NULL,
    score       DOUBLE PRECISION NOT NULL, -- 隐式评分: click=1, like=2, collect=3, comment=4
    updated_at  TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, item_id)
);

-- ========== 用户关系表 ==========
CREATE TABLE user_follows (
    follower_id VARCHAR(20) NOT NULL,
    followee_id VARCHAR(20) NOT NULL,    -- followee 可以是用户也可以是作者名
    follow_type VARCHAR(10) DEFAULT 'author',  -- 'author','user','friend'
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (follower_id, followee_id)
);

-- ========== 协同过滤相似度表（定时计算） ==========
CREATE TABLE user_similarity (
    user_a      VARCHAR(20) NOT NULL,
    user_b      VARCHAR(20) NOT NULL,
    similarity  DOUBLE PRECISION NOT NULL,  -- 余弦相似度
    updated_at  TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_a, user_b)
);

CREATE TABLE item_similarity (
    item_a      VARCHAR(20) NOT NULL,
    item_b      VARCHAR(20) NOT NULL,
    similarity  DOUBLE PRECISION NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (item_a, item_b)
);

-- ========== 矩阵分解隐因子表（模型训练后写入） ==========
CREATE TABLE user_factors (
    user_id     VARCHAR(20) PRIMARY KEY,
    factors     DOUBLE PRECISION[] NOT NULL,  -- k 维向量: {0.518, 0.332, ...}
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE item_factors (
    item_id     VARCHAR(20) PRIMARY KEY,
    factors     DOUBLE PRECISION[] NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- ========== 负反馈表 ==========
CREATE TABLE user_dislikes (
    user_id     VARCHAR(20) NOT NULL,
    item_id     VARCHAR(20),             -- NULL 表示屏蔽作者
    author      VARCHAR(100),            -- 屏蔽的作者
    reason      VARCHAR(30),             -- 'not_interested','seen_similar','low_quality','block_author'
    category    VARCHAR(50),             -- 不感兴趣的类别
    created_at  TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_dislike_user ON user_dislikes (user_id);

-- ========== 曝光去重表（避免重复推荐） ==========
CREATE TABLE user_exposures (
    user_id     VARCHAR(20) NOT NULL,
    item_id     VARCHAR(20) NOT NULL,
    exposed_at  TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_exposure_user_time ON user_exposures (user_id, exposed_at DESC);
-- 定期清理 7 天前的曝光记录
-- DELETE FROM user_exposures WHERE exposed_at < NOW() - INTERVAL '7 days';

5.2 插入示例数据

-- 用户
INSERT INTO users (user_id, nickname, age, gender, city, tags) VALUES
('U1001', '小美', 24, '女', '成都', '{"美食","探店","穿搭"}'),
('U1002', '老王', 35, '男', '北京', '{"科技","数码","编程"}'),
('U1003', '阿杰', 28, '男', '广州', '{"健身","美食","旅行"}'),
('U1004', '小雪', 22, '女', '上海', '{"穿搭","美妆","追星"}'),
('U1005', '大叔', 42, '男', '深圳', '{"钓鱼","汽车","美食"}');

-- 作品
INSERT INTO items (item_id, title, category, author, tags, likes, collects, comments,
                   published_at) VALUES
('I2001', '成都Top10火锅测评', '美食', '吃货达人',
 '{"美食","火锅","成都","探店"}', 1200, 450, 89, NOW() - INTERVAL '3 hours'),
('I2002', 'iPhone17全面评测', '科技', '数码博主',
 '{"科技","数码","iPhone","评测"}', 3500, 800, 220, NOW() - INTERVAL '6 hours'),
('I2003', '三亚5天自由行攻略', '旅行', '旅行家',
 '{"旅行","三亚","攻略","海边"}', 2800, 1500, 156, NOW() - INTERVAL '12 hours'),
('I2004', '今秋必买的5件外套', '穿搭', '时尚编辑',
 '{"穿搭","秋装","外套","时尚"}', 800, 600, 45, NOW() - INTERVAL '2 hours'),
('I2005', '家庭健身30天计划', '健身', '健身教练',
 '{"健身","减脂","家庭","计划"}', 1500, 900, 78, NOW() - INTERVAL '24 hours'),
('I2006', '重庆小面做法教程', '美食', '厨房达人',
 '{"美食","做饭","重庆","面食"}', 600, 350, 30, NOW() - INTERVAL '8 hours'),
('I2007', '2026年最值得买的手机', '科技', '数码博主',
 '{"科技","手机","排行","推荐"}', 8000, 2000, 500, NOW() - INTERVAL '48 hours'),
('I2008', '广州早茶必吃榜', '美食', '吃货达人',
 '{"美食","早茶","广州","探店"}', 900, 400, 60, NOW() - INTERVAL '5 hours'),
('I2009', '秋季显瘦穿搭公式', '穿搭', '时尚博主',
 '{"穿搭","显瘦","秋装","女装"}', 300, 200, 15, NOW() - INTERVAL '1 hour'),
('I2010', '野钓鲫鱼技巧分享', '钓鱼', '钓鱼老李',
 '{"钓鱼","鲫鱼","野钓","户外"}', 400, 250, 35, NOW() - INTERVAL '10 hours'),
('I2011', 'React vs Vue 2026对比', '编程', '程序员',
 '{"编程","前端","React","Vue"}', 1100, 700, 120, NOW() - INTERVAL '20 hours'),
('I2012', '周杰伦演唱会现场', '娱乐', '追星girls',
 '{"娱乐","周杰伦","演唱会","追星"}', 5000, 1200, 350, NOW() - INTERVAL '4 hours');

-- 行为日志
INSERT INTO behavior_logs (user_id, item_id, action, duration_ms, context) VALUES
('U1001', 'I2001', 'like', 45000, 'feed'),
('U1001', 'I2001', 'collect', 45000, 'feed'),
('U1001', 'I2006', 'like', 30000, 'feed'),
('U1001', 'I2004', 'collect', 60000, 'feed'),
('U1001', 'I2009', 'click', 20000, 'feed'),
('U1002', 'I2002', 'like', 120000, 'feed'),
('U1002', 'I2002', 'collect', 120000, 'feed'),
('U1002', 'I2002', 'comment', 120000, 'feed'),
('U1002', 'I2007', 'like', 90000, 'feed'),
('U1002', 'I2011', 'collect', 80000, 'feed'),
('U1003', 'I2005', 'like', 50000, 'feed'),
('U1003', 'I2005', 'collect', 50000, 'feed'),
('U1003', 'I2008', 'like', 35000, 'feed'),
('U1003', 'I2003', 'collect', 55000, 'feed'),
('U1004', 'I2004', 'like', 40000, 'feed'),
('U1004', 'I2012', 'like', 90000, 'feed'),
('U1004', 'I2012', 'collect', 90000, 'feed'),
('U1004', 'I2012', 'comment', 90000, 'feed'),
('U1005', 'I2010', 'like', 60000, 'feed'),
('U1005', 'I2010', 'collect', 60000, 'feed'),
('U1005', 'I2001', 'like', 40000, 'feed');

-- 关注关系
INSERT INTO user_follows (follower_id, followee_id, follow_type) VALUES
('U1001', '吃货达人', 'author'),
('U1001', '时尚编辑', 'author'),
('U1002', '数码博主', 'author'),
('U1002', '程序员', 'author'),
('U1003', '健身教练', 'author'),
('U1003', '吃货达人', 'author'),
('U1004', '时尚博主', 'author'),
('U1004', '追星girls', 'author'),
('U1001', 'U1004', 'friend'),
('U1004', 'U1001', 'friend');

5.3 核心查询——各路召回的 SQL 实现

-- ========== 1. 标签匹配召回 ==========
-- 利用 PostgreSQL 数组交集 & 运算符
SELECT i.item_id, i.title, i.category,
       ARRAY_LENGTH(i.tags & u.tags, 1) AS match_count,
       -- Jaccard = 交集 / 并集
       ARRAY_LENGTH(i.tags & u.tags, 1)::FLOAT
         / ARRAY_LENGTH(i.tags | u.tags, 1) AS jaccard
FROM items i
CROSS JOIN (SELECT tags FROM users WHERE user_id = 'U1001') u
WHERE i.tags && u.tags  -- && = 有交集
  AND i.item_id NOT IN (
      SELECT item_id FROM user_exposures
      WHERE user_id = 'U1001' AND exposed_at > NOW() - INTERVAL '24 hours'
  )
ORDER BY jaccard DESC
LIMIT 200;


-- ========== 2. 协同过滤召回(User-Based) ==========
-- 找相似用户喜欢的、目标用户没看过的
SELECT DISTINCT bl.item_id, us.similarity
FROM user_similarity us
JOIN behavior_logs bl ON bl.user_id = us.user_b
  AND bl.action IN ('like', 'collect', 'comment')
WHERE us.user_a = 'U1001'
  AND bl.item_id NOT IN (
      SELECT item_id FROM behavior_logs WHERE user_id = 'U1001'
  )
ORDER BY us.similarity DESC
LIMIT 200;


-- ========== 3. 热度召回 ==========
-- 热度分定时计算好存在 items.hot_score 字段
SELECT item_id, title, hot_score
FROM items
WHERE published_at > NOW() - INTERVAL '72 hours'
  AND item_id NOT IN (
      SELECT item_id FROM user_exposures
      WHERE user_id = 'U1001' AND exposed_at > NOW() - INTERVAL '24 hours'
  )
ORDER BY hot_score DESC
LIMIT 100;


-- ========== 4. 关注作者召回 ==========
SELECT i.item_id, i.title, i.author
FROM items i
JOIN user_follows uf ON uf.followee_id = i.author
WHERE uf.follower_id = 'U1001'
  AND uf.follow_type = 'author'
  AND i.published_at > NOW() - INTERVAL '7 days'
ORDER BY i.published_at DESC
LIMIT 50;


-- ========== 5. 矩阵分解预测分(向量点积) ==========
-- PostgreSQL 中计算两个数组的点积
CREATE OR REPLACE FUNCTION dot_product(a DOUBLE PRECISION[], b DOUBLE PRECISION[])
RETURNS DOUBLE PRECISION AS $$
DECLARE
    result DOUBLE PRECISION := 0;
BEGIN
    FOR idx IN 1..ARRAY_LENGTH(a, 1) LOOP
        result := result + a[idx] * b[idx];
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 对候选集中的每个 item 计算预测评分
SELECT if2.item_id,
       dot_product(uf.factors, if2.factors) AS predicted_score
FROM user_factors uf
CROSS JOIN item_factors if2
WHERE uf.user_id = 'U1001'
  AND if2.item_id = ANY(ARRAY['I2003','I2005','I2008','I2012'])  -- 候选 item 列表
ORDER BY predicted_score DESC;


-- ========== 6. 热度分定时更新（Job: 每 10 分钟） ==========
UPDATE items SET
    hot_score = (likes + 2.0 * collects + 3.0 * comments)
                / POWER(EXTRACT(EPOCH FROM NOW() - published_at)/3600 + 2, 1.5),
    updated_at = NOW()
WHERE published_at > NOW() - INTERVAL '7 days';


-- ========== 7. 隐式评分更新（Job: 每小时） ==========
INSERT INTO user_item_scores (user_id, item_id, score)
SELECT user_id, item_id,
       SUM(CASE action
           WHEN 'click'   THEN 1
           WHEN 'like'    THEN 2
           WHEN 'collect' THEN 3
           WHEN 'comment' THEN 4
           WHEN 'share'   THEN 5
           ELSE 0
       END) AS score
FROM behavior_logs
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY user_id, item_id
ON CONFLICT (user_id, item_id)
DO UPDATE SET score = EXCLUDED.score, updated_at = NOW();

5.4 API 接口设计

六、数据流转全景

七、推荐系统评估指标