基于指定规则筛选Amazon搜索结果的BigQuery SQL实现需求

需求说明 #

针对每个keyword和report_date，按以下规则筛选首屏（共3条）结果：

• 优先选取最多3条Sponsored（赞助推广）结果
• 若赞助结果不足3条，用Organic（自然搜索）结果补足至3条
• 若无赞助结果，直接取前3条自然结果

示例数据 #

-- 数据表结构及示例数据
WITH amazon_search_results AS (
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 3 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 4 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 5 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 3 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test2' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test2' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test2' AS keyword, '2022-10-10' AS report_date, 3 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test2' AS keyword, '2022-10-10' AS report_date, 4 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 3 AS rank_no, 'Organic' AS result_type
)

预期结果 #

• test1：前3条Sponsored结果
• test2：前3条Organic结果
• test3：前2条Sponsored结果 + 第1条Organic结果

BigQuery SQL解决方案 #

WITH amazon_search_results AS (
  -- 替换为你的实际数据表，或保留示例数据测试
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 3 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 4 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 5 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test1' AS keyword, '2022-10-10' AS report_date, 3 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test2' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test2' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test2' AS keyword, '2022-10-10' AS report_date, 3 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test2' AS keyword, '2022-10-10' AS report_date, 4 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Sponsored' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 1 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 2 AS rank_no, 'Organic' AS result_type UNION ALL
  SELECT 'test3' AS keyword, '2022-10-10' AS report_date, 3 AS rank_no, 'Organic' AS result_type
),
ranked_results AS (
  SELECT
    *,
    -- 按结果类型优先级排序：Sponsored优先，同类型内按rank_no升序
    ROW_NUMBER() OVER (
      PARTITION BY keyword, report_date
      ORDER BY 
        CASE result_type WHEN 'Sponsored' THEN 0 ELSE 1 END,
        rank_no
    ) AS overall_rank
  FROM amazon_search_results
)
SELECT
  keyword,
  report_date,
  rank_no,
  result_type
FROM ranked_results
WHERE overall_rank <= 3
ORDER BY keyword, report_date, overall_rank;

逻辑解释 #

1. 优先级排序：通过CASE语句给Sponsored结果赋予更高排序优先级（值为0），Organic为1；同类型内按rank_no从小到大排序，确保取到同类结果中的前N条。
2. 全局排名：用ROW_NUMBER()按keyword和report_date分组生成全局overall_rank，该排名会先覆盖Sponsored的前3条，再用Organic补足剩余名额。
3. 结果筛选：最后保留overall_rank <=3的记录，正好满足首屏3条的要求。

搜索方向建议 #

如果需要扩展逻辑，可以搜索以下关键词：

• BigQuery窗口函数（ROW_NUMBER、PARTITION BY）
• 条件排序（CASE ORDER BY）
• 分组数据优先级筛选

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