Use of rank () over function

1. Over () is an analytic function that can be used in conjunction with the rank () function or in conjunction with other functions.
Take the top three points for each subject, and the SQL statements are as follows:

where t.rk<=3;

Arranges (rank ()) functions. These permutation functions provide the ability to define a collection (using the PARTITION clause) and then arrange the elements within the collection according to a sort order, using the Scott User's EMP table as an example to illustrate how rank over PARTITION uses

1) Check employee salary and sum up continuously

Select Deptno,ename,sal,

SUM (SAL) over (order by ename) SUM1,/* means continuous summation */
SUM (SAL) over () sum2,/* equals sum sum (SAL) */
100* round (Sal/sum (SAL) over (), 4) "bal%"
From EMP

The results are as follows:

DEPTNO ename SAL SUM1 SUM2 bal%
---------- ---------- ---------- ---------- ---------- ----------
ADAMS 1100 1100 29025 3.79
ALLEN 1600 2700 29025 5.51
BLAKE 2850 5550 29025 9.82
CLARK 2450 8000 29025 8.44
FORD 3000 11000 29025 10.34
JAMES 950 11950 29025 3.27
JONES 2975 14925 29025 10.25
Ten KING 5000 19925 29025 17.23
MARTIN 1250 21175 29025 4.31
MILLER 1300 22475 29025 4.48
SCOTT 3000 25475 29025 10.34

DEPTNO ename SAL SUM1 SUM2 bal%
---------- ---------- ---------- ---------- ---------- ----------
SMITH 800 26275 29025 2.76
TURNER 1500 27775 29025 5.17
WARD 1250 29025 29025 4.31

2) as follows:

Select Deptno,ename,sal,
SUM (SAL) over (partition by Deptno ORDER by ename) sum1,/* means partitioning by department number, sorted by name and successively summed */
SUM (SAL) over (partition by Deptno) sum2,/* represents department partition, sum */
SUM (SAL) over (partition by Deptno ORDER by Sal) sum3,/* sorted by salary and successively summed */
100* round (Sal/sum (SAL) over (), 4) "bal%"
From EMP

The results are as follows:

DEPTNO ename SAL SUM1 SUM2 SUM3 bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
CLARK 2450 2450 8750 3750 8.44
Ten KING 5000 7450 8750 8750 17.23
MILLER 1300 8750 8750 1300 4.48
ADAMS 1100 1100 10875 1900 3.79
FORD 3000 4100 10875 10875 10.34
JONES 2975 7075 10875 4875 10.25
SCOTT 3000 10075 10875 10875 10.34
SMITH 800 10875 10875 800 2.76
ALLEN 1600 1600 9400 6550 5.51
BLAKE 2850 4450 9400 9400 9.82
JAMES 950 5400 9400 950 3.27

DEPTNO ename SAL SUM1 SUM2 SUM3 bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
MARTIN 1250 6650 9400 3450 4.31
TURNER 1500 8150 9400 4950 5.17
WARD 1250 9400 9400 3450 4.31

3) as follows:

Select Empno,deptno,sal,
SUM (SAL) over (partition by Deptno) "Deptsum",/* by department partition, and Sum */
Rank () over (partition by deptno ORDER by sal Desc nulls) rank,/* by department partition, sort by salary and calculate ordinal number */
Dense_rank () over (partition by deptno ORDER BY Sal Desc nulls last) D_rank,
Row_number () over (partition by deptno ORDER BY Sal Desc nulls last) Row_rank
From EMP

Note:

Rank () is mainly used for sorting, and gives the ordinal number

Dense_rank (): The function is the same as rank (), except that rank () gives the same sequence number for the sorted data, the next data sequence jumps directly, and Dense_rank () is not, such as data: 1,2,2,4,5,6 ... This is the form of rank ()

1,2,2,3,4,5, .... This is the form of Dense_rank ()

1,2,3,4,5,6 ... This is the form of row_number ()

Row_number () is used sequentially, equivalent to the rownum value in our normal query.

In fact, from the above three examples, it is not difficult to see over the (partition by ...) The overall concept that I understand is

Partition by: The field partition of the finger, if not, for the entire data

