Shulou(Shulou.com)06/03 Report--

What is the over analysis function? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Over (Partition by...) A super cowboy ORACLE unique function.

I have been using ORACLE every day for almost 2 years. I have only recently come into contact with this powerful and flexible function. What a shame!

Analysis function over and windowing function of oracle

One: analyze the function over

Oracle has provided parsing functions since 8.1.6, which are used to calculate certain aggregate values based on groups, which differ from aggregate functions in

Multiple rows are returned for each group, while the aggregate function returns only one row for each group.

Here are a few examples to illustrate its application.

1: count the turnover of a store.

Date sale

1 20

2 15

3 14

4 18

5 30

Rule: statistics by day: count the total amount of the previous few days every day.

The results obtained are:

DATE SALE SUM

1 20 20-1 day

2 15 35-1 day + 2 days

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

4 18 67.

5 30 97.

2: count the information of the students who are the first 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

Through:

--

Select * from

(

Select name,class,s,rank () over (partition by class order by s desc) mm from T2

)

Where mm=1

--

The results are obtained:

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 seeking the first place, because if there are two students in the same class who are tied for first place, row_number () only returns one result.

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

-- rank () is the jump sort. When there are two second places, the fourth place comes next.

-- dense_rank () l is sorted sequentially, with two second places still following the third place

3. Classified 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 dint c partition by a sum (c) over (partition by a) from T2

The results are obtained:

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

Unable to get column B value

=

Select * from test

Data:

A B C

1 1 1

1 2 2

1 3 3

2 2 5

3 4 6

-add up the corresponding C field values with the same B field value

Select a dint bjorc, SUM (C) OVER (PARTITION BY B) C_Sum

From test

A B C C_SUM

1 1 1

1 2 2 7

2 2 5 7

1 3 3 3

3 4 6 6

-if you don't need to split the value of a field, use null

Eg: put the field value summary of C after each line

Select a dint bjorc, SUM (C) OVER (PARTITION BY null) C_Sum

From test

A B C C_SUM

1 1 1 17

1 2 2 17

1 3 3 17

2 2 5 17

3 4 6 17

Ask for personal wages as a percentage of departmental wages

SQL > select * from salary

NAME DEPT SAL

-

A 10 2000

B 10 3000

C 10 5000

D 20 4000

SQL > select name,dept,sal,sal*100/sum (sal) over (partition by dept) percent from salary

NAME DEPT SAL PERCENT

A 10 2000 20

B 10 3000 30

C 10 5000 50

D 20 4000 100

Two: windowing function

The windowing function specifies the size of the data window in which the analysis function works, which may vary with rows, as an example:

1:

Over (order by salary) accumulates according to the order of salary. Order by is a default windowing function.

Over (partition by deptno) by department division

2:

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

The corresponding data window for each row is that the previous row amplitude value does not exceed 5, and the subsequent row amplitude value does not exceed 5.

For example: for the following

Aa

one

two

two

two

three

four

five

six

seven

nine

Sum (aa) over (order by aa range between 2 preceding and 2 following)

The result is that

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 line of aa=5, the sum is 5-1 select t.idrecoverecompenchar (b.rn) | |'/'| | t.id) e

2 from test t

(select rownum rn from (select max (to_number (id)) mid from test) connect by rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.