Oracle analysis functions

Paste to the old zizhu's website: http://www.laozizhu.com/view-Oracle%E5%88%86%E6%9E%90%E5%87%BD%E6%95%B0%E8%AF%A6%E8%A7%A3-20279.htm (he will not complain to me, not allowed to repost the article)

 

It is designed by Oracle analysis functions for issues such as "total business volume", "Finding the percentage in A group", or "calculating the top few.

Analysis functions are efficient and easy to use.

Analysis functions are calculated based on a group of rows. This is different from Aggregate functions and is widely used in OLAP environments.

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.

Syntax:

<Analytic-function> (<argument>, <argument> ,...)

Over (

<Query-partition-clause>

<Order-by-clause>

<Windowing-clause>

)

Where:

1 over is a keyword used to identify analysis functions.

2 <analytic-function> is the name of the specified analysis function. There are many Oracle analysis functions.

3 <argument> is a parameter. You can select 0-3 parameters for the analysis function.

4. The format of the partition clause <query-partition-clause> is:

Partition by <value_exp> [, value_expr]...

The keyword partition by clause logically divides a single result set into N groups based on the conditions of the partition expression. "Partition" and "group" here"

Are synonyms.

5. Order-by-clause specifies how data exists in a partition. The format is:

Order [siblings] By {expr | position | c_alias} [ASC | DESC] [nulls first | nulls last]

Where:

(1) ASC | Desc: Specifies the order.

(2) nulls first | nulls last: Specifies the first or last position of the returned row containing null values in the ordered sequence.

6 window clause windowing-clause

A fixed or changed data window method is provided. The analysis function operates the data. In a group of Windows based on any change or fixed,

This clause allows the analysis function to calculate its value.

Format:

{Rows | range}

{

{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...: indicates the start and end of a window.

(3) unbounded preceding: indicates that the window starts from the first row of the partition.

(4) current row: indicates that the window starts from the current row.

Create Table EMP (

Deptno varchar2 (20), -- department code

Ename varchar2 (20), -- name

Sal number (10); -- Salary

Insert into EMP values ('10', 'andy1 ', 2000 );

Insert into EMP values ('10', 'and2', 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 );

1. Continuous summation

Select deptno, ename, Sal, sum (SAL) over (order by ename) continuous summation 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 discontinuous summation

Select deptno, ename, Sal, sum (SAL) over () discontinuous summation from EMP;

Deptno ename Sal discontinuous 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) continuous summation,

Sum (SAL) over () sum,

100 * Round (SAL/sum (SAL) over (), 4) "share (% )"

From EMP

/

Deptno ename Sal sum of continuous sum (%)

---------------------------------------------------------------------------------

10 andy1 2000 2000 43000 4.65

10 andy2 3000 5000 43000 6.98

10 andy3 2000 7000 43000

30 jack1 5000 12000 43000

30 jack2 6000 18000 43000

30 jack3 7000 25000 43000

20 leno1 4000 29000 43000 9.3

20 leno2 8000 37000 43000 18.6

20 leno3 6000 43000 43000 13.95

4. subpartition query.

The sum of consecutive salaries by department.

(1) Select deptno, sum (SAL) over (partition by deptno order by ename) Calculate the sum continuously by department from EMP;

Deptno calculates the sum by Department consecutively

------------------------------------

10 2000

10 5000

10 7000

20 4000

20 12000

20 18000

30 5000

30 11000

30 18000

(2) Calculate the sum by department

Select deptno, sum (SAL) over (partition by deptno) calculates the sum continuously by department from EMP;

Deptno calculates the sum by department

------------------------------------

10 7000

10 7000

10 7000

20 18000

20 18000

20 18000

30 18000

30 18000

30 18000

(3) The sum is not calculated continuously by department

Select deptno, sum (SAL) over (order by deptno, ename) does not calculate the sum continuously by department from EMP;

Deptno does not calculate the sum continuously by department

--------------------------------------

10 2000

10 5000

10 7000

20 11000

20 19000

20 25000

30 30000

30 36000

30 43000

(4) If the sum of all employees is not calculated by department, the effect is equivalent to sum (SAL)

Select deptno, sum (SAL) over (order by deptno, ename) does not calculate the sum continuously by department from EMP;

Deptno does not calculate the sum continuously by department

--------------------------------------

10 2000

10 5000

10 7000

20 11000

20 19000

20 25000

30 30000

30 36000

30 43000

(5) Select deptno, ename, Sal,

Sum (SAL) over (partition by deptno order by ename) Department continuous sum, -- department salary "continuous" sum

Sum (SAL) over (partition by deptno) Department sum, -- the sum of Department statistics, the sum of the same department unchanged

100 * Round (SAL/sum (SAL) over (partition by deptno), 4) "department share (% )",

Sum (SAL) over (order by deptno, ename) continuous sum, -- the salary of all departments "continuous" sum

Sum (SAL) over () sum, -- sum (SAL) over () is equivalent to sum (SAL), total salary of all employees.

100 * Round (SAL/sum (SAL) over (), 4) "total share (% )"

From EMP;

Deptno ename Sal Department continuous sum Department total department share (%) total sum of continuous sum (%)

------------------------------------------------------------------------------------------------------------------

10 andy1 2000 2000 7000 28.57 2000 43000

10 andy2 3000 5000 7000 42.86 5000 43000

10 andy3 2000 7000 7000 28.57 7000 43000

20 leno1 4000 4000 18000 22.22 11000 43000 9.3

20 leno2 8000 12000 18000 44.44 19000 43000

20 leno3 6000 18000 18000 33.33 25000 43000 13.95

30 jack1 5000 5000 18000 27.78 30000 43000

30 jack2 6000 11000 18000 33.33 36000 43000

30 jack3 7000 18000 18000 38.89 43000 43000

(6) TOP-N Query

6.1 query the highest wage records of each department

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 Android 3000 1

20 leno2 8000 1

30 jack3 7000 1

6.2 sort the ranking of employees in the Department and the company by salary.

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 windows

A window clause is a sliding window of data. The analysis functions of the window form a group of windows.

Select deptno "department ID", ename "department name", Sal "salary ",

Sum (SAL) over (partition by deptno order by ename rows 2 preceding) "sliding total"

From EMP order by deptno, ename;

Partition by deptno: equivalent to group by deptno

Rows 2: the first two rows are added.

Preceding: Indicates starting from the first row of each department.

6. range window

Range windows is only valid for data of the data value and date type. (Sal)

Select deptno, ename, Sal, count (*) over (order by Sal ASC range 3 preceding) Total

7 rows window

Is a physical unit, including the number of physical rows in the window. There is no limit on the data type.

Calculate the average salary of each record and the previous two records.

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. Determine the first or last row in each group.

Use the first_vale and last_value functions to select each row and the last row from a group.

Calculates the minimum or highest wage for each department.

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

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

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)