Oracle analytic function over and open window function

Analysis function over and open window function

One: Analysis function over

Oracle provides analytic functions from 8.1.6, which are used to compute some sort of aggregation value based on a group, and the difference between it and the aggregate function is

Multiple rows are returned for each group, and aggregate functions return only one row for each group.

Here are a few examples to illustrate its application.

1: Statistics The turnover of a store.

Date Sale

1 20

2 15

3 14

4 18

5 30

Rule: Daily Statistics: The total number of days before

The results obtained:

DATE SALE SUM

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

1 20 20--1 days

2 15 35--1 days + 2 days

3 14 49--1 days + 2 days + 3 days

4 18 67.

5 30 97.

2: Statistics of the first class results of the students information

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

--

Get the result:

NAME CLASS S MM

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

DSS 1 95 1

GDS 2 92 1

GF 3 99 1

DDD 3 99 1

Attention:

1. In the first place, you cannot use Row_number (), because if there are two in the class and first, Row_number () returns only one result

The difference between 2.rank () and Dense_rank () is:

--rank () is a jump sort, with two second names followed by fourth place

--dense_rank () L is a sequential sort, with two second names still followed by 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

Get the 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 you use Sum,group by, you can only get

A SUM (C)

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

H 3

M 4

N 6

X 9

Cannot get B column value

=====

Two: Open Window function

The open Window function specifies the size of the data window that the profiling function works on, and the size of the data window may change as the row changes, for example:

1.

Over (orders by salary) accumulates according to the salary sort, and the order by is a default open window function

Over (partition by DEPTNO) Division by Department

2.

Over (order by salary range between 5 preceding and 5 following)

The corresponding data window for each row is not more than 5 before the row amplitude value, and then the row amplitude value does not exceed