Analytical Functions

Oracle provides several analytical functions that enable users to perform complex calculations on sets of rows returned by a query. These functions allow users to compute cumulative, aggregate, and ranking values without the need for self-joins or subqueries.

Here are the commonly used Oracle analytical functions with examples:

RANK(): The RANK() function returns the rank of each row within a result set, with ties receiving the same rank and the next rank being skipped.

Example:

select salary, rank() over(order by salary desc) as r from employees;

DENSE_RANK(): The DENSE_RANK() function returns the rank of each row within a result set, with ties receiving the same rank and no rank being skipped.

Example:

select salary,dense_rank() over(order by salary desc) as dr from employees;

ROW_NUMBER(): The ROW_NUMBER() function returns a unique number for each row within a result set.

Example:

select employee_id,last_name, salary, ROW_NUMBER() over (order by salary desc) row_num from employees

LAG(): The LAG() function returns the value of a column from a previous row in the result set.

Example:

select employee_id,last_name,job_id,salary, lag(salary, 1, 0) over(order by salary) as sal_prev from employees;

LEAD(): The LEAD() function returns the value of a column from a following row in the result set.

Example:

select employee_id,last_name,job_id,salary, lead(salary, 1, 0) over (order by salary) as sal_prev from   employees;

FIRST_VALUE(): The FIRST_VALUE() function returns the value of the first row within a partition of the result set.

Example:

select employee_id,department_id,salary, first_value(salary ignore nulls) over (partition by department_id order by salary asc nulls last) value_result from employees;

LAST_VALUE(): The LAST_VALUE() function returns the value of the last row within a partition of the result set.

Example:

select employee_id,  department_id,  salary,   last_value(salary ignore nulls) over(partition by department_id order by salary asc nulls last) value_result  from employees;

SUM(): The SUM() function returns the sum of a column within a result set.

Example:

select employee_id,sum(salary) over(order by employee_id) as sum_sal from employees;

AVG(): The AVG() function returns the average of a column within a result set.

Example:

select employee_id,department_id,salary,avg(salary) over(partition by department_id order by department_id)as avg_dept_wise from employees;

MAX(): The MAX() function returns the maximum value of a column within a result set.

Example:

select employee_id,department_id,salary,max(salary) over(partition by department_id order by department_id) max_dept_wise from employees;

MIN(): The MIN() function returns the minimum value of a column within a result set.

Example:

select employee_id,department_id,salary,min(salary) over(partition by department_id order by department_id) min_dept_wise from employees;

These are some of the commonly used Oracle analytical functions with examples.