9. [SQL기초] 집계함수와 그룹화

집계 함수

집계 함수는 대상을 군집으로 묶어서 전체 개수, 또는 전체 합계를 구할 수 있도록 하는 내장 함수입니다. WHERE절의 조건식으로 선택한 행들의 전체 개수를 알고 싶은데 결과 행을 일일이 세고 있을 수는 없습니다. 조건식에 맞는 결과에서 특정 컬럼 값의 합계나 평균값을 알고 싶을 때도 집계 함수는 사용됩니다.

쉽게 엑셀의 계산 함수와 같다고 생각하면 됩니다. 자주 사용하는 집계 함수를 알아보겠습니다.

전체 개수 구하기

함수는 COUNT()로 사용합니다. 조건절로 선택한 행의 전체 개수를 반환합니다.

주문정보(p_order) 테이블에서 오늘 등록된 전체 주문 개수를 알고 싶으면 다음과 같이 쿼리문을 작성합니다.

Select count(*) from p_order where str_to_date(substring(current_timestamp, 1, 10),'%Y-%m-%d') <= wt and wt < str_to_date(substring(current_timestamp + interval 1 day, 1, 10),'%Y-%m-%d');

COUNT(*)는 전체 행의 개수를 반환하는 집계 함수입니다. “*”는 SELECT문을 처음 배울 때 모든 컬럼을 나타낸다고 배웠습니다. “*”를 대신해서 반환된 결과 행의 열 이름을 대신 사용해도 됩니다. 행의 개수를 세는 함수이기 때문에 어떤 컬럼인지가 중요하지는 않습니다.

“*”는 COUNT() 집계 함수에만 사용할 수 있는 특별한 컬럼 표시로 다른 집계 함수에서는 사용할 수 없습니다.

그리고 언제나 NULL은 예외 처리입니다. “*” 대신 열 이름을 표시해서 특정 컬럼의 개수를 세는 경우, 컬럼에 NULL이 있으면 해당 컬럼이 있는 행은 개수를 셀 때 제외됩니다. WHERE절의 조건식이 참이 되는 모든 행 개수를 얻으려면 “*”를 사용하는 것이 좋습니다.

Select count(mobile) from p_order;

이 쿼리문은 4를 반환합니다. 전체 주문 개수는 5개지만 mobile 컬럼에 NUL이 있는 주문이 1개 있어서 제외됩니다.

오늘 날짜의 주문을 구하기 위한 WHERE절의 검색 조건 범위는 앞서 배웠던 날짜 연산 함수를 이용한 것입니다.

str_to_date(substring(current_timestamp, 1, 10),'%Y-%m-%d') 계산식은 오늘 날짜 자정이 되고, str_to_date(substring(current_timestamp + interval 1 day, 1, 10),'%Y-%m-%d') 계산식은 내일 날짜 자정이 됩니다.

따라서 오늘 날짜의 24시간 범위에 해당하는 시간 사이의 주문만 가져옵니다.

COUNT() 집계 함수가 반환하는 결과는 양의 정수 값 한 개입니다. 반환하는 값이 들어있는 1행 1열로 된 값 1개 만입니다. 서버 프로그램에서 반환 값을 행으로 가져다 사용하려면 컬럼 이름을 별칭으로 지정해야 합니다.

Select count(*) as ordercount from p_order;

합계 구하기

함수는 SUM()으로 사용합니다. 특정 숫자 컬럼의 합계를 반환합니다. SUM() 함수 파라미터에는 열 이름, 또는 계산식을 사용할 수 있습니다.

주문정보(p_order) 테이블의 전체 합계 금액을 구하려면 다음과 같이 쿼리문을 작성합니다.

Select sum(p_sell_price * p_num) from p_order;

집계 함수를 여러 개 사용해서 여러 가지 집계 결과를 구할 수 있습니다. 반환되는 값 들은 한 행에 컬럼 이름으로 구분되어 표시됩니다.

Select count(*) as ‘주문개수’, sum(p_num) as ‘상품개수합계’, sum(p_sell_price * p_num) as ‘매출합계’ from p_order;

평균값 구하기

