Detailed Oracle analytic functions

2010-12-14  来源:本站原创  分类:Database  人气:134 

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:
<analytic-function> (<argument>, <argument> ,...)
over (
<query-partition-clause>
<order-by-clause>
<windowing-clause>
)
Of which:
1 over the keyword used to identify the analysis function.

2 <analytic-function> is the name of the specified analysis function. Oracle analytic function a lot.

3 <argument> for the parameters of the function can select the 0-3 parameters.

4 partition clause <query-partition-clause> 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 order-by-clause specifies how the data is there sub-region. 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, windowing-clause 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 sub-partition 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) TOP-N 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 5-1 <= aa <= 5 +2 and is for aa = 2, sum = 1 +2 +2 +2 +3 +4 = 14;
Another example for aa = 9 ,9-1 <= 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

相关文章
  • Detailed Oracle analytic functions 2010-12-14

    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

  • Oracle Analytic Functions RANK (), ROW_NUMBER (), LAG (), etc. Use (reproduced) 2010-11-15

    Oracle Analytic Functions RANK (), ROW_NUMBER (), LAG () to use such ROW_NUMBER () OVER (PARTITION BY COL1 ORDER BY COL2) Said the group based on COL1, COL2 sort according to the group and this internal value that each number within the ordered seque

  • oracle analytic functions over (transfer) 2010-08-13

    1, Oracle analysis functions Description: In the daily production environment, we have contacted more than yes OLTP Xitong (ie Online Transaction Process), the system is characterized by Jubei Shishiyaoqiu, Or at least for the length of time Xiangyin

  • Oracle Analytic Functions: RANK, DENSE_RANK, FIRST and LAST 2010-12-13

    Oracle / PLSQL: Rank Function: http://techonthenet.com/oracle/functions/rank.php Oracle Rank: http://psoug.org/reference/rank.html RANK, DENSE_RANK, FIRST and LAST Analytic Functions: http://www.oracle-base.com/articles/misc/RankDenseRankFirstLastAna

  • Oracle Analytic Functions: RANK, DENSE_RANK, FIRST and LAST; PARTITION BY 2010-12-13

    Oracle / PLSQL: Rank Function: http://techonthenet.com/oracle/functions/rank.php Oracle Rank: http://psoug.org/reference/rank.html RANK, DENSE_RANK, FIRST and LAST Analytic Functions: http://www.oracle-base.com/articles/misc/RankDenseRankFirstLastAna

  • oracle analytic functions over and the window function 2011-01-11

    oracle analytic functions over and the window function A: Analysis of function over 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, and aggregate functions

  • Oracle analytic functions. Model multidimensional functions and a brief description of the function, the main newspaper for BI 2011-06-10

    The following code is tested and can be directly run Oracle analytic functions, multi-dimensional functions, and a brief description of Model functions, mainly for BI reporting statistics, not very comprehensive, but BI has done a little description

  • Layman Oracle analytic functions 2011-07-04

    Oracle development topics of: analysis of function (OVER) 1 Oracle development topics of: analysis of the function 2 (Rank, Dense_rank, row_number) 6 Oracle development topics of: analysis of the function 3 (Top / Bottom N, First / Last, NTile) 10 Or

  • About oracle analytic functions Summary (attach a line to switch out examples) 2011-07-11

    oracle analytic functions is the sort of data grouped Here with the emp table and dept table as an example. Title: Find the top four in each sector wage employee information There are three cases: 1 with the rank (), ranking is not continuous, jumpin

  • ORACLE - Oracle analytic functions detailed in [1] 2010-06-07

    1. Analysis of the function 1 (OVER) Contents: =============================================== About 1.Oracle analysis functions 2. Oracle simple example of analysis functions 3. Of an analytic function OVER, Oracle analysis functions Description: In

  • Oracle Analytic Functions RANK (), ROW_NUMBER (), LAG () to use such 2010-10-15

    oracle analytic function rank (), row_number (), lag () to use such row_number () over (partition by col1 order by col2) Said the group based on col1, col2 order in the group and this internal value based on said sorted order within each number (the

  • oracle analytic functions dense_rank over partition 2011-03-14

    oracle analysis and statements: http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm # DWHSG0205 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:122801500346829407 oracle aggregate functions: http://psoug.or

  • oracle analytic functions notes automatically generated serial number query results --- 2010-09-20

    Under normal circumstances, can be as follows: select rownum, a from A; But when later associated with multiple tables, order by order when the select rownum, a from A,B where A.a=B.b order by a. Field ; rownum could be chaos. At this time, we can us

  • ORACLE - Oracle analytic functions detailed in [2] 2010-06-07

    1. Analysis of the function 2 (rank \ dense_rank \ row_number) Directory =============================================== 1. Using rownum to record position 2. Using the analysis function to the record position 3. Use of the records division for the r

  • oracle analytic functions 2010-04-18

    Analysis function is oracle816 introduced a new concept , We analyzed the data to provide a simple and efficient approach. In the analysis of function occurs before , We must use the self-associated queries, subqueries or inline views , Even complex

  • Oracle analytic functions of the use of (zt) 2010-07-30

    2/11/2005 09:43 FPOracle analysis functions use the description Analysis function is oracle816 the Gainian introduced a new, As we analyze the data provider of a simple and efficient approach. In analysis Hanshuchuxian Yi Qian, we Bixushiyong Zi Unit

  • Using Oracle analytic functions and sys_connect_by_path row_number row of data into multi-line 2010-11-11

    demo scene to oracle database table emp own example: select ename, deptno from emp order by deptno; ENAME DEPTNO CLARK 10 KING 10 MILLER 10 SMITH 20 ADAMS 20 FORD 20 SCOTT 20 JONES 20 ALLEN 30 BLAKE 30 MARTIN 30 JAMES 30 TURNER 30 WARD 30 Now people

  • oracle analytic functions lag lead (turn) 2010-08-13

    Lag and Lead functions can be removed in the same field in a query, the first N rows of data and the value after the N line. This operation can be used to connect to the same table the table to achieve, but the use of LAG and LEAD have higher efficie

  • Using Oracle analytic functions and sys_connect_by_path row_number multiple lines of data together into one 2010-11-11

    demo scene, comes to oracle database table emp as an example: select ename, deptno from emp order by deptno; ENAME DEPTNO CLARK 10 KING 10 MILLER 10 SMITH 20 ADAMS 20 FORD 20 SCOTT 20 JONES 20 ALLEN 30 BLAKE 30 MARTIN 30 JAMES 30 TURNER 30 WARD 30 No

  • Over Oracle analytic functions 2010-12-10

    mark Reference: http://www.blogjava.net/pengpenglin/archive/2008/06/25/210536.html # part1