BigQuery中基于GA数据填充交易表缺失字段并取最后值的问题

针对你的需求，核心是先提取每个session（date+fullVisitorID+visitID）下每个商品SKU的最后一次点击记录，再用这些记录填充交易表的缺失字段，最后合并数据。以下是修改后的完整代码：

WITH
  listTable AS (
  SELECT
    date,
    fullVisitorID,
    visitID,
    (
    SELECT
      value
    FROM
      UNNEST(hits.customdimensions)
    WHERE
      INDEX = 58
    GROUP BY
      value ) AS Test,
    hits.hitNumber,
    CASE
      WHEN product.isImpression = TRUE THEN "Impression"
      WHEN product.isClick = TRUE THEN "Click"
  END
    AS Action,
    productListName,
    product.productListPosition AS productListPosition,
    (
    SELECT
      value
    FROM
      UNNEST(product.customdimensions)
    WHERE
      INDEX = 68
    GROUP BY
      value ) AS Sponsored,
    (
    SELECT
      value
    FROM
      UNNEST(product.customdimensions)
    WHERE
      INDEX = 76
    GROUP BY
      value ) AS searchResultType,
    product.productSKU,
    product.productPrice / 1000000 AS Price
  FROM
    `ga_sessions_20230603`,
    UNNEST (hits) AS hits,
    UNNEST (hits.product) AS product
  WHERE
    productListName IN ( "searchpage",
      "categorypage",
      "dropdown_initial",
      "dropdown_results" )
    AND ( product.isImpression = TRUE
      OR product.isClick = TRUE )
  ORDER BY
    date,
    fullVisitorID ASC,
    visitID ASC,
    hitNumber ASC,
    productListPosition ASC ),

  -- 提取每个session+SKU的最后一次点击记录
  latestClickTable AS (
  SELECT
    date,
    fullVisitorID,
    visitID,
    Test,
    productListName,
    productListPosition,
    Sponsored,
    searchResultType,
    productSKU
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY date, fullVisitorID, visitID, productSKU ORDER BY hitNumber DESC) AS rn
    FROM listTable
    WHERE Action = "Click"
  )
  WHERE rn = 1 ),

  transTable AS (
  SELECT
    t.date,
    t.fullVisitorID,
    t.visitID,
    l.Test,
    t.hitNumber,
    "Purchase" AS Action,
    l.productListName,
    l.productListPosition,
    l.Sponsored,
    l.searchResultType,
    t.productSKU,
    t.Price
  FROM (
    SELECT
      date,
      fullVisitorID,
      visitID,
      hits.hitNumber,
      product.productSKU,
      product.productPrice / 1000000 AS Price
    FROM
      `ga_sessions_20230603`,
      UNNEST (hits) AS hits,
      UNNEST (hits.product) AS product
    WHERE
      hits.eCommerceAction.action_type = "6"
      AND CONCAT(date, fullVisitorID, visitID) IN (
      SELECT
        DISTINCT CONCAT(date, fullVisitorID, visitID)
      FROM
        listTable
      WHERE
        Action = "Click" )
    ORDER BY
      date,
      fullVisitorID ASC,
      visitID ASC,
      hitNumber ASC
  ) t
  LEFT JOIN latestClickTable l
  ON t.date = l.date
  AND t.fullVisitorID = l.fullVisitorID
  AND t.visitID = l.visitID
  AND t.productSKU = l.productSKU )

-- 合并曝光/点击表和填充后的交易表
SELECT * FROM listTable
UNION ALL
SELECT * FROM transTable

关键逻辑说明 #

1. latestClickTable 构建：

   • 使用ROW_NUMBER()窗口函数，按date, fullVisitorID, visitID, productSKU分组，以hitNumber降序排序，取排序后第一条（rn=1），即每个session下每个商品的最后一次点击记录。
   • 这一步直接解决了"多匹配项取最后一个值"的需求，比LAST_VALUE()更直观，便于后续关联操作。

2. transTable 字段填充：

   • 将原交易表查询作为子查询t，通过LEFT JOIN关联latestClickTable，用点击记录中的字段填充交易表的Test、productListName等缺失字段。
   • 关联条件严格匹配date、fullVisitorID、visitID（session唯一标识）和productSKU，确保关联的是同一用户同一session下同一商品的点击记录。

3. 数据合并：

   • 最后用UNION ALL合并处理后的listTable（曝光+点击数据）和transTable（填充后的交易数据），得到完整的用户行为-交易数据链。

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