[SQL/2과목] 함수(2) - 다중행 함수(그룹함수, 윈도우함수)

이번 포스팅은 함수의 두번째 포스팅!! 다중행 함수 내용이다. 

---

다중행 함수

 

다중행함수란 여러개의 행을 입력받아 단 하나의 결과값을 반환하는 함수이다. 

 

다중행 함수는 크게 그룹함수, 윈도우함수로 나뉜다. 이를 봐보자.

---

그룹함수

그룹함수는 여러행을 하나의 그룹으로 묶어, 그 그룹에 대한 하나의 요약된 결과값을 반환하는 함수이다. 

집계 함수	사용 목적
COUNT(*)	NULL 값을 포함한 행의 수를 출력한다.
COUNT(표현식)	표현식의 값이 NULL인 것을 제외한 행의 수를 출력한다.
SUM([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 합계를 출력한다.
AVG([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 평균을 출력한다.
MAX([DISTINCT | ALL] 표현식)	표현식의 최댓값을 출력한다. (문자, 날짜 데이터 타입도 사용 가능)
MIN([DISTINCT | ALL] 표현식)	표현식의 최솟값을 출력한다. (문자, 날짜 데이터 타입도 사용 가능)
STDDEV([DISTINCT | ALL] 표현식)	표현식의 표준편차를 출력한다.
VARIAN([DISTINCT | ALL] 표현식)	표현식의 분산을 출력한다.
기타 통계함수	벤더별로 다양한 통계식을 제공한다.

 

그룹함수는 단독으로 쓰이면 전체 테이블에 대한 단 하나의 결과를 내지만, GROUP BY 및 HAVING절에선 엄청 잘 활용된다고 한다! 집계함수를 GROUP BY랑 쓸때, 그룹함수 역할을 한다고 보면 된다.

---

윈도우함수

윈도우함수는 GROUP BY처럼 행을 그룹으로 묶어 요약하지 않으면서, 각 행에 대한 특정 범위의 행들을 참조하여 순위, 누적합계, 비율 등을 계산하는 고급함수다. SELECT 절에서만 기술된다. 윈도우 함수의 핵심은 OVER() 절이다! 늘 얘랑 쓰인다. OVER()절이 계산 수행할 범위, 즉 윈도우를 지정한다.

 

구문

SELECT
	WINDOW_FUNCTION (ARGUMENTS)
    	OVER( [PARTITION BY 컬럼] [ORDER BY] [ROWS/RANGE BETWEEN ~ AND] ) 
FROM
	TABLE;

 

여기서 PARTITION BY라는 것은, 계산할 그룹을 여러 그룹으로 나누는것이다! GROUP BY는 행을 합쳐서 그룹화시키지만, 얜 합치진 않는다. 만약 PARTITION BY가 생략되어있다면 전체 행을 하나의 파티션으로 보는 것이다!

 

그리고 여기서의 ORDER BY는 파티션 내에서 어떤 순서로 계산 수행할지를 정하는 것이다. 순위함수에서는 순위를 매기는 기준이되고, 집계함수에서는 누적합을 계산하는 기준이된다고한다.

 

ROWS나 RANGE는 윈도우 프레임을 정의하는 부분이다. PARTITION은 전체 데이터를 나누는 창문이라치면, ROWS는 그 창문 안에서!! 현재 행을 기준으로 계산에 포함시킬 작은 범위를 지정하는 것이다. 이제 ROWS는 물리적인 순서를 따른다. ROWS는 몇번째 행부터 몇번째 행까지! 를 정해준다. RANGE는 논리적인 값을 따른다. 이 값부터 이 값까지~ 인것이다. 이떄 쓰이는 키워드는 다음과 같다.

• CURRENT ROW : 현재 행
• n PRECEDING : n행 앞(ROWS), 현재값 - n (RANGE)
• n FOLLOWING : n행 뒤(ROWS), 현재값 + n (RANGE)
• UNBOUNDED PRECEDING : 맨 앞 행
• UNBOUNDED FOLLOWING : 맨 뒷 행

 윈도우의 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 이다. 맨처음 행부터 현재 행까지인것이다~

윈도우함수 종류

순위함수

 

ORDER BY 절과 함께 사용하며 파티션 내의 순위를 계산한다.

• RANK() : 각 행의 순위를 반환한다. 공동순위가 있다면 그 개수만큼 순위를 건너뛴다! (2등이 둘이면 3등이 없는거)
• DENSE_RANK() : 각 행의 순위를 반환한다. 공동순위가있어도 건너뛰지않는다. (2등이 둘이어도 다음 순위는 3등)
• ROW_NUMBER() : 행의 출력되는 위치를 순위로 제공한다.

이 표를 보면 바로 이해할 것이다! 지금은 단순하게 보지만 이걸 이제 파티션을 나누고, 오더바이를 한 상태에서! 보는거다~~

값	RANK()	DENSE_RANK()	ROW_NUMBER()
100	1	1	1
200	2	2	2
200	2	2	3
300	4	3	4

집계함수

 

이 친구는 위에서 봤을 것이다. 위에 표에 적은 집계함수를 윈도우함수에도 적용 가능하다.

행 순서 함수

 

파티션 내에서 현재 행을 기준으로 앞이나 뒤에 있는 행의 값을 가져온다.

• FIRST_VALUE() : 윈도우 별 처음 행을 반환한다.
• LAST_VALUE() : 윈도우 별 마지막 행을 반환한다. LAST_VALUE는 기본 윈도우 프레임의 영향을 받아 의도와 다르게 동작하는 경우가 많다!!! 파티션 전체에서 마지막 값을 구하려면 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 프레임을 명시해 주어야 한다. 마지막이니까 뒤로 범위를 잡는 거다!
• LAG(컬럼, [칸수], [기본값]) : 현재 행을 기준으로 이전 행을 가져온다. 몇칸 이전, 가져올 이전행이 없을시 대체값(기본값)을 인자로 설정해줄수있으며 얘네가 생략되면 1칸, NULL이다.
• LEAD(컬럼, [칸수], [기본값]) : 현재 행을 기준으로 다음 행을 가져온다. LAG과 같은 방식이다.

비율함수

 

파티션이라는 전체 그룹 내에서 현재 행이 차지하는 상대적 위치나 비율을 0과 1 사이의 값으로 계산한다.

• RATIO_TO_REPORT() : 파티션 내의 합계에서 현재 행의 값이 차지하는 비율을 계산 (현재 행의 값 / 파티션 총합)
• PERCENT_RANK() : 파티션 내에서 현재 행의 상대적인 순위 백분율 계산. 1등은 0, 꼴찌는 1로 두고 계산한다. ( 해당 행의 RANK() - 1 ) / ( 파티션의 총 행 수 -1 ) ex) 파티션에 행수가 11개고 내가 3등이면, 2/10 해서 0.2
• CUME_DIST() : 파티션 내에서 현재 행의 누적 분포를 계산. 현재 행의 값보다 작거나 같은 값을 가진 행들의 비율 (현재 행보다 순위가 낮거나 같은 행 수 / 파티션 총 행 수 ) 상위 또는 하위 몇%구간인지를 구할 때 많이 쓰인다.

---

여기까지 윈도우함수를 알아보았다....... 레전드다.. 진짜 어렵다 중간에 시험 때려치고싶었따 하지만.. 어떻게든 포스팅을 끝내본다...... 다음엔..,. TOP N 쿼리..? 이런걸 해보겠다..... 하 어려워잉