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`
데이터 분석, 어떻게 시작하면 좋을까?
