Big Query - Group By子句不能与NEST（） [英] Big Query - Group By Clause not working with NEST()

问题描述

 选择语料库，NEST（字）
 FROM [ publicdata：samples.shakespeare] 
 GROUP BY语料库
 LIMIT 1000 
  

行语料库f0_

1 1kinghenryiv brave

2 1kinghenryiv行业

3 1kinghenryiv叛逆

有人能告诉我我在做什么错吗？

解决方案

没有错>
根据

你也可以运行下面的查询来查看NEST（）实际上是否有效：

  SELECT语料库，COUNT（1）AS cnt 
 FROM（
选择语料库，NEST（单词）
 FROM [publicdata：samples.shakespeare] 
 GROUP BY语料库
 LIMIT 1000 
）
 GROUP BY语料库
  

Here is the reproduction on their public test data -

SELECT corpus, NEST(word) 
FROM [publicdata:samples.shakespeare] 
GROUP BY corpus 
LIMIT 1000

Row corpus f0_
1 1kinghenryiv brave
2 1kinghenryiv profession
3 1kinghenryiv treason

Can someone tell me what I am doing wrong ?

解决方案

Nothing wrong
Per https://cloud.google.com/bigquery/query-reference#aggfunctions

BigQuery automatically flattens query results, so if you use the NEST function on the top level query, the results won't contain repeated fields. Use the NEST function when using a subselect that produces intermediate results for immediate use by the same query.

Number of returning rows proves this (1000 in query - but 41852 in result, because it is flattened:

You also can run below query to see that NEST() actually works:

SELECT corpus, COUNT(1) AS cnt 
FROM (
  SELECT corpus, NEST(word) 
  FROM [publicdata:samples.shakespeare] 
  GROUP BY corpus 
  LIMIT 1000
)
GROUP BY corpus

这篇关于Big Query - Group By子句不能与NEST（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！