在大查询中重命名所有列名以加入目的 [英] Rename all column names for joining purpose in big query

问题描述

我需要连接两个具有完全相同列名的表。在加入步骤之前，我需要重命名列。 每个表格包含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：

行inv.company inv.vendor inv.transaction inv.type prof.company 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 inv.company inv.vendor  inv.transaction inv.type    prof.company    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                prof     

As 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

这篇关于在大查询中重命名所有列名以加入目的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！