添加到购物车产品的平均产品列表位置

无法测试,但这可能会帮助您:

SELECT
  sku,
  name,
  (SELECT AVG(position) FROM UNNEST(positions) AS position) AS avg_position
FROM(
  SELECT
    ARRAY(SELECT AS STRUCT
      productSKU AS sku,
      v2ProductName AS name,
      ARRAY_AGG(IF(productListName='Search Results', productListPosition, NULL) IGNORE NULLS) AS positions,
      MAX(IF(ecommerceAction.action_type='3', TRUE, NULL)) AS is_carted
    FROM UNNEST(hits) LEFT JOIN UNNEST(product)
    GROUP BY sku, name) AS hits
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE
    (_TABLE_SUFFIX BETWEEN '20170601' AND '20170631')
    AND EXISTS(SELECT 1 FROM UNNEST(hits), UNNEST(product) WHERE productListName = 'Search Results')
    AND EXISTS(SELECT 1 FROM UNNEST(hits) WHERE ecommerceaction.action_type = '3')
), UNNEST(hits)
WHERE is_carted
  AND ARRAY_LENGTH(positions) > 0