Google Bigquery -无效查询
Google Bigquery -无效查询
提问于 2018-12-06 03:16:58
我在Google BigQuery (见下文)中执行了以下查询(标准SQL)，但它导致了一个错误。有人知道哪里出问题了吗？在查询中，出于保密原因，我屏蔽了project-id和dataset-id。
有没有人知道如何解决这个错误并有一个正确的查询？提前谢谢你！
所需尺寸：
Date
hits.product.productSKU
custom dimension 47 (= product scope dimension)
custom dimension 48 (= product scope dimension)
hits.eCommerceAction.action_type
hits.eCommerceAction.step所需指标：
COUNT(hits.eCommerceAction.action_type)
COUNT(hits.product.productSKU)以下查询中出现以下错误：
"Cannot access field productSKU on a value with type ARRAY<STRUCT<productSKU STRING, v2ProductName STRING, v2ProductCategory STRING, ...>> at [3:16]"查询：
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回答 1
Stack Overflow用户
发布于 2018-12-06 04:57:44
您必须取消product的嵌套。下面是一个使用公共ga数据的示例：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53639279
复制相关文章
相似问题
腾讯云开发者
