如何用BigQuery SQL从三张推荐表按规则为每个用户取Top5推荐

实现思路 #

• 核心逻辑为给不同来源的推荐结果设置优先级，优先级顺序为「表1合法结果 > 表2合法结果 > 表3补全结果」，按优先级排序后取每个用户前5条即可满足规则要求
• 提前单独过滤每张表每个用户的可推荐上限，避免后续合并时出现超额取数的问题

前置说明 #

以下方案默认三张表的基础结构如下，可根据实际业务的表结构、字段名调整：

• 表1rec_model1：包含字段user_id（用户ID）、product_id（商品ID）、ProductRecommendation（排序字段）
• 表2rec_model2：包含字段user_id（用户ID）、product_id（商品ID）、score（推荐分数，用于降序排序）
• 表3rec_model3：包含字段user_id（用户ID）、product_id（商品ID），排序规则可自定义调整

实现代码 #

WITH
-- 步骤1：处理表1，每个用户按ProductRecommendation升序最多取2条，优先级设为最高
model1 AS (
  SELECT 
    user_id,
    product_id,
    1 AS priority,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY ProductRecommendation ASC) AS rn
  FROM `你的项目ID.你的数据集名.rec_model1`
  QUALIFY rn <= 2
),
-- 步骤2：统计每个用户从表1已获取的推荐条数，用于限制表2的取数上限
user_m1_count AS (
  SELECT user_id, COUNT(*) AS m1_rec_cnt
  FROM model1
  GROUP BY user_id
),
-- 步骤3：处理表2，每个用户最多取5减去表1已取条数的数量，按分数降序排序，优先级次高
model2 AS (
  SELECT 
    m2.user_id,
    m2.product_id,
    2 AS priority,
    ROW_NUMBER() OVER(PARTITION BY m2.user_id ORDER BY m2.score DESC) AS rn
  FROM `你的项目ID.你的数据集名.rec_model2` m2
  LEFT JOIN user_m1_count uc ON m2.user_id = uc.user_id
  QUALIFY rn <= 5 - IFNULL(uc.m1_rec_cnt, 0)
),
-- 步骤4：处理表3作为补全池，优先级最低，排序规则可根据业务需求调整
model3 AS (
  SELECT 
    user_id,
    product_id,
    3 AS priority,
    -- 此处排序规则可替换为表3实际的排序逻辑，比如按热度、推荐分数降序等
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY product_id ASC) AS rn
  FROM `你的项目ID.你的数据集名.rec_model3`
)
-- 合并所有来源推荐，按优先级排序取每个用户前5条
SELECT user_id, product_id
FROM (
  SELECT user_id, product_id, priority FROM model1
  UNION ALL
  SELECT user_id, product_id, priority FROM model2
  UNION ALL
  SELECT user_id, product_id, priority FROM model3
) all_recommendations
QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY priority ASC) <= 5
ORDER BY user_id, priority ASC

可选优化 #

• 若需要对同一个用户的推荐商品去重（避免同个商品在多张表重复出现），可在合并所有推荐的子查询中增加去重逻辑：先按user_id+product_id分组，优先保留优先级更高的记录，再取前5条即可
• 可根据业务需要在最终输出中增加推荐来源、对应分数等扩展字段

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