Oracle analysis function over and Window Function

I. analysis function over
Oracle provides analysis functions starting from 8.1.6. The analysis function is used to calculate a group-based aggregate value. Different from the aggregate function, it returns multiple rows for each group, the aggregate function returns only one row for each group.

 

Count 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

 

 

 

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:
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

SQL> select sum (aa) over (order by aa range between 2 preceding and 2 following) from A1;

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)

 

 

 

--

 

Common analysis functions are listed as follows:

Row_number () over (partition by... order ...)
Rank () over (partition
... Order ...)
Dense_rank () over (partition by... order ...)
Count ()
Over (partition by... order ...)
Max () over (partition by... order
...)
Min () over (partition by... order ...)
Sum () over (partition...
Order ...)
Avg () over (partition by... order ...)
First_value ()
Over (partition by... order ...)
Last_value () over (partition by... order
By ...)
Lag () over (partition by... order ...)
Lead () over (partition
... Order ...)

--

--

--

 

Common analysis functions are listed as follows:

 

1. row_number () over (partition by... order ...)
2. rank () over (partition by... order ...)
3. dense_rank () over (partition by... order ...)
4. count () over (partition by... order ...)
5. max () over (partition by... order ...)
6. min () over (partition by... order ...)
7. sum () over (partition by... order ...)
8. avg () over (partition by... order ...)
9. first_value () over (partition by... order ...)
10. last_value () over (partition by... order ...)
11. lag () over (partition by... order ...)
12. lead () over (partition by... order ...)

 

 

 

About partition

 

These are all analysis functions. It seems that row_number () is similar to rownum only after 8.0, and the function is more powerful (it can be sorted from 1 on each group)
Rank () is the Skip sorting. When there are two second names, the next is the fourth name (also in each group)
Dense_rank () is a continuous sorting, with two second names still followed by the third.

In contrast, row_number is a lag (arg1, arg2, arg3) with no repeated values ):
Arg1 is the expression returned from other rows. arg2 is the offset of the current row partition to be retrieved. Is a positive offset, and the number of rows in the past is retrieved. Arg3 is the value returned when the number indicated by arg2 exceeds the group range.

 

 

1.
Select deptno, row_number () over (partition by deptno order by sal) from
Emp order by deptno;
2.
Select deptno, rank () over (partition by deptno
Order by sal) from emp order by deptno;
3.
Select deptno, dense_rank ()
Over (partition by deptno order by sal) from emp order by deptno;
4.
Select
Deptno, ename, sal, lag (ename, 1, null) over (partition by deptno order by ename) from
Emp ord er by deptno;
5.
Select deptno, ename, sal, lag (ename, 2, 'example ')
Over (partition by deptno order by ename) from em p
Order
Deptno;
6.
Select deptno, sal, sum (sal) over (partition by deptno) from
Emp; -- the total values after each record are select deptno, sum (sal) from emp group by deptno;
7.
Calculate the average salary of each department and the wage difference between each person and the Department.

 

Select deptno, ename, sal,
Round (avg (sal) over (partition by deptno ))
As dept_avg_sal,
Round (sal-avg (sal) over (partition by deptno)
Dept_sal_diff
From emp;