SQL score statistical analysis-ranking and SQL score statistical analysis

SQL score statistical analysis-ranking and SQL score statistical analysis

ROW_NUMBER (), RANK (), DENSE_RANK (), NTILE ()

I recently wrote the Performance Statistics Service for nearly half a month, and was tortured by SQL. The following describes some minor problems encountered in SQL Performance Statistics and Analysis, first, we will introduce the ranking problem that is frequently encountered in the score analysis. Here we have to mention the four good brothers in SQL: ROW_NUMBER (), RANK (), DENSE_RANK (), and NTILE, let's unveil them one by one:

First, create a studentScore table (studentScore ).

1. ROW_NUMBER ()

Definition: The ROW_NUMBER () function is used to sort the data queried by the select statement. Each data entry is appended with a sequence number. It cannot be used for ranking of student scores. It is generally used for paging queries,
For example, 10-students are queried in the first 10.

Instance:

1.1 sort student scores

Here, the number is the number of each student in the descending order of studentScore (score ).

1.2 obtain the score of the second student

The idea used here is that the paging query is another layer of select outside the original SQL.
Where t. number> = 1 and t. number <= 10 is to obtain the score information of the top 10 students.

2. RANK ()

Definition: RANK () function. As the name implies, a ranking function can RANK a field. Why is ROW_NUMBER () A sorting function different from ROW_NUMBER, when there are students with the same score, ROW_NUMBER () is sorted in sequence. Their sequence numbers are different, while Rank () is different and their ranking is the same. The following is an example:

2.1 ranking of student scores

How is ROW_NUMBER () the same as RANK? Because the scores of the students are different, the rankings are the same as those of the students. The following changes will show the differences.

When two students share the same score, the score changes. RANK () is 1 2 2, while ROW_NUMBER () is still 1 2 3, which is the difference between RANK () and ROW_NUMBER ().

3. DENSE_RANK ()

Definition: The DENSE_RANK () function is also a ranking function. Similar to RANK () function, it is also used to RANK fields. What is the difference between it and RANK? Example:

Instance:

DENSE_RANK () is a dense ranking. The difference between RANK () and DENSE_RANK () is continuous, and RANK () is a skip ranking, therefore, the RANK function is RANK ().

4. NTILE ()

Definition: NTILE () function is used to distribute rows in an ordered partition to a specified number of groups. Each group has a number. The number starts from 1, just like 'partition, it can be divided into several zones.

Instance:

Three queries are made here. The first query is divided into one 'region'. Therefore, the number of query results is 1, and the second query is divided into two zones. The query result is 1, 2, this indicates that the first 'region' is the data with two numbers: 1 and 2.

Here, the SQL ranking problem is over. Next time we will introduce some deep SQL ranking statements.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.