Tag:oracle
Category:Oracle
Article From:https://www.cnblogs.com/liuqiang18/p/9124875.html

Reprint: https://www.cnblogs.com/benio/archive/2011/06/01/2066106.html

Learning steps:
1. Have a Oracle EBS demo environment or a PROD environment
2. copyThe following code is entered in PL/SQL
3. Combined with interpretation analysis
4. If the page is a bit messy, please copy it to TXT

/*Suppose 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 rankings
rownum row_number, –Line number
round((rownum + 1) / 4) page_number, –Every 4 lines of one page
ntile(2) over(ORDER BY emp.salary DESC) page_number_nt, –The average is divided into two categories

AVG(emp.salary) over(PARTITION BY emp.manager_id) avg_salary_department, –The average salary of the Department
SUM(emp.salary) over(PARTITION BY emp.manager_id) sum_salary_department, –The total salary of the Department
COUNT(emp.salary) over(PARTITION BY emp.manager_id) count_emp_department, –All the employees in the Department
dense_rank() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) rank_salary_dept, –The staff’s Department salary rankings
dense_rank() over(ORDER BY emp.salary DESC) rank_salary_company, –The company’s entire company rankings

MIN(emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept, –The Department’s minimum salary
MIN(emp.salary) keep(dense_rank FIRST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept_first, –The Department’s minimum salary
first_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) min_salary_dept_firstv, –The Department’s minimum salary

MAX(emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept, –The top 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, –The top salary of the Department
last_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) max_salary_dept_lastv, –The top salary of the Department

lag(emp.full_name, 1, ’00’) over(ORDER BY emp.salary DESC) last_persion, –A person who is paid in the first place.
lead(emp.full_name, 1, ’00’) over(ORDER BY emp.salary DESC) next_persion –A person who is paid in the next one.
FROM fwk_tbx_employees emp
ORDER BY emp.salary DESC

1. Understanding of basic concepts

Analysis function
1. As the name implies, the analysis function is a certain analysis based on the main query results, such as departmental summary, sub door mean value and so on.

Data window
1. Oracle The analysis function is built on the so-called data window, and the data window can be understood as a data set. The data of main query can be divided into different data sets according to different standards. For example, partition BY manager_id
According to manager_id, the data of main query is divided into N (N represents different Manager_id), different data windows.
2. Secondly, the data window should also be implemented in a certain order through ORDER BY.

The difference and connection between analysis function and GROUP BY
1. Most functions of the analysis function can be aggregated through GROUP BY.
2. The number of rows in the analysis function query is determined by the main query. The result of the row number of GROUP BY is determined by the unique combination of the sets behind the GROUP BY, which is usually less than the result of the main query.

2. Detailed explanation of typical format

SUM(emp.salary) over(PARTITION BY emp.manager_id) sum_salary_department, –The total salary of the Department

Functional introduction:
The total salary of the current line of staff in the Department
AVG,countSimilar to it

Process understanding
1. First, query the data set according to MANAGER_ID segmentation.
2. Find the data set corresponding to the MANAGER_ID of the current line
3. Summing up the above data sets, generating a result attached to the newly added column.

dense_rank() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) rank_salary_dept, –The staff’s Department salary rankings

Functional introduction:
The current ranking of the corresponding personnel in the Department’s salary ranks (no parallel, the same value will also be sorted sequentially and sequentially).
RANK The function, in contrast, is to appear side by side, and juxtaposing will lead to discontinuities such as A and B for the first place, then there will be no second, and third directly.
Process understanding
1. First, query the data set according to MANAGER_ID segmentation.
2. Sort the data set corresponding to current row MANAGER_ID
3. Extract the corresponding line numbers of the bank and attach them to the additional columns.

MIN(emp.salary) keep(dense_rank FIRST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept_first, –The Department’s minimum salary

Functional introduction:
The minimum salary of the current correspondent in the Department concerned.
MAXA function is similar to it
Process understanding
1. First, query the data set according to MANAGER_ID segmentation.
2. Sort the data set corresponding to the current row MANAGER_ID, extract the first row, and the value of the front row is equal, then return to many rows.
3. In the rows returned, extract the least paid row and extract the salary field.

first_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) min_salary_dept_firstv, –The Department’s minimum salary

Functional introduction:
The minimum salary of the current correspondent in the Department concerned.
last_valueOn the contrary, the last value is calculated.

Process understanding
1. First, query the data set according to MANAGER_ID segmentation.
2. Sort the data set corresponding to current row MANAGER_ID
3. Extracting the salary field of the first line

LAG(EMP.FULL_NAME, 1, ’00’) OVER (ORDER BY EMP.SALARY DESC) LAST_PERSION, –A person who is paid in the first place.

Functional introduction:
The person in the overall salary rank is higher than himself.
lead A function is opposite to someone who is behind himself.

Parameter introduction:
LAG(p_segment, p_distance, p_defaualt_val)
1. p_segment: Fields to be extracted
2. p_distance:>=0The number, a number of people in front of the current staff
3. p_defaualt_val: There is no default value when dangling goes ahead.

Process understanding
1. First, sort out the queried data sets in descending order according to their salaries.
2. The p_segment field of the previous p_distance bit extraction

Link of this Article: Powerful Oracle analysis function

Leave a Reply

Your email address will not be published. Required fields are marked *