[03] – Window function in SQL

create table employee (employee_id id, full_name varchar(100), departement varchar(100), salary float);  

insert into employee values(100,'Full Name1', 'SALES',    1000.00); 
insert into employee values(101,'Full Name2',    'IT', 1500.00);
insert into employee values(102,'Full Name3',    'SALES', 2000.00);
insert into employee values(103,'Full Name4',    'SALES', 1700.00);
insert into employee values(104,'Full Name5',    'IT', 1800.00);
insert into employee values(105,'Full Name6', 'ACCOUNTS', 1200.00);
insert into employee values(106,'Full Name7', 'ACCOUNTS', 1100.00);
commit;

1. Ranking Window #

• Common use is to find N top record based on some value
• RANK():
  • Get order with a frame (partition)
  • If two records have same rank (ex: rank = 3) the next rank will be 5 (rank 4 will be skipped)

SELECT
    RANK() OVER (PARTITION BY departement ORDER BY salary DESC) AS dept_ranking,
    departement, employee_id, full_name, salary
FROM employee; 

• DENSE_RANK():
  • Is identical as RANK() function except that it does not skip any rank

SELECT
    DENSE_RANK() OVER (PARTITION BY departement ORDER BY salary DESC) AS dept_ranking,
    departement, employee_id, full_name, salary
FROM employee;

• ROW_NUMBER():
  • Assign the number to each record
  • Can be used with or without PARTITION BY

SELECT
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS dept_ranking,
    departement, employee_id, full_name, salary
FROM employee;

2. Aggregate Window #

-- SUM, AVG, MIN, MAX, COUNT
SELECT
    SUM(salary) OVER (PARTITION BY departement) AS total_salary,
    departement, employee_id, full_name, salary
FROM employee;