首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >Google Bigquery -无效查询

Google Bigquery -无效查询
EN

Stack Overflow用户
提问于 2018-12-06 03:16:58
回答 1查看 430关注 0票数 0

我在Google BigQuery (见下文)中执行了以下查询(标准SQL),但它导致了一个错误。有人知道哪里出问题了吗?在查询中,出于保密原因,我屏蔽了project-id和dataset-id。

有没有人知道如何解决这个错误并有一个正确的查询?提前谢谢你!

所需尺寸:

代码语言:javascript
复制
Date
hits.product.productSKU
custom dimension 47 (= product scope dimension)
custom dimension 48 (= product scope dimension)
hits.eCommerceAction.action_type
hits.eCommerceAction.step

所需指标:

代码语言:javascript
复制
COUNT(hits.eCommerceAction.action_type)
COUNT(hits.product.productSKU)

以下查询中出现以下错误:

代码语言:javascript
复制
"Cannot access field productSKU on a value with type ARRAY<STRUCT<productSKU STRING, v2ProductName STRING, v2ProductCategory STRING, ...>> at [3:16]"

查询:

代码语言:javascript
复制
SELECT
date AS Date,
hits.product.productSKU AS SKU,
(
SELECT
cd.value
FROM
hits.customDimensions AS cd
WHERE
cd.index=47 ) AS CD47,
(
SELECT
cd.value
FROM
hits.customDimensions AS cd
WHERE
cd.index=48 ) AS CD48,
hits.eCommerceAction.action_type AS Type,
hits.eCommerceAction.step AS Step,
COUNT(hits.eCommerceAction.action_type) AS Nr,
COUNT(hits.product.productSKU) AS NrSKU
FROM
`[projectid].[datasetid].ga_sessions*`,
UNNEST(hits) AS hits
WHERE
_TABLE_SUFFIX BETWEEN '20181103'
AND '20181103'
AND hits.page.hostname = 'www.bla.nl'
GROUP BY
Date,
Step,
Type,
SKU,
CD47,
CD48
EN

回答 1

Stack Overflow用户

发布于 2018-12-06 04:57:44

您必须取消product的嵌套。下面是一个使用公共ga数据的示例:

代码语言:javascript
复制
select date, productSKU, CD47, CD48, Type, Step, avg(array_length(nr)), avg(array_length(skus))  from (
  SELECT
    date AS Date,
    array((select p.productSKU as productSKU from unnest(hits.product) p)) skus,
    (SELECT cd.value FROM hits.customDimensions AS cd WHERE cd.index=47) AS CD47,
    (SELECT cd.value FROM hits.customDimensions AS cd WHERE cd.index=48) AS CD48,
    hits.eCommerceAction.action_type AS Type,
    hits.eCommerceAction.step AS Step,
    array((select hits.eCommerceAction.action_type)) AS Nr
  from 
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, unnest(hits) hits
), unnest(skus) productSKU
group by 
date, 
productSKU, 
CD47, 
CD48, 
Type, 
Step
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/53639279

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档