将 NTILE 函数与维基百科上的公共示例数据集一起使用时"Response too large Error"

Question

基本上，我正在测试NTILE函数，将维基百科表的总字数划分为100个桶。我在使用网络控制台时收到"响应过大错误"。因此，我尝试了使用--destination_table和--allow_large_results选项的bq CLI工具，但没有成功。

以下是查询：

SELECT id, num_characters, NTILE(100) OVER (ORDER BY num_characters) percentile
FROM [publicdata:samples.wikipedia]

如何正确检索结果？

Answer 1

BigQuery惊人性能的秘诀：分配工作负载。每当您发出查询时，大量计算机都会开始并行读取所有数据，对其进行处理，并将其传递给链中的其他计算机。

然而，有些操作很难并行化——通常是在完成所有其他操作之后运行的函数。这些操作不是分布式的，而是限制在一台计算机中可以容纳的所有数据中。ORDER BY和OVER()就是其中的一些函数，因为最终需要一台机器对整个结果集进行排序。

好消息是我们还有其他选择。QUANTILES能够浏览所有数据，同时计算近似结果：

SELECT QUANTILES(num_characters, 100)
FROM [publicdata:samples.wikipedia]
Query complete (1.7s elapsed, 2.34 GB processed)

或者运行与原始问题中相同的OVER(ORDERBY)，但运行一个数据样本：

SELECT id, num_characters, NTILE(100) OVER (ORDER BY num_characters) percentile 
FROM [publicdata:samples.wikipedia]
WHERE id % 10 = 0;
Query complete (258.7s elapsed, 4.68 GB processed)

你会看到两者产生相似的结果(一个是近似结果，另一个是采样结果)，但其中一个要快得多。

Answer 2

您可以使用UDF函数来获取所有数据的nTiles，而无需内存饱和和采样，尽管它的性能可能较差。

我使用这种方法来计算值所处的百分比，就像内置的NTILE函数一样。当应用于大量行时，内置函数将在ORDER BY操作期间耗尽内存。

用户定义的函数采用分位数数组(在本例中使用APPROX_QUANTILES生成)和值，并返回值所在的分位数。

-- Define UDF
CREATE TEMPORARY FUNCTION getNTile(quantiles ARRAY<INT64>, val INT64)
RETURNS INT64
LANGUAGE js AS """
var ntile = 0;
while(parseInt(val, 10) > parseInt(quantiles[ntile], 10) && ntile < quantiles.length) {
ntile+=1;
}
return ntile;
""";
-- Calculate an array of approximate quantiles, in this case percentiles
WITH master AS (
SELECT
id,
num_characters
FROM
`publicdata.samples.wikipedia`
),
quantiles AS (
SELECT
APPROX_QUANTILES(num_characters, 99) AS approx
FROM
master
)
-- Use UDF with approx quantile array to find nTile
SELECT
id,
num_characters,
getNTile(quantiles.approx, num_characters) AS percentile
FROM
master,
quantiles

UDF中的JS代码强制值为parseInt的整数。这可能是一个BigQuery错误，但由于某种原因，这些值以字符串的形式出现，否则就很奇怪了。

希望这能帮助到别人。