Several oracle analysis functions

Oracle analysis functions 1. automatic summary functions rollup, cube, 2. rank function, rank, dense_rank, row_number 3. lag, lead function 4. increase the moving of sum, avg, and 5. ratio_to_report processing function 6. first, last base-based analysis functions 1. Oracle ROLLUP and CUBE use the group by statement of Oracle in addition to the most basic syntax, it also supports ROLLUP and CUBE statements. For Group by ROLLUP (A, B, C), group by is performed on (A, B, C), and then group by is performed on (A, B, then perform group by for (A) And then perform group by for the entire table. For group by cube (A, B, C), group by (A, B, C) is first performed, followed BY (A, B ), (A, C), (A), (B, C), (B), (C), and finally perform the GROUP BY operation on the entire table. Grouping_id () can beautify the effect. In addition to the GROUPING function, you can also use GROUPING_ID to identify the result of group. You can also Group by Rollup (A, (B, C), Group by A Rollup (B, C ),...... In this way, it is very convenient to combine statistics in the form you want. 2. Function Description of Rank: Calculate the relative positions of each row returned BY the query and other rows based on the value of the expression in the order by clause. The data in the group is ordered BY the order by clause, and each row is assigned a number to form a sequence. The sequence starts from 1 and accumulates later. This sequence also increases each time the value of the order by expression changes. Rows with the same value obtain the same number (equivalent when null is considered ). However, if the two rows do get the same Order, the ordinal number will jump. If the ordinal number of the two rows is 1, there is no ordinal number 2. The sequence will assign a value of 3 to the next row in the group, and DENSE_RANK will not skip. Rank () is a skip sorting. For example, if there are two second names, dense_rank () l is a continuous sorting, for example, when there are two second names, they still follow the third name. 3. Function Description of "First": extract the row with the First value from the set returned by DENSE_RANK (multiple rows may be taken because the values may be equal ), therefore, the complete syntax needs to add a set function at the beginning to retrieve records. 4. Last usage Function Description: extracts the row with the Last value from the set returned by DENSE_RANK (multiple rows may be taken because the values may be equal ), therefore, the complete syntax needs to add a set function at the beginning to retrieve records. 5. Lag usage Function Description: Other rows in the result set can be accessed without self-connection. It allows processing of the cursor, just as if the cursor is an array. In a given group, you can refer to the rows before the current row, so that you can select the previous row from the group with the current row. Offset is a positive integer. Its default value is 1. If the index is out of the window range, the default value is returned (the first row in the group is returned by default). The opposite function is LEAD.