PercentileRank Function

Feature Requests
1

Would love to see a PercentileRank function, much like what exists in Excel:

Percentile Ranking

  1. Determine which field/column you want to calculate percentile ranking for

  2. Order (Asc/Desc) needs to be taken into account depending on what the user is trying to achieve. The data point might require descending sort as larger number would be considered "top 99th percentile". Another data point might mean a smaller number is "better" or "top 99th percentile"

  3. Apply sequential rank order (1 thru x number of records); let's call this [Rank]

  4. How to calculate the PercentileRank: 1 - ([Rank] - 1) / (Count of rows - 1)

2

Just to clarify, do you want the hypothetical-set version as in Excel or the per-row window function as in PostgreSQL, MySQL, etc.? (Or both?)

In the Excel version, you pass the function the input set to define standing, then a value to compute the rank for; it returns the percent rank compared to the standing. For the window function, it outputs a new column with the rank for each row.

For reference, there is a percent_rank() window function in PostgreSQL and MySQL. The Excel function is PERCENTRANK. (Note link is from macOS Excel.) PostgreSQL has a hypothetical-set version as well.

3

Sorry for the long delay; I’ve been out of office for a couple of weeks.

Thanks for raising this question as I hadn’t considered the differences between the SQL v Excel functions. After reading up on them (and admittedly still somewhat unclear), it would seem the per-row, percent_rank() solution would make the most sense.