BigQuery查询客户最近3件购买商品的优化方案咨询

现有一张存储客户、购买商品及交易日期的大表（假设表名为customer_purchases，字段为customer_name、item、transaction_date），需要生成包含客户名称、最近3件购买商品（分列为last_item、second_item、third_item）的结果集。针对你当前用ROW_NUMBER()+CTE+CASE的实现，这里提供两种更简洁高效的方案：

方法1：条件聚合（通用兼容型） #

这是适配绝大多数关系型数据库的方案，代码紧凑且性能稳定：

SELECT 
  customer_name,
  MAX(CASE WHEN rn = 1 THEN item END) AS last_item,
  MAX(CASE WHEN rn = 2 THEN item END) AS second_item,
  MAX(CASE WHEN rn = 3 THEN item END) AS third_item
FROM (
  SELECT 
    customer_name,
    item,
    transaction_date,
    -- 按客户分区，交易日期倒序排名，最近的购买排第1
    ROW_NUMBER() OVER(PARTITION BY customer_name ORDER BY transaction_date DESC) AS rn
  FROM customer_purchases
) t
WHERE rn <= 3 -- 仅保留最近3条记录，减少聚合计算量
GROUP BY customer_name;

说明： #

• 修正了你原方案中PARTITION BY customer,item的逻辑问题：原逻辑会将同一客户的不同商品分开排名，无法正确获取"最近3件购买的商品"，这里改为仅按客户分区。
• 如果需要处理同一日期有多笔购买的场景，可根据业务需求替换ROW_NUMBER()为RANK()（同日期购买的商品名次并列，可能导致某客户的third_item为空）或DENSE_RANK()（并列名次不占用后续位次）。
• 若客户购买不足3件，对应列会返回NULL，可通过COALESCE(MAX(...), '无')替换为自定义默认值。

方法2：PIVOT行转列（数据库专属优化） #

如果你的数据库支持PIVOT语法（如SQL Server、Oracle、PostgreSQL 11+），可以用更直观的行转列写法：

以SQL Server为例：

SELECT 
  customer_name,
  [1] AS last_item,
  [2] AS second_item,
  [3] AS third_item
FROM (
  SELECT 
    customer_name,
    item,
    ROW_NUMBER() OVER(PARTITION BY customer_name ORDER BY transaction_date DESC) AS rn
  FROM customer_purchases
) t
PIVOT (
  MAX(item) FOR rn IN ([1], [2], [3])
) p;

说明： #

• PIVOT语法将排名后的行数据直接转为列，代码可读性更强。
• 性能上和条件聚合相当，数据库优化器通常会生成类似的执行计划，但兼容性不如条件聚合。

额外优化建议： #

• 确保transaction_date包含时间戳（若有），若仅存日期，可在ORDER BY中追加交易ID等唯一字段，避免排序结果不稳定。
• 若表数据量极大，可给customer_name和transaction_date联合创建索引，大幅提升排名子查询的执行速度。

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