支持BigQuery SQL中的UNION功能

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

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

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

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

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

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