How to use Rank,over partition function in Oracle _oracle

Arranges (rank ()) functions. These permutation functions provide the ability to define a set (using the PARTITION clause), and then arrange the elements in the collection according to some sort of order, and the following is an example of the Scott User's EMP table to illustrate how the rank over PARTITION is used.

1 Check employee salary and sum continuously

Select Deptno,ename,sal,

sum (sal ename) sum1, 
sum (SAL) over () sum2,             
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
2850 5550 29025 9.82 BLAKE
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,
sum (SAL) over (partition by DEPTNO) sum2,
sum (SAL) over (partition by Deptno to Sal) sum3,
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
2850 4450 9400 9400 9.82 BLAKE
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",
rank () over (partition by Deptno Sal desc nulls last) rank,
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:

The rang () function is used primarily for sorting and gives an ordinal

Dense_rank (): The function is the same as rank (), except that rank () gives the same number to the sorted data, and the next data serial number jumps in and out of the Dense_rank () is not, for example, the 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 Row_number () function form

The Row_number () function is used sequentially, which is 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 whole concept that I understand is

Partition by: The field partition of the word, if not the whole data
ORDER BY: Successive operations (such as sum), sorting (rank (), and so on) in the specified field, if not specified, are equivalent to the overall sum operation of the data in the specified partition collection.

The above is Rank,over partition function Basic use method, hope to be helpful to everybody.