Eg: Related parsing: Table t_pi_part field ID code namevalue 1 222 avalue 2 222 bvalue 3 333 C add a row mark to the part code with the same code and sort select P by ID. *, row_number () over (partition by P. code Order order by. id DESC) as row_index from t_pi_part P; I. analysis functions
Over
OracleAnalytics functions are provided from 8.1.6. analytics functions are used to calculate a group-based aggregate value. What is different from Aggregate 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:
--
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.
=====
Select * from test
Data:
A B C
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6
--- Add the C column values with the same B field values to the total number.
Select a, B, c, sum (c) over (partition by B) c_sum
From Test
A B C c_sum
1 1 1 1
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6
--- If you do not need to split the values of a column, use null.
Eg: Put the column value summary of C behind each line
Select a, B, c, sum (c) over (partition by null) c_sum
From Test
A B C c_sum
1 1 1 17
1 2 2 17
1 3 3 17
2 2 5 17
3 4 6 17
Calculate the percentage of the individual's salary to the Department's salary
SQL> select * from salary;
Name dept Sal
-------------------
A 10 2000
B 10 3000
C 10 5000
D 20 4000
SQL> select name, DEPT, Sal, Sal * 100/sum (SAL) over (partition by dept) percent from salary;
Name dept Sal percent
-----------------------------
A 10, 2000, 20
B 10, 3000, 30
C 10, 5000, 50
D 20 4000 100
Ii. 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:
Over (Order by salary) is sorted by salary. Order by 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)
