Create column for the quantile number of a value in BigQuery

user_10256448 0
Viewed 2960

I have a table with two columns: id and score. I'd like to create a third column that equals the quantile that an individual's score falls in. I'd like to do this in BigQuery's standardSQL.

Here's my_table:

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

and afterwards I'd like to have the following table (example shown with quartiles, but I'd be interested in quartiles/quintiles/deciles)

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

It would be excellent if this were to work on 100 million rows. I've looked around to see a couple solutions that seem to use legacy sql, and the solutions using RANK() functions don't seem to work for really large datasets. Thanks!

bigquery-standard-sql google-bigquery sql
2 Answers

If I understand correctly, you can use ntile(). For instance, if you wanted a value from 1-4, you can do:

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

If you want to enumerate the values, then use rank() or dense_rank():

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

I see, your problem is getting the code to work, because BigQuery tends to run out of resources without a partition by. One method is to break up the score into different groups. I think this logic does what you want:

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;

This breaks the score into 1000 groups (perhaps that is too many for an integer). And then reconstructs the ranking.

If your score has relatively low cardinality, then join with aggregation works:

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;

Once you have the rank() (or row_number()) you can easily calculate the tiles yourself (hint: division).

user_11440350

Output suggest me rank() :

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