Créer une colonne pour le quantile d'une valeur dans BigQuery

Question

J'ai un tableau avec deux colonnes : id y score . J'aimerais créer une troisième colonne qui équivaut au quantile que l'indice de performance d'un individu score tombe dans. J'aimerais faire cela dans le standardSQL de BigQuery.

Voici my_table :

+----+--------+
| id | score  |
+----+--------+
|  1 |      2 |
|  2 |     13 |
|  3 |     -2 |
|  4 |      7 |
+----+--------+

et ensuite j'aimerais avoir le tableau suivant (exemple montré avec les quartiles, mais je serais intéressé par les quartiles/quintiles/déciles)

+----+--------+----------+
| id | score  | quaRtile |
+----+--------+----------+
|  1 |      2 |        2 |
|  2 |     13 |        4 |
|  3 |     -2 |        1 |
|  4 |      7 |        3 |
+----+--------+----------+

Il serait excellent que cela fonctionne sur 100 millions de lignes. J'ai regardé autour de moi pour voir quelques solutions qui semblent utiliser ancien système SQL et le solutions utilisant RANK() ne semblent pas fonctionner pour les très grands ensembles de données. Merci !

Answer 1

Si je comprends bien, vous pouvez utiliser ntile() . Par exemple, si vous voulez une valeur de 1 à 4, vous pouvez le faire :

select t.*, ntile(4) over (order by score) as tile
from t;

Si vous voulez énumérer les valeurs, alors utilisez rank() o dense_rank() :

select t.*, rank() over (order by score) as tile
from t;

Je vois, votre problème est de faire fonctionner le code, parce que BigQuery a tendance à manquer de ressources sans un partition by . Une méthode consiste à répartir le score en différents groupes. Je pense que cette logique fait ce que vous voulez :

select *, 
       ( (count(*) over (partition by cast(score / 1000 as int64) order by cast(score / 1000 as int64)) -
          count(*) over (partition by cast(score / 1000 as int64))
         ) +
         rank() over (partition by cast(score / 1000 as int64) order by regi_id)
      ) as therank,
      -- rank() over (order by score) as therank
from t;

Cela divise le score en 1000 groupes (c'est peut-être trop pour un nombre entier). Et reconstruit ensuite le classement.

Si votre score a une cardinalité relativement faible, alors join avec des travaux d'agrégation :

select t.*, (running_cnt - cnt + 1) as therank
from t join
     (select score, count(*) as cnt, sum(count(*)) over (order by score) as running_cnt
      from t
      group by score
     ) s
     on t.score = s.score;

Une fois que vous avez le rank() (ou row_number() ), vous pouvez facilement calculer les tuiles vous-même (indice : division).

Answer 2

La sortie me suggère rank() :

SELECT *, RANK() OVER (ORDER BY score) as quantile 
FROM table t
ORDER BY id;