hansolo hansolo - 3 months ago 18
SQL Question

SQL - Group Values by Percentile/Merge Rankings

I have multiple tables that contain the name of a company/attribute and a ranking.

enter image description here

I would like to write a piece of code which allows a range of Scores to be placed into specific Groups based on the percentile of the score in relationship to tables Score total. I provided a very easy use case to demonstrate what I am looking for, splitting a group of 10 companies into 5 groups, but I would like to scales this in order to apply the 5 groups to data sets with many rows WITHOUT having to specify values in a CASE statement.

enter image description here

Answer

NTILE(5) OVER(ORDER BY score) might actually put rows with the same value into different quantiles (This is probably not what you want, at least I never liked that).

It's quite similar to

5 * (row_number() over (order by score) - 1) / count(*) over ()

but if the number of rows can't be evenly divided the remainder rows are added to the first quantiles when using NTILE and randomly for ROW_NUMBER.

To assign all the rows with the same value to the same quantile you need to do your own calculation:

5 * (rank() over (order by score) - 1) / count(*) over ()
Comments