Oracle Analytic functions

Oracle's analytic functions are powerful, and work is often used over the years. This time, the usual use of the analysis function to sort out. for future viewing.

We take cases to learn, so it is more easy to understand.

1. Build a table

CREATE table earnings--Working Money Table  (    earnmonth varchar2 (6),--Working month    area VARCHAR2 (20),--working areas    Sno VARCHAR2 (1 0),---worker number    sname varchar2 (20),--worker's name    times int,--Number of jobs this month    singleincome (10,2),--How much money each time you earn    Perso Nincome Number (10,2) – Total revenue for the month  )  

2. Inserting experimental data

INSERT into earnings values (' 200912 ', ' peiping ', ' 511601 ', ' big qui ', 11,30,11*30);  INSERT into earnings values (' 200912 ', ' peiping ', ' 511602 ', ' Grand Kay ', 8,25,8*25);  INSERT into earnings values (' 200912 ', ' peiping ', ' 511603 ', ' Little East ', 30,6.25,30*6.25);  INSERT into earnings values (' 200912 ', ' peiping ', ' 511604 ', ' big Bright ', 16,8.25,16*8.25);    INSERT into earnings values (' 200912 ', ' peiping ', ' 511605 ', ' base worship ', 30,11,30*11);  INSERT into earnings values (' 200912 ', ' jinling ', ' 511301 ', ' Little Jade ', 15,12.25,15*12.25);  INSERT into earnings values (' 200912 ', ' jinling ', ' 511302 ', ' Little van ', 27,16.67,27*16.67);  INSERT into earnings values (' 200912 ', ' jinling ', ' 511303 ', ' Xiao Ni ', 7,33.33,7*33.33);  INSERT into earnings values (' 200912 ', ' jinling ', ' 511304 ', ' Little Liz ', 0,18,0);    INSERT into earnings values (' 200912 ', ' jinling ', ' 511305 ', ' Snow Son ', 11,9.88,11*9.88);  INSERT into earnings values (' 201001 ', ' peiping ', ' 511601 ', ' big qui ', 0,30,0);  INSERT into earnings values (' 201001 ', ' peiping ', ' 511602 ', ' Grand Kay ', 14,25,14*25);  INSERT into earnings values (' 201001 ', ' peiping ', ' 511603 ', ' Little East ', 19,6.25,19*6.25); INSERT into earnings values (' 201001 ', ' peiping ', ' 511604 ', ' bigBright ', 7,8.25,7*8.25);    INSERT into earnings values (' 201001 ', ' peiping ', ' 511605 ', ' base worship ', 21,11,21*11);  INSERT into earnings values (' 201001 ', ' jinling ', ' 511301 ', ' Little Jade ', 6,12.25,6*12.25);  INSERT into earnings values (' 201001 ', ' jinling ', ' 511302 ', ' Little van ', 17,16.67,17*16.67);  INSERT into earnings values (' 201001 ', ' jinling ', ' 511303 ', ' Xiao Ni ', 27,33.33,27*33.33);  INSERT into earnings values (' 201001 ', ' jinling ', ' 511304 ', ' Little Liz ', 16,18,16*18); INSERT into earnings values (' 201001 ', ' jinling ', ' 511305 ', ' Snow Son ', 11,9.88,11*9.88); Commit

3. View Experimental data

SELECT * from earnings;

Query results such as the following

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "/>

4, the SUM function according to the month. Statistics of total revenue per region

Select Earnmonth, area, sum (personincome) from  earnings  

Query results such as the following

5, rollup function according to the month, the regional statistical income

Select Earnmonth, area, sum (personincome) from  earnings  

Query results such as the following

6, the cube function according to the month, the area carries on the income summary

Select Earnmonth, area, sum (personincome) from  earnings  Group by Cube (Earnmonth,area)  ORDER by Earnmonth, Area nulls last;

Query results such as the following
Summary: Sum is a function of statistical summation.
Group BY is a grouping function. Grouped according to Earnmonth and area priorities.
The above three cases are grouped in accordance with Earnmonth, and then grouped by area within the earnmonth, and the sum of personincome is aggregated 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), the GROUP by order
(A, B, C)
(A, B)
A
Finally, a group by operation is performed on the whole table.

