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);