BigQuery does not seem to have support for UNION yet: https://developers.google.com/bigquery/docs/query-reference
BigQuery似乎还没有支持UNION:https://developers.google.com/bigquery/docs/query-reference
(I don't mean unioning tables together for the source. It has that.)
(我不是指将源表联合起来。它有。)
Is it coming soon?
它即将推出吗?
5 个解决方案
#1
44
If you want UNION so that you can combine query results, you can use subselects in BigQuery:
如果您想要UNION以便可以组合查询结果,则可以在BigQuery中使用子选择:
SELECT foo, bar
FROM
(SELECT integer(id) AS foo, string(title) AS bar
FROM publicdata:samples.wikipedia limit 10),
(SELECT integer(year) AS foo, string(state) AS bar
FROM publicdata:samples.natality limit 10);
This is almost exactly equivalent to the SQL
这几乎完全等同于SQL
SELECT id AS foo, title AS bar
FROM publicdata:samples.wikipedia limit 10
UNION ALL
SELECT year AS foo, state AS bar
FROM publicdata:samples.natality limit 10;
(note that if want SQL UNION and not UNION ALL this won't work)
(注意,如果想要SQL UNION而不是UNION ALL,这将不起作用)
Alternately, you could run two queries and append the result.
或者,您可以运行两个查询并附加结果。
#2
4
BigQuery recently added support for Standard SQL, including the UNION
operation.
BigQuery最近添加了对标准SQL的支持,包括UNION操作。
When submitting a query through the web UI, just make sure to uncheck "Use Legacy SQL" under the SQL Version rubric:
通过Web UI提交查询时,只需确保取消选中SQL Version规则下的“使用旧版SQL”:
#3
3
You can always do:
你可以随时做:
SELECT * FROM (query 1), (query 2);
It does the same thing as :
它做同样的事情:
SELECT * from query1 UNION select * from query 2;
#4
0
Unions are indeed supported. An excerpt from the link that you posted:
工会确实得到了支持。您发布的链接的摘录:
Note: Unlike many other SQL-based systems, BigQuery uses the comma syntax to indicate table unions, not joins. This means you can run a query over several tables with compatible schemas as follows:
注意:与许多其他基于SQL的系统不同,BigQuery使用逗号语法来指示表联合,而不是联接。这意味着您可以使用兼容模式对多个表运行查询,如下所示:
// Find suspicious activity over several days
//在几天内查找可疑活动
SELECT FORMAT_UTC_USEC(event.timestamp_in_usec) AS time, request_url
FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503]
WHERE event.username = 'root' AND NOT event.source_ip.is_internal;
#5
0
Note that, if you're using standard SQL, the comma operator now means JOIN
- you have to use the UNION
syntax if you want a union:
请注意,如果您使用的是标准SQL,则逗号运算符现在意味着JOIN - 如果需要联合,则必须使用UNION语法:
In legacy SQL, the comma operator , has the non-standard meaning of UNION ALL when applied to tables. In standard SQL, the comma operator has the standard meaning of JOIN.
在旧SQL中,逗号运算符在应用于表时具有UNION ALL的非标准含义。在标准SQL中,逗号运算符具有JOIN的标准含义。
For example:
例如:
#standardSQL
SELECT
column_name,
count(*)
from
(SELECT * FROM me.table1 UNION ALL SELECT * FROM me.table2)
group by 1
#1
44
If you want UNION so that you can combine query results, you can use subselects in BigQuery:
如果您想要UNION以便可以组合查询结果,则可以在BigQuery中使用子选择:
SELECT foo, bar
FROM
(SELECT integer(id) AS foo, string(title) AS bar
FROM publicdata:samples.wikipedia limit 10),
(SELECT integer(year) AS foo, string(state) AS bar
FROM publicdata:samples.natality limit 10);
This is almost exactly equivalent to the SQL
这几乎完全等同于SQL
SELECT id AS foo, title AS bar
FROM publicdata:samples.wikipedia limit 10
UNION ALL
SELECT year AS foo, state AS bar
FROM publicdata:samples.natality limit 10;
(note that if want SQL UNION and not UNION ALL this won't work)
(注意,如果想要SQL UNION而不是UNION ALL,这将不起作用)
Alternately, you could run two queries and append the result.
或者,您可以运行两个查询并附加结果。
#2
4
BigQuery recently added support for Standard SQL, including the UNION
operation.
BigQuery最近添加了对标准SQL的支持,包括UNION操作。
When submitting a query through the web UI, just make sure to uncheck "Use Legacy SQL" under the SQL Version rubric:
通过Web UI提交查询时,只需确保取消选中SQL Version规则下的“使用旧版SQL”:
#3
3
You can always do:
你可以随时做:
SELECT * FROM (query 1), (query 2);
It does the same thing as :
它做同样的事情:
SELECT * from query1 UNION select * from query 2;
#4
0
Unions are indeed supported. An excerpt from the link that you posted:
工会确实得到了支持。您发布的链接的摘录:
Note: Unlike many other SQL-based systems, BigQuery uses the comma syntax to indicate table unions, not joins. This means you can run a query over several tables with compatible schemas as follows:
注意:与许多其他基于SQL的系统不同,BigQuery使用逗号语法来指示表联合,而不是联接。这意味着您可以使用兼容模式对多个表运行查询,如下所示:
// Find suspicious activity over several days
//在几天内查找可疑活动
SELECT FORMAT_UTC_USEC(event.timestamp_in_usec) AS time, request_url
FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503]
WHERE event.username = 'root' AND NOT event.source_ip.is_internal;
#5
0
Note that, if you're using standard SQL, the comma operator now means JOIN
- you have to use the UNION
syntax if you want a union:
请注意,如果您使用的是标准SQL,则逗号运算符现在意味着JOIN - 如果需要联合,则必须使用UNION语法:
In legacy SQL, the comma operator , has the non-standard meaning of UNION ALL when applied to tables. In standard SQL, the comma operator has the standard meaning of JOIN.
在旧SQL中,逗号运算符在应用于表时具有UNION ALL的非标准含义。在标准SQL中,逗号运算符具有JOIN的标准含义。
For example:
例如:
#standardSQL
SELECT
column_name,
count(*)
from
(SELECT * FROM me.table1 UNION ALL SELECT * FROM me.table2)
group by 1