Usage of SQL over

Reprinted from: http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html

Over (PARTITION by) function introduction

Open Window function
Oracle provides analytic functions starting from 8.1.6, which are used to calculate some sort of aggregate value based on groups, which differs from aggregate functions in that multiple rows are returned for each group, whereas aggregate functions return only one row for each group.
The window function specifies the size of the data window in which the analysis function works, which may change depending on the row, as shown in the following example:
After the 1:over:
Over (order by salary) is cumulative by salary sort, and order by is a default window-opening function
Over (partition by Deptno) according to departmental zoning

Over (partition by Deptno order by salary)

2: Window Range:
Over (order by salary range between 5 preceding and 5 following): The window range is within the range of the current row data amplitude minus 5 plus 5.

Example:

--sum (s) over (order by s range between 2 preceding and 2 following) represents a sum in the range of 2 or 2

Select Name,class,s, sum (s) over (order by s range between 2 preceding and 2 following) mm from T2
ADF 3 45 45--45 plus 2 minus 2 is 43 to 47, but s is within this range only 45
ASDF 3 55 55
CFE 2 74 74
3DD 3 78 158--78 in the 76 to 80 range 78, 80, Sum 158
FDA 1 80 158
GDS 2 92 92
FFD 1 95 190
DSS 1 95 190
DDD 3 99 198
GF 3 99 198

Over (order by salary rows between 5 preceding and 5 following): The window range moves 5 rows before and after the current row.
Example:

--sum (s) over (order by s rows between 2 preceding and 2 following) represents the range between the upper and lower rows
Select Name,class,s, sum (s) over (order by s rows between 2 preceding and 2 following) mm from T2
ADF 3 45 174 (45+55+74=174)
ASDF 3 55 252 (45+55+74+78=252)
CFE 2 74 332 (74+55+45+78+80=332)
3DD 3 78 379 (78+74+55+80+92=379)
FDA 1 80 419
GDS 2 92 440
FFD 1 95 461
DSS 1 95 480
DDD 3 99 388
GF 3 99 293

Over (order by salary range between unbounded preceding and unbounded following) or
Over (order by salary rows between unbounded preceding and unbounded following): Window does not restrict

3. Introduction of several functions combined with over function

Use of Row_number () over (), rank () over () and Dense_rank () over () functions
The following is the class score table T2 to illustrate its application

T2 table information is as follows:
CFE 2 74
DSS 1 95
FFD 1 95
FDA 1 80
GDS 2 92
GF 3 99
DDD 3 99
ADF 3 45
ASDF 3 55
3DD 3 78

SELECT * FROM
(
Select Name,class,s,rank () over (partition by class order by S desc) mm from T2
)
where Mm=1;
The resulting results are:
DSS 1 95 1
FFD 1 95 1
GDS 2 92 1
GF 3 99 1
DDD 3 99 1

Attention:
1. Row_number () cannot be used when seeking the first grade, because if the class has two side-by-side first, row_number () returns only one result;
SELECT * FROM
(
Select Name,class,s,row_number () over (partition by class order by S desc) mm from T2
)
where Mm=1;
1 95 1--95 has two but only one
2 92 1
3 99 1--99 has two, but it only shows one.

2.rank () and Dense_rank () can find all of them:
As can be seen on the use of rank can be tied to the first name to find out;
Rank () and Dense_rank () difference:
--rank () is a jumping sort, with two second names followed by fourth place;
Select Name,class,s,rank () over (partition by class order by S desc) mm from T2
DSS 1 95 1
FFD 1 95 1
FDA 1 80 3--jump straight to the third
GDS 2 92 1
CFE 2 74 2
GF 3 99 1
DDD 3 99 1
3DD 3 78 3
ASDF 3 55 4
ADF 3 45 5
--dense_rank () L is a sequential sort, with two second names still following the third place
Select Name,class,s,dense_rank () over (partition by class order by S desc) mm from T2
DSS 1 95 1
FFD 1 95 1
FDA 1 80 2--sequential sequencing (still 2)
GDS 2 92 1
CFE 2 74 2
GF 3 99 1
DDD 3 99 1
3DD 3 78 2
ASDF 3 55 3
ADF 3 45 4

