在BigQuery中依据最新sale_date更新region_name的技术咨询

我有如下结构的交易数据表：

需要实现：将每个customer_id对应的所有记录的region_name替换为该用户最新交易日期对应的region_name，预期结果如下：

注意不能使用UPDATE语句修改原数据集，我之前尝试的SQL无法满足需求：

UPDATE transactions t
JOIN (
  SELECT customer_id, MAX(sale_date) AS latest_sale_date
  FROM transactions
  GROUP BY customer_id
) latest_sales
ON t.customer_id = latest_sales.customer_id AND t.sale_date = latest_sales.latest_sale_date
SET t.region_name = (
  SELECT region_name
  FROM transactions
  WHERE customer_id = t.customer_id AND sale_date = latest_sales.latest_sale_date
)

问题分析 #

你之前的SQL不仅使用了UPDATE修改原表，而且仅更新了用户最新交易日期的那条记录，没有覆盖该用户的所有记录，完全不符合需求。我们需要生成新的结果集，而非修改原数据表。

方法一：窗口函数实现（推荐） #

利用ROW_NUMBER()窗口函数标记每个用户的最新交易记录，再关联获取对应区域名称：

SELECT
  t.customer_id,
  latest.region_name AS region_name,
  t.sale_date
FROM transactions t
JOIN (
  SELECT
    customer_id,
    region_name,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY STR_TO_DATE(sale_date, '%d/%m/%Y') DESC) AS rn
  FROM transactions
) latest ON t.customer_id = latest.customer_id AND latest.rn = 1
ORDER BY t.customer_id, t.sale_date;

说明：因为你的sale_date是字符串格式，必须用STR_TO_DATE()转换为日期类型才能正确排序，避免字符串排序逻辑错误。如果sale_date本身是日期类型，可直接用sale_date替代STR_TO_DATE(sale_date, '%d/%m/%Y')。

方法二：子查询关联实现 #

先获取每个用户的最新交易日期及对应区域名称，再和原表关联生成结果：

SELECT
  t.customer_id,
  lr.region_name AS region_name,
  t.sale_date
FROM transactions t
JOIN (
  SELECT
    t1.customer_id,
    t1.region_name
  FROM transactions t1
  WHERE STR_TO_DATE(t1.sale_date, '%d/%m/%Y') = (
    SELECT MAX(STR_TO_DATE(t2.sale_date, '%d/%m/%Y'))
    FROM transactions t2
    WHERE t2.customer_id = t1.customer_id
  )
) lr ON t.customer_id = lr.customer_id
ORDER BY t.customer_id, t.sale_date;

两种方法均不会修改原数据集，直接生成符合预期的结果。

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