Oracle Analytic function Three--sum,avg,min,max,count

Original in August 02, 2009, October 22, 2009 migration to this.

Oracle Analysis Function--sum,avg,min,max,count

SUM

Function Description: This function calculates the cumulative and the expression in the group.

Sample: The following example calculates the salary cumulative value of an employee under the same manager

MIN

Feature Description: Finds the minimum value of an expression in a data window in a group.

Sample: In the example below, Dept_min returns the minimum salary value for the department in which the current line is located

MAX

Feature Description: Finds the maximum value of an expression in a data window in a group.

Sample: In the example below, Dept_max returns the maximum salary value for the current line's department.

Avg

Function Description: Used to calculate the average of expressions within a group and data window.

Sample: The following example column C_mavg calculates the average salary report for each employee in the employee table

SELECT

DEPARTMENT_ID,

first_name| | ' '|| Last_Name Employee_Name,

Hire_date,

Salary

MIN (Salary) over (PARTITION by department_id Order by Hire_date) as Dept_min,

MAX (Salary) over (PARTITION by department_id Order by Hire_date) as Dept_max,

AVG (Salary) over (PARTITION by department_id Order by Hire_date) as Dept_avg,

SUM (Salary) over (PARTITION by department_id Order by Hire_date) as dept_sum/*,

COUNT (*) over (salary) as Count_by_salary,

COUNT (*) Over (salary RANGE BETWEEN preceding and following) as count_by_salary_range*/

From Employees

COUNT

Function Description: The cumulative count of what happens in a group, if you specify * or some Non-null constants, count will count all rows, and if you specify an expression, Count returns the number of non-null assignments of the expression, and when the same value appears, these equal values are included in the calculated value; you can use DISTINCT to record the number of rows that appear after removing exactly the same data from a group.

Sample: In the following example, calculate the number of rows in [n-50,n+150] that each employee pays in the vicinity of the current line in the order of salary, and N represents the current line's salary

For example, Philtanker's salary is 2200, the line in front of him is more than 2200-50 of 1 lines, in the row after him the salary is less than or equal to 2200 + 150, so the count count value is Cnt3 2 (including its own current line); CNT 2 values equal to all rows less than or equal to the SALARY value of the current row

SELECT

DEPARTMENT_ID,

first_name| | ' '|| Last_Name Employee_Name,

Salary

COUNT (*) over (salary) as Count_by_salary,

COUNT (*) Over (salary RANGE BETWEEN preceding and following) as Count_by_salary_range

From Employees

WHERE department_id in (10,20,30);