我需要连接两个具有完全相同列名的表。在加入步骤之前，我需要重命名列。 每个表格包含100列以上。不知道是否有任何方法可以添加前缀或后缀来重命名所有列，而不是使用 AS 。我在BigQuery上使用标准SQL。我在下面举了一个例子来说明。我想知道BigQuery中是否有任何功能：
UPDATE inv SET CONCAT （column_name，'_inv'）AS column_name...示例...提前感谢您！ WITH inv AS（ SELECT '001'AS公司，'abc'as vendor， 800.00 AS transaction，'inv'AS type UNION ALL SELECT '002'AS公司，' efg'AS供应商， 23.4 AS交易，'inv'AS类型）， prof AS（ SELECT '001'AS公司， 'abc'as vendor， 800.00 AS transaction，'prof'AS type UNION ALL SELECT '002'AS公司，'efg 'AS供应商， 23.4 AS交易，'prof'AS类型） SELECT inv。*， prof。* FROM inv FULL JOIN prof USING （company， vendor， transaction） 解决方案
SELECT inv。*，prof。* 这显然是以结尾的重复列名称不支持。 ... 使用 SELECT inv，prof 如下所示
$ b #standardSQL WITH inv AS（ SELECT'001'AS公司'abc 'AS供应商，800.00 AS交易，'inv'AS类型UNION ALL SELECT'002'AS公司，'efg'AS供应商，23.4 AS交易，'inv'AS类型），prof AS SELECT'001'AS公司，'abc'AS卖主，800.00 AS交易，'prof'AS类型UNION ALL SELECT'002'AS公司，'efg'AS卖主，23.4 AS交易' prof'AS类型） SELECT inv，prof FROM inv FULL JOIN prof USING（公司，供应商，交易）result：
行invpany inv.vendor inv.transaction inv.type profpany prof.vendor prof.transaction prof.type 1 001 abc 800.0 inv 0 01 abc 800.0 prof 2 002 efg 23.4 inv 002 efg 23.4 prof正如您所见结果行现在有两个结构/记录 - 每个记录都分别来自相应的表
我认为这是您最好的选择，因为所有的实际原因
I need to join two tables with exactly the same column names. I will need to rename the columns before the joining step. Each table contains 100+ columns. I wonder if there's any way to add prefix or suffix to rename all the columns, rather than manually change them with AS. I'm using standard SQL on BigQuery. I put an example in below for illustration. I wonder if there's any function in BigQuery like:
UPDATE inv SET CONCAT(column_name, '_inv') AS column_name... example ... thank you in advance!
WITH inv AS ( SELECT '001' AS company, 'abc' AS vendor, 800.00 AS transaction, 'inv' AS type UNION ALL SELECT '002' AS company, 'efg' AS vendor, 23.4 AS transaction, 'inv' AS type ), prof AS ( SELECT '001' AS company, 'abc' AS vendor, 800.00 AS transaction, 'prof' AS type UNION ALL SELECT '002' AS company, 'efg' AS vendor, 23.4 AS transaction, 'prof' AS type ) SELECT inv.*, prof.* FROM inv FULL JOIN prof USING (company, vendor, transaction)解决方案
Instead of SELECT inv.*, prof.* which obviously ends up with Duplicate column names in the result are not supported. ... use SELECT inv, prof as shown below
#standardSQL WITH inv AS ( SELECT'001' AS company,'abc' AS vendor,800.00 AS transaction,'inv' AS type UNION ALL SELECT'002' AS company,'efg' AS vendor,23.4 AS transaction,'inv' AS type ), prof AS ( SELECT'001' AS company,'abc' AS vendor,800.00 AS transaction,'prof' AS type UNION ALL SELECT'002' AS company,'efg' AS vendor,23.4 AS transaction,'prof' AS type ) SELECT inv, prof FROM inv FULL JOIN prof USING (company, vendor, transaction)result :
Row invpany inv.vendor inv.transaction inv.type profpany prof.vendor prof.transaction prof.type 1 001 abc 800.0 inv 001 abc 800.0 prof 2 002 efg 23.4 inv 002 efg 23.4 profAs you can see resulted row now has two structs/records - each holding respective entry from respective table
I think this is your best option here from all practical reasons
更多推荐
在大查询中重命名所有列名以加入目的
发布评论