Use of--sum () over ()
Select Name,class,s, sum (s) over (partition by class order by S desc) mm from T2--Score summation according to class
DSS 1 95 190--because two 95 are the first, so the summation is the addition of two first place
FFD 1 95 190
FDA 1 80 270--first place plus second place
GDS 2 92 92
CFE 2 74 166
GF 3 99 198
DDD 3 99 198
3DD 3 78 276
ASDF 3 55 331
ADF 3 45 376

Use of First_value () over () and Last_value () over ()

--Find out the first record type and last record type of each circuit in these three circuits

SELECT Opr_id,res_type,
First_value (Res_type) over (PARTITION by opr_id ORDER by Res_type),
Last_value (Res_type) over (PARTITION by opr_id ORDER by res_type rows between unbounded preceding and unbounded following) High
From Rm_circuit_route
WHERE opr_id in (' 000100190000000000021311 ', ' 000100190000000000021355 ', ' 000100190000000000021339 ')
ORDER by opr_id;

Note: rows between unbounded preceding and unbounded following use

--the result of not using rows between unbounded preceding and unbounded following when taking Last_value

SELECT Opr_id,res_type,
First_value (Res_type) over (PARTITION by opr_id ORDER by Res_type),
Last_value (Res_type) over (PARTITION by opr_id ORDER by Res_type) high
From Rm_circuit_route
WHERE opr_id in (' 000100190000000000021311 ', ' 000100190000000000021355 ', ' 000100190000000000021339 ')
ORDER by opr_id;
As you can see, if you do not use

Rows between unbounded preceding and unbounded following, removed Last_value as arranged with Res_type, Therefore, the type of the last line of the circuit to be taken out is not extracted according to the range of the circuit, but is extracted in the range of Res_type.

Use of ignore nulls in First_value and Last_value
The data are as follows:

After removing the first record of the circuit, plus ignore nulls, if the first one is judged to be empty, the default is to remove one, and the result is as follows:

