Oracle over () function usage instructions

Example 1

The code is as follows

Copy Code

–select * from TMN_ZHBG ORDER BY id DESC Select ID, mc,fs,class,sum (FS) over (the order by Fs,id) continuous accumulation, sum (FS) over (order by FS) continuous accumulation of xx,sum (FS) over (partition by ID) to group the sum, SUM (FS) over (partition by-ID ORDER by FS) group ID Continuous From ( Select 1 ID, ' yw ' mc,50 fs,1 class from dual UNION ALL Select 1, ' SX ', 81,1 from dual UNION ALL Select 1, ' yy ', 20,1 from dual UNION ALL Select 2, ' yw ', 81,1 from dual UNION ALL Select 2, ' SX ', 32,1 from dual UNION ALL Select 2, ' yy ', 21,1 from dual UNION ALL Select 4, ' yw ', 10,1 from dual UNION ALL Select 4, ' SX ', 82,1 from dual UNION ALL Select 4, ' yy ', 20,1 from dual ) A

Add an example

Table T_pi_part
Field ID code Name
Value 1 222 A
Value 2 222 b
Value 3 333 c
Add line labels to the same part code for code, sorted by ID

The code is as follows	Copy Code
Select P.*, Row_number () over (partition by P.code ORDER BY a.id Desc) as Row_index to T_pi_part p;

One: Analysis function over
Oracle provides analytic functions from 8.1.6, which are used to compute some sort of aggregation value based on a group, and the difference between it and the aggregate function is
Multiple rows are returned for each group, and aggregate functions return only one row for each group.
Here are a few examples to illustrate its application.
1: Statistics The turnover of a store.
Date Sale
1 20
2 15
3 14
4 18
5 30
Rule: Daily Statistics: The total number of days before
The results obtained:
DATE SALE SUM
----- -------- ------
1 20 20--1 days
2 15 35--1 days + 2 days
3 14 49--1 days + 2 days + 3 days
4 18 67.
5 30 97.

2: Statistics of the first class results of the students information
NAME CLASS S
----- ----- ----------------------
FDA 1 80
FFD 1 78
DSS 1 95
CFE 2 74
GDS 2 92
GF 3 99
DDD 3 99
ADF 3 45
ASDF 3 55
3DD 3 78

Pass:
--

The code is as follows	Copy Code
SELECT * FROM ( Select Name,class,s,rank () over (partition by class order by S desc) mm from T2 ) where Mm=1

--
Get the result:
NAME CLASS S MM
----- ----- ---------------------- ----------------------
DSS 1 95 1
GDS 2 92 1
GF 3 99 1
DDD 3 99 1

Attention:
1. In the first place, you cannot use Row_number (), because if there are two in the class and first, Row_number () returns only one result
The difference between 2.rank () and Dense_rank () is:
--rank () is a jump sort, with two second names followed by fourth place
--dense_rank () L is a sequential sort, with two second names still followed by third