支持BigQuery SQL中的UNION功能

时间:2022-06-01 19:16:03

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: 支持BigQuery SQL中的UNION功能

通过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: 支持BigQuery SQL中的UNION功能

通过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