Use the Oracle analysis function ROW_NUMBER ()

Use the Oracle analysis function ROW_NUMBER ()

1. row_number () over () sorting function:

(1) row_number () over () group sorting function:

When the row_number () over () function is used, the execution of grouping and sorting in over () is later than that of where group by order.

Partition by is used to group A result set. If it is not specified, it regards the entire result set as a group. What is different from the aggregate function is that it can return multiple records in a group, aggregate functions generally have only one record that reflects the statistical value.

For example, emp is sorted by group of departments.

SQL> select empno, deptno, sal, row_number () over (partition by deptno order by sal desc) rank from emp;

EMPNO DEPTNO SAL RANK
----------------------------------------
7839 10 5000 1
7782 10 2450 2
7934 10 1300 3
7788 20 3000 1
7902 20 3000 2
7566, 20, 2975, 3
7876 20 1100 4
7369 20 800 5
7698 30 2850 1
7499 30 1600 2
7844 30 1500 3
7654 30 1250 4
7521 30 1250 5
7900 30 950 6
14 rows selected.

(2) Sort query results: (no group)

SQL> select empno, deptno, sal, row_number () over (order by sal desc) rank from emp;

EMPNO DEPTNO SAL RANK
----------------------------------------
7839 10 5000 1
7902 20 3000 2
7788, 20, 3000, 3
7566 20 2975 4
7698 30 2850 5
7782, 10, 2450, 6
7499 30 1600 7
7844 30 1500 8
7934 10 1300 9
7521 30 1250 10
7654 30 1250 11
7876 20 1100 12
7900 30 950 13
7369, 20, 800, 14
14 rows selected.

Row_number () over () is similar to rownum and more powerful (can be sorted from 1 hour in each group ).

2. rank () over () is a skip sorting. When there are two second names, the next is the fourth one (also in each group ).

SQL> select empno, deptno, sal, rank () over (partition by deptno order by sal desc) rank from emp;

EMPNO DEPTNO SAL RANK
----------------------------------------
7839 10 5000 1
7782 10 2450 2
7934 10 1300 3
7788 20 3000 1
7902 20 3000 1
7566 20 2975 3 -- skip
7876 20 1100 4
7369 20 800 5
7698 30 2850 1
7499 30 1600 2
7844 30 1500 3
7654 30 1250 4
7521 30 1250 4
7900 30 950 6
14 rows selected.

SQL> select empno, deptno, sal, rank () over (order by sal desc) rank from emp;

EMPNO DEPTNO SAL RANK
----------------------------------------
7839 10 5000 1
7902 20 3000 2
7788 20 3000 2
7566 20 2975 4 -- skip
7698 30 2850 5
7782, 10, 2450, 6
7499 30 1600 7
7844 30 1500 8
7934 10 1300 9
7521 30 1250 10
7654 30 1250 10
7876 20 1100 12
7900 30 950 13
7369, 20, 800, 14
14 rows selected.

3. dense_rank () over () is a continuous sorting. When there are two second names, they still follow the third name. In contrast, row_number does not have repeated values.

SQL> select empno, deptno, sal, dense_rank () over (order by sal desc) rank from emp;

EMPNO DEPTNO SAL RANK
----------------------------------------
7839 10 5000 1
7902 20 3000 2
7788 20 3000 2
7566, 20, 2975, 3
7698 30 2850 4
7782 10 2450 5
7499 30 1600 6
7844 30 1500 7
7934 10 1300 8
7521 30 1250 9
7654 30 1250 9
7876, 20, 1100, 10
7900 30 950 11
7369 20 800 12
14 rows selected.

SQL> select empno, deptno, sal, dense_rank () over (partition by deptno order by sal desc) rank from emp;

EMPNO DEPTNO SAL RANK
----------------------------------------
7839 10 5000 1
7782 10 2450 2
7934 10 1300 3
7788 20 3000 1
7902 20 3000 1
7566 20 2975 2 -- Do not skip
7876, 20, 1100, 3
7369 20 800 4
7698 30 2850 1
7499 30 1600 2
7844 30 1500 3
7654 30 1250 4
7521 30 1250 4
7900 30 950 5
14 rows selected.

Use ROW_NUMBER to delete duplicate data

--- If the table TAB contains columns a, B, and c, you can use the following statements to delete duplicate rows that are the same for a, B, and c.

Delete from (select year, QUARTER, RESULTS, row_number () over (partition by YEAR, QUARTER, RESULTS order by YEAR, QUARTER, RESULTS) AS ROW_NO from sale)

WHERE ROW_NO> 1

Https://www.bkjia.com/topicnews.aspx? Tid = 12

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151305.htm