--lag () over () function usage (fetch first n rows of data)
Lag (expresstion,<offset>,<default>)
With A As
(select 1 ID, ' a ' name from dual
Union
Select 2 ID, ' B ' name from dual
Union
Select 3 ID, ' C ' name from dual
Union
Select 4 ID, ' d ' name from dual
Union
Select 5 ID, ' E ' name from dual
)
Select Id,name,lag (id,1, ') over [order by name] from A;

--lead () over () function usage (after fetching n rows of data)
Lead (expresstion,<offset>,<default>)
With A As
(select 1 ID, ' a ' name from dual
Union
Select 2 ID, ' B ' name from dual
Union
Select 3 ID, ' C ' name from dual
Union
Select 4 ID, ' d ' name from dual
Union
Select 5 ID, ' E ' name from dual
)
Select Id,name,lead (id,1, ') over [order by name] from A;

--ratio_to_report (a) function usage ratio_to_report () in parentheses is the numerator, and over () is the denominator in parentheses
With a as (select 1 A from dual
UNION ALL
Select 1 A from dual
UNION ALL
Select 1 A from dual
UNION ALL
Select 2 A from dual
UNION ALL
Select 3 A from dual
UNION ALL
Select 4 A from dual
UNION ALL
Select 4 A from dual
UNION ALL
Select 5 A from dual
)
Select a, Ratio_to_report (a) over (partition by a) b from a
Order by A;

With a as (select 1 A from dual
UNION ALL
Select 1 A from dual
UNION ALL
Select 1 A from dual
UNION ALL
Select 2 A from dual
UNION ALL
Select 3 A from dual
UNION ALL
Select 4 A from dual
UNION ALL
Select 4 A from dual
UNION ALL
Select 5 A from dual
)
Select a, Ratio_to_report (a) over () b from a-the denominator defaults to the entire proportion
Order by A;

With a as (select 1 A from dual
UNION ALL
Select 1 A from dual
UNION ALL
Select 1 A from dual
UNION ALL
Select 2 A from dual
UNION ALL
Select 3 A from dual
UNION ALL
Select 4 A from dual
UNION ALL
Select 4 A from dual
UNION ALL
Select 5 A from dual
)
Select a, Ratio_to_report (a) over () b from a
Percentage of GROUP by a order by a;--

Percent_rank usage
Calculation method: The group rank ordinal-1 divided by the group all the number of rows-1, as shown below the PR1 and the value obtained by the Percent_rank function is the same:
SELECT A.deptno,
A.ename,
A.sal,
A.R,
B.N,
(a.r-1)/(n-1) PR1,
Percent_rank () over (PARTITION by A.deptno ORDER by A.sal) PR2
From (SELECT Deptno,
ENAME,
Sal
Rank () over (PARTITION by Deptno ORDER by Sal) R--Calculates the rank number in the group
From EMP
ORDER by Deptno, Sal) A,
(SELECT Deptno, COUNT (1) n from emp GROUP by Deptno) B--Calculates all members of each department by department
WHERE A.deptno = B.deptno;

cume_dist function
Calculation method: The group rank ordinal divided by the group all the number of rows, but if there is a side-by-side situation, you need to add the number of side-1,
The PR1 computed as shown below is the same as the value obtained by the Percent_rank function:
SELECT A.deptno,
A.ename,
A.sal,
A.R,
B.N,
C.rn,
(A.R + c.rn-1)/n Pr1,
Cume_dist () over (PARTITION by A.deptno ORDER by A.sal) PR2
From (SELECT Deptno,
ENAME,
Sal
Rank () over (PARTITION by Deptno ORDER by Sal) r
From EMP
ORDER by Deptno, Sal) A,
(SELECT Deptno, COUNT (1) n from emp GROUP by Deptno) b,
(SELECT Deptno, R, COUNT (1) rn,sal
From (SELECT deptno,sal,
Rank () over (PARTITION by Deptno ORDER by Sal) r
From EMP)
GROUP by Deptno, r,sal
Order by Deptno) C--c table is to get the same number of employee salaries per department
WHERE A.deptno = B.deptno
and A.deptno = C.deptno (+)
and a.sal = C.sal;


Percentile_cont function
Meaning: Enter a percentage (which is the value computed by the Percent_rank function) and return the average of that percentage position
As below, the input percentage is 0.7, because 0.7 is between 0.6 and 0.8, so the result is 0.6 of the corresponding SAL 1500 plus 0.8 of the corresponding Sal 1600 average
SELECT ename,
Sal
Deptno
Percentile_cont (0.7) within GROUP (ORDER by Sal) through (PARTITION by Deptno) "Percentile_cont",
Percent_rank () over (PARTITION by Deptno ORDER by Sal) "Percent_rank"
From EMP
WHERE Deptno in (30, 60);

If you enter a percentage of 0.6, the corresponding SAL value is directly 0.6, which is 1500
SELECT ename,
Sal
Deptno
Percentile_cont (0.6) within GROUP (ORDER by Sal) through (PARTITION by Deptno) "Percentile_cont",
Percent_rank () over (PARTITION by Deptno ORDER by Sal) "Percent_rank"
From EMP
WHERE Deptno in (30, 60);

Percentile_disc function
Function Description: Returns a data value corresponding to the distribution percent value of the input, the calculation method of the distribution percentage is shown in function cume_dist, if there is no exact corresponding data value, take the next value greater than the distribution value.
Note: The difference between this function and Percentile_cont is that the alternative value returned when the corresponding distribution value is not found is calculated differently

Sample: In the following example, 0.7 of the distribution value in department 30 does not have a corresponding cume_dist value, so take off a distribution value 0.83333333 corresponding to the salary to replace

SELECT ename,
Sal,
Deptno,
Percentile_disc (0.7) within GROUP (ORDER by Sal) through (PARTITION by Deptno) "Perce Ntile_disc ",
Cume_dist () over (PARTITION by Deptno ORDER by Sal) ' Cume_dist '
from emp
WHERE Deptno in (30, 60);