윈도우 함수

 

SQL 전문가 가이드

SQL 전문가 가이드는 SQL 자격검정 대비 수험서로 데이터베이스와 데이터 모델링에 대한 지식을 바탕으로 최적의 성능을 발휘할 수 있도록 SQL을 작성하고 데이터베이스 프로그램이나 응용 소프트웨어의 성능을 최적화하고 데이터베이스 개체의 설계와 구현을 위해 데이터 모델링의 이해, SQL기본 및 활용, SQL 고급 활용 및 튜닝 등의 실무가이드로 구성되어 있다.

저자

한국데이터산업진흥원

출판

한국데이터산업진흥원

출판일

2021.03.15

윈도우 함수

• 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수
• 데이터웨어하우스에서 발전한 기능
• 분석함수, 순위함수
• 중첩해서 사용 불가능, 서브쿼리에서 사용 가능

WINDOW 함수 종류

• 그룹 내 순위 관련 함수
• 그룹 내 집계 관련 함수
• 그룹 내 행 순서 관련 함수
• 그룹 내 비율 관련 함수
• 선형 분석을 포함한 통계 분석 관련 함수 → 통계에 특화된 기능이므로 설명 X

WINDOW 함수 문법

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블명;

• OVER 키워드 필수 포함
• WINDOW_FUNCTION : 기존에 사용하던 함수 & WINDOW 함수용으로 추가된 함수
• ARGUMENTS(인수) : 함수에 따라 0~n개의 인수
• PARTITION BY 절 : 전체 집합을 기준에 따라 소그룹으로 나눌 수 있다.
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할지 ORDER BY절 기술
• WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
  • ROWS : 물리적인 결과 행의 수
  • RANGE : 논리적인 값에 의한 범위

  ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
  				 AND UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
  
  ROWS | RANGE UNBOUNDED PRECEDING | CURRENT | VALUE_EXPR PRECEDING

---

그룹 내 순위 함수

RANK 함수

• ORDER BY를 포함한 쿼리문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수
• 특정 범위(PARTITION) 내에서 순위를 구할 수도 있고, 전체 데이터에 대한 순위를 구할 수도 있음
• 동일한 값에 대해서는 동일한 순위 부여

DENSE_RANK 함수

• RANK와 유사하나 동일한 순위를 하나의 건수로 취급하는 점이 다름

ROW_NUMBER 함수

• 동일한 값이라도 고유한 순위 부여

RANK VS DENSE_RANK VS ROW_NUMBER

이름	월급	RANK	DENSE_RANK	ROW_NUMBER
KING	5000	1	1	1
FORD	3000	2	2	2
SCOTT	3000	2	2	3
JONES	2975	4	3	4

일반 집계 함수

SUM 함수

• 파티션 별 윈도우의 합을 구함

MAX 함수

• 파티션 별 윈도우의 최대값을 구함

MIN 함수

• 파티션 별 윈도우의 최소값을 구함

AVG 함수

• 파티션 별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값 구함

COUNT 함수

• 파티션 별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구함

그룹 내 행 순서 함수

SQL Server에서는 지원X

FIRST_VALUE 함수

• 파티션 별 위도우에서 가장 먼저 나온 값 구함
• 공동 등수가 있을 때 의도적으로 세부 항목을 정렬하고 싶다면 별도의 정렬 조건을 가진 인라인 뷰를 사용하거나 OVER () 내의 ORDER BY 절에 칼럼을 추가해야함
• MIN 함수를 활용해 같은 결과를 얻을 수 있음

LAST_VALUE 함수

• 파티션 별 위도우에서 가장 나중에 나온 값을 구함
• 공동 등수가 있을 때 의도적으로 세부 항목을 정렬하고 싶다면 별도의 정렬 조건을 가진 인라인 뷰를 사용하거나 OVER () 내의 ORDER BY 절에 칼럼을 추가해야함
• MAX 함수를 활용해 같은 결과를 얻을 수 있음

LAG 함수

• 파티션 별 윈도우에서 이전 몇 번째 행의 값을 가져옴
• LAG(가져올 데이터의 칼럼명, 몇 번째 앞의 행을 가져올지, 데이터가 NULL일 경우 바꿔줄 값)

LEAD 함수

• 파티션 별 윈도우에서 이후 몇 번재 행의 값을 가져옴
• LEAD(가져올 데이터의 칼럼명, 몇 번째 후의 행을 가져올지,데이터가 NULL일 경우 바꿔줄 값)

그룹 내 비율 함수

RATIO_TO_REPPORT 함수

• 파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함
• 0 < 결과값 ≤ 1 ⇒ 개별 비율 합을 구하면 1
• SQL Server에서는 지원 X

PERCENT_RANK 함수

• 파티션 별 윈도우에서 값이 아닌 행의 순서별 백분율을 구함
• 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 함
• 0 ≤ 결과값 ≤ 1
• 같은 값을 가지면 같은 ORDER로 취급
• SQL Server에서는 지원 X

CUME_DIST 함수

• 파티션 별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구함
• 0 < 결과값 ≤1
• 같은 값을 가지면 같은 ORDER로 취급, 뒤 행의 함수 결과 값을 기준으로 함
• SQL Server에서는 지원 X

NTILE 함수

• 파티션 별 전체 건수를 인수 값으로 N등분한 결과를 구함
• ex) 14명을 4조로 나눌 때 14/4 = 3이고 14%4 = 2이므로 4 + 4 + 3 + 3명으로 조를 나눔