SQL (17) - 윈도우 함수

이번 포스트에서는 SQL의 윈도우 함수에 대해 알아보겠습니다.

윈도우 함수는 행들의 서브 집합을 대상으로, 각 행별 계산을 통해 단일고정값을 출력하는 함수입니다.

간단하게 표현하자면 데이터 테이블에서 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수입니다.

 

1) 윈도우 함수를 사용하는 이유

일단, 기존의 그룹함수를 사용하는 경우를 한번 살펴보겠습니다.

SELECT playerID
FROM salaries
GROUP BY playerID
ORDER BY MAX(salary) DESC;

 

위 쿼리의 실행 결과는 다음과 같습니다.

 

만약, 위의 쿼리에서 salary 열과 관련된 데이터를 가져오고 싶다면 어떻게 해야 할까요?

다음과 같이 로직을 바꿔보겠습니다.

SELECT playerID, salary
FROM salaries
GROUP BY playerID
ORDER BY MAX(salary) DESC;

 

실행 결과는 다음과 같습니다.

 

위와 같이 그룹화를 하게 되면 데이터 테이블의 일부가 손실됩니다.

따라서 데이터를 사용하려면 간접적으로 집계함수등을 사용하여 데이터를 가져와야 합니다.

SELECT playerID, MAX(salary)
FROM salaries
GROUP BY playerID
ORDER BY MAX(salary) DESC;

 

위 쿼리의 실행 결과는 다음과 같습니다.

 

 

이런 상황에서 데이터의 손실없이 행과 행의 관계를 정의할 수 있는 것이 바로 윈도우 함수입니다.

 

2) 윈도우 함수의 구성

윈도우 함수는 다음과 같은 방식으로 사용할 수 있습니다.

 

~OVER([PARTITION] [ORDER BY] [ROWS])

 

순서대로 다음과 같습니다.

• PARTITION : 데이터를 어떤 부분 집합으로 나눌 것인지
• ORDER BY : 데이터를 어떤 순서로 정렬할 것인지
• ROWS : 데이터를 어떤 범위로 나타낼 것인지

이제 예제를 통해 윈도우 함수를 사용하는 방법을 알아보겠습니다.

 

3) PARTITION

3 - 1) 일반적인 PARTITION

-- playerID 별 순위를 따로 표현
SELECT *,
	RANK() OVER (PARTITION BY playerID ORDER BY salary DESC)
FROM salaries
ORDER BY playerID;

 

RANK 연산자를 사용해 playerID를 순위로 분류하되, salary의 값이 높은 순서로 표현합니다.

실행 결과는 다음과 같습니다.

 

3 - 2) LAG(바로 이전) / LEAD(바로 다음)을 적용한 PARTITION

--  playerID 별 LAG(바로 이전) 순위, LEAD(바로 다음) 순위를 표현
SELECT *,
	RANK() OVER (PARTITION BY playerID ORDER BY salary DESC),
	LAG(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS prevSalary,
	LEAD(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS nextSalary
FROM salaries
ORDER BY playerID;

 

LAG와 LEAD를 적용하면 현재 데이터의 바로 이전과 바로 다음의 순위를 표현합니다.

만약, 표현할 데이터가 없다면 NULL로 채워줍니다.

 

실행결과는 다음과 같습니다.

 

4) ORDER BY

-- 전체 데이터를 연봉 순서로 나열하고, 순위를 표기
SELECT *,
	ROW_NUMBER() OVER (ORDER BY salary DESC), -- 행 번호
	RANK() OVER (ORDER BY salary DESC),	  -- 랭킹(같은 순위를 하나로 묶고, 다음 순위는 원본 자신의 순위를 가짐)
	DENSE_RANK() OVER (ORDER BY salary DESC), -- 랭킹(같은 순위를 하나로 묶고, 다음 순위는 자신의 순위 + 1의 순위를 가짐)
	NTILE(100) OVER (ORDER BY salary DESC)	  -- 상위 %를 표시
FROM salaries;

 

위와 같이 ORDER BY 윈도우 함수를 적용한 쿼리문을 만들어보았습니다.

넣어준 각 속성에 따라 정렬을 수행하게 됩니다.

실제 실행 결과는 다음과 같습니다.

 

 

5) ROW

5 - 1) ROW를 생략하는 경우

-- 범위를 의미하는 ROWS를 따로 지정하지 않으면 FIRST ~ CURRENT 까지의 데이터를 범위로 잡는다
SELECT *,
	RANK() OVER (PARTITION BY playerID ORDER BY salary DESC),
	FIRST_VALUE(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS best,
	LAST_VALUE(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS worst
FROM salaries
ORDER BY playerID;

 

ROW 함수를 따로 사용하지 않아 범위를 지정해주지 않으면

윈도우 함수는 기본적으로 프레임이라 불리는 처음 ~ 현재까지의 데이터를 범위로 삼아 동작합니다.

따라서 위 쿼리문의 실행 결과는 다음과 같습니다.

 

각 행이 쿼리 작업 범위의 마지막이 되어 동작하고 있다는 것을 확인할 수 있습니다.

 

5 - 2) ROW를 적용

SELECT *,
	RANK() OVER (PARTITION BY playerID ORDER BY salary DESC),
	FIRST_VALUE(salary) OVER (PARTITION BY playerID 
								ORDER BY salary DESC
								ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 맨 처음부터 현재까지의 행을 범위로
							  ) AS best,
	LAST_VALUE(salary) OVER (PARTITION BY playerID 
								ORDER BY salary DESC
								ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- 현재부터 맨 아래까지의 행을 범위로
							) AS worst
FROM salaries
ORDER BY playerID;

 

따라서 5 - 1의 쿼리를 위와 같이 수정하였습니다.

각 쿼리별로 ROW를 통해 범위를 적용하였고 제일 처음부터 ~ 현재 행까지, 현재 행부터 ~ 마지막 행까지

쿼리가 동작하게 됩니다.

 

최종실행 결과는 다음과 같습니다.