Oracle analysis Functions (2)

Next, we will parse the parsing function syntax format (syntax format diagram refer to: http://blog.csdn.net/yidian815/article/details/12709223).

Function name

When it comes to it people, it's a simple thing, not a description.

Function arguments:

Profiling functions typically have 0-3 parameters.

Partition clause:

By partitioning clauses, you can partition a recordset and then perform statistical operations on each partition separately. In analytic functions, you do not have to enclose clauses by using partition clauses. In a query, we can use multiple analytic functions, each of which can use separate partitioning rules. If no partition clause is specified, the entire query result is used as a partition by default.

Example:

No partition clause specified:

 sql> Select Rownum,prod_subcategory_id,sum (1) over (order by prod_subcategory_id rows between unbounded Precedin   
      
    G and current row) as FX from products where prod_subcategory_id > 2053;          RowNum prod_subcategory_id FX---------------------------------------2 2054 1 1 2054 2 3 2054 3 4 20                54 4 5 2054 5 6 2054 6 8  
        2055 7 7 2055 8 9 2055 9  
        13 2055 10 12 2055 11 11 2055 12         10 2055 13 17 2056 14 16 2056  
15 15 2056 16        2056 2056 
 

Specify partition clauses:

 sql> Select Rownum,prod_subcategory_id,sum (1) over (partition by prod_subcategory_id ORDER by Prod_subcategory_   
      
    ID rows between unbounded preceding and current row) as FX from products where prod_subcategory_id > 2053;          RowNum prod_subcategory_id FX---------------------------------------2 2054                1 1 2054 2 3 2054 3 4                2054 4 5 2054 5 6 2054 6 8  
        2055 1 7 2055 2 9 2055 3          13 2055 4 12 2055 5 11 2055 6 10 2055 7 17 2056 1 16 205   6 2 15             2056 3 2056 4 2056 5 

Sort clauses:

The sort clause is used to sort the dataset in the partition. In the SORT clause section, we can specify more than one field, and if the sort field cannot uniquely identify a record, the result of the sort may be unique, so we need to pay special attention to the result of the analysis function.

1: For partition function cume_dist Dense_rank ntile Percent_rank RANK, each record with the same identity returns the same result.

2:row_number returns a unique value for each record, but if an ORDER BY clause cannot uniquely identify a record, the result of row_number processing may be indeterminate because it depends on the sort result.

3: The rest of the partition functions of the window clause, if the range clause, that is, the logical range, then its calculation is determined, if you use the ROWS clause, and physical scope, then the calculation results may be indeterminate, each record of the statistical results are different. For example, the current record row has a value of 100, and if you use range between 2 preceding and present row, all records with row values between 100-2 and 100 participate in statistical operations. If you use the rows between 2 preceding and current row, the records that are in the range of 2 for the row offset of the present row participate in the statistic operation. Because the ordering is not unique, the record within the line offset is indeterminate, but the record within a range of values is determined.

4: If you use the Range clause in the window, the sort clause can only use one sort field (a number of fields cannot be determined by a range of values), and the value of the sorted field must be either date or a numeric type. Except in the following cases:

–range BETWEEN unbounded preceding and current ROW. The short form is RANGE unbounded preceding.

–range BETWEEN Current ROW and unbounded following

–range BETWEEN current row and current row

–range BETWEEN unbounded preceding and unbounded following

NOTE: Sort clauses cannot use the slibing keyword, nor can you use aliases and locations to specify which columns you want to sort.

Example:

 sql> Select P.prod_subcategory_id,rank () over [order by prod_subcategory_id] from the products p where p.prod_subcate  
      
gory_id > 2053; prod_subcategory_id RANK (orderbyprod_subcategory_id)-----------------------------------------------------  
           ----2054 1 2054 1 2054 1                   2054 1 2054 1 2054 1 2055                  7 2055 7 2055 7 2055 7 2055 7 prod_subcategory_id RANK (orderbyprod_subcategory_id)------                  ---------------------------------------------------2055 7 2055  
        7 2056 14 2056 14 2056 14   2056 2056 
 

Each record with the same value determined by the ORDER BY clause has the same result as the parse function.