Introduction to over (partition by) Functions

Window Function
Oracle provides analysis functions starting from 8.1.6. The analysis function is used to calculate a group-based aggregate value. The difference between the analysis function and the aggregate function is that multiple rows are returned for each group, the aggregate function returns only one row for each group.

The Window Function specifies the size of the data window used by the analysis function. The size of the data window may change with the change of rows. For example:
1: write after over:
Order by salary is a default window function.
Over (partition by deptno) by Department Partition

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 after the data margin of the current row is reduced by 5 plus 5.

Example:

-- Sum (s) over (order by s range between 2 preceding and 2 following) indicates the 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 that is 43 to 47, but s is only 45 in this range
ASDF 3 55 55
CFE 2 74
3DD 3 78 158 -- 78 in the range of 76 to 80 has 158, 80, and the sum is.
FDA 1 80 158
GDS 2 92
FFD 1 95 190
DSS 1 95 190
Ddd 3 99 198

GF 399 198

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

Example:

-- Sum (s) over (order by S rows between 2 preceding and 2 following) indicates 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 399 293

Over (order by salary range between unbounded preceding and unbounded following) or
Over (order by salary rows between unbounded preceding and unbounded following): the window is not limited.

3. Introduction to several functions combined with over functions

Row_number () over (), rank () over (), and dense_rank () over () Functions

The following table describes the application of the class sequence table T2.
Table T2 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 result is:
DSS 1 95 1
FFD 1 95 1
GDS 2 92 1
GF 3 99 1
Ddd 3 99 1

Note:
1. row_number () is not allowed when the first score is obtained, because if there are two parallel orders in the same class, 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 only one

2. rank () and dense_rank () can all be searched out:
As shown above, rank can be used to search for the first place in the same position;
Difference between rank () and dense_rank:
-- Rank () is the Skip sorting. When there are two second names, the next is the fourth name;
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 directly 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 continuous sorting, with two second names still followed by the third
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-continuous sorting (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 -- sum score based on class
DSS 1 95 190 -- since both 95 are the first place, the sum of the first place is the sum of the two.
FFD 1 95 190
FDA 1 80 270 -- first and second
GDS 2 92
CFE 2 74 166
GF 399 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 the first record type and the last record type for each of the three circuits

Select opr_id, res_type,
First_value (res_type) over (partition by opr_id order by res_type) low,
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 ('123', '123', '123 ')
Order by opr_id;

Note: Use of rows between unbounded preceding and unbounded following

-- The result of removing rows between unbounded preceding and unbounded following from last_value

Select opr_id, res_type,
First_value (res_type) over (partition by opr_id order by res_type) low,
Last_value (res_type) over (partition by opr_id order by res_type) high
From rm_circuit_route
Where opr_id in ('123', '123', '123 ')
Order by opr_id;

As you can see, if you do not use

Rows between unbounded preceding and unbounded following, the retrieved last_value is arranged with res_type, so the type of records in the last line of the retrieved circuit is not extracted according to the range of the circuit, extracted in the range of res_type.

Use of ignore nulls in first_value and last_value
The data is as follows:

After the first record of the circuit is taken out and ignore nulls is added, if the first field is null, the next record is taken by default. The result is as follows:

-- Lag () over () function usage (retrieve the first n rows of data)
Lag (expresstion, <OFFSET >,< default>)
With a
(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;

-- Lead () over () function usage (N rows of data after extraction)

Lead (expresstion, <OFFSET >,< default>)
With a
(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;

-- Ratio_to_report (a) function usage: ratio_to_report () contains molecules, and over () contains denominator.
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
Order by;

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 default denominator is the entire proportion
Order by;

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
Group by a order by a; -- percentage After grouping