함수는 AVG()로 사용합니다. 행 별 평균값을 구하는 데 사용하는 집계 함수입니다. 주문 당 평균 판매 금액을 알고 싶으면 다음과 같이 쿼리문을 작성합니다.

Select avg(p_sell_price * p_num) as ‘평단가’ from p_order;

평균값은 전체 컬럼 합계를 행 개수로 나눈 값과 같습니다. AVG() 집계 함수는 SUM() / COUNT(*)와 같습니다. 앞의 쿼리문은 SUM()과 COUNT() 집계 함수로도 표현할 수 있습니다.

Select sum(p_sell_price * p_num) / count(*) as '평단가' from p_order;

AVG() 집계 함수도 NULL 값이 있는 행은 평균값 계산에서 제외합니다. SUM()과 COUNT() 함수로 평균값을 구할 때 컬럼에 NULL이 들어갈 수 있는 행은 AVG()와 값이 달라질 수 있습니다.

찜해놓기(wishlist) 테이블에서 판매가(p_sell_price)의 평균가를 구해보겠습니다. 테이블을 정의할 때 판매가에 NULL이 들어갈 수 없도록 하는 게 맞는 방법이지만, NULL 값이 들어가도록 수정된 테이블입니다.

Select sum(p_sell_price) / count(*) as '평단가' from p_order;

평단가는 다음과 같이 계산됩니다.

AVG() 집계 함수로 실제 평균가를 구하려면 다음과 같이 작성합니다.

Select avg(p_sell_price) as '평단가' from p_order;

값이 다른 이유는 SUM()과 COUNT()로 평균가를 구할 때 나누는 행 개수를 count(*)로 구한 것입니다.

AVG() 함수로 평균가를 구할 때 해당 컬럼에 있는 NULL은 계산에서 제외되기 때문에 나누는 행의 개수만큼 줄어듭니다. Count(*)로 나누는 행의 개수를 구하면 NULL이 포함된 전체 행의 개수를 구하기 때문에 더 작은 평균값이 구해지게 됩니다.

NULL이 있는 컬럼의 평균가를 SUM()과 COUNT() 함수로 정확하게 구하려면 다음과 같이 COUNT() 함수를 수정해서 작성해야 합니다.

Select sum(p_sell_price) / count(p_sell_price) as '평단가' from p_order;

NULL을 포함해서 평균값 구하기

CASE 조건문을 사용해 NULL에 대한 조건 처리를 하면 NULL을 포함한 평균값을 구할 수 있습니다.

CASE 조건문을 이용해 NULL이면 0으로 값을 바꾸는 처리를 하면 NULL에 대한 처리가 깔끔하게 되는 쿼리문을 완성할 수 있습니다.

Select avg(case when p_sell_price is null then 0 else p_sell_price end) as '평단가' from p_order;

앞서의 AVG() 집계 함수로 구한 평균값과 값이 달라지는 것은 NULL이 들어있는 행이 집계에 포함되면서 나누는 개수가 더 늘어났기 때문입니다.

최소/최대값 구하기

최소값은 MIN(), 최대값은 MAX()로 컬럼의 최소값과 최대값을 구합니다. 대소를 비교하는 것이기 때문에 숫자, 문자열, 시간 데이터 형인 컬럼에 모두 사용할 수 있습니다.

주문정보(p_order) 테이블에서 주문 합계 금액이 가장 큰 것과 가장 작은 금액, 그리고 최대 주문 수량을 알아보겠습니다.

Select min(p_num * p_sell_price) as ‘최저주문액’, max(p_num * p_sell_price) as ‘최고주문액’, max(p_num) as ‘최대수량’ from p_order;

다른 집계 함수와 마찬가지로 NULL은 무시됩니다.

SELECT문은 WHERE절의 조건식이 SELECT절의 컬럼 집계 함수보다 먼저 실행되기 때문에 WHERE 절로 집계 함수의 범위를 제한할 수 있습니다.

상품정보(product) 테이블에서 판매가 50,000 미만의 상품 중에서 가장 비싼 상품 가격을 알고 싶으면 다음과 같이 쿼리문을 작성할 수 있습니다.

