Google BigQuery SQL：防止连接后重命名列前缀

假设您有一个表“table_with_100_columns”。

您希望通过简单的联接再添加一列...而不更改所有列名称。换句话说，你想写类似的东西

SELECT a.* as <a's columns without prefix>, additional_field
FROM [table_with_100_columns] a
JOIN [table_with_2_columns] b
ON a.col1 = b.key

您应该能够执行此操作来生成一个包含 101 列的新表，而无需手动重命名每一列。现在我知道如何做到这一点的唯一方法如下：

SELECT
  a.col1 as col1,
  a.col2 as col2,
  a.col3 as col3,
  ...
  a.col100 as col100,
  b.additional_field as additional_field
FROM [table_with_100_columns] a
JOIN [table_with_2_columns] b
ON a.col1 = b.key

仅仅为了向表中添加一列而必须编写 100 行不必要的代码是令人难以置信的低效 - 所以我希望有一种更好的方法来在加入时保留列名称？

UPDATE

这在 BigQuery 中似乎还不可能。它非常容易实现，我向 Google BigQuery 团队提出以下建议：

if no fields share a name in SELECT clause:
  if no subtable reference names given:
    Do not rename fields after JOIN

这不会破坏任何当前功能，并为非常有用的功能添加简单支持。

我认为这个问题是 BigQuery Legacy SQL 特有的。
如果您将使用 Big Standard SQL - 您将不会遇到此问题 - 请参阅下面的示例

#standardSQL
WITH table_with_100_columns AS (
  SELECT 11 AS col1, 21 AS col2, 31 AS col3 UNION ALL 
  SELECT 12 AS col1, 22 AS col2, 32 AS col3 UNION ALL
  SELECT 13 AS col1, 23 AS col2, 33 AS col3 UNION ALL
  SELECT 14 AS col1, 24 AS col2, 34 AS col3 UNION ALL
  SELECT 15 AS col1, 25 AS col2, 35 AS col3   
),
table_with_2_columns AS (
  SELECT 11 AS key, 17 AS additional_field UNION ALL
  SELECT 12 AS key, 27 AS additional_field UNION ALL
  SELECT 13 AS key, 37 AS additional_field UNION ALL
  SELECT 14 AS key, 47 AS additional_field UNION ALL
  SELECT 15 AS key, 57 AS additional_field   
)
SELECT a.*, additional_field
FROM `table_with_100_columns` AS a
JOIN `table_with_2_columns` AS b
ON a.col1 = b.key  

See 从旧版 SQL 迁移 https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql如果您需要将查询的其余部分重写为标准 SQL

输出将如下所示，带有原始列名称（无前缀）

col1    col2    col3    additional_field     
13      23      33      37   
11      21      31      17   
15      25      35      57   
12      22      32      27   
14      24      34      47