Suppose that 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.

7, the grouping function in the above example. is to use the rollup and cube functions to produce NULL for the result set. The grouping function can be used to confirm
Which field the record is from
Grouping function use method, with a parameter, the parameter is the field name, the result is based on the field to return 1, and vice versa return 0

Select Decode (Grouping (earnmonth), 1, ' All months ', earnmonth) month,         Decode (grouping (area), 1, ' All regions ', area) region, SUM ( Personincome) The total amount from the  earnings  Group by Cube (Earnmonth,area)  order by Earnmonth,area nulls last;

Query results such as the following

8. Rank () Over open window function

According to the month, the region, asks the job income sorts

Select Earnmonth Month, area, sname worker, Personincome Income,          rank (partition by Earnmonth,area ORDER by Personincome DESC) Ranking  

Query results such as the following

9, Dense_rank () over window function according to the month, the region, the job income sort 2

Select Earnmonth Month, area, sname worker, personincome Income,          Dense_rank () over (partition by Earnmonth,area ORDER by person Income desc) Ranking  

Query results such as the following

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "/>

10, row_number () over window function according to the month, the region, the job income sort 3

Select Earnmonth Month, area, sname worker, personincome Income,          row_number () over (partition by Earnmonth,area ORDER by person Income desc) Ranking  

Query results such as the following

By (8) (9) (10) found the difference of Rank,dense_rank,row_number:
The result set assumes that there are two identical data. Then rank will be ranked in a jumping position,
For example, two second. Then there is no third next is the fourth;
But Dense_rank will not jump the rankings. Two second next or third;
Row_number is the most bull, even if two data is the same. The rankings are not the same.

11, sum cumulative sum according to the month to find out the total income of all workers, according to the income from less to more than the order

Select Earnmonth Month, area, sname worker,          sum (personincome) over (partition by Earnmonth,area ORDER by Personincome) total revenue C3/>from earnings;

Query results such as the following

12. The Max,min,avg and SUM functions are used to calculate the highest, lowest, average and total wage income in accordance with the month and region.

SELECT distinct Earnmonth month, area region,         Max (personincome) over (partition by Earnmonth,area) highest value,         min ( Personincome) over (partition by Earnmonth,area) lowest value,         avg (personincome) over (partition by Earnmonth,area) average,         SUM (personincome) over (partition by Earnmonth,area) total from  earnings;

Query results such as the following

13, lag and lead function to find out every worker last month and next month have no money (personincome more than 0 is to make money)

Select Earnmonth this month, sname worker,         Lag (decode (NVL (personincome,0), 0, ' not earned ', ' earned '), 1,0) over (partition by sname ORDER BY Earnmonth) last month, lead         (Decode (NVL (personincome,0), 0, ' not earned ', ' earned '), 1,0) over (partition by sname ORDER by Earnmonth) next month from  earnings;

Query results such as the following

Description: The lag and lead functions are able to fetch data from the first n rows and the last n rows of a field in a single query (data that can be other fields, such as the word Jianyi of the previous row or the next two lines based on the field a query)

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).
Of
Value_expression: Can be a field or a built-in function.
Offset is a positive integer. Default 1, refers to the forward or the next several records. Because the first row in the group does not have a previous line, the last line does not have a subsequent row.
Default is used to deal with this information, the default feeling empty.

?
Again talk about the so-called open window function, according to I met, open window function is over ([Query_partition_clase] order_by_clause).

Say. I use sum sum, rank sort and so on. But what do I have to do? Over provides a form, which can be grouped according to what, use partition by, and then internally sort by what within the group, using order by.

Oracle Analytic functions