Select max(sell_price) as ‘최고가 상품’ from product where sell_price < 50000;

중복 없는 집계 결과 구하기

컬럼을 기준으로 집계를 구하는 집계 함수들의 결과 값에는 컬럼 값의 중복이 포함되어 있습니다. 집계를 구할 때는 중복이 없는 값의 개수나 합계를 구할 필요가 있고 DISTINCT 키워드를 사용해 중복을 제거합니다.

중복이 없다는 것은 집계를 구하는 컬럼의 값이 같은 것이 없는 유일한 값이라는 뜻입니다. 예를 들어 주문 정보에서 유일한 회원 아이디 개수를 구하면 전체 회원 대비로 몇 명의 회원이 주문을 했는지를 파악할 수 있습니다.

주문정보(p_order) 테이블에는 한 회원이 여러 번 주문을 한 경우도 있기 때문에 회원 아이디에 중복이 있습니다.주문정보 테이블에서 상품을 주문한 유일한 회원 수를 구해보겠습니다.

Select count(DISTINCT userid) from p_order;

DISTINCT 키워드는 집계 함수 안에 집계를 위해 표시하는 열 이름 앞에 공백의 띄워 표시합니다. 키워드를 표시하는 것만으로 중복 없는 컬럼 값의 개수를 구할 수 있습니다.

DISTINCT의 반대 키워드로 ALL이 있습니다. ALL은 중복을 허용하는 것입니다. 키워드를 표시하지 않으면 중복을 허용하는 것이기 때문에 ALL과 같아서 실제로는 ALL 키워드는 사용하지 않습니다.

그룹화

그룹화는 결과 데이터를 종류별로 구분해서 개수, 또는 합계 등을 구하기 위해 쿼리문에 추가하는 절입니다. “GROUP BY”로 사용하며 WHERE절 뒤에 옵니다. WHERE절이 없으면 FROM절 뒤에 그룹화 절이 위치합니다.

기본 구문

SELECT 열 이름1, 열 이름2, … FROM 테이블 이름 WHERE 조건문 GROUP BY 열 이름1, 열 이름2, … ORDER BY 열 이름1, 열 이름2;

GROUP BY 절이 오는 위치를 표시하기 위해 쿼리문 구조가 길게 표현되었지만, GROUP BY만 있는 단순한 쿼리문은 다음 같은 구조가 됩니다.

SELECT 열 이름1, 열 이름2, … FROM 테이블 이름 GROUP BY 열 이름1;

GROUP BY 절로 그룹화를 하려면 GROUP BY 절에 같은 값으로 묶기 위한 기준 컬럼을 한 개 이상 표시해야 합니다. GROUP BY절에 표시한 컬럼의 같은 값끼리 묶어서 집계를 합니다. 집계를 할 때는 SELECT절에 집계 함수를 함께 사용해서 같은 값끼리 묶인 행의 개수, 또는 합계를 구하게 됩니다.

GROUP BY로 묶인 컬럼은 DISTINCT로 유일한 컬럼 값들을 구한 것과 같습니다. 중요한 다른 점은 DISTINCT는 키워드를 사용한 컬럼 자신의 개수나 합계를 구하지만, GROUP BY로 컬럼을 그룹화하면 다른 컬럼 값의 집계 결과를 얻게 됩니다.

중복 없는 컬럼 값 얻기

GROUP BY 절로 중복 없는 컬럼 값들을 가져와보겠습니다. 주문정보(p_order) 테이블에서 주문이 있었던 상품 코드 목록을 가져옵니다.

Select p_code from p_order group by p_code;

단순하지만 GROUP BY 절로 컬럼을 그룹화하면 중복이 없는 컬럼 값의 목록이 반환됩니다.

여기까지는 그룹화가 DISTINCT를 사용하는 방법과 차이가 없습니다. 중복이 없는 컬럼 값의 목록을 단순히 얻으려면 다음처럼 DISTINCT 키워드로 작성해도 됩니다.

Select distinct p_code from p_order;

집계 구하기

