UNNEST(hit.eCommerceAction)、Google Bigquery [英] UNNEST(hit.eCommerceAction), Google Bigquery

问题描述

我使用相同的逻辑来取消嵌套 hit.eCommerceAction，但它不像其他字段那样工作.对这个问题有什么帮助吗?此外， max(if()) 函数是否是用于获取两个 hits.customeDimenison.value 的正确函数?

I used the same logic to unnest hit.eCommerceAction, but it's not working as other fields. Any help on this problem? Also, Is the max(if()) function the right function to use to get two hits.customeDimenison.value?

SELECT
 Date
  ,COUNT(DISTINCT FULLVISITORID)
 , product.v2ProductCategory

  ,max(if(customDimensions.index=2, customDimensions.value,null))  as dest
  ,max(if(customDimensions.index=21, customDimensions.value,null))  as pax
 ,eCommerceAction.action_type
 ,product.v2ProductName

FROM `table` as t
  CROSS JOIN UNNEST(hits) AS hit
  CROSS JOIN UNNEST(hit.customDimensions) AS customDimensions
  CROSS JOIN UNNEST(hit.eCommerceAction) as eCommerceAction
  CROSS JOIN UNNEST(hit.product) AS product
GROUP BY 
      Date
      ,product.v2ProductCategory
    ,eCommerceAction.action_type
,product.v2ProductName

我得到的错误代码是错误:UNNEST 中引用的值必须是数组.UNNEST 包含类型为 STRUCT

The Error code I am getting is Error: Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT

推荐答案

我可以用更简单的查询重现错误:

I can reproduce the error with a simpler query:

#standardSQL
SELECT DISTINCT hit.eCommerceAction.action_type 
FROM `73156703.ga_sessions_20170109` t
  , UNNEST(hits) hit
  , UNNEST(hit.customDimensions) customDimensions
  , UNNEST(hit.eCommerceAction) as eCommerceAction

这里的问题是 eCommerceAction 不是 REPEATED 记录，因此没有 UNNEST 的数组.

The issue here is that eCommerceAction is not a REPEATED record, hence there is no array to UNNEST.

固定查询:

#standardSQL
SELECT DISTINCT hit.eCommerceAction.action_type 
FROM `ga_sessions_20170109`  t
  , UNNEST(hits) hit
  , UNNEST(hit.customDimensions) customDimensions

这篇关于UNNEST(hit.eCommerceAction)、Google Bigquery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！