BigQuery：如何关联每个SKU的最新匹配价格行

我有两张表：一张是包含嵌套line_items结构的订单表，另一张是存储各产品SKU价格历史的价格历史表。

订单表 #

价格历史表 #

期望输出 #

我需要获取下单时对应的产品成本，当前用的查询语句如下：

SELECT order_id, order_date,
  ARRAY(
    SELECT AS STRUCT
    item_sku,
    item_quantity,
    item_subtotal,
    cost.product_cost
    FROM UNNEST(line_items) as items
    JOIN `price_history_table` as cost
    ON items.item_sku = cost.sku AND effective_date < order_date 
  ) AS line_items,
  FROM
  `order_data_table`

这个查询能运行，但会为价格历史表中每条匹配的记录生成单独的line_item数组行。我只想匹配该SKU的最新价格，想加类似ORDER BY effective_date DESC LIMIT 1的逻辑，但不知道怎么正确添加。

以下两种写法都能实现需求，针对BigQuery语法优化：

方法一：用QUALIFY子句直接筛选最新价格 #

这是最简洁的写法，利用窗口函数给每个SKU的价格按生效日期倒序编号，只保留编号为1的最新记录：

SELECT 
  order_id, 
  order_date,
  ARRAY(
    SELECT AS STRUCT
      items.item_sku,
      items.item_quantity,
      items.item_subtotal,
      cost.成本 AS product_cost
    FROM UNNEST(line_items) as items
    JOIN `price_history_table` as cost
      ON items.item_sku = cost.商品SKU 
      AND cost.生效日期 < order_date
    QUALIFY ROW_NUMBER() OVER (PARTITION BY items.item_sku ORDER BY cost.生效日期 DESC) = 1
  ) AS line_items
FROM `order_data_table`

方法二：预筛选价格历史表的最新记录 #

如果你的环境不支持QUALIFY，可以先对价格历史表预处理，再关联订单表：

SELECT 
  order_id, 
  order_date,
  ARRAY(
    SELECT AS STRUCT
      items.item_sku,
      items.item_quantity,
      items.item_subtotal,
      latest_cost.成本 AS product_cost
    FROM UNNEST(line_items) as items
    JOIN (
      SELECT 
        商品SKU,
        成本,
        生效日期,
        ROW_NUMBER() OVER (PARTITION BY 商品SKU ORDER BY 生效日期 DESC) AS rn
      FROM `price_history_table`
    ) AS latest_cost
      ON items.item_sku = latest_cost.商品SKU 
      AND latest_cost.生效日期 < order_date
      AND latest_cost.rn = 1
  ) AS line_items
FROM `order_data_table`

关键说明 #

• ROW_NUMBER() OVER (PARTITION BY 商品SKU ORDER BY 生效日期 DESC)：给每个SKU的价格记录按生效日期从新到旧编号，最新的价格编号为1。
• 修正了原查询中字段名不匹配的问题（比如价格历史表的商品SKU对应订单行的item_sku，成本对应需要的产品成本字段）。

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