Analytical Functions or Window Functions in Oracle:Â
======================================================
1. Rank()Â Â Â — RANK() over(order by ranking_cloumn asc|desc)
 –RANK() over(partition by group_column order by ranking_cloumn asc|desc)
2. Dense_Rank()Â — Dense_RANK() over(order by ranking_cloumn asc|desc)
3. Row_number()
4. Lead()
5. Lag()
==================================================================================
select * from employees;
select sum(salary) from employees;
— 749716
select employee_id,first_name,salary,department_id,
sum(salary) over(partition by department_id) ,sum(salary) over() from employees;
==================================================================================
RANK() OVER(Order by salary desc)
RANK() OVER(Order by salary desc)
select employee_id,first_name,email,phone_number,salary,department_id,
rank() over(order by salary) RANK from employees;
select employee_id,first_name,email,phone_number,salary,department_id,
rank() over(order by salary desc) RANK from employees;
–dense rank()
select employee_id,first_name,email,phone_number,salary,department_id,
dense_rank() over(order by salary) RANK from employees;
select employee_id,first_name,email,phone_number,salary,department_id,
dense_rank() over(order by salary desc) RANK from employees;
–row_number()
select employee_id,first_name,email,phone_number,hire_date,salary,department_id,
row_number() over(order by salary desc) no_ties from employees;
select employee_id,first_name,email,phone_number,hire_date,salary,department_id,
row_number() over(order by salary desc,hire_date) no_ties from employees;
——————————————————————————–
group by == > Partition by
select employee_id,first_name,email,phone_number,salary,department_id,
rank() over(partition by department_id order by salary) RANK from employees;
select employee_id,first_name,email,phone_number,salary,department_id,
rank() over(partition by department_id order by salary desc) RANK from employees;
———————————————————————————
select employee_id,first_name,email,phone_number,salary,department_id,
dense_rank() over(partition by department_id order by salary) RANK from employees;
select employee_id,first_name,email,phone_number,salary,department_id,
dense_rank() over(partition by department_id order by salary desc) RANK from employees;
———————————————————————————–
–Least 5 salaried employees
select employee_id,first_name,email,phone_number,salary,department_id,rank from
(select employee_id,first_name,email,phone_number,salary,department_id,
rank() over(order by salary) RANK from employees) where rank <=5;
— Top 5 earners
select employee_id,first_name,email,phone_number,salary,department_id,rank from
(select employee_id,first_name,email,phone_number,salary,department_id,
rank() over(order by salary desc) RANK from employees)Â where rank <=5;
——————————————————————————–
–Least 5 salaried employees
select employee_id,first_name,email,phone_number,salary,department_id,rank from
(select employee_id,first_name,email,phone_number,salary,department_id,
dense_rank() over(order by salary) RANK from employees) where rank <=5;
— Top 5 earners
select employee_id,first_name,email,phone_number,salary,department_id,rank from
(select employee_id,first_name,email,phone_number,salary,department_id,
dense_rank() over(order by salary desc) RANK from employees)Â where rank <=5;
——————————————————————————–
— Top 5 earners with department_name
select a.employee_id,
a.first_name,
a.email,
a.phone_number,
a.salary,
a.department_id,
d.department_name,
a.rank from
(select employee_id,first_name,email,phone_number,salary,department_id,
rank() over(order by salary desc) “RANK” from employees) a inner join departments d
on a.department_id=d.department_id where a.rank <=5 order by a.rank;
——————————————————————————–
100
middle record
select * from employees where rownum <=5;
107
select sount(rownum)=count(rownum)/2;
select rownum,e.* from employees e where rownum <=(select round(count(*)/2) from employees)
minus
select rownum,e.*Â from employees e where rownum <(select round(count(*)/2) from employees);
select * from employees;
——————————————————————————–
Lead()
Lag()
select employee_id,first_name,email,phone_number,hire_date,salary,department_id,
lead (hire_date) over(order by hire_date) after_hire,
lead (first_name) over(order by hire_date) after_hire from employees;
select * from (
select employee_id,first_name,email,phone_number,hire_date,salary,department_id,
lag (hire_date) over(order by hire_date) before_hire_date,
lag (first_name) over(order by hire_date) before_hire_name from employees) where employee_id=142;
——————————————————————————————–
select employee_id,first_name,email,phone_number,hire_date,salary,department_id,
lead (FIRST_NAME) over(order by salary) high_salary_person,
lead (salary) over(order by salary) high_salary from employees;
———————————————————————————————
select employee_id,first_name,salary,department_id,sum(salary) over() from employees;
select employee_id,first_name,salary,department_id,sum(salary) over(partition by department_id),
sum(salary) over()Â from employees;
——————————————————————————–
— Non-ANSI Syntax
with dept_count as (
 select deptno, count(*) as dept_count
 from  emp
 group by deptno)
select e.ename as employee_name,
    dc.dept_count as emp_dept_count
from  emp e,
    dept_count dc
where e.deptno = dc.deptno;
— ANSI Syntax
with dept_count as (
 select deptno, count(*) as dept_count
 from  emp
 group by deptno)
select e.ename as employee_name,
    dc.dept_count as emp_dept_count
from  emp e
    join dept_count dc on e.deptno = dc.deptno;
