SQL score Statistics ANALYSIS-ranking issues

Row_number (), RANK (), Dense_rank (), NTILE ()

Recently wrote nearly half a month of the results of statistical services, by SQL tortured to death, the following introduction in the SQL results in statistical analysis of some of the small problems, the first introduction of the performance analysis often encountered in the problem is the ranking problem, here will have to mention the SQL Row_number (), rank (), Dense_rank (), NTILE () These four good brothers, the following one by one uncover their mysterious veil:

First create a Student score table (Studentscore) does not do more to introduce the following

1.row_number ()

definition : row_number () function is to the select query to the data to sort, each data plus a serial number, he can not be used in the ranking of student performance, generally more for paging query,
For example, query the first 10 queries 10-100 students.

Example :

1.1 Ranking of student grades

Here number is each student's ordinal according to Studentscore (score) DESC reverse order

1.2 Get the second student's score information

The idea here is that the idea of paging query is to set a layer of select in the original SQL
where T.number>=1 and t.number<=10 is not the first 10 students to obtain the results of the information satisfied.

2.RANK ()

definition : Rank () function, as the name implies the ranking function, you can rank a certain field, why here and Row_number () is not like that, Row_number () is the sort, when there is the same score of students, Row_number () will be sorted sequentially, their serial number is different, and rank () does not appear the same, their rankings are the same. Let's look at the example below:

2.1 Rankings of students ' grades

How do you find Row_number () and rank ()? Because the student grades are different, so the ranking and sorting, the following changes will find the difference.

When there are two student grades the same is the change inside. Rank () is 1 2 2, and Row_number () is 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 (), and a field rank, what is the difference between rank ()? See Example:

Example :

Dense_rank () A dense ranking he and rank () difference is that the ranking of continuity, Dense_rank () ranking is continuous, rank () is the ranking of jumps, so the general use of the ranking function is rank ().

4.NTILE ()

definition : the NTILE () function distributes the rows in an ordered partition to a specified number of groups, each with numbering, numbering starting from 1, as we say ' partitions ', divided into several zones, and how many are in a zone.

Example :

Here 3 times, the first divided into 1 ' district ', so the query result number is all 1, the second is divided into 2 districts, the query result is 1 1 2, meaning that the first ' district ' is 1 12 numbers of data, the second ' district ' only 2 this data.

Here, the problem of SQL ranking is finished, the next time to introduce some deep SQL ranking statements.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL score Statistics ANALYSIS-ranking issues