外联当量 - GBQ [英] Outer Union equivalent - GBQ

问题描述

我已经在这篇文章中看到了外部联合的工作方式

您可以避免指定所有列 - 如下例所示(但在这种情况下您不会控制列的顺序)

选择 *来自`project.dataset.table1`全外连接`project.dataset.table2`使用(b)

如果您需要保留订单 - 见下文

select t1.*, t2.* except(b)来自`project.dataset.table1` t1全外连接`project.dataset.table2` t2使用(b)

如果你真的需要union - 你可以在下面使用

select a, b, null as d from `project.dataset.table1`联合所有从`project.dataset.table2`中选择null作为a、b、d

I've seen how an outer union would work in this post

https://stackoverflow.com/a/52524364/11883834

and I wanted to know how I would go about implementing something similar in Google Big Query, as when I try to run an outer union, I just get given back an error (I think because outer unions are not supported in GBQ).

E.G.

Table1
+---+---+
| a | b |
+---+---+
| 1 | X |
| 2 | Y |
+---+---+

Table2

+---+---+
| b | d |
+---+---+
| U | 1 |
+---+---+

CREATE TABLE OuterUnionTable AS
SELECT * FROM Table1
OUTER UNION CORR
SELECT * FROM Table2

OuterUnionTable
+----+----+---+
| a  | b  | d |
+----+----+---+
|  1 | X  |   |
|  2 | Y  |   |
|    | U  | 1 |
+----+----+---+

解决方案

Looks like you are looking for below

select a, b, d 
from `project.dataset.table1`
full outer join `project.dataset.table2`
using(b)       

if applied to sample data in your question - output is

You can avoid to specify all columns - like in below example (but you will not control order of columns in this case)

select * 
from `project.dataset.table1`
full outer join `project.dataset.table2`
using(b) 

In case if you need to preserve order - see below

select t1.*, t2.* except(b)
from `project.dataset.table1` t1
full outer join `project.dataset.table2` t2
using(b)  

In case if you really need union - you can use below

select a, b, null as d from `project.dataset.table1`
union all
select null as a, b, d from `project.dataset.table2`

这篇关于外联当量 - GBQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！