sql - 重命名所有列名以在大查询中加入

Question

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

Answer 1

而不是 SELECT inv.*, prof.* 显然以不支持结果中的重复列名结束。 ... 使用 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     

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

出于所有实际原因，我认为这是您在这里的最佳选择