响应太大而无法返回限制1; [英] Response too large to return with LIMIT 1;

问题描述

  SELECT * FROM（
） SELECT a.title，a.counter，MAX（b.num_characters）as max 
 FROM（
 SELECT title，count（*）as counter FROM publicdata：samples.wikipedia 
 GROUP EACH BY标题
 ORDER BY counter DESC 
 LIMIT 10 
）a JOIN 
（SELECT title，num_characters FROM publicdata：samples.wikipedia 
）b ON a.title = b.title 
 GROUP BY a.title，a.counter）
 LIMIT 1; 
  

虽然这是有效的，但我得到的回复太大而无法返回。第一个子查询运行良好，我想要做的是获得更多的列。但是我失败了。

不要担心限制1，在到达该阶段之前，响应会变得太大。

尝试跳过第二个子查询，因为它仅从大数据集中选择2列，而不进行过滤。一个可行的替代方案是：

$ pre $ SELECT
a.title，a.counter，MAX（b.num_characters）AS max
FROM
publicdata：samples.wikipedia b JOIN（
SELECT
title，COUNT（*）AS计数器
FROM
publicdata：samples.wikipedia
GROUP EACH BY标题
ORDER BY
计数器DESC
LIMIT 10）a
ON a.title = b.title
GROUP BY
a。 title，
.counter

运行时间为15.4秒。

我们可以更快地使用TOP（）：
$ b

  SELECT 
 a .title title，counter，MAX（num_characters）max 
 FROM 
 publicdata：samples.wikipedia b 
 JOIN 
（
 SELECT 
 TOP（title，10 ）AS标题，COUNT（*）AS计数器
 FROM 
 publicdata：samples.wikipedia 
）a 
 ON a.title = b.title 
 GROUP BY 
 
 
 
 
 
 TOP（）的作用更简单更快（SELECT COUNT（*）/ GROUP / LIMIT）。
 
 
 
  https://developers.google.com / bigquery / docs / query-reference＃top-function  
 
 
 
现在只运行6.5s，处理15.9 GB。 b
I was playing with bigquery and ran into a problem, my Query was:
SELECT * FROM (
SELECT a.title,  a.counter , MAX(b.num_characters) as max
FROM (
  SELECT title, count(*) as counter FROM publicdata:samples.wikipedia
  GROUP EACH BY title
  ORDER BY counter DESC
  LIMIT 10
) a JOIN
(SELECT title,num_characters FROM publicdata:samples.wikipedia
) b ON a.title = b.title
GROUP BY a.title, a.counter)
LIMIT 1;
Although this is valid, I'm getting response too large to return. The first Subquery is running fine, what I want to do is get a bit more column for it. But I fail.
解决方案
Don't worry about the "limit 1", the response gets too large before reaching that stage.

Try skipping the second subquery, as it is only selecting 2 columns from the large dataset, without filtering it. A working alternative is:
SELECT
  a.title, a.counter, MAX(b.num_characters) AS max
FROM
  publicdata:samples.wikipedia b JOIN(
  SELECT
    title, COUNT(*) AS counter
  FROM
    publicdata:samples.wikipedia
    GROUP EACH BY title
  ORDER BY
    counter DESC
  LIMIT 10) a
  ON a.title = b.title
GROUP BY
  a.title,
  a.counter
This runs in 15.4 seconds.

We can do it faster, using TOP():
SELECT
  a.title title, counter, MAX(num_characters) max
FROM
  publicdata:samples.wikipedia b
JOIN
  (
  SELECT
    TOP(title, 10) AS title, COUNT(*) AS counter
  FROM
    publicdata:samples.wikipedia
    ) a
  ON a.title=b.title
GROUP BY
  title, counter
TOP() acts as a simpler and faster (SELECT COUNT(*)/GROUP/LIMIT).

https://developers.google.com/bigquery/docs/query-reference#top-function

Now it runs in only 6.5s, processing 15.9 GB.

                        
这篇关于响应太大而无法返回限制1;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！