有没有在BigQuery Standard SQL中预先加入表名的方法？ [英] Is there a way to prepend joined tablename in BigQuery Standard SQL?

问题描述

我想知道是否可以强制标准SQL预先加入表名，这样我就可以从a和b的所有字段中预先加上a_和b_，而无需手动命名每个字段。 IE浏览器。我想做一个 SELECT * ，我不想生成每个字段名。

我知道如果你从Legacy SQL切换到标准SQL，a

$ $ p $ SELECT
FROM first_table a
JOIN second_table b
ON a.key = b.key

不会自动添加a_和b_到每个输出变量，如此问题所述：如何移除/避免prequery tablename in bigquery？但我想知道这个行为是否可以修改。

解决方案

唯一的选择是通过使用一个点来分隔这些名称，也就是从每一边返回一个结构体：

  SELECT a，b 
 FROM first_table a 
加入second_table b 
 ON a.key = b.key; 
  

如果您只希望每个表中的某些列，可以使用子选择：

  SELECT a，b 
 FROM（SELECT x，y，key FROM first_table）a 
 JOIN（SELECT foo，bar，key FROM second_table）b 
 ON a.key = b.key; 
  

I would like to know if it is possible to force standard SQL to prepend joined tablenames, so that I can get all fields from a and b with a_ and b_ prepended, without manually naming each field. Ie. I want to do a SELECT *, I don't want to generate each fieldname.

I know that if you switch from Legacy SQL to Standard SQL, a

SELECT *
FROM first_table a
JOIN second_table b
ON a.key = b.key

won't automatically prepend the a_ and b_ to each outputted variable, as described in this question: How to remove/avoid prepended tablename in bigquery? But I want to know if that behavior can be modified.

解决方案

The only option is to "separate" the names by using a dot, i.e. returning a struct with the columns from each side:

SELECT a, b
FROM first_table a
JOIN second_table b
ON a.key = b.key;

If you only want certain columns from each table, you can use subselects:

SELECT a, b
FROM (SELECT x, y, key FROM first_table) a
JOIN (SELECT foo, bar, key FROM second_table) b
ON a.key = b.key;

这篇关于有没有在BigQuery Standard SQL中预先加入表名的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！