OVER (partition by) function introduction, overpartition

OVER (partition by) function introduction, overpartition

• Problem scenarios　

Recently, in the project, we encountered a sum for each type and the proportion of this type. At that time, we considered finding the sum for each type, in java, the sum and sum of the sum of each type are obtained respectively. Later, it was a little troublesome to think about this, and the persistence layer in the project used the iBatis framework. All the considerations were to start with SQL to simplify this problem.

Then the SQL solution is:

1 select t. channel as pattern, 2 COUNT (T. TRANSACTIONKEY) AS T_COUNT, 3 SUM (T. AMT) AS T_AMT, 4 ROUND (100 * SUM (T. AMT)/SUM (T. AMT) OVER (partition by 1), 2) AS AMT_PERCENT, 5 ROUND (100 * COUNT (T. TRANSACTIONKEY)/SUM (COUNT (T. TRANSACTIONKEY) OVER (partition by 1), 2) AS COUNT_PERCENT6 from xx (Table Name) T7 where t. PARTY_ID = '000000' 8 group by t. CHANNEL

I admire the power of SQL here, So I thoroughly studied the OVER (PARTITION BY) function of objective el.

• Introduction

Window Function. Oracle provides analysis functions starting from 8.1.6. The analysis function is used to calculate a group-based aggregate value. Different from the aggregate function, it returns multiple rows for each group, the aggregate function returns only one row for each group.

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.

The following test case data statement is as follows:

1 create table T2_TEMP (2 NAME varchar2 (10) primary key, 3 CLASS varchar2 (10), 4 sroce number 5) 6 7 insert into T2_TEMP (NAME, CLASS, SROCE) 8 values ('cfe ', '2', 74); 9 10 insert into T2_TEMP (NAME, CLASS, SROCE) 11 values ('dss', '1', 95 ); 12 13 insert into T2_TEMP (NAME, CLASS, SROCE) 14 values ('ffd', '1', 95); 15 16 insert into T2_TEMP (NAME, CLASS, SROCE) 17 values ('fda ', '1', 80); 18 19 insert into T2_TEMP (NAME, CLASS, SROCE) 20 values ('gds', '2', 92 ); 21 22 insert into T2_TEMP (NAME, CLASS, SROCE) 23 values ('gf ', '3', 99); 24 25 insert into T2_TEMP (NAME, CLASS, SROCE) 26 values ('ddd ', '3', 99); 27 28 insert into T2_TEMP (NAME, CLASS, SROCE) 29 values ('adf', '3', 45 ); 30 31 insert into T2_TEMP (NAME, CLASS, SROCE) 32 values ('asdf ', '3', 55); 33 34 insert into T2_TEMP (NAME, CLASS, SROCE) 35 values ('3dd', '3', 78 );View Code

1. Write the over function:

Over (partition by class order by sroce) is accumulated by sroce. order by is a default window opening function, which is partitioned by class.

2. Window range:

Over (order by sroce range between 5 preceding and 5 following): The window range is within the range after the current row's data range is reduced by 5 plus 5.

Over (order by sroce rows between 5 preceding and 5 following): The window range is 5 rows before and after the current row.

3. Introduction to functions combined with over () Functions

(1) query the first place in each class:

1 SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

Result:

The result 1 is: 2 dss 1 95 13 ffd 1 95 14 gds 2 92 15 gf 3 99 16 ddd 3 99 1

Note: 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.

1 SELECT * FROM (select t.name,t.class,t.sroce,row_number() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

Result:

dss      1        95        1  gfs      2        92        1ddd      3        99        1 

It can be seen that the first place is the combination of two people, and only one result is displayed.

(2) rank () and dense_rank () can be used to search for all the items, and rank can be used to search for the first place. The difference between rank () and dense_rank () is as follows: rank () is the Skip sorting. When there are two second names, the next is the fourth name.

Rank the class score:

1 select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

Query results:

dss        1        95        1ffd        1        95        1fda        1        80        3gds        2        92        1cfe        2        74        2gf         3        99        1ddd        3        99        13dd        3        78        3asdf       3        55        4adf        3        45        5

Dense_rank () l is a continuous sorting, with two second names still followed by the third

1 select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

Query results:

dss        1        95        1ffd        1        95        1fda        1        80        2 gds        2        92        1cfe        2        74        2gf         3        99        1ddd        3        99        13dd        3        78        2asdf       3        55        3adf        3        45        4

3. Use sum () over ()

Sum scores by class

1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

Dss 1 95 190 -- since both 95 are the first, so the sum is the sum of the two first places ffd 1 95 190 fda 1 80 270 -- the first place and the second place gds 2 92 92cfe 2 74 166gf 3 99 198ddd 3 99 1983dd 3 78 276 asdf 3 55 331adf 3 45 376

4. Use of first_value () over () and last_value () over ()

1 select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;2 select t.name,t.class,t.sroce,last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

Calculate the first score and the last score respectively.

5. Use sum () over ()

1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

Calculate the total score of the class.

There are many usage cases below, so I will not list them one by one. A brief introduction is similar to the above usage:

 

Count () over (partition by... order by...): calculates the total number of groups.
Max () over (partition by... order by...): returns the maximum value after grouping.
Min () over (partition by... order by...): calculates the minimum value after grouping.
Avg () over (partition by... order by...): calculates the average value after grouping.
Lag () over (partition by... order by...): extract the first n rows of data.

Lead () over (partition by... order by...): n rows of data after removal.

Ratio_to_report () over (partition by... order by...): Ratio_to_report () contains molecules, and over () contains denominator.

Percent_rank () over (partition by... order ...):

6. Differences between over partition by and group:

Group by is a simple grouping of the reserved rows in the search results. It is generally used together with Aggregate functions, such as max, min, sum, avg, and count. Although partition by has the grouping function, it also has other advanced functions.