Use of Oracle analytic functions (mainly rollup usage)

analytic functions are a new concept introduced in Oracle 8.1.6, providing a simple and efficient way to analyze data. in the analysis function previously, we had to use a self-query, subquery, or inline view, or even a complex stored procedure implementation statement, now as long as a simple SQL statement can be achieved, and there is considerable improvement in the efficiency of implementation.

Analysis Function Reference manual:http://xsb.itpub.net/post/419/33028

How to use analytic functions
1. Automatic summary function Rollup,cube,
2. Rank function, Rank,dense_rank,row_number
3. Lag,lead function
4. Sum,avg, movement increase, moving average
5. Ratio_to_report Report Processing function
6. Analysis function of First,last base

I in the project because of the subtotal, the total statistics, the front thought of union all, but this is a bit cumbersome and inefficient, from the Internet to find that the data is Oracle 8i, Oracl 9i, Oracle 10g has analyzed the function of data statistics processing, So I learned a little bit about the use of these functions, to share it for everyone to learn together.

1. Oracle Rollup and Cube usage
Oracle's GROUP BY statement supports rollup and cube statements in addition to the most basic syntax. In the case of group by ROLLUP (A, B, c), the group by IS first performed on (A, B, c), then group by (A, B), then (a) group by, and finally the group by operation of the whole table.

In the case of group by CUBE (A, B, c), the group by IS first performed (A, B, C), followed by (A, B), (A, C), (a), (b, C), (b), (c), and finally the group by operation of the whole table. GROUPING_ID () can beautify the effect. In addition to using the grouping function, you can also use grouping_id to identify the results of group by.

You can also group by Rollup (A, (b,c)), GROUP by A Rollup (b,c), ... It is convenient to combine statistics in the form you want.

2. Usage of rank

Function Description: Calculates the relative position of the other rows from each row returned by the query, based on the value of the expression in the ORDER BY clause. The data in the group is sorted by the ORDER BY clause, and then a number is assigned to each row, forming a sequence that starts at 1 and accumulates backwards. The sequence also increases each time the value of an order by expression changes. Rows with the same value get the same number ordinal (equal when NULL is considered). However, if two rows do get the same sort, the ordinal will then jump. If the two-line ordinal is 1, there is no ordinal 2, and the sequence assigns a value to the next line in the group 3,dense_rank there is no jump.
Rank () is a jumping sort, for example, with two second names followed by fourth place (also within each group)

Dense_rank () L is a sequential sort, with two second names still followed by the third place.

3. Usage of first

Function Description: The row from the collection returned by Dense_rank is fetched from the first value (possibly many rows, because the values may be equal), so the complete syntax needs to be preceded by a collection function to fetch the records from.

4. Usage of last

Function Description: A row from the collection returned by Dense_rank is taken out of the last side of a value (possibly many rows, because the values may be equal), so the complete syntax needs to be preceded by a collection function to fetch the records from.

5. Usage of lag

Function Description: You can access other rows in the result set without having to self-connect. It allows the cursor to be processed as if the cursor were an array. The row before the current row can be referenced in a given group so that the previous row can be selected from the group along with the current row. Offset is a positive integer with a default value of 1, and if the index is outside the window, the default value is returned (the first row in the group is returned by default), and the opposite function is the lead.

The use of specific functions can go to Baidu to learn, here is just a brief introduction of the role.

Mastering the usage of these functions can reduce many detours in program development, and can greatly improve the operational efficiency.

Use of Oracle analytic functions (mainly rollup usage)