sql - 谷歌 BigQuery SQL : Prevent column prefix renaming after join

Question

假设您有一个表“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 行不必要的代码是令人难以置信的低效 - 所以我希望有更好的方法在连接时保留列名？

更新

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

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

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

Answer 1

我认为这个问题是 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  

参见 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