在BigQuery中按组计算百分位数
提问于 2020-10-06 03:23:02
在四处寻找之后，我找不到一个解决方案。下面是一个例子：
with
my_data as (
select 1 as num, 'a' as letter union all
select 2 as num, 'a' as letter union all
select 3 as num, 'a' as letter union all
select 4 as num, 'a' as letter union all
select 5 as num, 'a' as letter union all
select 6 as num, 'b' as letter union all
select 7 as num, 'b' as letter union all
select 8 as num, 'b' as letter union all
select 9 as num, 'b' as letter union all
select 10 as num, 'b' as letter
)
select
letter,
approx_quantiles(num, 100) as value
from my_data
group by letter我们希望为按letter分组的num列计算0- 100个分位数。当前查询只返回2行，因为value列看起来像一个数组。我们需要的是上面的查询返回202行，结构如下：
letter value pctile
a 1 0
a 1 1
a 1 2
a 1 3
a 1 4
...
b 1 0
b 1 1
b 1 2
b 1 3
b 1 4...where pctile列是0- 100，value列是与pctile列中的百分位数关联的值。这不是最好的示例，因为我们试图计算0- 100百分位数，并且示例数据只有10行，但我认为这足以反映问题。
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-10-06 05:06:33
下面是针对BigQuery标准SQL的说明
#standardSQL
SELECT letter, value, pctile
FROM (
SELECT
letter,
APPROX_QUANTILES(num, 100) AS value
FROM my_data
GROUP BY letter
) t, t.value WITH OFFSET AS pctile页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64218911
复制相关文章
点击加载更多
腾讯云开发者
