MySQL RANK Window Function

The Ranking method allocates a rank inside the partition of an outcome of a set to every row. A row’s rank is determined by incrementing one figure of ranks from the row which precedes it. MySQL utilizes a ranking feature that helps one rank per row in a schema partition. In MySQL, the rating methods are indeed a semi-part of the window method. You will be using the rating methods in MySQL with either the subsequent clauses:

• The methods always function with an Over() clause.
• In chronological order, they allocate each row a rank.
• Depending on the ORDER BY, the functions allocate a rank to every row.
• Rows always seem to have a rank allocated to them, beginning with one for every new partition.

In total, there are three kinds of ranking functions, as follows:

• Rank
• Dense Rank
• Percent Rank

MySQL RANK():

This is a method that gives a rank inside a partition or outcome array with gaps per row. Chronologically, the ranking of rows is not-allocated all the time (i.e., increased by one from the previous row). Even when you have a tie amongst several of the values, at that point, the rank() utility applies the very same ranking to it. Also, its prior rank plus a figure of repeated numbers may be the subsequent rank number.

To understand ranking, open the command-line client shell and type your MySQL password to start using it.

Assume that we have a below table named “same” within a database “data”, with some records.

>> SELECT * FROM data.same;

Example 01: Simple RANK()

Below, we have been using the Rank function within the SELECT command. This query selects the column “id” from the table “same” while ranking it according to the column “id”. As you can see, we have given the ranking column a name, which is “my_rank”. The ranking will now be stored in this column, as shown below.

>> SELECT id, RANK() OVER( ORDER BY id ) my_rank FROM fata.same;

Example 02: RANK() Using PARTITION

Assume another table “employee” in a database “data” with the following records. Let us have another instance that splits the result set into segments.

>> SELECT * FROM data.employee;

To consume the RANK() method, the subsequent instruction assigns the rank to every row and divides the outcome set into partitions utilizing “Age” and sorting them depending on “Salary”. This query has been fetching all the records while ranking in a column “new_rank”. You can see the output of this query below. It has sorted the table according to “Salary” and divided it according to “Age”.

>> SELECT *, RANK() OVER(PARTITION BY Age ORDER BY Salary) new_rank FROM data.employee;

MySQL DENSE_Rank():

This is a functionality where, without any holes, determines a rank per each row inside a division or result set. The ranking of rows is most often allocated in sequential order. At times, you have a tie-in amongst values, and therefore it is assigned to the exact rank by the dense rank, and its subsequent rank is the next succeeding number.

Example 01: Simple DENSE_RANK()

Suppose we have a table “employee”, and you have to rank the table columns, “Name”, and “Salary” according to the Column “Name”. We have created a new column “dens_Rank” to store the rating of the records in it. Upon executing the below query, we have the following results with different ranking to all values.

>> SELECT Name, Salary, DENSE_RANK() OVER( ORDER BY Name ) dens_rank FROM data.employee;

Example 02: DENSE_RANK() Using PARTITION

Let us see another instance that splits the outcome set into segments. According to the below syntax, the resulting set partitioned by the PARTITION BY phrase is returned by the FROM statement, and the DENSE_RANK() method is then smeared to each section using the column “Name”. Then, for each segment, the ORDER BY phrase smears to determine the rows’ imperative using the column “Age”.

>> SELECT Name, Age, Salary, DENSE_RANK() OVER(PARTITION BY Name ORDER BY Age) new_rank FROM data.employee;

Upon executing the above query, you can see we have a very distinct result as compared to Single dense_rank() method in the above example. We have got the same repeated value for every row value, as you can see below. It’s the tie of rank values.

MySQL PERCENT_RANK():

It is indeed a percentage ranking (comparative rank) method that computes for rows inside a partition or outcome collection. This method returns a list from either a value scale of zero to 1.

Example 01: Simple PERCENT_RANK()

Using the table “employee”, we have been looking at the example of the simple PERCENT_RANK() method. We have a given below query for this. The per_rank column has been generated by the PERCENT_Rank() method to rank the result set in the percentage form. We have been fetching the data according to the sorting order of column “Age” and then we have been ranking the values from this table. The query result for this example given us a percentage ranking for the values as presented in the image below.

>> SELECT *, PERCENT_RANK() OVER( ORDER BY Age ) per_rank FROM data.employee;

Example 02: PERCENT_RANK() Using PARTITION

After doing the simple example of PERCENT_RANK(), now it’s the turn for the “PARTITION BY” clause. We have been using the same table “employee”. Let’s have another glimpse of another instance that splits the result set into sections. Given from the below syntax, the resulting set wall off by the PARTITION BY expression is reimbursed by the FROM declaration, as well as the PERCENT_RANK() method is then utilized to rank each row order by the column “Name”. In the image displayed below, you can see the result set contains only 0 and 1 values.

>> SELECT *, PERCENT_RANK() OVER(PARTITION BY Salary ORDER BY Name) per_rank FROM data.employee;

Conclusion:

Finally, we have done all three ranking functions for rows used in MySQL, via the MySQL command-line client shell. Also, we have taken into consideration both the simple and PARTITION BY clause in our study.

from Linux Hint https://ift.tt/3r0ja9E