중복이 없도록 컬럼 값들을 그룹화해서 묶고 나면 같은 컬럼 값을 가진 행이 몇 개 있는지, 또는 다른 컬럼 들의 값을 합한 합계는 얼마인지 알아야 할 필요가 있습니다. GROUP BY절과 함께 집계 함수를 이용하면 그룹화로 묶은 행들의 개수나 컬럼 값 합계를 구할 수 있습니다.

주문정보(p_order) 테이블에서 상품코드 별로 주문 개수가 몇 개이고, 총 상품 합계가 얼마인지 알아보겠습니다. 그리고 합계 수량을 기준으로 내림 차순으로 정렬해 인기 판매 상품이 어떤 것인지 한눈에 알 수 있도록 합니다.

Select p_code as ‘상품코드’, count(p_code) as ‘주문개수’, sum(p_num) as ‘합계수량’ from p_order group by p_code;

집계를 구했지만, 집계 내용이 많으면 어떤 상품이 많이 팔리고, 어떤 상품이 적게 팔렸는지 파악하기가 쉽지 않습니다. ORDER BY 절로 집계 결과를 정렬해서 볼 수 있으면 결과 전체를 살펴보지 않아도 내용 파악이 쉬워집니다. ORDER BY 절의 정렬은 SELECT절 실행 뒤에 실행되기 때문에 SELECT절의 집계 함수의 별칭을 그대로 가져와서 정렬에 사용할 수 있습니다.

Select p_code as ‘상품코드’, count(p_code) as ‘주문개수’, sum(p_num) as ‘합계수량’ from p_order group by p_code order by ‘합계수량’ desc;

다음처럼 WHERE 절로 월 단위 기간 조건까지 설정하면, 이달의 판매 인기 상품 목록을 많이 판매된 상품 순으로 구할 수 있습니다.

Select p_code as '상품코드', count(p_code) as '주문개수', sum(p_num) as '합계수량' from p_order where str_to_date('2021-03-01', '%Y-%m-%d') <= wt and wt < str_to_date('2021-04-01', '%Y-%m-%d') group by p_code order by '합계수량' desc;

집계 결과의 제약 조건 설정

쿼리문에서 선택할 행들을 고르는 조건식은 WHERE 절로 설정합니다. 기간, 수량 등 컬럼 값으로 조건식을 설정하면, 조건식에 해당하는 행들이 선택되고 선택된 행들에 대해서 집계 함수가 적용됩니다. 원하는 조건이 컬럼 값의 조건이 아니라 집계 결과의 조건일 때는 조금 다른 조건 설정을 해야 합니다.

WHERE절에 집계 결과에 대해 조건을 설정해보겠습니다. 주문 상품의 집계 합계가 1보다 큰 상품만 가져오기로 합니다.

Select p_code as ‘상품코드’, count(p_code) as ‘주문개수’, sum(p_num) as ‘합계수량’ from p_order where sum(p_num) > 1 group by p_code;

에러가 발생합니다. MySQL에서는 “Invalid use of group function”으로 에러가 표시됩니다. “그룹 함수를 잘못 사용했다”인데 뜻이 정확하지 않습니다.

다른 데이터베이스에서는 보다 명확하게 에러의 원인을 알려줍니다. SQL Server는 앞의 쿼리문 실행에 대해 “집계가 HAVING 절이나 SELECT 목록에 포함된 하위 쿼리 내에 없으면 WHERE 절에 나타날 수 없습니다. 또한 집계 중인 열은 외부 참조입니다.”라고 비교적 정확한 에러 메시지를 표시합니다.

집계 함수를 HAVING절에 사용하거나, 하위 쿼리로 집계를 구한 것이 아니면 WHERE절에 집계 함수를 사용할 수 없다는 뜻입니다.

여전히 어렵지만, 집계 함수 결과를 조건식으로 사용하려면 HAVING 절을 사용해야 한다는 것은 에러 메시지를 통해 알 수 있습니다.

집계 함수는 HAVING 절을 사용해 별도로 조건식을 사용해야 합니다.

그에 앞서 SELECT문의 각 절이 실행되는 순서를 알아야 합니다. SELECT문의 각 절은 다음 순서대로 실행됩니다.

WHERE절 -> GROUP BY -> SELECT -> ORDER BY

