正常运行的BigQuery查询创建视图时出现列名歧义错误

当你单独运行查询时，BigQuery的交互式执行会自动为重复列添加后缀（如a_1、b_1）来避免冲突，但创建视图时，BigQuery要求结果集必须有唯一且明确的列名，不允许依赖这种自动重命名机制，这就是报错的核心原因。

解决方法有两种：

方法1：查询内显式重命名重复列 #

在SELECT语句中给每个重复列指定唯一别名，示例：

with
t0 as (select a, b, c from T),
t1 as (select a, b from t0 where c = 'x'),
t2 as (select a, b from t0 where c = 'y')
select 
  t1.a as a_x,
  t1.b as b_x,
  t2.a as a_y,
  t2.b as b_y
from t1 full outer join t2 on t1.a = t2.a

方法2：创建视图时指定列名列表 #

在CREATE VIEW语句后直接定义列名，覆盖查询结果的默认列名，示例：

create or replace view <view_id> (a_x, b_x, a_y, b_y) as (
  with
  t0 as (select a, b, c from T),
  t1 as (select a, b from t0 where c = 'x'),
  t2 as (select a, b from t0 where c = 'y')
  select * from t1 full outer join t2 on t1.a = t2.a
);

两种方法都能确保视图的列名唯一，解决报错问题。

内容的提问来源于stack exchange，提问作者Kumar Arunachal