[MSSQL]분석 함수 개념 및 예제(윈도우 함수)

서론

 

분석 함수(윈도우 함수)에 대한 정리를 언젠가 한번 하고 싶었는데 기억하고 있던 개념이 점차 흐려진 듯하여 이를 계기로 정리하게 되었습니다.

 

---

 

본론

 

[구문 형식]

 

SELECT [분석함수] OVER ( [ PARTITION BY 파티션 컬럼 리스트] [ ORDER BY 정렬 컬럼 리스트] [ROWS|RANGE BETWEEN])

 

 

[특징]

 

• GROUP마다 단일 행을 반환하는 집계 함수와는 달리 분석함수는 전체 결과 집합(분석을 수행하기 전의 집합)의 각 행마다 집계 결과를 보여줍니다.

[문법 정리]

 

OVER

• 분석함수임을 나타내는 단어입니다. 아래와 같은 분석함수의 옵션을 지정해줄 수 있습니다.

PARTITION BY

• 집계할 분석 대상들을 그룹화합니다.
• 지정하지 않을 경우 전체 결과 집합의 모든 행을 하나의 그룹으로 취급합니다.

ORDER BY

• 지정된 파티션 내에서 컬럼들을 정렬하여 그 순서로 집계 결과를 표현합니다.
• 지정하지 않을 경우 파티션의 모든 행을 사용합니다.
• 여기에 지정한다고 해서 최종 결과에 대한 ORDER BY 가 되는 것이 아닙니다. 그래서 최종 결과에 대한 정렬이 필요할 경우 따로 지정해줘야 합니다.(아래 예제의 ORDER BY [GROUP], [NO]에 해당)

ROWS

• ORDER BY로 정렬한 결과에 대해 집계 수행 단위를 행으로 지정합니다.

RANGE

• ORDER BY로 정렬한 결과에 대해 집계 수행 단위를 정렬 컬럼 범위로 지정합니다.

BETWEEN ~ AND

• 집계 수행의 시작과 끝 위치를 지정합니다.

UNBOUNDED PRECEDING

• 집계 수행의 시작 위치가 첫 번째 행 또는 범위임을 나타냅니다.

UNBOUNDED FOLLOWING

• 집계 수행의 시작 마지막 위치가 마지막 행 또는 범위임을 나타냅니다.

CURRENT ROW

• 집계 수행의 시작 위치가 현재 행 또는 범위임을 나타냅니다.

 

[주의 사항]

 

※ ROWS, RANGE 차이

• ROWS : 행 단위
• RANGE : ORDER BY에 명시한 컬럼의 범위 단위
• RANGE는 정렬 컬럼에 중복이 있을 경우 하나의 범위로 취급합니다. (아래 예제의 QTY2, QTY6의 다른 반환 결과 참조)

※ ROWS, RANGE를 지정하지 않을 경우

• 이 인수를 지정하지 않을 경우 RANGE UNBOUNDED PRECEDING AND CURRENT ROW가 기본값으로 지정됩니다(아래 예제의 QTY1, QTY6의 결괏값이 같음으로 확인 가능)

 

[수행 원리 분석]

 

SELECT  [NO],
		[GROUP],
		QTY,
		SUM(QTY) OVER (PARTITION BY [GROUP] ORDER BY [NO] DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) QTY1
FROM ( 
		SELECT 1 [NO], 'A' [GROUP], 1 QTY
		UNION ALL
		SELECT 2 [NO], 'A' [GROUP], 2 QTY
		UNION ALL
		SELECT 3 [NO], 'A' [GROUP], 3 QTY
		UNION ALL
		SELECT 4 [NO], 'A' [GROUP], 1 QTY
		UNION ALL
		SELECT 5 [NO], 'A' [GROUP], 2 QTY
		UNION ALL
		SELECT 6 [NO], 'A' [GROUP], 3 QTY
		UNION ALL
		SELECT 7 [NO], 'B' [GROUP], 4 QTY
		UNION ALL
		SELECT 8 [NO], 'B' [GROUP], 5 QTY
		UNION ALL
		SELECT 9 [NO], 'C' [GROUP], 6 QTY
)A
ORDER BY [GROUP], [NO] DESC

 

[예제]

 

SELECT  
		[NO]
		,[GROUP]
		,QTY
		,SUM(QTY) OVER (PARTITION BY [GROUP] ORDER BY [NO]) QTY1
		,SUM(QTY) OVER (PARTITION BY [GROUP] ORDER BY [NO] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) QTY2
		,SUM(QTY) OVER (PARTITION BY [GROUP] ORDER BY [NO] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) QTY3
		,SUM(QTY) OVER (PARTITION BY [GROUP] ORDER BY [NO] ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) QTY4
		,SUM(QTY) OVER (PARTITION BY [GROUP] ORDER BY [NO] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) QTY5
		,SUM(QTY) OVER (PARTITION BY [GROUP] ORDER BY [NO] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) QTY6
		,SUM(QTY) OVER (PARTITION BY [GROUP] ORDER BY [NO] RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) QTY7
FROM ( 
		SELECT 1 [NO], 'A' [GROUP], 1 QTY
		UNION ALL
		SELECT 1 [NO], 'A' [GROUP], 2 QTY
		UNION ALL
		SELECT 2 [NO], 'A' [GROUP], 3 QTY
		UNION ALL
		SELECT 2 [NO], 'A' [GROUP], 1 QTY
		UNION ALL
		SELECT 5 [NO], 'A' [GROUP], 2 QTY
		UNION ALL
		SELECT 6 [NO], 'A' [GROUP], 3 QTY
		UNION ALL
		SELECT 7 [NO], 'B' [GROUP], 4 QTY
		UNION ALL
		SELECT 8 [NO], 'B' [GROUP], 5 QTY
		UNION ALL
		SELECT 9 [NO], 'C' [GROUP], 6 QTY
)A
ORDER BY [GROUP], [NO]

 

---

 

결론

 

분석 함수를 잘 사용하면 반복적으로 테이블을 액세스해야 하는 쿼리를 한 번의 액세스만으로 처리, 누계 합계 구하기 등 여러모로 활용할 수 있는 부분이 많습니다. 테이블 반복 엑세스 줄이기 예는 아래 문제를 풀어보면 이해하실 수 있을 겁니다. 시간이 되실 때 한번 풀어보시면 분석 함수 활용법을 익히는데 더욱 도움이 되실 것 같습니다.

 

[분석함수 관련 문제]

DEVDO 블로그 - [SQLP] 테이블 한번 읽게 끔 튜닝!서술식문제 튜닝실습 18-1

https://blog.naver.com/oracledo/220488529134

 

Reference

 

꿈꾸는 개발자, DBA 커뮤니티 구루비 - 분석함수란?

http://www.gurubee.net/lecture/2671

Microsoft docs - SELECT - OVER 절(Transact-SQL)

https://docs.microsoft.com/ko-kr/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

꿈꾸는 개발자, DBA 커뮤니티 구루비 - [윈도우 분석 함수 범위 설정] 질문있습니다.

http://www.gurubee.net/article/82978

꿈꾸는 개발자, DBA 커뮤니티 구루비 - SQL 전문가 가이드 (2013년)

http://www.gurubee.net/lecture/2382