Oracle Analytic functions

This article describes Oracle Analytic function usage, first building the library:

SQL code

2. CREATE TABLE earnings --working Money table
4. (
6. Earnmonth VARCHAR2 (6), --Working month
8. Area VARCHAR2 (a), --working areas
10. Sno Varchar2 (Ten), --migrant worker number
12. Sname Varchar2 (a), --the name of the working person
14. times int, --Number of jobs this month
16. Singleincome number (10,2), --How much do you make each time?
18. Personincome number (10,2) --Total revenue for the month
20. )

Then insert the experimental data:

SQL code

2. INSERT into earnings values (' 200912 ',' peiping ',' 511601 ',' big qui ', 11,30,11*30);
4. INSERT into earnings values (' 200912 ',' peiping ',' 511602 ',' Grand Kay ', 8,25,8*25);
6. INSERT into earnings values (' 200912 ',' peiping ',' 511603 ',' Little East ', 30,6.25,30*6.25);
8. INSERT into earnings values (' 200912 ',' peiping ',' 511604 ',' big Bright ', 16,8.25,16*8.25);
10. INSERT into earnings values (' 200912 ',' peiping ',' 511605 ',' humble worship ', 30,11,30*11);
14. INSERT into earnings values (' 200912 ',' jinling ',' 511301 ',' Little Jade ', 15,12.25,15*12.25);
16. INSERT into earnings values (' 200912 ',' jinling ',' 511302 ',' little van ', 27,16.67,27*16.67);
18. INSERT into earnings values (' 200912 ',' jinling ',' 511303 ',' Xiao ni ', 7,33.33,7*33.33);
20. INSERT into earnings values (' 200912 ',' jinling ',' 511304 ',' little Liz ', 0,18,0);
22. INSERT into earnings values (' 200912 ',' jinling ',' 511305 ',' snow ', 11,9.88,11*9.88);
26. INSERT into earnings values (' 201001 ',' peiping ',' 511601 ',' big qui ', 0,30,0);
28. INSERT into earnings values (' 201001 ',' peiping ',' 511602 ',' Grand Kay ', 14,25,14*25);
30. INSERT into earnings values (' 201001 ',' peiping ',' 511603 ',' Little East ', 19,6.25,19*6.25);
32. INSERT into earnings values (' 201001 ',' peiping ',' 511604 ',' big Bright ', 7,8.25,7*8.25);
34. INSERT into earnings values (' 201001 ',' peiping ',' 511605 ',' humble worship ', 21,11,21*11);
38. INSERT into earnings values (' 201001 ',' jinling ',' 511301 ',' Little Jade ', 6,12.25,6*12.25);
40. INSERT into earnings values (' 201001 ',' jinling ',' 511302 ',' little van ', 17,16.67,17*16.67);
42. INSERT into earnings values (' 201001 ',' jinling ',' 511303 ',' Xiao ni ', 27,33.33,27*33.33);
44. INSERT into earnings values (' 201001 ',' jinling ',' 511304 ',' little Liz ', 16,18,16*18);
46. INSERT into earnings values (' 201001 ',' jinling ',' 511305 ',' snow ', 11,9.88,11*9.88);

Then look at the library you just built:

SQL code

1. SELECT * from earnings;

(1) Sum function, Statistical summation
To count the total revenue per region by month

SQL code

1. Select Earnmonth, area, sum (personincome)
2. From earnings
3. Group by Earnmonth,area;

View the results as follows:

(2) Rollup function
By month, regional statistics revenue

SQL code

1. Select Earnmonth, area, sum (personincome)
2. From earnings
3. Group by Rollup (Earnmonth,area);

View the results as follows:

(3) Cube function
Total revenue totals by month, region

SQL code

1. Select Earnmonth, area, sum (personincome)
2. From earnings
3. Group by Cube (earnmonth,area)
4. ORDER by Earnmonth,area nulls ;

The results are as follows:

Summary: Sum is a function of statistical summation.
Group BY is a grouping function, grouped by Earnmonth and area order.
The above three cases are grouped by Earnmonth first, and then grouped by area within Earnmonth, and the sum of Personincome is counted in the region group.
Group BY is not followed by a direct grouping.
Group BY is followed by rollup on a purely group by group plus a summary statistic of Earnmonth.
The group by is followed by Cube is the Earnmonth summary statistics on the basis of area re-statistics.
The other nulls last was to put the null value at the end.