ORDER BY: Continuous operation in the specified field (such as Sum, sort (rank (), etc.), if not specified, is equivalent to the whole sum operation on the data in the specified partition collection

Usage of the Oracle aggregate function rank ()

Sql> select * from Test_a;

ID Playname Score

-------------------- -------------------- ----------

100 AA

101 AA

BB 99

98 BB

101 AA

101 AA

The requirement is to sort the score in descending order, print all the fields, and if the same Playname score only take the highest score, if the playname gets more than the same maximum score, only one of them is taken (for example: AA gets 3 times 101, then only one of them), The final result is:

RK ID Palyname Score

---------- -------------------- -------------------- ----------

1 101 AA

1 99 bb

Originally I wanted to use the Max function, and the results came straight out:

Sql> select Max (score), Palyname from Test_a Group by Palyname;

MAX (Score) Palyname

---------- --------------------

101 AA

bb

But you want to print all the fields? OTL

Even with nesting, there is no way to solve the repetition of the highest score:

Sql> SELECT DISTINCT * from Test_a T where score in (select Max (score) from Test_a Group by Palyname) Order by Score de Sc

ID Palyname Score

-------------------- -------------------- ----------

101 AA

101 AA

BB 99

Because the same playname corresponding ID is different, so with distinct can not filter out the same playname of the same top points.

So I had to use rank ().

The basic syntax for rank is:

RANK () over ([Query_partition_clause] order_by_clause)

Example 1:

　　

Table:a (subject, score)

　　

Mathematics, 80

Chinese, 70

Mathematics, 90

Mathematics, 60

Mathematics, 100

Chinese, 88

Chinese, 65

Chinese, 77

　　

Now the result I want is: (that is, you want the top 3 points for each subject)

　　

Mathematics, 100

Mathematics, 90

Mathematics, 80

Chinese, 88

Chinese, 77

Chinese, 70

　　

Then the statement is written like this:

　　

SELECT * FROM (select rank () over (partition by account ORDER BY fraction desc) rk,a.* from a) t

where t.rk<=3;

Group by account, then sort by fractions, assign rank to ranked results, rank by top three

Example 2:

　　

There are tables table contents as follows

　　

COL1 COL2

1 1

2 1

3 2

3 1

4 1

4 2

5 2

5 2

6 2

　　

Analysis function: Lists Col2 groups after sorting according to Col1, and generates numeric columns. The comparison is used to find out the first few information of each section in the score table.

　　

Select A.*,rank () over (PARTITION by col2 ORDER by col1) ' RANK ' from Table A;

　　

The results are as follows:

　　

COL1 COL2 Rank

1 1 1

2 1 2

3 1 3

4 1 4

3 2 1

4 2 2

5 2 3

5 2 3

6 2 5

This example is more intuitive, according to the col2 grouping, according to the Clo1 sort, we can find:

5 2 3

5 2 3

6 2 5

That is, if the two rows of records are identical, they will be given the same rank, and the row after them, because the previous side of the 3rd, so that the next record becomes the 5th, and if we use Dense_rank here, then the next one will become 4th

Example 3:

　　

Total function: Calculates the sorted value of the value (4,1) under Orade by Col1,col2, which is the position of col1=4,col2=1 after sorting

　　

Select Rank (4,1) within GROUP (ORDER by Col1,col2) ' rank ' from table;

　　

The results are as follows:

Rank

4

By the above method, we can get the rank ranking of the data col1 for the 4,col2 1.

Examples of Dense_rank:

Dense_rank and rank () use the same, but there is one difference: Dence_rank in the tie-in relationship is that the correlation level will not be skipped. Rank jumps over the

　　

For example: Table

　　

A B C

A Liu Wang

A Jin Shu

A Cai Kai

b Yang du

B Lin Ying

b Yao Cai

B Yang 99

　　

For example: When rank is:

　　

Select M.a,m.b,m.c,rank () over (partition by a order by B) Liu from test3 m

　　

A B C LIU

A Cai Kai 1

A Jin Shu 2

A Liu Wang 3

B Lin Ying 1

B Yang du 2

B Yang 99 2

b Yao Cai 4

　　

And if you use Dense_rank as:

　　

Select M.a,m.b,m.c,dense_rank () over (partition by a order by B) Liu from test3 m

　　

A B C LIU

A Cai Kai 1

A Jin Shu 2

A Liu Wang 3

B Lin Ying 1

B Yang du 2

B Yang 99 2

B Yao Cai 3

Then go back to the previous demand,

Sql> SELECT DISTINCT * FROM (select rank () over (partition by Playname order by score Desc,id) rk,t.* from test_a t) whe Re rk=1;

RK ID Playname Score

---------- -------------------- -------------------- ----------

1 101 AA

1 99 bb

Here order by score Desc,id is sorted by score descending and ID, that is, because the same playname corresponding IDs are different, so the same playname, same score, but different IDs, Such 2 rows of data get different rank, and rk=1, that is, only take rank=1, that is, the highest score. This completes the requirement.

Use of rank () over function