BigQuery select *除了两列 [英] BigQuery select * except two columns

问题描述

我想从公开的BigQuery github_repos数据集中选择所有内容，但两条记录除外：author.name AND difference.old_mode。基于我提出的类似问题，我想我要运行一个类似于
$ b

  #standardSQL 
 SELECT * REPLACE（（SELECT AS STRUCT author。* EXCEPT（name））AS author），
 REPLACE（（SELECT AS STRUCT difference。* EXCEPT（old_mode）as AS difference）
 FROM`bigquery-public-data .github_repos.commits` 
 LIMIT 1000; 
  

如果我运行作者排除，效果很好：

  #standardSQL 
 SELECT * REPLACE（（SELECT AS STRUCT author。* EXCEPT（name））AS author）
 FROM`bigquery-public-data .github_repos.commits` 
 LIMIT 1000; 
  

然而，差异排除有错误：

  #standardSQL 
 SELECT * REPLACE（（SELECT AS STRUCT difference。* EXCEPT（old_mode））AS区别）
 FROM`bigquery-public-data.github_repos .commits` 
 LIMIT 1000; 
  

错误：

 类型ARRAY< STRUCT< old_mode INT64，new_mode INT64，old_path STRING，...>>>在[2:41] 
  

谢谢。

更新
不是 SQL server 问题。

解决方案

作为一个独立的示例，考虑这个查询：

  WITH T AS（
 SELECT 10 AS a，ARRAY< STRUCT< x INT64，y STRING，z BOOL> ;> [
（1，'foo'，true）] AS arr UNION ALL 
 SELECT 11，ARRAY< STRUCT< x INT64，y STRING，z BOOL>> [
 2，'bar'，false），（3，'baz'，true）] 
）
 SELECT * FROM T; 
  

它返回类型为 a code> INT64 和一列 arr 类型 ARRAY< STRUCT< x INT64，y STRING，z BOOL> ;> 。如果你想返回 arr 的修改，其中数组中的结构省略 y ，你可以使用 SELECT * REPLACE 和 SELECT * EXCEPT ：

<$ p $ （
（1，'foo'，true））（
选择10作为a，阵列< STRUCT< x INT64，y STRING，z BOOL>> ] AS arr UNION ALL
SELECT 11，ARRAY< STRUCT< x INT64，y STRING，z BOOL>> [
（2，'bar'，false），（3，'baz'，true ）]
）
SELECT * REPLACE（ARRAY（SELECT as STRUCT * EXCEPT（y）FROM UNNEST（arr））as arr）
FROM T;

这个想法是用一个新数组替换原始数组，我们使用 ARRAY 带有 SELECT AS STRUCT 和 * EXCEPT 的子查询重构带有struct元素的字段不包含 y 。

回到问题的查询中，您可以应用同样的想法到差异和 old_mode ：

  SELECT * REPLACE（
 ARRAY（SELECT AS STRUCT * EXCEPT（old_mode）FROM UNNEST（difference））as difference 
）
 FROM`bigquery-public-data。 github_repos.commits` 
 LIMIT 1000; 
  

查询结果包含差异数组struct不包含 old_mode 字段。

I would like to select everything from the public BigQuery github_repos dataset except from two records: author.name AND difference.old_mode. Based on a similar question I asked, I think I want to run a query similar to

#standardSQL
SELECT * REPLACE ((SELECT AS STRUCT author.* EXCEPT (name)) AS author), 
REPLACE ((SELECT AS STRUCT difference.* EXCEPT (old_mode)) AS difference)
FROM `bigquery-public-data.github_repos.commits`
LIMIT 1000;

If I run the author exclusion, it works well:

#standardSQL
SELECT * REPLACE ((SELECT AS STRUCT author.* EXCEPT (name)) AS author)
FROM `bigquery-public-data.github_repos.commits`
LIMIT 1000;

However, the difference exclusion has an error:

#standardSQL
SELECT * REPLACE ((SELECT AS STRUCT difference.* EXCEPT (old_mode)) AS difference)
FROM `bigquery-public-data.github_repos.commits`
LIMIT 1000;

Error:

Dot-star is not supported for type ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, ...>> at [2:41]

Thank you.

Update Not a duplicate of SQL server question.

解决方案

As a self-contained example, consider this query:

WITH T AS (
  SELECT 10 AS a, ARRAY<STRUCT<x INT64, y STRING, z BOOL>>[
      (1, 'foo', true)] AS arr UNION ALL
  SELECT 11, ARRAY<STRUCT<x INT64, y STRING, z BOOL>>[
      (2, 'bar', false), (3, 'baz', true)]
)
SELECT * FROM T;

It returns a column a of type INT64 and a column arr of type ARRAY<STRUCT<x INT64, y STRING, z BOOL>>. If you wanted to return a modification of arr where the struct inside the array omits y, you could use a combination of SELECT * REPLACE and SELECT * EXCEPT:

WITH T AS (
  SELECT 10 AS a, ARRAY<STRUCT<x INT64, y STRING, z BOOL>>[
      (1, 'foo', true)] AS arr UNION ALL
  SELECT 11, ARRAY<STRUCT<x INT64, y STRING, z BOOL>>[
      (2, 'bar', false), (3, 'baz', true)]
)
SELECT * REPLACE(ARRAY(SELECT AS STRUCT * EXCEPT (y) FROM UNNEST(arr)) AS arr)
FROM T;

The idea is to replace the original array with a new one, and we use an ARRAY subquery with SELECT AS STRUCT and * EXCEPT to reconstruct the array with struct elements whose fields don't include y.

Going back to the query in the question, you can apply the same idea to difference and old_mode:

SELECT * REPLACE (
  ARRAY(SELECT AS STRUCT * EXCEPT (old_mode) FROM UNNEST(difference)) AS difference
)
FROM `bigquery-public-data.github_repos.commits`
LIMIT 1000;

The query result contains a difference array whose struct doesn't include the old_mode field.

这篇关于BigQuery select *除了两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！