윈도우 함수 (Window Functions)

group by와 다른 점:

group by: 그룹들을 collapse하지 않고(한 줄 요약하지 않고)

window function: 각각의 row에 결과물을 보여준다.

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

window 함수 사용

group by 사용

누적 합 구하기:

1. sum(컬럼) over (order by 컬럼) as cumsum

2. sum(컬럼) over (partition by 컬럼 order by 컬럼) as cumsum_with_partition

3. self-join 활용하기

4. select sub-query 사용하기

 

순위 정하기:

row_number(), rank(), dense_rank()

 

데이터 위치 바꾸기:

lag(): 데이터 밀기

lead(): 데이터 당기기

 

이전에 푼 180.Consecutive Numbers (lead 또는 lag 사용 가능)

https://leetcode.com/problems/consecutive-numbers/

select distinct l.num as ConsecutiveNums
from (
    select num
        , lead(num, 1) over (order by id) as next
        , lead(num, 2) over (order by id) as afternext
    from logs
) l
where l.num = l.next and l.num = l.afternext

 

이전에 푼 184. Department Highest Salary

https://leetcode.com/problems/department-highest-salary/

select ms.department
        , ms.name as Employee
        , ms.salary
from (
    select employee.name
            , employee.salary
            , department.name as Department
            , max(salary) over (partition by departmentid) max_salary
    from employee
        inner join department on employee.departmentid = department.id
    ) ms
where ms.salary = ms.max_salary

 

185. Department Top Three Salaries

https://leetcode.com/problems/department-top-three-salaries/

select t.department
        , t.employee
        , t.salary
from (
    select department.name as department
            , employee.name as employee
            , employee.salary
            , dense_rank() over (partition by departmentid order by salary desc) as dr
    from employee
        inner join department on employee.departmentid = department.id
    ) t
where t.dr <= 3