Introduction to Oracle Analytics functions

Oracle's analytic functions are powerful, and work is often used over the years. This time, the usual analysis functions are sorted out for later viewing.

We learn by taking cases, so it's easier to understand them.

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;

The query results are as follows

4. The SUM function counts the total revenue per region by month

Select Earnmonth, area, sum (personincome) from  earnings  

The query results are as follows

5. Rollup function by month, area statistic income

Select Earnmonth, area, sum (personincome) from  earnings  

The query results are as follows

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

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

The query 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.
7, grouping function in the above example, is to use the rollup and cube functions will be null for the result set, 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

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

The query results are as follows

8. Rank () Over open window function

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

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

The query results are as follows

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  

The query results are as follows

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  

The query results are as follows

By (8) (9) (10) 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.

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 C1/>from earnings;

The query results are as follows

12. The Max,min,avg and SUM functions are used to calculate the highest, lowest, average and total wage income by 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;

The query results are as follows

13. Lag and lead function find out whether each worker has made any money last month or next month (Personincome is more than 0 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;

The query results are as follows

Description: The lag and lead functions can fetch data from the first n rows and the last n rows of a field 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 field a)

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.

Introduction to Oracle Analytics functions