Common Oracle analysis functions

Learning steps:
1. Have the Oracle EBS demo environment or PROD Environment
2. copy the following code to PL/SQL
3. interpret the analysis results with support
4. Copy the webpage to TXT if it is messy.

/* Assume that a manager represents a department.
*/
SELECT emp. full_name,
Emp. salary,
Emp. manager_id,
Row_number () over (partition by emp. manager_id order by emp. salary DESC) row_number_dept, -- department ranking
Rownum row_number, -- row number
Round (rownum + 1)/4) page_number, -- one page per 4 rows
Ntile (2) over (order by emp. salary DESC) page_number_nt, -- divide into two classes equally

AVG (emp. salary) over (partition by emp. manager_id) avg_salary_department, -- average salary of the Department
SUM (emp. salary) over (partition by emp. manager_id) sum_salary_department, -- total salary of this department
COUNT (emp. salary) over (partition by emp. manager_id) count_emp_department, -- all employees of the Department
Dense_rank () over (partition by emp. manager_id order by emp. salary DESC) rank_salary_dept -- department salary ranking of the employee
Dense_rank () over (order by emp. salary DESC) rank_salary_company -- ranking of the entire company

MIN (emp. salary) over (partition by emp. manager_id) min_salary_dept, -- minimum salary of the Department
MIN (emp. salary) keep (dense_rank first order by emp. salary) over (partition by emp. manager_id) min_salary_dept_first, -- minimum salary of the Department
First_value (emp. salary) over (partition by emp. manager_id order by emp. salary) min_salary_dept_firstv, -- minimum salary of the Department

MAX (emp. salary) over (partition by emp. manager_id) max_salary_dept, -- highest salary of the Department
MAX (emp. salary) keep (dense_rank last order by emp. salary) over (partition by emp. manager_id) max_salary_dept_last, -- highest salary of the Department
Last_value (emp. salary) over (partition by emp. manager_id order by emp. salary) max_salary_dept_lastv, -- highest salary of the Department

Lag (emp. full_name, 1, '00') over (order by emp. salary DESC) last_persion, -- the person whose salary is in front of him
Lead (emp. full_name, 1, '00') over (order by emp. salary DESC) next_persion -- the person with the last salary
FROM fwk_tbx_employees emp
Order by emp. salary DESC

1. Basic Concepts

Analysis functions
1. As the name suggests, the analysis function performs some analysis based on the primary query results, such as department-specific summary and department-specific average value.

Data window
1. Oracle analysis functions are built on the so-called data window, which can be understood as a data set. The data of the primary query can be divided into different datasets according to different standards. For example, partition BY manager_id
The data queried by the master is divided into N (N indicates the number of different manager_id) data windows by Manager_id.
2. Second, the data window should also be implemented in a certain ORDER through order.

Differences and relationships between analysis functions and GROUP
1. Most functions of analysis functions can be aggregated by group.
2. the number of rows queried BY the analysis function is determined BY the primary query, and the result of the number of rows in the group by statement is determined BY the unique combination of the sets following the group by statement, generally, the number of rows is less than the number of results in the primary query.


2. Explanation of typical formats

SUM (emp. salary) over (partition by emp. manager_id) sum_salary_department, -- total salary of this department

Function introduction:
Total salary of the department of the current row's corresponding personnel
AVG and count are similar

Process understanding
1. Split the queried dataset by MANAGER_ID.
2. Find the dataset corresponding to the MANAGER_ID of the current row.
3. sum the preceding data sets and generate a result that is attached to the newly added column.


Dense_rank () over (partition by emp. manager_id order by emp. salary DESC) rank_salary_dept -- department salary ranking of the employee

Function introduction:
The salary ranking of the corresponding personnel in the current row in the Department (the same value will be sorted in different order and the sorting is continuous)
The RANK function is opposite to the RANK function. In this case, the RANK is not consecutive. For example, if A and B are tied for the first place, there will be no second place.
Process understanding
1. Split the queried dataset by MANAGER_ID.
2. Sort the dataset corresponding to the MANAGER_ID of the current row.
3. Extract the row number corresponding to the row and attach it to the additional column.

MIN (emp. salary) keep (dense_rank first order by emp. salary) over (partition by emp. manager_id) min_salary_dept_first, -- minimum salary of the Department

Function introduction:
Minimum salary of the current row's personnel in the department
The MAX function is similar
Process understanding
1. Split the queried dataset by MANAGER_ID.
2. Sort the dataset corresponding to the MANAGER_ID of the current row and extract the first row. If the value of the first row is equal, multiple rows are returned.
3. In the returned multiple rows, extract the row with the lowest salary and the salary Field

First_value (emp. salary) over (partition by emp. manager_id order by emp. salary) min_salary_dept_firstv, -- minimum salary of the Department

Function introduction:
Minimum salary of the current row's personnel in the department
The opposite of last_value is the last value.

Process understanding
1. Split the queried dataset by MANAGER_ID.
2. Sort the dataset corresponding to the MANAGER_ID of the current row.
3. Extract the salary field of the first line


LAG (EMP. FULL_NAME, 1, '00') OVER (order by emp. salary desc) LAST_PERSION, -- the person whose SALARY is in front of him

Function introduction:
In the overall salary ranking, the name of a person who is higher than himself
The lead function is opposite.

Parameter introduction:
LAG (p_segment, p_distance, p_defaualt_val)
1. p_segment: field to be extracted
2. p_distance:> = 0, which indicates the number of digits before the current employee
3. p_defaualt_val: The default value is displayed when the front line is no better than the front line.

Process understanding
1. First, sort the queried dataset in descending order of salary.
2. Extract the p_segment field of the p_distance bit before