SQL 활용 그룹 함수와 윈도우 함수
그룹 함수
조회 결과의 여러 행을 이용하여 통계 정보를 도출하는 함수
- 집계 함수 : COUNT, SUM, AVG, MAX, MIN 등
- 소계(총계) 함수 : ROLLUP, CUBE, GROUPING SETS 등
ROLLUP
중간 집계 값을 산출하기 위해 지정 컬럼 수보다 하나 더 큰 레벨만큼의 중간 집계값이 생성됨
지정 컬럼은 계층별로 생성되기 때문에 순서가 바뀌면 수행 결과가 바뀌게 됨
소계의 집계 대상이 되는 컬럼을 ROLLUP 뒤에 기재하고 소계 집계 대상이 아닌 컬럼은 GROUP BY에 기재. SELECT에 포함되는 컬럼이 GROUP BY 또는 ROLLUP 뒤에 기재되어야 함
SELECT 컬럼1, 컬럼2, ..., 집계 함수
FROM 테이블명
[WHERE ...]
GROUP BY [컬럼 ...] ROLLUP (컬럼1, 컬럼2, ...)
[HAVING ...]
[ORDER BY ...]CUBE
결합할 수 있는 모든 값에 대해 다차원 집계를 생성하는 그룹 함수로, 가능한 소계만을 생성하는 ROLLUP과 구분됨. CUBE는 수행 시 내부적으로 대상 컬럼의 순서를 변경하여 또 한 번의 쿼리를 수행함
총계를 모든 컬럼을 수행한 후 한쪽에서 제거되는 과정에 의해 ROLLUP에 비해 계산이 많음
연산량이 많아 시스템에 부담을 줄 수 있음
SELECT 컬럼1, 컬럼2, ..., 집계 함수
FROM 테이블명
[WHERE ...]
GROUP BY [컬럼명1, ...] CUBE (컬럼a, ...)
[HAVING ...]
[ORDER BY ...]GROUPING SETS
다양한 소계 집합을 만들 수 있으며, 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있고 ROLLUP이나 CUBE와 달리 컬럼 간 순서와 무관한 결과가 나올 수 있음
ORDER BY를 이용하여 집계 대상 그룹과의 표시 순서를 조정하여 체계적으로 보여줄 수 있음
연산량이 많아 시스템에 부담을 줄 수 있음
SSELECT 컬럼1, 컬럼2, ..., 집계 함수
FROM 테이블명
[WHERE ...]
GROUP BY [컬럼명1, ...] GROUPING SETS (컬럼명1, ...)
[HAVING ...]
[ORDER BY...]GROUPING
ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이며 소계를 나타내는 행을 구분할 수 있게 해줌
그룹화된 결과에 소계가 포함된 행들을 구별할 수 있게 도와주며 GROUPING 함수를 이용하면 소계가 계산된 행에서는 결과값을 1로 나머지 행을 0으로 표시할 수 있음
윈도우 함수
행과 행 간의 관계를 쉽게 정의하기 위한 함수로 분석 함수 또는 순위 함수라고 함
SELECT 함수명(파라미터)
OVER ([PARTITION BY 컬럼1, 컬럼2, ...]
[ORDER BY 컬럼A, 컬럼B, ...])
FROM 테이블명순위 함수
RANK : 순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순서를 건너뜀. 예를 들어, 1, 2, 2, 4, 5, 6
DENSE_RANK : 순위를 매기면서 같은 순위가 존재해도 다음 순위를 건너뛰지 않고, 이어서 매김. 예를 들어, 1, 2, 2, 3, 4, 5
ROW_NUMBER : 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여. 예를 들어, 1, 2, 3, 4, 5, 6
행 순서 함수
FIRST_VALUE : 파티션별 가장 처음에 위치한 데이터를 구하는 함수. MIN과 동일한 결과 출력
LAST_VALUE : 파티션별 가장 마지막에 위치한 데이터를 구하는 함수. MAX와 동일한 결과 출력
LAG : 파티션별 특정수만큼 앞에 있는 데이터를 구하는 함수
LEAD : 파티션별 특정수만큼 뒤에 있는 데이터를 구하는 함수
비율 함수
RATIO_TO_REPORT : 파티션 별 합계에서 차지하는 비율을 구하는 함수
PERCENT_RANK : 해당 파티션의 맨 위 끝 행을 0 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수
CUME_DIST : 해당 파티션에서 누적 백분율을 구하는 함수
NTILE : 주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수