UNNEST（hit.eCommerceAction），Google Bigquery [英] UNNEST(hit.eCommerceAction), Google Bigquery

问题描述

我使用相同的逻辑来解除hit.eCommerceAction，但它不适用于其他字段。对这个问题有什么帮助？另外，max（if（））函数是用来获得两个匹配的正确函数。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.

固定查询：

Fixed query:

#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屋！