[SQL] 윈도우 함수, 그룹 함수

1. 윈도우함수() + OVER()

- 순위, 집계 등 행과 행사이의 관계를 정의하는 함수, OVER 구문을 필수로 한다

- 순위함수(RANK, DENSE_RANK/ROW_NUMBER) + 집계함수(AVG/SUM/MAX/MIN)로 구성

- 언제쓸까? 특정 테이블의 특정 행만 선택해서 볼때

* 특정 테이블의 특정 컬럼만 볼땐 JOIN

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

-- MySQL 8.0.22 이상
-- MariaDB 10.2.0 이상

- ARGUMENTS: 윈도우 함수에 따라서 필요한 인수

- PARTITION BY: 전체 집합에 대해 소그룹으로 나누는 기준

- ORDER BY: 소그룹에 대한 정렬 기준

- WINDOWING: 행에 대한 범위 기준

WINDOWING	설명
ROWS	물리적 단위로 행의 집합을 지정 (그룹 범위 재지정)
UNBOUNDED PRECEDING	윈도우의 시작 위치가 첫 번째 행
UNBOUNDED FOLLOWING	윈도우의 마지막 위치가 마지막 행
CURRENT ROW	윈도우의 시작 위치가 현재 행

 

1) 순위함수 RANK(공란) OVER ([PARTITION BY 칼럼] [ORDER BY절] [WINDOWING 절])

- RANK(): 동일한 값에 동일한 순위, 다음 순위는 건너뜀

- DENSE_RANK(): 동일한 값에 동일한 순위, 건너뛰지 않고 연속적인 순위

- ROW_NEMBER(): 동일한 값에도 고유한 순위

 

2) 일반집계함수

- GROUP BY없이 SUM, MAX, MIN과 같은 집계함수를 사용할 수 있다

 

3) 그룹 내 행 순서 함수

- FIRST_VALUE(컬럼명): 가장 먼저 나온 값을 구한다

- LAST_VALUE(컬럼명): 가장 나중에 나온 값을 구한다

- LAG(컬럼명, X): 이전 X번째 행을 가져온다

- LEAD(컬럼명, X) : 이후 X번째 행을 가져온다

 

4) 그룹 내 비율 함수

- RATIO_TO_REPORT: 파티션 내 전체 SUM에 대한 비율(MariaDB에서는 제공되지 않아 명시적으로 구현)

- PERCENT_RANK(): 파티션 내 순위를 백분율로 구한다(제일 높은 순위 행은 0, 가장 낮은 순위 행은 1)

- CUME_DIST(): 파티션 내 현재 행보다 작거나 같은 건들의 수 누적 백분율로 구한다()

- NTILE(): 파티션 내 행들을 N등분한 결과를 구한다

 

2. 그룹함수

1) GROUP BY A,B WITH ROLL UP: GROUP BY의 소계 및 총계를 보여줌(MariaDB 기준)

* ORACLE, Postrgre 기준: GROUP BY ROLL UP(A,B)

2) GROUP BY N개 UNION: 가능한 ROLL UP들의 합(ROLL UP(A,B)+ROLL UP(B,A))

*ORACLE, Postrgre 기준: CUBE(A,B)

3) GROUPING SETS: 각 컬럼에 대해 GROUP BY로 생성한 통계를 모두 UNION ALL 한 결과와 동일