brunch

You can make anything
by writing

- C.S.Lewis -

by 레오군 May 02. 2020

SQL window function 정리

몰라도 살아가는 데 큰 지장 없지만, 알면 SQL 다룰 때 세상 편함

사내 SQL 스터디 참고자료 만들다가, 생각난 김에 window function 주요 내용을 한번 정리해본다.  사실 SQL window function은 나도 잘 안써서 (이거 쓸 정도로 데이터 가공이 필요하면 그냥 로컬에 내려받아서 R에서 처리-_- 하는 안좋은 습관이...) 한번씩 써야할 때마다 문법 찾아보곤 했었는데, 이렇게 한 판 정리해두니 좋구만... 



기본 문법: 


AGGREGATE_FUNCTION() OVER()()

- SUM(), AVG(), MAX() 등의기본 집계함수도 사용 가능하지만
- RANK(), ROW_NUMBER(), LEAD(), NTILE() 등의 윈도우 전용 집계함수(?)를 쓸 수 있다는게 장점


AGGREGATE_FUNCTION() OVER(PARTITION BY [컬럼] ORDER BY [컬럼])()

- 특정 그룹별로 집계함수를 적용하는 경우, PARTITION BY 와 ORDER BY 사용


AGGREGATE_FUNCTION() OVER(PARTITION BY [컬럼] ORDER BY [컬럼] ROWS BETWEEN [범위] AND [범위])()

- 집계함수의 범위를 특정 윈도우로 지정하고자 하는 경우, ROWS BETWEEN A AND B 구문을 활용

- CURRENT ROW, N PRECEDING, N FOLLOWING, UNBOUNDED PRECEDING,  UNBOUNDED FOLLOWING 사용 가능



연습 문제: 

[employee 테이블 예시]



#1. 개인별 salary 옆에, 전체의 평균 salary 컬럼을 추가하기

- 집계함수 + OVER() 라는 기본 구문 사용

- 집계함수가 사용되었지만, WINDOW FUNCTION으로 사용되었으므로 GROUP BY 하지 않아도 됨

SELECT employee_id, department, salary, AVG(salary) OVER() AS avg_salary
FROM `employees` 



#2. 개인별 salary 옆에, 각 개인이 속한 department의 평균 salary 컬럼을 추가하기

- OVER() 안에 PARTITION BY 구문을 써서 그룹핑 가능

SELECT employee_id, department, salary, AVG(salary)
OVER(PARTITION BY department) AS avg_salary_partition
FROM `employees`



#3. 개인별 salary 옆에, salary_rank를 추가하기

- OVER() 안에 ORDER BY 구문을 써서 정렬 가능

- RANK 관련 함수는 아래 3개를 주로 사용한다

1) ROW_NUMBER - 항상 unique한 순위 부여 (동일값이라고 하더라도 어떻게든 순위를 구분한다)

2) RANK - 동일값인 경우 동일순위 부여.  공동 순위가 있는 경우 해당 순위 건너뛰고 부여

3) DENSE_RANK - 동일값인 경우 동일순위 부여.  공동 순위가 있는 경우 해당 순위 연이어서 부여

SELECT employee_id, department, salary, 
ROW_NUMBER() OVER(ORDER BY salary DESC) AS salary_rank_unique
FROM `employees`



#4. 개인별 salary 옆에, department별로 그룹핑해서 salary_rank를 추가하기

- OVER() 안에 PARTITION BY와 ORDER BY 구문을 둘 다 사용하면 된다

- 마찬가지로 랭킹 부여 방식에 따라 ROW_NUMBER, RANK, DENSE_RANK 중 선택적으로 사용

SELECT employee_id, department, salary, 
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS salary_rank_in_dep
FROM `employees`



#5. 부서 내에서의 개인별 salary 순으로 정렬하고, 개인별 salary 차이 구하기

- LEAD() 또는 LAG() 함수를 사용할 수 있다

- LEAD()와 LAG() 함수는 Auguments를 입력해야 함 (이 경우 salary)

- LEAD(salary, 2) 와 같은 식으로 몇 단계를 lead(혹은 lag) 할 것인지 정의할 수 있다

SELECT employee_id, department, salary, 
LEAD(salary) OVER(PARTITION BY department ORDER BY salary desc) AS next_salary,
salary - LEAD(salary) OVER(PARTITION BY department ORDER BY salary desc) AS diff
FROM `employees`



#6. 부서 내에서의 개인별 salary 순으로 정렬하고, 부서별 누적 salary 컬럼을 표시하기

- 집계함수와 함께 사용하는 경우, ROWS BETWEEN () AND () 구문을 통해 윈도우 범위 지정가능

- 사용가능한 범위는 아래와 같다

1) CURRENT ROW (현재 행)

2) N PRECEDING (N행 앞)

3) N FOLLOWING (N행 뒤)

4) UNBOUNDED PRECEDING (이전 행 전부)

5) UNBOUNDED FOLLOWING (이후 행 전부)

SELECT employee_id, department, salary, 
SUM(salary) OVER(PARTITION BY department ORDER BY salary 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS accum_salary
FROM `employees`





데이터 분석, 어떻게 시작하면 좋을까?

그로스해킹 : 데이터와 실험을 통해 성장하는 서비스를 만드는 방법

매거진의 이전글 [Book] 프로덕트 오너

매거진 선택

키워드 선택 0 / 3 0
브런치는 최신 브라우저에 최적화 되어있습니다. IE chrome safari