집계성 SQL 작성
(1) 데이터 분석 함수의 개념
- 총합, 평균 등의 데이터 분석을 위해서는 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수이다.
- 데이터 분석을 위한 다중 행 함수의 공통적인 특성은 아래와 같다.
- 단일 행을 기반으로 산출하지 않고 복수 행을 그룹별로 모아 놓고 그룹당 단일 계산 결과를 반환한다.
- GROUP BY 구문을 활용하여 복수 행을 그룹핑한다.
- SELECT, HAVING, ORDER BY 등의 구문에 활용한다.
(2) 데이터 분석 함수의 종류
| 함수 | 설명 |
| 집계 함수 | 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수 |
| 그룹 함수 | 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수 |
| 윈도 함수 | 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능 |
(3) 집계 함수
- 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수이다.
1. 집계 함수 구문
SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, ...
[HAVING 조건식(집계함수 포함)]- WHERE 조건으로 지정된 데이터 집합으로부터 그룹화된 집합에 대한 조건 선택 시에 HAVING을 사용하는 것이다.
- GROUP BY 구문 뒤에는 테이블을 구분하는 컬럼을 기재하여 그룹화한다.
- HAVING 구문은 그룹화된 집합에 대한 조건 지정 시 사용하고, 상수나 집약 함수, 집약 키를 사용할 수 있다.
① GROUP BY 구문
- SQL에서는 WHERE 구문을 활용하여 조건별 대상 ROW를 선택한다.
- 복수 ROw 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별할 필요가 있다.
- GROUP BY는 그와 같은 경우에 사용하며, 아래와 같은 특성을 가진다.
- NULL 값을 가지는 ROW는 제외한 후 산출한다.
- SELECT에서 사용하는 것과 같은 ALIAS 사용이 불가하다.
- WHERE 구문 안에 포함되지 않는다.
- WHERE 구문은 GROUP BY보다 먼저 실행되고, 대상이 되는 단일 행을 사전에 선별하는 역할을 한다.
- GROUP BY 구문은 실제 구체적 데이터 분석값을 보고자 하는 컬럼 단위를 선정할 때 사용되는 기준이 되며, 이 부분의 조정을 통해 사용자가 원하는 분석 데이터를 볼 수 있게 해준다.
② HAVING 구문
- WHERE 구문 내에는 사용할 수 없는 집계 함수의 구문을 적용하여 복수 행의 계산 결과를 조건별로 적용하는 데 사용된다.
- 일반적으로 GROUP BY 뒤에 기재하며, GROUP BY 구문의 기존 항목이나 소그룹 집계 함수를 활용한 조건을 적용하는 데 사용한다.
- 쉽게 생각하면 GROUP BY 및 집계 함수에 대한 WHERE 구문이다.
2. 집계 함수의 종류
- 집계 특성상 숫자 유형의 계산에 사용되는 것이 대다수이나, MAX/MIN 또는 COUNT와 같이 문자열 유형의 최대/최소나 건수 계산 등에도 사용한다.
- COUNT : 복수 행의 줄 수를 반환하는 함수
- SUM : 복수 행의 해당 컬럼 간의 합계를 계산하는 함수
- AVG : 복수 행의 해당 컬럼 간의 평균을 계산하는 함수
- MAX : 복수 행의 해당 컬럼 중 최댓값을 계산하는 함수
- MIN : 복수 행의 해당 컬럼 중 최솟값을 계산하는 함수
- STDDEV : 복수 행의 해당 컬럼 간의 표준편차를 계산하는 함수
- VARIAN : 복수 행의 해당 컬럼 간의 분산을 계산하는 함수
(4) 그룹 함수
- 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수이다.
1. 그룹 함수의 유형
① ROLLUP 함수
- ROLLUP에 의해 지정된 컬럼은 소계 등 중간 집계 값을 산출하기 위한 그룹 함수이다.
- 지정 컬럼의 수보다 하나 더 큰 레벨만큼의 중간 집계 값이 생성딘다.
- ROLLUP의 지정 컬럼은 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀜을 유의한다.
- 소계 집계 대상이 되는 컬럼을 ROLLUP 뒤에 기재하고, 소계 집계 대상이 아닌 경우 GROUP BY 뒤에 기재한다.
- SELECT 뒤에 포함되는 컬럼이 GROUP BY 또는 ROLLUP 뒤에 기재되어야 한다는 점만 숙지하고 쿼리를 작성한다.
- ORDER BY 구문을 활용해 계층 내 정렬에 사용이 가능하며, SQL의 결과를 보다 체계적으로 보여 준다.
② CUBE 함수
- 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수이다.
- 연산이 많아 시스템에 부담을 준다.
③ GROUPING SETS 함수
- 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며, ROLLUP이나 CUBE와는 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있는 그룹 함수이다.
- GROUPING SETS를 이용해 다양한 소계 집합을 만들 수 있다.
- ORDER BY를 사용하여 집계 대상 그룹과의 표시 순서를 조정하여 체계적으로 보여줄 수 있다.
(5) 윈도 함수
- 윈도 함수는 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 함수이다.
- 윈도 함수를 OLAP 함수라고도 한다.
1. 순위 함수
| 순위 함수 | 설명 |
| RANK | 특정 항목(컬럼)에 대한 순위를 구하는 함수 동일 순위의 레코드 존재 시 후순위는 넘어감 |
| DENSE_RANK | 레코드의 순위를 계산 동일 순위의 레코드 존재 시에도 후순위를 넘어가지 않음(2위, 2위, 2위, 3위, 4위....) |
| ROW_NUMBER | 레코드의 순위를 계산 동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여(2위, 3위, 4위, 5위, 6위...) |