Chap 7. SQL 집계, 그룹화 그리고 서브쿼리

SQL은 DB라는 데이터 집합을 다루는 언어이다. 집합을 다룰 때 개수, 합계 등의 계산을 도우는 함수 또한 존재한다. 이를 집계함수라고 한다. 집계함수는 집합을 인수로 지정하며 하나의 값을 반환한다. SELECT, ORDER BY 등에 사용가능.

WHERE 구문에는 사용 안됨. 그 이유 링크

복수의 값(집합)에서 하나의 값을 계산하는 것.

COUNT 행 개수 구하기.

기본 설명

COUNT(집합)

COUNT 함수는 인수로 주어진 집합의 개수를 구해 반환한다.

SELECT COUNT (*) FROM 테이블명 WHERE 조건식;

SELECT 문에 COUNT를 입력하면 테이블 내에 해당 조건식에 맞는 행의 개수를 알려준다. 이때 COUNT 함수 속 메타문자 *는 모든을 의미함으로 모든열을 지정한 것이다. 오직 COUNT 함수만 *를 사용할 수 있다.

열 지정하여 개수 구하기 및 NULL

SELECT COUNT (열명) FROM 테이블명 WHERE 조건식;

위 구문으로 작성하면 조건에 맞는 행을 추출하고 지정한 열에 존재하는 데이터의 개수를 구하여 반환한다. 존재하는 데이터만의 개수를 구한다는건 NULL값은 제외한다는 것이다.

조건에 맞는 전체 행은 n개일 지언정 지정한 열에 NULL값이 1개가 있다면 COUNT(열명)의 반환값은 n-1개이다.

DISTINCT 중복 제거

분석할 때 집합 안에 중복된 값이 있는지 확인하거나 중복을 제거할 경우가 있다. 이때 DISTINCT 함수를 사용한다.

기본 설명

SELECT (ALL) 열명 FROM 테이블명 # SELECT 기본문구 

SELECT DISTINCT 열명 FROM 테이블명 # 중복 제거 

첫 번째 구문은 SELECT문의 기본형으로 사실은 ALL이라는 예약어가 생략된 것이다. 모든 테이블을 검색하는 것이다.

두 번째 구문은 DISTINCT 예약어를 사용하여 중복 제거를 할 수 있다.

집계함수에서 DISTINCT

행의 개수를 계산할 때 중복된 값은 뺀 개수를 구하고 싶을 때가 있다.

간단히 생각해서 SELECT DISTINCT COUNT(열명) 이라고 검색할 수 있지만 연산 처리 순서로 COUNT가 먼저 계산되어 이는 오류가 뜬다. 때문에 정확한 구문작성방법은.

SELECT COUNT(ALL 열명1), COUNT(DISTINCT 열명2) FROM 테이블명;

이렇게 인수 안에 DISTINCT 예약어를 입력해야한다.

SUM, AVG, MIN, MAX

COUNT 함수 이외의 집계함수들이다. 통상적으로는 프로그래밍에서 반복문을 통해 처리를 하는 경우가 많지만 SQL에서는 집계함수로 간략하게 집계를 낼 수 있다.

SUM

합계를 구하는 집계함수다.

SUM(집합)

수치형 데이터만 활용가능하며 NULL값은 제거한 뒤 합계를 낸다.

AVG

평균을 구하는 집계함수이다.

SUM(집합) / COUNT(집합) 이런 집계함수 반환값을 다시 연산하여 계산할 수 있지만 AVG 함수로 간단히 계산할 수 있다.

수치형 데이터만 계산하며 NULL값은 무시한다 만일 NULL도 0으로 계산하고 싶을 시 반환하여 계산한다.

MAX, MIN

최대, 최소를 구한다. 이 함수의 경우 문자열형과 날짜시간형에도 사용할 수 있다. NULL값은 무시한다.

GROUP BY 그룹화

개인적으로 집계함수를 더욱 쓸모있게 하는 궁합은 당연 그룹화 GROUP BY 구문이라 생각한다.

