重命名所有列名称以在大型查询中加入

一尘不染

重命名所有列名称以在大型查询中加入

sql

我需要使用完全相同的列名来连接两个表。在连接步骤之前，我将需要重命名列。 每个表包含100多个列。
我想知道是否有任何方法可以添加前缀或后缀来重命名所有列，而不是使用手动更改它们AS。我在BigQuery上使用标准SQL。我在下面举例说明。我想知道BigQuery中是否有任何功能，例如：

UPDATE
  inv
SET
  CONCAT(column_name, '_inv') AS column_name

……例子……在此先谢谢你！

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)

阅读 173

2021-03-08

共1个答案

一尘不染

*SELECT inv.*, prof.*显然，最终Duplicate column names in the result are not supported. ... *使用 SELECT inv, prof如下所示， 而不是 最终使用

#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)

结果：

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

如您所见，结果行现在具有两个结构/记录-每个都包含来自相应表的相应条目

我认为，从所有实际原因出发，这是您最好的选择

2021-03-08