Oracle over () function instructions

I will not talk about the role of the over () function in oracle. Next I will share my understanding of the over () function usage and some examples of over () function applications.

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 (order by fs, id) continuous accumulation, sum (fs) over (order by fs) continuous accumulation XX, sum (fs) over (partition by id) group sum, Sum (fs) over (partition by id order by fs) group id consecutive 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, 'ss', 82,1 from dual Union all Select 4, 'yy', 20, 1 from dual )

Add an instance

Table t_pi_part
Field id code name
Value 1 222
Value 2 222 B
Value 3 333 c
Add a row mark to a part with the same code and sort it 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 from t_pi_part p;

 
 
 
I. analysis function over
Oracle provides analysis functions starting from 8.1.6. analysis functions are used to calculate a group-based aggregate value. What is different from aggregation functions?
Multiple rows are returned for each group, while the aggregate function returns only one row for each group.
The following examples illustrate the application.
1: Count the turnover of a store.
Date sale
1 20
2 15
3 14
4 18
5 30
Rule: daily statistics: the total amount of the previous days is calculated every day.
Result:
DATE SALE SUM
-------------------
1 20 20 -- 1 day
2 15 35 -- 1 day + 2 days
3 14 49 -- 1 day + 2 days + 3 days
4 18 67.
5 30 97.

2: Count the information of the first student in each class
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

--
Expected result:
NAME CLASS S MM
------------------------------------------------------
Dss 1 95 1
Gds 2 92 1
Gf 3 99 1
Ddd 3 99 1

Note:
1. row_number () cannot be used when the first score is obtained, because if there are two parallel orders in the same class, row_number () returns only one result.
2. The difference between rank () and dense_rank () is:
-- Rank () is the Skip sorting. When there are two second names, the next is the fourth name.
-- Dense_rank () l is a continuous sorting, with two second names still followed by the third