14 - Window Functions in MySQL

 14 - Window Functions in MySQL

https://youtu.be/7NBt0V8ebGk?si=mMdFDPdVIjphTG9X

TOTAL: 13.28

-- Window Functions

-- windows functions are really powerful and are somewhat like a group by - except they don't roll everything up into 1 row when grouping.

-- windows functions allow us to look at a partition or a group, but they each keep their own unique rows in the output

-- we will also look at things like Row Numbers, rank, and dense rank

SELECT *

FROM employee_demographics;

-- first let's look at group by

SELECT gender, ROUND(AVG(salary),1)

FROM employee_demographics dem

JOIN employee_salary sal

    ON dem.employee_id = sal.employee_id

GROUP BY gender

;

-- now let's try doing something similar with a window function

SELECT dem.employee_id, dem.first_name, gender, salary,

AVG(salary) OVER()

FROM employee_demographics dem

JOIN employee_salary sal

    ON dem.employee_id = sal.employee_id

;

-- now we can add any columns and it works. We could get this exact same output with a subquery in the select statement,

-- but window functions have a lot more functionality, let's take a look

-- if we use partition it's kind of like the group by except it doesn't roll up - it just partitions or breaks based on a column when doing the calculation

SELECT dem.employee_id, dem.first_name, gender, salary,

AVG(salary) OVER(PARTITION BY gender)

FROM employee_demographics dem

JOIN employee_salary sal

    ON dem.employee_id = sal.employee_id

;

-- now if we wanted to see what the salaries were for genders we could do that by using sum, but also we could use order by to get a rolling total

SELECT dem.employee_id, dem.first_name, gender, salary,

SUM(salary) OVER(PARTITION BY gender ORDER BY employee_id)

FROM employee_demographics dem

JOIN employee_salary sal

    ON dem.employee_id = sal.employee_id

;

-- Let's look at row_number rank and dense rank now

-- ROW_NUMBER() OVER() --> danh so thu tu tang dan (gap gia tri bang nhau cung tang)

SELECT dem.employee_id, dem.first_name, gender, salary,

ROW_NUMBER() OVER(PARTITION BY gender)

FROM employee_demographics dem

JOIN employee_salary sal

    ON dem.employee_id = sal.employee_id

;

-- let's  try ordering by salary so we can see the order of highest paid employees by gender

SELECT dem.employee_id, dem.first_name, gender, salary,

ROW_NUMBER() OVER(PARTITION BY gender ORDER BY salary desc)

FROM employee_demographics dem

JOIN employee_salary sal

    ON dem.employee_id = sal.employee_id

;

-- let's compare this to rank

-- gia tri xep thu hang chinh xac (gap gia tri bang nhau KHONG TANG)

-- chu y TOM vs JERRY

SELECT dem.employee_id, dem.first_name, gender, salary,

ROW_NUMBER() OVER(PARTITION BY gender ORDER BY salary desc) row_num,

Rank() OVER(PARTITION BY gender ORDER BY salary desc) rank_1

FROM employee_demographics dem

JOIN employee_salary sal

    ON dem.employee_id = sal.employee_id

;

-- notice rank repeats on tom ad jerry at 5, but then skips 6 to go to 7 -- this goes based off positional rank

-- dense_rank() k nhay gia tri neu truoc do co gia tri TRUNG

-- let's compare this to dense rank

SELECT dem.employee_id, dem.first_name, gender, salary,

ROW_NUMBER() OVER(PARTITION BY gender ORDER BY salary desc) row_num,

Rank() OVER(PARTITION BY gender ORDER BY salary desc) rank_1,

dense_rank() OVER(PARTITION BY gender ORDER BY salary desc) dense_rank_2 -- this is numerically ordered instead of positional like rank

FROM employee_demographics dem

JOIN employee_salary sal

    ON dem.employee_id = sal.employee_id

;