Oracle analysis function ROW_NUMBER ()

Oracle analysis function RANK () | ROW_NUMBER () | LAG () use the ROW_NUMBER () function: row_number () is similar to rownum, more powerful (can be sorted from 1 on each group), more efficient (? The efficiency is worse. In actual tests ). ROW_NUMBER () usage: ROW_NUMBER () OVER (PARTITIONBYC

Oracle analysis function RANK () | ROW_NUMBER () | LAG () use the ROW_NUMBER () function: row_number () is similar to rownum, more powerful (can be sorted from 1 on each group), more efficient (? The efficiency is worse. In actual tests ). ROW_NUMBER () usage: ROW_NUMBER () OVER (PARTITION BY C

Oracle analysis function RANK () | ROW_NUMBER () | LAG () Usage Details

ROW_NUMBER () function:

Row_number () is similar to rownum and more powerful (can be sorted from 1 on each group), which is more efficient (? The efficiency seems to be worse. In actual tests ).

How to Use ROW_NUMBER:
ROW_NUMBER () OVER (partition by COL1 order by COL2)
Detailed description:
Group by COL1
Sort by COL2 within the group
This value indicates the sequential number after sorting in each group (the continuous and unique in the group)

1. The ROW_NUMBER () syntax is as follows:
3. 1. row_number () over (order by column desc) first returns a serial number for each record in descending order for the column:
4. Select d. *, ROW_NUMBER () OVER (order by d. R _ OPATE_NUM DESC) AS distinct FROM REPORT_DATA D

The ROW_NUMBER () syntax is as follows: 1. row_number () over (order by column desc) first returns a serial number for each record in descending order for the column: select d. *, ROW_NUMBER () OVER (order by d. R _ OPATE_NUM DESC) AS region FROM REPORT_DATA D

[SQL]View plaincopyprint?

1. 2. row_number () over (partition by column1 order by column2 asc) first groups data by column1, and then sort the grouped data in column2 ascending order.
2. Select personalid, ct_smp_type, row_number () over (partition by personalid order by ct_smp_type asc) rn from neogoodsrule
4. Example of syntax 1: Obtain the ranking information of the top 100 employees, as follows:

2. row_number () over (partition by column1 order by column2 asc) first groups data by column1, and then sort the grouped data in column2 Ascending order select personalid, ct_smp_type, row_number () over (partition by personalid order by ct_smp_type asc) rn from neogoodsrule Syntax 1: Obtain the ranking information of the top 100 employees, as shown below

[SQL]View plaincopyprint?

1. WITH REPORT_DATA AS  

2. (Select dw. DWID, DW. DWMC, JY. JYXM, JY. Release, RMPC. R_OPATE_NUM
3. FROM REPORT_MONTH_PERSON_COUNT RMPC, effect_jyxx JY, T_DWXX DW
4. Where rmpc. JYID = JY. JYUSERID
5. And jy. SSDW = DW. DWID
6. And rmpc. R_YEAR = 2013
7. And rmpc. R_MONTH = 6
8. And jy. ssdw like '20140901'
9. Order by rmpc. R_OPATE_NUM DESC)
10. Select B .*
11. FROM (select d. *, ROW_NUMBER () OVER (order by d. R _ OPATE_NUM DESC) AS limit
12. FROM REPORT_DATA D
13. ) B
14. Where B. Memory <= 100
15. Order by B. Limit
    
17. 
    

[SQL]View plaincopyprint?

 
 

  
  
WITH REPORT_DATA
  
  
(Select dw. DWID, DW. DWMC, JY. JYXM, JY. Release, RMPC. R_OPATE_NUM
  
  
FROM REPORT_MONTH_PERSON_COUNT RMPC, effect_jyxx JY, T_DWXX DW
  
  
Where rmpc. JYID = JY. JYUSERID
  
  
And jy. SSDW = DW. DWID
  
  
And rmpc. R_YEAR = 2013
  
  
And rmpc. R_MONTH = 6
  
  
And jy. ssdw like '20140901'
  
  
Order by rmpc. R_OPATE_NUM DESC)
  
  
Select B .*
  
  
FROM (select d. *, ROW_NUMBER () OVER (order by d. R _ OPATE_NUM DESC) AS limit
  
  
FROM REPORT_DATA D
  
  
) B
  
  
Where B. Memory <= 100
  
  
Order by B. Limit
 
 
WITH REPORT_DATA AS  (SELECT DW.DWID,DW.DWMC,JY.JYXM,JY.JH,RMPC.R_OPATE_NUM   FROM REPORT_MONTH_PERSON_COUNT RMPC,JWT_JYXX JY,T_DWXX DW  WHERE RMPC.JYID = JY.JYUSERID  AND JY.SSDW = DW.DWID  AND RMPC.R_YEAR = 2013  AND RMPC.R_MONTH = 6  AND JY.SSDW LIKE '4102%'  ORDER BY RMPC.R_OPATE_NUM DESC)SELECT B.*FROM (SELECT D.*, ROW_NUMBER() OVER(ORDER BY D.R_OPATE_NUM DESC) AS INX       FROM REPORT_DATA D       ) BWHERE B.INX <=100ORDER BY B.INX 

The following is an example of deleting duplicate data through row_number () over (...). It is for reference only:

Delete from acc_fundnv
Where rowid in (select row1
From (select rowid row1,
Row_number () over (partition by HOST_ID order by rowid) maid
From acc_fundnv)
Where lev> 1)

RANK ():In sorting, the ranking is the same as the ranking. You can join two first places and then add 3rd.

LAG:After grouping and sorting, the difference between the first record and the next record in the group is reduced. The first record can return NULL.

BTW

Rank () is the Skip sorting. When there are two second names, the next is the fourth name (also in each group)
Dense_rank () l is a continuous sorting, with two second names still followed by the third.
In contrast, row_number does not have repeated values.
Lag (arg1, arg2, arg3 ):
Arg1 is the expression returned from other rows.
Arg2 is the offset of the current row partition to be retrieved. Is a positive offset, and the number of rows in the past is retrieved.
Arg3 is the value returned when the number indicated by arg2 exceeds the group range.

For more information, see oracle function analysis (for example, over ().
Oracle analysis function technical details (with window function over () Oracle LAG and LEAD analysis functions