Après avoir cherché, je n'ai pas trouvé de solution à ce sujet. Avec l'exemple suivant :

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

Nous cherchons à calculer 0 - 100 quantiles pour la colonne num, regroupés par letter. La requête actuelle ne renvoie que 2 lignes, car la colonne value ressemble à un tableau. Ce dont nous avons besoin, c'est que la requête ci-dessus renvoie 202 lignes, structurées comme telles :

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

...où la colonne pctile est comprise entre 0 et 100 et la colonne value est la valeur associée au centile dans la colonne pctile. Ce n'est pas le meilleur exemple car nous essayons de composer de 0 à 100 centiles, et les données de l'exemple n'ont que 10 lignes, mais je pense que cela suffit pour refléter le problème.

0
Canovice 6 oct. 2020 à 06:23

1 réponse

Meilleure réponse

Ci-dessous est pour 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
1
Mikhail Berlyant 6 oct. 2020 à 05:06