Rollup and Cube differences:
If it is rollup (A, B, C) then the GROUP by order
(A, B, C)
(A, B)
A
Finally, a group by operation is performed on the whole table.

If the group by CUBE (A, B, C), the group by sequence
(A, B, C)
(A, B)
(A, C)
(A),
(B, C)
B
(C),
Finally, a group by operation is performed on the whole table.

(4) Grouping function
In the above example, the rollup and cube functions are used to generate null for the result set, and the grouping function can be used to confirm
Which field the record is from
Grouping function usage, with a parameter, the argument is the field name, the result is that the field will be returned to 1, and vice versa return 0

SQL code

1. Select Decode (grouping (earnmonth), 1,' All months ', earnmonth) month,
2. Decode (area, 1,' all regions ', areas), sum (personincome) total amount
3. From earnings
4. Group by Cube (earnmonth,area)
5. ORDER by Earnmonth,area nulls ;

View the results as follows:

(5) rank () Over open window function
According to the month, the region, the job income sorts

SQL code

1. Select Earnmonth Month, area, sname migrant worker, personincome income,
2. Rank () over (partition by Earnmonth,area ORDER by personincome desc) ranking
3. From earnings;

View results:

(6) Dense_rank () over open window function
According to the month, the region, the job income sort 2

SQL code

1. Select Earnmonth Month, area, sname migrant worker, personincome income,
2. Dense_rank () over (partition by Earnmonth,area ORDER by personincome desc) ranking
3. From earnings;

The results are as follows:

(7) Row_number () over open window function
According to the month, the region, the job income sort 3

SQL code

1. Select Earnmonth Month, area, sname migrant worker, personincome income,
2. Row_number () over (partition by Earnmonth,area ORDER by personincome desc) ranking
3. From earnings;

The results are as follows:

By (5) (6) (7) Find the difference between Rank,dense_rank,row_number:
If there are two identical data in the result set, rank will be ranked in a jump-
For example two second, then no third next is the fourth;
But Dense_rank will not jump the rankings, two second next or third;
Row_number, even if two data is the same, the ranking is not the same.

(8) Sum cumulative sum
According to the month to find out the total income of all workers, according to the income from less to more sorts

SQL code

1. Select Earnmonth Month, area, sname migrant worker,
2. sum (personincome) over (partition by Earnmonth,area ORDER by Personincome) Total revenue
3. From earnings;

View the results as follows:

(9) Comprehensive application of MAX,MIN,AVG and SUM functions
Earn the highest, lowest, average and total wage income by month and region

SQL code

2. Select distinct earnmonth month, area,
4. Max (personincome) over (partition by Earnmonth,area) highest value,
6. min (personincome) over (partition by Earnmonth,area) lowest value,
8. avg (personincome) over (partition by Earnmonth,area) average,
10. sum (personincome) over (partition by Earnmonth,area) Total
12. From earnings;

The results are as follows:

(ten) Lag and lead functions
Find out if each worker is earning money last month and next month (Personincome is more than 0)

SQL code

1. Select Earnmonth this month, sname working people,
2. Lag (Decode (NVL (personincome,0), 0,' not earned ',' earned '), 1,0) over (partition by sname order by Earnmonth) last month, /c6>
3. Lead (Decode (NVL (personincome,0), 0,' not earned ',' earned '), 1,0) over (partition by sname ORDER by Earnmonth) Next month
4. From earnings;

Note: The lag and lead functions can take the first n rows of a field and the last n rows of data in a single query (which can be data from other fields, such as the word Jianyi of the previous row or the next two lines based on the field a), originally without the analysis function when using the subquery method, but more trouble, ashamed, I use a subquery some still can not find out.

The syntax is as follows:

Lag (value_expression [, offset] [, default]) over ([query_partition_clase] order_by_clause);
Leads (value_expression [, offset] [, default]) over ([query_partition_clase] order_by_clause);
which
Value_expression: Can be a field or a built-in function.
Offset is a positive integer, which defaults to 1, indicating the number of records forward or backward. Because the first record in the group does not have a previous row, the last line does not have a subsequent row,
Default is used to process such information, which defaults to null.

Again talk about the so-called open window function, according to I met, open window function is over ([Query_partition_clase] order_by_clause). For example, I use sum sum, rank sort and so on, but what am I going to do? Over provides a window that can be grouped according to what, using partition by, and then internally sorted within the group based on what, using order by.

This is what I understand as a window-opening function. Well, this article first wrote this, and then have any further experience to add.

Oracle Analytic functions