Google BigQuery SQL：加入后阻止列前缀重命名 [英] Google BigQuery SQL: Prevent column prefix renaming after join
问题描述
你想用一个简单的连接再添加一列...而不用改变所有的列名。换句话说，你希望写一些类似于
SELECT a。*作为< a的没有前缀的列> ;, additional_field
FROM [table_with_100_columns] a
JOIN [table_with_2_columns] b
ON a.col1 = b.key
您应该可以执行此操作来生成具有101列的新表格，而无需手动重命名每一列。现在我知道如何做到这一点的唯一方法如下：
选择
a.col1作为col1，
a.col2作为col2，
a.col3作为col3，
...
a.col100作为col100，
b.additional_field作为additional_field
FROM [table_with_100_columns] a
JOIN [table_with_2_columns] b
ON a.col1 = b.key
必须编写100行不必要的代码只是为了向表中添加更多列是令人难以置信的低效率 - 所以我希望有更好的方法来保存列名时加入？
UPDATE
看来这在BigQuery中尚不可行。这很容易实现，我向Google BigQuery团队推荐以下内容：
如果在SELECT子句中没有字段共享名称：
如果没有给出子表引用名称：
在JOIN
这不会破坏任何当前的功能，并增加对一个非常有用的功能的简单支持。
如果您将使用大标准SQL - 您将不会遇到此问题 - 请参阅下面的示例
< （
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键，17 AS additional_field UNION ALL
SELECT 12 AS键，27 AS additional_field UNION ALL
SELECT 13 AS键，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
请参阅从旧版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
Say you have a table "table_with_100_columns."
And you want to add one more column with a simple join... without changing all of the column names. In other words, you wish to write something like
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
You should be able to do this to generate a new table with 101 columns, without having to rename every single column by hand. Right now the only way I know how to do this as follows:
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
Having to write 100 unnecessary lines of code simply to add one more column to a table is unbelievably inefficient - so I'm hoping there is a better way to preserve column names while joining?
UPDATE
It appears this is not yet possible in BigQuery. It is very easy to implement and I suggest the following to the Google BigQuery team:
if no fields share a name in SELECT clause:
if no subtable reference names given:
Do not rename fields after JOIN
This will not break any current functionality and adds simple support for a very useful feature.
I think this problem is specific to BigQuery Legacy SQL.
If you will use Big Standard SQL - you will not have this issue - see example below
#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 Migrating from legacy SQL in case if you need rewrite the rest of the query to be in Standard SQL
The output will be as below with original column names (w/o prefixes)
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
这篇关于Google BigQuery SQL：加入后阻止列前缀重命名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
