Analysis functions with high efficiency, easy to use.
Analysis function is based on a set of lines to be calculated. This differs from the aggregate function and is widely used in OLAP environment.
Oracle began offering analysis functions from 8.1.6 to analyze the function used to calculate the aggregate value based on some kind of group it and the difference between aggregate function is to return multiple rows for each group, and aggregate functions only return one row for each group .
Syntax:
<analyticfunction> (<argument>, <argument> ,...)
over (
<querypartitionclause>
<orderbyclause>
<windowingclause>
)
Of which:
1 over the keyword used to identify the analysis function.
2 <analyticfunction> is the name of the specified analysis function. Oracle analytic function a lot.
3 <argument> for the parameters of the function can select the 03 parameters.
4 partition clause <querypartitionclause> the format:
partition by <value_exp> [, value_expr] ...
Keywords partition by clause, the expression under the conditions of the logical partition a single result set into N groups. Here the "partition partition" and "group group"
Are synonymous.
5 sort clause orderbyclause specifies how the data is there subregion. The format is:
order [siblings] by {expr  position  c_alias} [asc  desc] [nulls first  nulls last]
Of which:
(1) asc  desc: Specifies the sort order.
(2) nulls first  nulls last: specifies the return value contains an empty line should appear in the ordered sequence of the first or last position.
6 window, windowingclause clause
Given a fixed or changing data window method, the data analysis functions will operate. Any change in a group or on a fixed window,
Analysis of the function of the clause can be used to calculate its value.
Format:
{Rows  range}
{Between
{Unbounded preceding  current row  <value_expr> {preceding  following}
} And
{Unbounded preceding  current row  <value_expr> {preceding  following}
}  {Unbounded preceding  current row  <value_expr> {preceding  following
}}
(1) rows  range: This keyword defines a window.
(2) between ... and ...: the window refers to a start and end products.
(3) unbounded preceding: the specified window is from the partition (partition) the first line.
(4) current row: the specified window starting from the current line.
create table emp (
deptno varchar2 (20),  department code
ename varchar2 (20),  names
sal number (10 )); wages
insert into emp values ('10 ',' andy1 ', 2000);
insert into emp values ('10 ',' andy2 ', 3000);
insert into emp values ('10 ',' andy3 ', 2000);
insert into emp values ('20 ',' leno1 ', 4000);
insert into emp values ('20 ',' leno2 ', 8000);
insert into emp values ('20 ',' leno3 ', 6000);
insert into emp values ('30 ',' jack1 ', 5000);
insert into emp values ('30 ',' jack2 ', 6000);
insert into emp values ('30 ',' jack3 ', 7000);
A continuous sum
select deptno, ename, sal, sum (sal) over (order by ename) a continuous sum from emp;
DEPTNO ENAME SAL continuous summation
   
10 andy1 2000 2000
10 andy2 3000 5000
10 andy3 2000 7000
30 jack1 5000 12000
30 jack2 6000 18000
30 jack3 7000 25000
20 leno1 4000 29000
20 leno2 8000 37000
20 leno3 6000 43000
2 discrete summation
select deptno, ename, sal, sum (sal) over () is not continuous summation from emp;
DEPTNO ENAME SAL discrete summation
   
10 andy1 2000 43000
10 andy2 3000 43000
10 andy3 2000 43000
20 leno1 4000 43000
20 leno2 8000 43000
20 leno3 6000 43000
30 jack1 5000 43000
30 jack2 6000 43000
30 jack3 7000 43000
3.
select deptno, ename, sal,
sum (sal) over (order by ename) a continuous sum
sum (sal) over () total,
100 * round (sal / sum (sal) over (), 4) "share (%)"
from emp
/
DEPTNO ENAME SAL continuous sum total share (%)
      
10 andy1 2000 2000 43000 4.65
10 andy2 3000 5000 43000 6.98
10 andy3 2000 7000 43000 4.65
30 jack1 5000 12000 43000 11.63
30 jack2 6000 18000 43000 13.95
30 jack3 7000 25000 43000 16.28
20 leno1 4000 29000 43000 9.3
20 leno2 8000 37000 43000 18.6
20 leno3 6000 43000 43000 13.95
4. Using subpartition query.
The sum of salaries by department continuously.
(1) select deptno, sum (sal) over (partition by deptno order by ename) a continuous demand by sector the total from emp;
Continuous demand by sector, the sum of DEPTNO
 
10 2000
105,000
107,000
204,000
2012000
2018000
305,000
3011000
3018000
(2) the total demand by sector
select deptno, sum (sal) over (partition by deptno) continuous demand by sector the total from emp;
DEPTNO total demand by sector
 
107,000
107,000
107,000
2018000
2018000
2018000
3018000
3018000
3018000
(3) not continuous demand by sector, the sum of
select deptno, sum (sal) over (order by deptno, ename) not continuous demand by sector the total from emp;
DEPTNO continuous demand by sector is not the sum of
 
10 2000
105,000
107,000
2011000
2019000
2025000
3030000
3036000
3043000
(4) does not by sector, find the sum of all employees, the effect is equivalent to the sum (sal)
select deptno, sum (sal) over (order by deptno, ename) not continuous demand by sector the total from emp;
DEPTNO continuous demand by sector is not the sum of
 
10 2000
105,000
107,000
2011000
2019000
2025000
3030000
3036000
3043000
(5) select deptno, ename, sal,
sum (sal) over (partition by deptno order by ename) a continuous sum of sector  the sector salaries, "consecutive" Sum
sum (sal) over (partition by deptno) department of the sum  the sum of sector statistics, the same sum of the same department
100 * round (sal / sum (sal) over (partition by deptno), 4) "sectoral share (%)",
sum (sal) over (order by deptno, ename) consecutive sum,  the salaries of all sectors, "continuous" Sum
sum (sal) over () total,  where sum (sal) over () is equivalent to sum (sal), the total salaries of all employees
100 * round (sal / sum (sal) over (), 4) "Total share (%)"
from emp;
DEPTNO ENAME SAL sector sector Total sector share of continuous sum (%) share of the total sum of row sum (%)
          
10 andy1 2000 2000 7000 28.57 2000 4300 0 4.65
10 andy2 3000 5000 7000 42.86 5000 4300 0 6.98
10 andy3 2000 7000 7000 28.57 7000 4300 0 4.65
20 leno1 4000 4000 18000 22.22 11000 43000 9.3
20 leno2 8000 12000 18000 44.44 19000 43000 18.6
20 leno3 6000 18000 18000 33.33 25000 43000 13.95
30 jack1 5000 5000 18000 27.78 30000 43000 11.63
30 jack2 6000 11000 18000 33.33 36000 43000 13.95
30 jack3 7000 18000 18000 38.89 43000 43000 16.28
(6) TOPN query
6.1 check the sector recorded the highest wage
select * from (select deptno, ename, sal, row_number () over (partition by deptno order by sal desc) topn from emp) where topn = 1;
DEPTNO ENAME SAL TOPN
   
10 andy2 3000 1
20 leno2 8000 1
30 jack3 7000 1
6.2 high and low salaries for each employee in the sector and the company's ranking order.
select deptno, ename, sal, dense_rank () over (partition by deptno order by sal desc nulls last) as dept_ranking,
dense_rank () over (order by sal desc nulls last) as company_ranking
from emp;
DEPTNO ENAME SAL DEPT_RANKING COMPANY_RANKING
    
20 leno2 8000 1 1
30 jack3 7000 1 2
20 leno3 6000 2 3
30 jack2 6000 2 3
30 jack1 5000 3 4
20 leno1 4000 3 5
10 andy2 3000 1 6
10 andy1 2000 2 7
10 andy3 2000 2 7
5 window windows
Clause is the data window sliding window, the window of the window function as a group.
select deptno "sector ID", ename "Department Name", sal "wages"
sum (sal) over (partition by deptno order by ename rows 2 preceding) "sliding total"
from emp order by deptno, ename;
partition by deptno: the equivalent of group by deptno
rows 2: adding the first two lines that
preceding: that the first line from the beginning of each sector.
6 range window
Range windows only type of data values and date data is valid. (Sal)
select deptno, ename, sal, count (*) over (order by sal asc range 3 preceding) Total
7 line window is a physical unit that contains the physical lines in the window number. There is no limit on the data type.
Calculated for each record with its previous two records, the average wage.
set numformat 9999
select ename, sal,
avg (sal) over (order by deptno asc rows 2 preceding) avgasc,
count (*) over (order by deptno asc rows 2 preceding) cntasc,
avg (sal) over (order by deptno desc rows 2 preceding) avgdes,
count (*) over (order by deptno desc rows 2 preceding) cntdes
from emp order by deptno;
ENAME SAL AVGASC CNTASC AVGDES CNTDES
     
andy1 2000 2000 1 3666.66666 3
andy2 3000 2500 2 5666.66666 3
andy3 2000 2333.33333 3 2333.33333 3
leno1 4000 3000 3 5333.33333 3
leno2 8000 4666.66666 3 6333.33333 3
leno3 6000 6000 3 6000 3
jack1 5000 6333.33333 3 5500 2
jack2 6000 5666.66666 3 6000 1
jack3 7000 6000 3 6000 3
8 in each group to determine the first or last row with first_vale and last_value function selected from a group of line and last line of each statistical minimum or maximum salary of each department's employee information.
select deptno, ename, sal, first_value (ename) over (partition by deptno order by sal asc) as min_sal_has
from emp
order by deptno, ename;
select deptno, ename, sal, first_value (ename) over (partition by deptno order by sal desc) as min_sal_has
from emp
order by deptno, ename;
9 statistical information on each class of students grades first 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
By:

select * from
(
select name, class, s, rank () over (partition by class order by s desc) mm from t2
)
where mm = 1

Get results:
NAME CLASS S MM
    
dss 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1
Note:
1. In seeking the time the first result, can not use row_number (), because if the same class there are two tied for first, row_number () returns only one result
2.rank () and dense_rank () is the difference between:
 Rank () is the jumping order, two second place next is the fourth time
 Dense_rank () l is a continuous order, two second place is still followed by the third time
II: Window function window function specifies the data analysis functions work window size, the data window size may change with changes in the line, for example as follows:
1:
over (order by salary) in accordance with the salary ranking for cumulative, order by is the default window function
over (partition by deptno) partition by sector
2:
over (order by salary range between 5 preceding and 5 following)
Data window corresponding to each row before the row is the rate of not more than 5, then increase the value of no more than 5 lines
For example: For the following
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
  
110
2 14
2 14
2 14
3 18
4 18
522
6 18
7 22
99
That is, the line for aa = 5, sum is 51 <= aa <= 5 +2 and is for aa = 2, sum = 1 +2 +2 +2 +3 +4 = 14;
Another example for aa = 9 ,91 <= aa <= 9 +2 only 9 a number, so the sum = 9;
3: Other:
over (order by salary rows between 2 preceding and 4 following)
Corresponding to each row before the data window is 2 lines, then line 4
4: The following three statements are equivalent:
over (order by salary rows between unbounded preceding and unbounded following)
Each line corresponds to the data window is from the first line to last line, the equivalent:
over (order by salary range between unbounded preceding and unbounded following)
Equivalent
over (partition by null)
This article comes from CSDN blog, reproduced, please credit: http://blog.csdn.net/youjianbo_han_87/archive/2009/06/25/4297867.aspx