英文:
Why no data returns when I use unnest in bigquery?
问题
I was using JOIN before but my supervisor told me to do it without JOINS. So I did but now it returns no data at all. What's wrong here in this syntax?
英文:
SELECT
date,
count(visitNumber) as visits,
product.v2ProductName,
sum(productRevenue) as Revenue,
product.v2ProductCategory,
concat(trafficSource.source,'/', trafficSource.medium) as source_medium,
promo.promoName
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170*` t,
UNNEST(t.hits) as hits ,UNNEST(hits.product) as product, UNNEST(hits.promotion) as promo
WHERE
_TABLE_SUFFIX BETWEEN '601' AND '605'
Group by date, product.v2ProductName,product.v2ProductCategory ,promo.promoName, source_medium
ORDER BY date ASC
I was using JOIN before but my supervisor told me to do it without JOINS. So I did but now it returns no data at all. What's wrong here in this syntax?
答案1
得分: 0
尝试使用左连接与UNNEST：
SELECT
date,
count(visitNumber) as visits,
product.v2ProductName,
sum(productRevenue) as Revenue,
product.v2ProductCategory,
concat(trafficSource.source,'/', trafficSource.medium) as source_medium,
promo.promoName
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170*` t
LEFT JOIN UNNEST(t.hits) as hits
LEFT JOIN UNNEST(hits.product) as product
LEFT JOIN UNNEST(hits.promotion) as promo
WHERE
_TABLE_SUFFIX BETWEEN '601' AND '605'
Group by 1, product.v2ProductName, product.v2ProductCategory, promo.promoName, source_medium
ORDER BY date ASC;
英文:
Try using LEFT JOIN with UNNEST;
SELECT
date,
count(visitNumber) as visits,
product.v2ProductName,
sum(productRevenue) as Revenue,
product.v2ProductCategory,
concat(trafficSource.source,'/', trafficSource.medium) as source_medium,
promo.promoName
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170*` t
LEFT JOIN UNNEST(t.hits) as hits LEFT JOIN UNNEST(hits.product) as product LEFT JOIN UNNEST(hits.promotion) as promo
WHERE
_TABLE_SUFFIX BETWEEN '601' AND '605'
Group by 1, product.v2ProductName,product.v2ProductCategory ,promo.promoName, source_medium
ORDER BY date ASC;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论