SELECT절에서 값을 얻는 집계 함수는 GROUP BY 절로 그룹 단위로 행들을 묶은 후에 가능합니다. GROUP BY절로 그룹화를 하지 않은 데이터에 집계 함수를 사용할 수는 없습니다. 따라서 가장 먼저 실행되는 WHERE절에는 집계 함수를 사용할 수 없습니다.

HAVING 절은 GROUP BY절 실행 후 실행되는 부가 조건절입니다. GROUP BY절 전용의 WHERE절로 이해하면 되며, GROUP BY절이 없으면 HAVING절은 사용할 수 없습니다.

HAVING절이 있는 SELECT문의 실행 순서는 다음과 같아집니다.

WHERE절 -> GROUP BY -> HAVING -> SELECT -> ORDER BY

앞서 에러가 발생했던 쿼리문을 HAVING 절로 수정해서 상품 코드별 수량 합계가 1보다 큰 집계 결과를 가져와보겠습니다.

Select p_code as ‘상품코드’, count(p_code) as ‘주문개수’, sum(p_num) as ‘합계수량’ from p_order group by p_code having sum(p_num) > 1;

한 가지 더 주의할 점이 있습니다. HAVING 절은 SELECT절 앞에 실행되기 때문에 SELECT절에 선언한 별칭을 사용해 조건식을 사용할 수 없습니다. HAVING절에는 집계 함수 그대로를 조건식으로 사용해야 합니다.

다음과 같이 별칭으로 HAVING 절을 사용하면 아무런 결과를 반환하지 않습니다. 주의할 내용이라고 따로 설명을 하는 것은 데이터베이스에 따라 반환되는 결과가 다르기 때문입니다.

HAVING절에 사용한 문자열 별칭 ‘합계수량’은 숫자와 대소 비교를 하는 비정상 비교이기 때문에 쿼리문에서 에러가 발생하는 것이 맞지만 MySQL에서는 에러를 발생시키지 않고 빈 결과를 반환합니다. 결과가 없는 정상 쿼리문으로 인식된다는 뜻입니다.

Select p_code as ‘상품코드’, count(p_code) as ‘주문개수’, sum(p_num) as ‘합계수량’ from p_order group by p_code having ‘합계수량’ > 1;

복수 열로 그룹화하기

지금까지는 그룹화를 할 때 기준 컬럼 한 개 만을 사용해 중복이 없는 컬럼 값 목록과 집계 결과를 얻었습니다. GROUP BY절을 사용할 때는 SELECT절에 표시할 수 있는 열 이름에 제한이 있습니다. GROUP BY절에서 그룹화에 사용한 컬럼과 집계 함수만 SELECT절에 사용할 수 있으며, GROUP BY절에서 그룹화를 하지 않은 열 이름은 사용할 수 없습니다.

컬럼 값에 중복이 없어 그룹화를 하는 것이 의미가 없는 컬럼일지라도 SELECT절에서 컬럼을 선택하려면 GROUP BY절에 반드시 표시를 해야 합니다.

MySQL은 다른 데이터베이스와 달리 GROUP BY절에서 그룹화를 하지 않은 컬럼을 SELECT절에 사용할 수 있는 특징이 있습니다. 사용은 가능하지만, 여러 개의 컬럼 값 중에서 하나만 출력되기 때문에 문제가 있는 결과를 반환하게 됩니다.

Select id, mobile, p_code as '상품코드', sum(p_num) as '합계수량' from p_order group by p_code;

GROUP BY절에서 그룹화를 한 컬럼은 상품코드(p_code) 한 개지만, SELECT절에는 ID, MOBILE 2개의 컬럼이 더 있습니다. 상품코드로 그룹화를 하면서 ID와 MOBILE의 값은 여러 개가 선택될 수 있게 됩니다. 이때 MySQL은 데이터베이스에 가장 먼저 저장된 컬럼 값 1개를 가져와 표시하게 됩니다.

그룹화의 개념상 혼동을 가져올 수 있는 쿼리 결과를 반환하기 때문에 MySQL에서는 GROUP BY 절을 사용할 때 특히 주의해야 합니다.