我有一个查询:

WITH DAY_WIDGET_STAT AS (
SELECT hits.eventInfo.eventCategory, hits.eventInfo.eventAction, hits.eventInfo.eventLabel, hits.page.pagePath
FROM `api-open-broker.108613784.ga_sessions_20181125`, UNNEST(hits) as hits
WHERE hits.type='EVENT' and hits.eventInfo.eventCategory LIKE "%Widget%"
),
DAY_PAGEVIEWS_STAT AS (
SELECT hits.page.pagePath, COUNT(*) AS pageviews
FROM `api-open-broker.108613784.ga_sessions_20181125`,UNNEST(hits) AS hits
WHERE hits.type = 'PAGE' GROUP BY hits.page.pagePath ORDER BY pageviews DESC)

SELECT *
FROM DAY_WIDGET_STAT INNER JOIN DAY_PAGEVIEWS_STAT ON DAY_WIDGET_STAT.pagePath=DAY_PAGEVIEWS_STAT.pagePath
LIMIT 10
这给了我“不支持结果中的重复列名。找到重复项:pagePath”。
为什么?
表DAY_WIDGET_STAT:
Look
DAY_PAGEVIEWS_STAT:
Look

最佳答案

重复的是pagePath;这在两个CTE中都一样。在这种情况下,我可能会建议:

SELECT w.*,
       p.* EXCEPT (pagePath)
FROM DAY_WIDGET_STAT w INNER JOIN
     DAY_PAGEVIEWS_STAT p
     ON w.pagePath = p.pagePath
LIMIT 10;

或者,USING也应该执行您想要的操作:
SELECT *
FROM DAY_WIDGET_STAT w INNER JOIN
     DAY_PAGEVIEWS_STAT p
     USING (pagePath)
LIMIT 10;

您可能可以将查询重写为单个聚合,但是如果没有示例数据和所需结果,将很难弄清楚。如果您有兴趣,可以问另一个问题。

关于sql - BigQuery重复的栏名称,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53779191/

10-12 01:30
查看更多