为 bigquery 按百分位获取行 ID 的最佳方法是什么? [英] What's the best way to get the IDs of rows by percentile for bigquery?

问题描述

我正在 BigQuery 中使用维基百科的公共数据集.我正在尝试查找字符数排在第 75、80、85 和 90 个百分位的维基百科文章的 ID.

I am working with the public dataset of wikipedia in BigQuery. I am trying to find the ids of the wikipedia articles with character count ranked in the 75th, 80th, 85th and 90th percentile.

我现在知道返回分位数的 QUANTILES 函数，但如何检索所有 ID?

I am now aware of the QUANTILES function that returns the quantiles, but how can I retrieve all the IDs?

推荐答案

怎么样:

SELECT a.id 
FROM [publicdata:samples.wikipedia] a
CROSS JOIN (
 SELECT NTH(75, QUANTILES(num_characters, 100)) amin, NTH(76, QUANTILES(num_characters, 100)) amax
 FROM [publicdata:samples.wikipedia]) b
WHERE a.num_characters > b.amin AND a.num_characters < b.amax;

你对第 76、86 等百分位数的 ID 做了什么?您应该能够根据自己的具体需求调整之前的查询.

What are you doing with the ids in the 76th, 86th, etc., percentiles? You should be able to adapt the previous query depending on your exact needs.

(为了提高效率，您应该将 QUANTILES 查询结果提取到一个新表中，而不是每次都计算它 - 查询会运行得更快更便宜)

(For more efficiency you should extract the QUANTILES query results to a new table instead of computing it each time - queries will run faster and cheaper)

这篇关于为 bigquery 按百分位获取行 ID 的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！