[SQL 이해하기] - 14. SQL: 윈도우 함수 - 종류와 동작

 

이전 포스팅에서 윈도우 함수의 동작과 쿼리 안에서의 문법에 대해 살펴보았다.

2023.07.06 - [데이터 모델과 SQL] - [SQL 이해하기] - 13. SQL: 윈도우 함수

[SQL 이해하기] - 13. SQL: 윈도우 함수

 

이전 포스팅에서 윈도우 함수의 종류에 대해서 잠시 나열하였는데 이번 포스팅에서는 자주 쓰이는 함수들의 동작을 정리해보도록 하겠다.

 

- 순위 함수

· RANK(): 순위를 표시한다. 같은 순위가 여럿이면 다음 순위는 같은 순위의 개체 수 만큼 밀린다. 예) 1, 2, 2, 4, 5, 5, 7

· DENSE_RANK(): 순위를 표시한다. 같은 순위가 여럿이어도 다음 순위는 연속된다. 예) 1, 2, 2, 3, 5, 5, 6

· ROW_NUMBER(): 순서대로 나열한 후 단지 순번을 부여한다. 여러 개체가 같은 값을 가질 수 없다.

 

- 집계 함수

· SUM(): 합

· MAX()/MIN(): 최대값/최소값

· AVG(): 평균값

· COUNT(): 개수

 

- 행 지정 함수

· LAG(): 이전 행의 값을 가져온다.

SELECT LAG(대상 컬럼[, 이전 행 수] [, 값이 없는 경우 기본 값]) OVER ([PARTITION BY 기준 컬럼] [ORDER BY 기준 컬럼]) 
FROM 대상 테이블

예) SELECT LAG(SCORE, 2, 0) OVER (PARTITION BY SUBJECT ORDER BY SCORE DESC) FROM EXAMRESULT;
→ SCORE 컬럼의 2개 이전 행의 값을 가져온다.

 

· LEAD(): 이후 행의 값을 가져온다.

SELECT LEAD(대상 컬럼[, 이전 행 수] [, 값이 없는 경우 기본 값]) OVER ([PARTITION BY 기준 컬럼] [ORDER BY 기준 컬럼]) 
FROM 대상 테이블

예) SELECT LEAD(SCORE, 1, 0) OVER (PARTITION BY SUBJECT ORDER BY SCORE DESC) FROM EXAMRESULT;
→ SCORE 컬럼의 1개 이후 행(=다음 행)의 값을 가져온다.

 

· FIRST_VALUE(): 첫 행의 값을 가져온다.

SELECT FIRST_VALUE(대상 컬럼) OVER ([PARTITION BY 기준 컬럼] [ORDER BY 기준 컬럼]) 
FROM 대상 테이블

예) SELECT FIRST_VALUE(SCORE) OVER (PARTITION BY SUBJECT ORDER BY SCORE DESC) FROM EXAMRESULT;
→ SCORE 컬럼의 가장 첫 행의 값을 가져온다.

 

· LAST_VALUE(): 마지막 행의 값을 가져온다.

SELECT LAST_VALUE(대상 컬럼) OVER ([PARTITION BY 기준 컬럼] [ORDER BY 기준 컬럼]) 
FROM 대상 테이블

예) SELECT LAST_VALUE(SCORE) OVER (PARTITION BY SUBJECT ORDER BY SCORE DESC) FROM EXAMRESULT;
→ SCORE 컬럼의 가장 마지막 행의 값을 가져온다.

 

- 비율 함수

비율 함수는 가장 많이 사용하는 PERCENT_RANK()와 NTILE()만 예를 포함시켰다.

 

· CUME_DIST(): 대상 파티션에서의 현재 까지의 누적 백분율을 구한다.

· PERCENT_RANK(): 해당 파티션의 맨 위를 0, 맨 아래를 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구한다.

SELECT STUDENTID, SCORE, PERCENT_RANK() OVER (PARTITION BY SUBJECT ORDER BY SCORE) AS PERCENT 
FROM EXAMRESULT;

→ 시험결과(EXAMRESULT) 테이블의 각 과목을 점수 순으로 오름차순 정렬하고, 
현재 행이 해당 과목의 몇 퍼센트에 위치하는지 표시한다.

 

· NTILE(): 파티션을 기준 컬럼의 값을 이용해 N 등분한 후 현재 행의 위치가 몇 등분 한 영역에 속하는지 그 값을 구한 후 반환한다.

SELECT STUDENTID, SCORE, NTILE(5) OVER (PARTITION BY SUBJECT ORDER BY SCORE) AS NTILE5 
FROM EXAMRESULT;

→ 시험결과(EXAMRESULT) 테이블의 각 과목을 점수 순으로 오름차순 정렬하고, 
이를 5개의 등급으로 나눈 후 현재 행이 몇 등급에 해당하는지 표시한다.

· RATIO_TO_REPORT(): 파티션 별 합계에서 차지하는 비율을 계산한다.

 

 

■