How to Use partitionby in Oracle, oraclepartitionby

How to Use partitionby in Oracle, oraclepartitionby

The Parttion by keyword is an analytics function in Oracle, which can be grouped and sorted,

Used together with the row_number (), rank (), dense_rank () Functions

-- Row_number () ordered sorting

Select row_number () over (partition by class order by score desc) ranking, NO, score, class from A_TEST

-- Rank () skip sorting. If there are two first-level instances, the next step is the third-level instances.

Select rank () over (partition by class order by score desc) ranking, NO, score, class from A_TEST

-- Dense_rank (): consecutive sorting. If there are two first-level instances

Select dense_rank () over (partition by class order by score desc) ranking, NO, score, class from A_TEST

A_TEST

NO CLASS SCORE

1001 1 99

1002 2 98

1001 3 97

1004 1 98

1005 1 97

1006 1 99

1007 2 98

1008 3 97

Execution result

① Select row_number () over (partition by class order by score desc) ranking, NO, score, class from A_TEST;

NO SCORE CLASS

1 1001 99 1

2 1006 99 1

3 1004 98 1

4 1005 97 1

1 1002 98 2

2 1007 98 2

1 1008 97 3

2 1001 97 3

② Select rank () over (partition by class order by score desc) ranking, NO, score, class from A_TEST;

NO SCORE CLASS

1 1001 99 1

1 1006 99 1

3 1004 98 1

4 1005 97 1

1 1002 98 2

1 1007 98 2

1 1008 97 3

1 1001 97 3

③ Select dense_rank () over (partition by class order by score desc) ranking, NO, score, class from A_TEST;

NO SCORE CLASS

1 1001 99 1

1 1006 99 1

2 1004 98 1

3 1005 97 1

1 1002 98 2

1 1007 98 2

1 1008 97 3

1 1001 97 3

By ranking, you can know the differences between the three functions.