SQLite 윈도우 함수

각 부서별로 급여가 가장 높은 직원 목록을 알고 싶다면 어떻게 쿼리를 작성해야 할까? 윈도우 함수를 쓰지 않고 작업하려면 매우 복잡하고 비직관적인 쿼리를 작성해야 할 것이다. 윈도우 함수 rank 를 사용하면 다음과 같이 쉽게 구할 수 있다. over 절을 보면 부서 별로 파티션을 하고 급여로 정렬해 rank 를 구하는 것을 볼 수 있다.

select department_name dept_name
     , employee_id emp_id
     , emp_name
     , job_title
     , salary
from (
    select employee_id
         , first_name ||' '||last_name emp_name
         , department_name
         , job_title
         , salary
         , rank() over (partition by department_id order by salary desc) rnk
    from employees
    join departments using (department_id)
    join jobs using (job_id)
    )
where rnk = 1
order by salary desc
;

dept_name         emp_id  emp_name          job_title                        salary
----------------  ------  ----------------  -------------------------------  ------
Executive         100     Steven King       President                        24000
Sales             145     John Singh        Sales Manager                    14000
Marketing         201     Michael Martinez  Marketing Manager                13000
Finance           108     Nancy Gruenberg   Finance Manager                  12008
Accounting        205     Shelley Higgins   Accounting Manager               12008
Purchasing        114     Den Li            Purchasing Manager               11000
Public Relations  204     Hermann Brown     Public Relations Representative  10000
IT                103     Alexander James   Programmer                       9000
Shipping          121     Adam Fripp        Stock Manager                    8200
Human Resources   203     Susan Jacobs      Human Resources Representative   6500
Administration    200     Jennifer Whalen   Administration Assistant         4400

회사가 성장함에 따라 직원수가 늘어났다. 시간이 지남에 따라 직원이 어떻게 늘어났는지 확인하기 위해 연도별 누적 직원 수가 필요하다고 생각해보자. 이 문제 역시 윈도우 함수를 사용하면 간단히 해결할 수 있다. 연도별로 group by 해 연도별 직원수를 구한 다음, 윈도우 함수로 누적 합계를 구하면 된다.

날짜 함수는 DB마다 다르다. SQLite에서는 strftime 함수를 사용해 날짜에서 연도를 뽑아낼 수 있다.

select yyyy
     , sum(cnt) over (order by yyyy) cnt
from (
    select strftime('%Y', hire_date) yyyy
         , count() cnt
    from employees
    group by strftime('%Y', hire_date)
    )
;

yyyy  cnt
----  ---
2011  1
2012  8
2013  14
2014  24
2015  53
2016  77
2017  96
2018  107

여기에 조금 더해서, 연도별로 전년 대비 증가 직원수도 알고 싶다면 어떻게 해야 할까? 위 쿼리에서 sum 함수에 대한 프레임을 설정할 때 파티션을 지정하지 않았으므로, 프레임은 처음부터 현재 행까지가 된다. 처음부터 현재 행 바로 전까지만 합계를 구할 수 있다면 전년 대비 증가 직원수를 쉽게 구할 수 있다.

윈도우 함수에서는 다음과 같은 식으로 프레임을 정의해, 처음부터 현재 행 바로 전까지만 합을 구하는 것이 가능하다. 현재 행을 기준으로 해당 연도까지 누적 직원 수와 그 전년도까지 누적 직원수를 구했다면, 증가 직원수는 그 차가 된다.

select yyyy
     , cnt1 cnt
     , cnt1 - cnt2 delta
from (
    select yyyy
         , sum(cnt) over (order by yyyy) cnt1
         , sum(cnt) over (order by yyyy
                          range between unbounded preceding
                          and current row
                          exclude current row) cnt2
    from (
        select strftime('%Y', hire_date) yyyy
             , count() cnt
        from employees
        group by strftime('%Y', hire_date)
        )
    )
;

yyyy  cnt  delta
----  ---  -----
2011  1
2012  8    7
2013  14   6
2014  24   10
2015  53   29
2016  77   24
2017  96   19
2018  107  11

윈도우 함수에서 프레임 설정에 대한 상세 사항은 Window Functions > Frame Specifications를 참조하기 바란다.

위 쿼리에서 두 번째 sum 은 over 절이 너무 복잡한 것 같다. 만약 연도별 누적 직원수 쿼리 결과에서 바로 전 행을 참조할 수 있다면 어떨까? 다음과 같이 lag 함수를 사용하면 쿼리를 조금 간단하게 만들 수 있다. 현재 행 누적 직원수에서 바로 전 행 누적 직원수를 빼면 증가한 직원수가 된다.

select yyyy
     , cnt
     , cnt - lag(cnt, 1) over (order by yyyy) delta
from (
    select yyyy
         , sum(cnt) over (order by yyyy) cnt
    from (
        select strftime('%Y', hire_date) yyyy
             , count() cnt
        from employees
        group by strftime('%Y', hire_date)
        )
    )
;

yyyy  cnt  delta
----  ---  -----
2011  1
2012  8    7
2013  14   6
2014  24   10
2015  53   29
2016  77   24
2017  96   19
2018  107  11