Analysis Function over and Window Function
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: Calculate 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:
--
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
3. classification statistics (and display information) A B C
--------------------------
M a 2
N a 3
M a 2
N B 2
N B 1
X B 3
X B 2
X B 4
H B 3 select a, c, sum (c) over (partition by a) from T2
Expected result:
A B C sum (c) over (partitionbya)
-----------------------------------
H B 3 3
M a 2 4
M a 2 4
N a 3 6
N B 2 6
N B 1 6
X B 3 9
X B 2 9
X B 4 9
If sum and group by are used, only
A sum (c)
------------------------
H 3
M 4
N 6
X 9
Column B value cannot be obtained.
===== 2: Window Function
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:
Order by salary is a default window function.
Over (partition by deptno) by Department Partition
2:
Over (order by salary range between 5 preceding and 5 following)
The data window corresponding to each row is that the range value of the previous row cannot exceed 5, and the range value of the subsequent row cannot exceed 5
For example
AA
1
2
2
2
3
4
5
6
7
9
Sum (AA) over (order by AA range between 2 preceding and 2 following)
The result is:
AA sum
-----------------------------------------------------------------------------
1 10
2 14
2 14
2 14
3 18
4 18
5 22
6 18
7 22
9 9
That is to say, for a row with AA = 5, sum is the sum of 5-1 <= AA <= 5 + 2
For AA = 2, sum = 1 + 2 + 2 + 2 + 3 + 4 = 14;
For example, for AA = 9, 9-1 <= AA <= 9 + 2, there is only 9 numbers, so sum = 9;
3: Others:
Over (order by salary rows between 2 preceding and 4 following)
The data window corresponding to each row is the first two rows and the last four rows
4: The following three statements are equivalent:
Over (order by salary rows between unbounded preceding and unbounded following)
The data window corresponding to each row is from the first row to the last row, which is equivalent:
Over (order by salary range between unbounded preceding and unbounded following)
Equivalent
Over (partition by null)
