在使用默认表名称作为前缀连接表之后，在SELECT EXCEPT（）中的索引列 [英] Index column in SELECT EXCEPT() after joining table using default table name as prefix

问题描述

这个问题是从问题最初是关于在谷歌大查询中将表名重命名为前缀。
长话短说，我需要以确切名称连接两个表并保留列的起源。

我了解到我可以使用，例如：

  SELECT table_A，table_B 
 FROM table_A 
 FULL JOIN table_B 
 USING（KEY1，KEY2，KEY3）
  

这段代码会生成一个列名称的连接表，例如： table_A.KEY1 ， table_B.KEY1 在谷歌大查询的结果。

然而，我无法取消选择连接表中的一些列。例如，

  WITH WITH merged AS（
 SELECT table_A，table_B 
 FROM table_A 
 FULL JOIN table_B 
 USING（KEY1，KEY2，KEY3）
）
 
 SELECT * EXCEPT（table_A.KEY1，table_B.KEY1）
 FROM合并
  

我得到这个错误

语法错误：期望的）或，但得到。

有没有人有任何好主意如何绕过这个问题？先谢谢你！

这个操作超出了将长表转换为宽表的目的。
例如，我有一张长表

  KEY1 KEY2 KEY3 VALUE1 VALUE2 DOC_TYPE 
 1 2 3 AQ支付
 1 2 3 AQ发票
 2 3 4 DB支付
 2 3 4 DB发票
  

，最好使用KEY1 KEY2 KEY3重塑为宽表格：

  KEY1 KEY2 KEY3 VALUE1.pay VALUE2 .pay VALUE1.inv VALUE2.inv 
 1 2 3 AQAQ 
 2 3 4 DBDB 
  

由于表格包含许多列。手动重命名它们是不切实际的。我不知道Google的大型查询是否可以有一些捷径来做到这一点。

- 使用原始问题和相应答案中的命名，以保留上下文）

  #standardSQL 
 SELECT 
 key1，key2，key3，key4，
（SELECT AS STRUCT inv。* EXCEPT（key1，key2，key3 ，key4））inv，
（SELECT AS STRUCT prof。* EXCEPT（key1，key2，key3，key4））prof 
 FROM inv FULL JOIN prof 
 USING（key1，key2，key3， key4）
  

This question is following up from the question originally regarding renaming the column names with table names as prefixes in google big query. Long story short, I need to join two tables with exact names and keep the origins of the columns.

I learned that I can use, for example:

SELECT table_A , table_B
FROM table_A
FULL JOIN table_B
USING (KEY1, KEY2, KEY3)

This code would generate a joined table with column names for example: table_A.KEY1, table_B.KEY1 in the result on google big query.

However I have trouble to deselect some of the columns from the joined table. For example,

WITH merged AS (
    SELECT table_A , table_B
    FROM table_A
    FULL JOIN table_B
    USING (KEY1, KEY2, KEY3)
)

SELECT * EXCEPT(table_A.KEY1, table_B.KEY1)
FROM merged

I got this error

Syntax error: Expected ")" or "," but got "."

Does anybody have any good idea how to circumvent this issue? Thank you in advance!

This manipulation is out of the purpose of reshaping long table into wide table. For example, I have a long table

KEY1 KEY2 KEY3 VALUE1 VALUE2 DOC_TYPE
1     2     3   A      Q     PAY
1     2     3   A      Q     INVOICE
2     3     4   D      B     PAY
2     3     4   D      B     INVOICE

and ideally reshape into the wide table using KEY1 KEY2 KEY3:

KEY1 KEY2 KEY3 VALUE1.pay VALUE2.pay VALUE1.inv VALUE2.inv 
1     2     3   A         Q          A          Q   
2     3     4   D         B          D          B  

Since the table contains many columns. It is impractical to rename all of them manually. I wonder if Google big query can have some shortcut to do it.

解决方案

Below is for BigQuery Standard SQL (and I am re-using naming from your original question and respective answer so to preserve context)

#standardSQL
SELECT 
  key1, key2, key3, key4, 
  (SELECT AS STRUCT inv.* EXCEPT(key1, key2, key3, key4)) inv, 
  (SELECT AS STRUCT prof.* EXCEPT(key1, key2, key3, key4)) prof
FROM inv FULL JOIN prof
USING (key1, key2, key3, key4)

这篇关于在使用默认表名称作为前缀连接表之后，在SELECT EXCEPT（）中的索引列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！