How to Rank Your Data... Fast

At every stage of a project, it is useful to take advantage of methods that help simplify and speed up our work. Some of those methods are not so obvious. In this article, I will show my thought process of solving a problem of ranking and ordering a data set using math to your advantage.

This came up to be a pretty fast solution. Before implementing this solution a data set of 60,000 rows was ranking for about 1.5 hours, but after implementation it was reduced to just 1.5 minutes.

Let's imagine a table inside a database called Car containing information about specific cars and their parameters. Users want to see the rank of a car with points ranging from 0 to 100. The parameters include fuel consumption, engine type, and driving performance.

The actual solution for the problem is to move all the calculations to the database itself. One query and you are done. The root of the problem is generating a number in some arbitrary range based on specified parameters. The arctangent (atan) function is ideal since it is available in virtually every database engine.

This function is top limited, meaning that no matter the X value, Y value will never be bigger than the limit. The equation incorporates several parameters: A is a divider where the bigger the number the slower Y values will raise, T is the top limit, B is the bottom limit, and X is the sum of the parameters.

For implementation, Django is used to show the structure of a table, with the query available in Django ORM and also in raw SQL. The ORM query uses helper variables to make code cleaner, with the update query being straightforward and fast.

The general group of such functions is called activation functions and holds many specific functions like ReLU and others. The improvement in time is quite drastic, going from 1.5 hours to 1.5 minutes. Math and other domains already solved a lot of problems that cause a struggle on a daily basis in this business, so why not use them?