Oracle Statistics/Analysis functions

Oracle provides analytic functions starting from 8.1.6, which are used to calculate some sort of aggregate value based on a group, which differs from aggregate functions in that multiple rows are returned for each group, whereas aggregate functions return only one row for each group.
Grammar:

SQL code

1. <analytic-function> (<argument>,<argument>,...)
2. Over
3. <query-partition-clause>
4. <order-by-clause>
5. <windowing-clause>
6. )

Description
<1> over is a keyword used to identify analysis functions.
<2> <analytic-function> is the name of the specified analysis function.
<3> <argument> for parameters, the analysis function can select 0-3 parameters.
<4> partition clause <query-partition-clause> is in the following format:
Partition by<value_exp>[,value_expr] ...
The keyword partition BY clause logically divides a single result set into n groups based on the conditions of a partition expression. The "partitioned Partition" and "group groups" are synonyms here.
<5> sort clause order-by-clause Specifies how the data exists within the partition. The format is:
Order[siblings]by{expr|position|c_alias}[asc|desc][nulls First|nulls Last]
which
A.asc|desc: Specifies the sort order.
B.nulls First|nulls Last: Specifies that the return row containing the null value should appear in the first or final position in the ordered sequence.
<6> window clauses windowing-clause
A fixed or changing data window method is given, and the analysis function will manipulate the data. In a set of windows that are based on arbitrary changes or fixed
Use this clause to let the parse function calculate its value.
Format:
{Rows|range}
{Between
{unbounded preceding|current row |<value_expr>{preceding|following}
}and
{unbounded preceding|current row |<value_expr>{preceding|following}
}| {unbounded preceding|current row |<value_expr>{preceding|following
}}
A.rows|range: This keyword defines a window.
B.between...and: A starting point and an end point for a window product.
c.unbounded preceding: Indicates that the window starts at the first row of the partition (partition).
D.current row: Indicates that the window starts at the current line.

Open Window function:
The window function specifies the size of the data window in which the analysis function works, which may change depending on the row, as shown in the following example:

• over (order by salary) is accumulated by salary sort, and order by is a default window-opening function
• over (partition by DEPTNO) according to department partition
• over (order by salary range between preceding and following) the data window for each row is the previous row amplitude value not exceeding 50, followed by a row amplitude value of not more than
• over (order by salary rows between preceding and following) the corresponding data window for each row is the previous 50 lines, followed by 150 rows
• over (order by salary rows between unbounded preceding and unbounded following) each row corresponds to the Data window from the first row to the last row, equivalent: Over (ORDER by SA Lary range between unbounded preceding and unbounded following)

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. You can also group by Rollup (A, (b,c)), GROUP by A Rollup (b,c), ...
How to distinguish the total that is based on that field, this time, Oracle's grouping function. If the current summary record is derived from this field, the Grouping function returns 1, otherwise 0 is returned.
Example:

SQL code

1. Select NVL (area_code, ' total ') area_code,sum (local_fare) local_fare
2. From T
3. GROUP BY rollup (NVL (area_code, ' total '));

SQL code

1. Select Area_code,bill_month,sum (local_fare) local_fare
2. From T
3. Group by Cube (Area_code,bill_month)
4. Order by Area_code,bill_month nulls;

SQL code

1. Select Decode (Grouping (Area_code), 1, ' All area ', To_char (Area_code)) Area_code,
2. Decode (Grouping (bill_month), 1, ' All month ', Bill_month) Bill_month,
3. SUM (local_fare) local_fare
4. From T
5. Group by Cube (Area_code,bill_month)
6. Order by Area_code,bill_month nulls;

Simply put: In order to generate data statistics and horizontal subtotal statistics, you can use the ROLLUP operator in the GROUP BY clause. To generate data statistics, horizontal subtotals, and vertical subtotals, you can use the cube operator.
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 () is a sequential sort, with two second names still followed by the third place.
Example:

SQL code

1. Select Area_code,sum (local_fare) Local_fare,
2. Rank () over (order by sum (local_fare) desc) Fare_rank
3. From T
4. Group BY Area_code;

Results:
Area_code Local_fare Fare_rank
---------- -------------- ----------
5765 104548.72 1
5761 54225.41 2
5763 54225.41 2
5764 53156.77 4
5762 52039.62 5

SQL code

1. Select Area_code,sum (local_fare) Local_fare,
2. Dense_rank () over (order by sum (local_fare) desc) Fare_rank
3. From T
4. Group BY Area_code;

Results:
Area_code Local_fare Fare_rank
---------- -------------- ----------
5765 104548.72 1
5761 54225.41 2
5763 54225.41 2
5764 53156.77 3 This is the third place there.
5762 52039.62 4
Row_number
Function Description: Returns the offset of a row in an ordered group so that it can be used for line numbers sorted by a specific criterion.

SQL code

1. Select Area_code,sum (local_fare) Local_fare,
2. Row_number () over (order by sum (local_fare) desc) Fare_rank
3. From T
4. Group BY Area_code;

Results:
Area_code Local_fare Fare_rank
---------- -------------- ----------
5765 104548.72 1
5761 54225.41 2
5763 54225.41 3
5764 53156.77 4
Rank () Example:
A. Take out the last n users in a database

SQL code

1. Select User_id,tele_num,user_name,user_status,create_date
2. From (
3. Select User_id,tele_num,user_name,user_status,create_date,
4. Rank () Over (order by create_date Desc) Add_rank
5. From User_info
6. )
7. where Add_rank <=: n;

B. Deleting duplicate records in a database based on object_name
CREATE TABLE t as select Obj#,name from sys.obj$;
INSERT INTO T1 SELECT * from T1 several times.

SQL code

1. Delete from T1 where rowID in (
2. Select row_id from (
3. Select rowID row_id,row_number () over (partition by obj# ORDER by ROWID) RN
4. ) where RN <> 1
5. );

C. Take out the revenue from each region to rank in each month.

SQL code

1. Select Bill_month,area_code,sum (local_fare) Local_fare,
2. Rank () over (partition by Bill_month ORDER by sum (local_fare) desc) Area_rank
3. From T
4. GROUP BY Bill_month,area_code

Results:
Bill_month Area_code Local_fare Area_rank
--------------- --------------- -------------- ----------
200405 5765 25057.74 1
200405 5761 13060.43 2
200405 5763 13060.43 2
200405 5762 12643.79 4
200405 5764 12487.79 5
200406 5765 26058.46 1
200406 5761 13318.93 2
200406 5763 13318.93 2
200406 5764 13295.19 4
200406 5762 12795.06 5
200407 5765 26301.88 1
200407 5761 13710.27 2
200407 5763 13710.27 2
200407 5764 13444.09 4
200407 5762 13224.30 5
3, the use of First/last
First function Description: A row from the collection returned by Dense_rank is fetched from the top of the list (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.
Last feature Description: A row from the collection returned by the Dense_rank is taken out of a value that is in the final row (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.
Example: In the example below, Dense_rank is sorted by department, then by commission commission_pct, first takes out all the rows with the lowest commission, and then the previous min function takes the lowest paid value from this set; last takes out all the rows with the highest commission, The previous Max function then removes the highest-paid value from this collection.

SQL code

1. SELECT last_name, department_id, salary,  
2. MIN (Salary) KEEP (Dense_rank first ORDER by co mmission_pct)   
3. over (PARTITION by department_id) "Worst",  
4. MAX (Salary) KEEP (Dense_rank last ORDER by commission_pct)   
5. over (PARTITION by department_id) ' best '
6. from employees  
7. WHERE department_id in (20,80)   
8. ORDER by department_id, salary;& nbsp

Results:
Last_Name department_id SALARY Worst Best
------------------------- ------------- ---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000
4, the use of First_value/last_value
First_value, Last_value are two analytic functions. Returns the value of the first and last digit in the result set. The syntax is:
First_value (expr) over (analytic_clause)
Example:
Calculates the name of the first value of the data window sorted by payroll for the Department partition, and if there are multiple first values for the salary, the first name of the default sort is taken from multiple corresponding names.

SQL code

1. SELECT department_id, last_name, salary, First_value (last_name)
2. Over (PARTITION by department_id ORDER by salary ASC) as Lowest_sal
3. From Employees
4. WHERE department_id in (20,30);

Results:
department_id last_name SALARY Lowest_sal
------------- ------------------------- ---------- --------------
Fay 6000 Fay
Hartstein 13000 Fay
Colmenares 2500 Colmenares
Himuro 2600 Colmenares
Tobias 2800 Colmenares
Baida 2900 Colmenares
Khoo 3100 Colmenares
raphaely 11000 Colmenares
5, the use of Lag/lead
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.
Example: Lag and lead functions take out the total monthly and next month charges for each month

SQL code

1. Select Area_code,bill_month, local_fare cur_local_fare,  
2. Lag (local_fare,2,0) over (part Ition by Area_code ORDER by Bill_month) pre_local_fare,  
3. Lag (local_fare,1,0) over (partition by a Rea_code ORDER by Bill_month) last_local_fare,  
4. leads (local_fare,1,0) over (partition by Area_code ORDER by Bill_month) next_local_fare,  
5. leads (local_fare,2,0) over (partition by Area_code order B Y bill_month) post_local_fare  
6. from (  
7. Select Area_code,bill_month,sum (loca L_fare) local_fare  
8. from t  
9. GROUP by area_code,bill_month  
10. )  

Results:
Area_code bill_month cur_local_fare pre_local_fare last_local_fare next_local_fare POST_LOCAL_FARE
--------- ---------- -------------- -------------- --------------- --------------- ---------------
5761 200405 13060.433 0 0 13318.93 13710.265
5761 200406 13318.93 0 13060.433 13710.265 14135.781
5761 200407 13710.265 13060.433 13318.93 14135.781 0
5761 200408 14135.781 13318.93 13710.265 0 0
5762 200405 12643.791 0 0 12795.06 13224.297
5762 200406 12795.06 0 12643.791 13224.297 13376.468
5762 200407 13224.297 12643.791 12795.06 13376.468 0
5762 200408 13376.468 12795.06 13224.297 0 0
6.ratio_to_report Usage
Function Description: This function computes the value of expression/(expression), which gives a percentage relative to the total, which is the contribution of the current line to sum (expression).
Format: Ratio_to_report (expr) over (query_partition_clause)
Example: Calculate the percentage of each employee's salary for the total wage of such employee

SQL code

1. SELECT last_name, salary, Ratio_to_report (salary) over () as RR
2. From Employees
3. WHERE job_id = ' Pu_clerk ';

7.GROUPING Sets Usage
Starting with oracle9i, you can combine the results of multiple groupings using the grouping sets operator. You can also use GROUP by GROUPING sets instead of group by CUBE. You can apply to specify the total number of combinations you are interested in. Because it does not have to calculate that it does not need a collection (and it does not produce too many results), it is more efficient for the SQL engine.
The format is:
GROUP by GROUPING sets (list), (list) ...)
Here (list) is a sequence of columns in parentheses that generates a total number. To add a sum, you must add a (NUlL) grouping set.
Example: If you want to generate the total number of each product (including all customers and channels) and each customer/channel combination (including all products).

SQL code

1. SELECT prod_id, cust_id, channel_id, SUM (quantity_sold)
2. From sales
3. WHERE cust_id < 3
4. GROUP by GROUPING Sets (
5. (prod_id), (cust_id, channel_id), NULL
6. );

Example: The number of winners of a statistical staff

SQL code

1. SELECT
2. (case when grouping (A.C_XM) =1 and Grouping (A.N_JXDM) =1 and Grouping (A.C_RYDM) =1
3. Then ' Total '
4. When grouping (A.N_JXDM) =1 and Grouping (A.C_RYDM) =1
5. Then ' Subtotal '
6. ELSE A.C_XM
7. END) C_XM
8. , A.n_jxdm,a.c_rydm,count (*)
9. From T_SK_JPGL_HJMD A
10. GROUP by grouping sets (A.C_XM, (A.C_XM,A.N_JXDM,A.C_RYDM), NULL)

Excerpt from: http://log-cd.javaeye.com/blog/405187

Oracle Statistics/Analysis functions