기본 설명

SELECT * FROM 테이블명 GROUP BY 열1 (, 열2, ...);

GROUP BY 구문으로 지정한 열을 기준으로 그룹을 묶는 것이다. 해당 열의 여러개의 값 중 중복되는 것들끼리 (예, a는 a 행 끼리, b가 있는 행끼리) 묶어 그룹(집합)을 만드는 것이다. 이때 복수로 지정할 수 있다. 그럼 지정한 열들과의 조합을 하나로 보아 그룹을 만든다.

SELECT COUNT(num), SUM(num) FROM 테이블명 GROUP BY 열1 (, 열2, ...);

이렇게 형성된 그룹 별 로 집계 계산을 하는 것이다. 가령 A그룹에 3개 행, B그룹에 2개 행이 있다면 COUNT 함수로 각 그룹에 몇개의 행이 있는지, SUM 함수로 각 그룹의 합은 몇인지 알 수 있는 것이다.

집계함수는 집합(그룹)하나당 하나의 값을 반환

집계함수를 WHERE 구에서 사용하지 못하는 이유

SQL을 사용하면 그룹별 집계 결과를 다시 조건화 하여 추출하여 사용할 때가 있다. 조건식은 WHERE 구문으로 해결하면 된다 생각하지만 오류가 발생한다. 이는 SQL의 구문 내부처리 순서와 관련있다.

WHERE $\to$ GROUP BY $\to$ SELECT $\to$ ORDER BY

위 순서로 처리를 하기 때문에 그룹화 이전에 행을 추출하는 단계에서 그룹 별 집계 결과를 계산할 수 없기 때문이다. 더 자세한 내부 순서는 끝에 다시 작성하겠다.

HAVING 구

그룹별로 집계한 결과에서 조건에 맞는 값을 추출할 수 있는 구문이다.

HAVING 구는 GROUP BY 구의 조건 구문이라 생각하면 된다. 해당 조건식에는 그룹별로 집계된 열의 값이나 집계함수 결과가 전달되어 조건식의 참 결과만 클라이언트에게 전달된다.

결과적으로 WHERE 구와 HAVING 구에 지정된 조건으로 검색하는건 2단 구조가된다.

1. WHERE 구문으로 조건에 맞는 행을 검색.
2. 검색한 행들 끼리 그룹화.
3. 그룹화된 결과 집계 계산.
4. 집계 계산 결과 HAVING 절로 다시 조건 추출.

⭐️ 예시

name 열을 그룹화하여 행 개수가 1인 그룹을 검색하고자 한다!

SELECT name, COUNT(name) FROM table0 
GROUP BY name HAVING COUNT(name) = 1;

이렇게 구문을 작성하면 된다.

복수열의 그룹화

주의할 점 중 하나인데 GROUP BY에 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT 구에 기술하면 안된다.

깊게 생각해보면 그룹화를 하면 지정한 열 별 1개씩 행이 생성된다. 그럼 이때 그룹 내에 지정 열 이외의 열엔 여러개의 값이 존재 할 수 있다.

지정 열 name	과일	수
oldcar	사과	3
oldcar	딸기	2

이런 식으로 그룹이 생성되면 SELECT 구에 과일 열을 지정하면 사과를 반환할지 딸기를 반환할지 헷갈려 오류가 뜬다. 따라서 이때 집계함수를 이용한다면 1개의 값이 생성되어 문제 없이 생성할 수 있다.

결괏값 정렬

GROUP BY로 그룹화를 해도 DB 내부에서 어떤 순서에 맞게 정렬이 되진 않는다.

이럴 땐 ORDER BY를 이용하여 그룹별 집계 결과를 정렬하면 된다.

name 열로 그룹화 하여 합계를 구하고 내림차순으로 정렬

SELECT name, COUNT(name), SUM(quantity)
FROM table0 GROUP BY name ORDER BY SUM(quantity) DESC; 

SQL 카테고리 내 다른 글 보러가기