在BigQuery中按组计算百分位数

问题描述

四处搜寻后，我找不到解决方案.使用以下示例:

After searching around, I could not find a solution on this. With the following example:

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

我们希望为 num 列计算0-100个分位数，并按 letter 分组.当前查询仅返回2行，因为 value 列似乎是一个数组.我们需要的是上面的查询返回202行，结构如下:

We are looking to compute 0 - 100 quantiles for the num column, grouped by letter. The current query only returns 2 rows, as the value column seems like an array. What we need is for the above query to return 202 rows, structure as such:

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

...其中 pctile 列为0-100，而 value 列是与 pctile 中的百分位数关联的值柱子.这不是最佳示例，因为我们正在尝试计算0-100％的百分位数，并且示例数据仅包含10行，但是我认为这足以反映问题.

...where the pctile column is 0 - 100, and the value column is the value associated with the percentile in the pctile column. This isn't the best example because we are trying to compote 0 - 100 percentiles, and the example data only has 10 rows, however I think it is sufficient to reflect the problem.

推荐答案

下面是BigQuery标准SQL

Below is for BigQuery Standard 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

这篇关于在BigQuery中按组计算百分位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！