/*===============================================================
ANALYTICAL FUNCTIONS
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 06A β ANALYTICAL FUNCTIONS
Topics Covered
--------------
β’ Analytical function fundamentals
β’ OVER clause
β’ PARTITION BY
β’ ORDER BY in window functions
β’ ROW_NUMBER
β’ RANK
β’ DENSE_RANK
β’ NTILE
===============================================================*/
/*---------------------------------------------------------------
ANALYTICAL FUNCTION INTRODUCTION
---------------------------------------------------------------*/
/*
Analytical functions compute values across
a set of rows related to the current row.
Syntax
function_name() OVER (
PARTITION BY column
ORDER BY column
)
*/
/*---------------------------------------------------------------
QUESTION 1
Row number for all employees ordered by salary
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
ROW_NUMBER() OVER
(
ORDER BY salary DESC
) row_num
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 2
Row number per department
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) dept_rank
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 3
Rank employees by salary
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
RANK() OVER
(
ORDER BY salary DESC
) salary_rank
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 4
Dense rank employees by salary
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
DENSE_RANK() OVER
(
ORDER BY salary DESC
) dense_rank_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 5
Compare rank vs dense rank
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
RANK() OVER (ORDER BY salary DESC) rank_salary,
DENSE_RANK() OVER (ORDER BY salary DESC) dense_rank_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 6
Department salary ranking
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
RANK() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) dept_salary_rank
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 7
Top salary per department
---------------------------------------------------------------*/
SELECT *
FROM
(
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) dept_row
FROM hr.employees
)
WHERE dept_row = 1;
/*---------------------------------------------------------------
QUESTION 8
Top 3 salaries per department
---------------------------------------------------------------*/
SELECT *
FROM
(
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) salary_rank
FROM hr.employees
)
WHERE salary_rank <= 3;
/*---------------------------------------------------------------
NTILE FUNCTION
---------------------------------------------------------------*/
/*
NTILE divides rows into groups.
*/
/*---------------------------------------------------------------
QUESTION 9
Divide employees into 4 salary quartiles
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
NTILE(4) OVER
(
ORDER BY salary
) salary_quartile
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 10
Divide employees into 3 groups by salary
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
NTILE(3) OVER
(
ORDER BY salary
) salary_group
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 11
Quartiles within department
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
NTILE(4) OVER
(
PARTITION BY department_id
ORDER BY salary
) dept_salary_quartile
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 12
Department salary ranking report
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) dept_position
FROM hr.employees
ORDER BY department_id;
/*---------------------------------------------------------------
QUESTION 13
Company-wide salary ranking
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
RANK() OVER
(
ORDER BY salary DESC
) company_rank
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 14
Highest salary employee per department
---------------------------------------------------------------*/
SELECT *
FROM
(
SELECT
employee_id,
department_id,
salary,
RANK() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) dept_rank
FROM hr.employees
)
WHERE dept_rank = 1;
/*---------------------------------------------------------------
QUESTION 15
Top 5 highest salaries in company
---------------------------------------------------------------*/
SELECT *
FROM
(
SELECT
employee_id,
salary,
ROW_NUMBER() OVER
(
ORDER BY salary DESC
) salary_position
FROM hr.employees
)
WHERE salary_position <= 5;
/*---------------------------------------------------------------
QUESTION 16
Employees sorted by salary within department
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) dept_rank
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 17
Department salary distribution
---------------------------------------------------------------*/
SELECT
department_id,
employee_id,
salary,
NTILE(5) OVER
(
PARTITION BY department_id
ORDER BY salary
) salary_bucket
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 18
Compare employee salary to department ranking
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
RANK() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) dept_rank
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 19
Department salary percentile grouping
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
NTILE(10) OVER
(
ORDER BY salary
) percentile_group
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 20
Salary order number
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
ROW_NUMBER() OVER
(
ORDER BY salary
) salary_position
FROM hr.employees;
/*---------------------------------------------------------------
PERFORMANCE NOTES
---------------------------------------------------------------*/
/*
Window Function Tips
1 Analytical functions run after GROUP BY
2 Use PARTITION BY to control scope
3 Avoid unnecessary ordering
4 Use analytic functions instead of subqueries when possible
*/
/*---------------------------------------------------------------
END OF PART 06A
---------------------------------------------------------------*/
/*===============================================================
PART 06B β ADVANCED ANALYTICAL FUNCTIONS
Continuation of PART 06A
Topics Covered
--------------
β’ LAG
β’ LEAD
β’ FIRST_VALUE
β’ LAST_VALUE
β’ Running totals
β’ Moving averages
β’ Window frame clauses
β’ Analytical reports
===============================================================*/
/*---------------------------------------------------------------
LAG FUNCTION
---------------------------------------------------------------*/
/*
LAG allows access to a previous row
in the result set.
*/
/*---------------------------------------------------------------
QUESTION 21
Compare salary with previous employee
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
LAG(salary,1) OVER
(
ORDER BY hire_date
) previous_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 22
Department salary comparison
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
LAG(salary,1) OVER
(
PARTITION BY department_id
ORDER BY salary
) previous_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 23
Salary difference from previous employee
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
salary - LAG(salary) OVER
(
ORDER BY salary
) salary_difference
FROM hr.employees;
/*---------------------------------------------------------------
LEAD FUNCTION
---------------------------------------------------------------*/
/*
LEAD allows access to the next row
in the result set.
*/
/*---------------------------------------------------------------
QUESTION 24
Next employee salary
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
LEAD(salary,1) OVER
(
ORDER BY hire_date
) next_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 25
Department next salary comparison
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
LEAD(salary,1) OVER
(
PARTITION BY department_id
ORDER BY salary
) next_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 26
Salary increase to next employee
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
LEAD(salary) OVER
(
ORDER BY salary
) - salary salary_difference
FROM hr.employees;
/*---------------------------------------------------------------
FIRST_VALUE
---------------------------------------------------------------*/
/*
FIRST_VALUE returns the first value
within the window.
*/
/*---------------------------------------------------------------
QUESTION 27
First salary per department
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
FIRST_VALUE(salary) OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) highest_salary_dept
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 28
Lowest salary per department
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
FIRST_VALUE(salary) OVER
(
PARTITION BY department_id
ORDER BY salary
) lowest_salary_dept
FROM hr.employees;
/*---------------------------------------------------------------
LAST_VALUE
---------------------------------------------------------------*/
/*
LAST_VALUE returns the last value
in the window frame.
*/
/*---------------------------------------------------------------
QUESTION 29
Last salary in department
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
LAST_VALUE(salary) OVER
(
PARTITION BY department_id
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) dept_max_salary
FROM hr.employees;
/*---------------------------------------------------------------
RUNNING TOTAL
---------------------------------------------------------------*/
/*
Running totals accumulate values
across ordered rows.
*/
/*---------------------------------------------------------------
QUESTION 30
Running salary total
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
SUM(salary) OVER
(
ORDER BY employee_id
) running_total
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 31
Department running total salary
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
SUM(salary) OVER
(
PARTITION BY department_id
ORDER BY employee_id
) dept_running_total
FROM hr.employees;
/*---------------------------------------------------------------
MOVING AVERAGE
---------------------------------------------------------------*/
/*
Moving averages calculate averages
across a sliding window.
*/
/*---------------------------------------------------------------
QUESTION 32
Moving salary average
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
AVG(salary) OVER
(
ORDER BY employee_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) moving_average
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 33
Moving department salary average
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
AVG(salary) OVER
(
PARTITION BY department_id
ORDER BY employee_id
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
) dept_moving_avg
FROM hr.employees;
/*---------------------------------------------------------------
WINDOW FRAME EXAMPLES
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 34
Salary total for current and previous rows
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
SUM(salary) OVER
(
ORDER BY employee_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) partial_sum
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 35
Total salary over entire table
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
SUM(salary) OVER ()
company_total_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 36
Department total salary
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
SUM(salary) OVER
(
PARTITION BY department_id
) dept_total
FROM hr.employees;
/*---------------------------------------------------------------
ADVANCED ANALYTICAL REPORT
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 37
Employee salary statistics
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
AVG(salary) OVER () avg_salary,
MAX(salary) OVER () max_salary,
MIN(salary) OVER () min_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 38
Department salary statistics
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
AVG(salary) OVER
(
PARTITION BY department_id
) dept_avg_salary,
MAX(salary) OVER
(
PARTITION BY department_id
) dept_max_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 39
Salary percentile ranking
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
PERCENT_RANK() OVER
(
ORDER BY salary
) salary_percent_rank
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 40
Salary cumulative distribution
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
CUME_DIST() OVER
(
ORDER BY salary
) cumulative_distribution
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 41
Salary ranking with percent distribution
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
RANK() OVER (ORDER BY salary DESC) rank_salary,
PERCENT_RANK() OVER (ORDER BY salary DESC) percent_rank_salary
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 42
Department salary ranking report
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) dept_row,
RANK() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) dept_rank
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 43
Running department statistics
---------------------------------------------------------------*/
SELECT
employee_id,
department_id,
salary,
SUM(salary) OVER
(
PARTITION BY department_id
ORDER BY employee_id
) running_total,
AVG(salary) OVER
(
PARTITION BY department_id
ORDER BY employee_id
) running_avg
FROM hr.employees;
/*---------------------------------------------------------------
ANALYTICAL FUNCTION PERFORMANCE NOTES
---------------------------------------------------------------*/
/*
Optimization Tips
1 Partition wisely
2 Avoid unnecessary window ordering
3 Use indexes on partition columns
4 Analytical functions often outperform subqueries
*/
/*---------------------------------------------------------------
END OF PART 06
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express
or implied warranties, including but not limited to warranties of accuracy, completeness,
reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges
and agrees that they do so at their own risk. The author shall not be held liable for any
direct, indirect, incidental, consequential, special, or exemplary damages, including but
not limited to loss of data, loss of profits, system failures, security breaches, or any
other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries,
and configurations in a controlled, non-production (test) environment prior to deploying
them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials
complies with all applicable local, national, and international laws, regulations,
and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless
the author from any claims, damages, liabilities, or expenses arising from their use.
---------------------------------------------------------------*/