GROUP BY子句中的名称id不明确;它可以在[28:146]引用SELECT列表中的多个列

Question

在这个查询中(顺便说一下，我只是在学习BigQuery和SQL(

SELECT * 
FROM ( 
SELECT
status_2 as id, utm_source, utm_medium, utm_campaign, 
utm_term, ga_client_id as client_id
FROM `my file` 
WHERE length(status_2) > 5
), (
SELECT id, utm_source, utm_medium, utm_campaign, utm_term, client_id 
FROM `another file` 
WHERE id is not null
) 
GROUP BY id, utm_source, utm_medium, utm_campaign, utm_term, client_id

我得到这个错误(每个(：

GROUP BY子句中的名称id不明确；它可以指在[28:146]的SELECT列表中的多个列

如何更正？

Answer 1

您可以通过.使用并集而不是组

SELECT
a.status_2 as id, a.utm_source, a.utm_medium, a.utm_campaign, 
a.utm_term, a.ga_client_id as client_id
FROM `my file` a
WHERE length(a.status_2) > 5
UNION
SELECT b.id, b.utm_source, b.utm_medium, b.utm_campaign, b.utm_term, b.client_id 
FROM `another file` b
WHERE b.id is not null

Answer 2

对于不明确的错误，这只是意味着您正在从多个具有名为id的字段的表中进行查询。您需要指定所指的id。

正如其他人所提到的，知道自己想要完成什么是件好事。您是希望将两个表的UNION合并为一个单选项，还是希望将两张表联接在一起？

Answer 3

问题是您使用了两次id，并且每个id都引用了不同的表。

你可以使用别名来避免这个不明确的id。

SELECT * 
FROM ( 
SELECT
status_2 as mf.id, mf.utm_source, mf.utm_medium, mf.utm_campaign, 
mf.utm_term, mf.ga_client_id as mf.client_id
FROM `my file` mf
WHERE length(status_2) > 5
), (
SELECT af.id, af.utm_source, af.utm_medium, af.utm_campaign, af.utm_term, af.client_id 
FROM `another file` af
WHERE id is not null
) 
GROUP BY mf.id, af.utm_source, af.utm_medium, af.utm_campaign, af.utm_term, mf.client_id