집계 함수: 데이터 그룹화

 실험용 데이터베이스로 계속 사용하고있는 sqldb의 경우에서도 볼 수 있듯이 한 유저가 여러 구매기록이 있는 것과같이 특정 개수를 나타내는 숫자가 여러곳에 퍼져있는 경우가 있다. 이를 모아주는(그룹화) 것이 GROUP BY절이다.

 

GROUP BY절

USE sqldb ;
SELECT userID, amount FROM buytbl ORDER BY userID ; -- 유저들이 구매한 내역이 흩어져있다

SELECT userID, SUM(amount) FROM buytbl GROUP BY userID ; -- 한 유저가 구매한 총 수량을 합쳐서 볼 수 있다
SELECT userID AS '사용자 아이디', SUM(amount) AS '총 구매 개수'
	FROM buytbl GROUP BY userID ; -- AS를 통해 별칭을 정해줘서 더 보기 좋은 결과표를 만들 수 있다
    
SELECT userID AS '사용자 아이디', SUM(price*amount) AS '총 구매액'
	FROM buytbl GROUP BY userID ;

 여러곳에 흩어진 값들을 특정 속성에 따라 묶어주는 기능이므로 대게 계산식으로 나타내주는 집계 함수와 같이 쓰인다.

 

집계 함수

-- 평균값 (AVG)
USE sqldb ;
SELECT AVG(amount) AS '평균 구매 개수' FROM buytbl ; -- 한 번 구매시 평균적인 구매량을 구하는 쿼리

SELECT userID, AVG(amount) AS '평균 구매 개수' FROM buytbl GROUP BY userID ; -- 각 유저당 평균 구매량을 구하는 쿼리

-- 최댓값 (MAX), 최소값 (MIN)
SELECT name, MAX(height), MIN(height) FROM usertbl ; -- 가장 큰 키와 작은 키를 구했지만 각자 어떤 유저인지는 나타내지 못했다 
	-- 수정 --
SELECT name, MAX(height), MIN(height) FROM usertbl GROUP BY name ; -- 이것도 각자 키를 MAX와 MIN 열로 나눠서 보여줄 뿐이다
	-- 수정 --
SELECT name, height
	FROM usertbl
	WHERE height = (SELECT MAX(height) FROM usertbl)
    OR height = (SELECT MIN(height) FROM usertbl) ; -- 서브쿼리를 이용해서 나타내면 원하는 결과를 볼 수 있다

-- 숫자 세기 (COUNT)
SELECT COUNT(*) AS '회원수' FROM usertbl; -- 전체 회원을 셌다

SELECT COUNT(mobile1) AS '핸드폰이 있는 사용자' FROM usertbl ; -- 휴대폰 정보를 입력한 사람의 숫자를 셌다

 이외에도 더 다양한 집계 함수들이 있지만 필요한 계산이 있을 때마다 검색해서 사용하는 것이 더 효율적인 것 같아 대표적으로 자주 쓰이는 것들을 예시로 적어놨다. 가만히 보면 엑셀에서 쓰던 함수들과 비슷한 결처럼 느껴진다. 앞으로의 실습들을 통해서 더 다양한 함수들을 접해보자.

 

 이러한 집계 함수를 사용할 때에는 조건을 부여할 때 WHERE절 대신 HAVING을 사용해야 한다. 해당 개념과 관련해 실습한 내용을 따라가면서 사용법을 알아보자.

-- 이전에 총 구매액을 구한 것에서 1000 이상인 사람을 골라서 봐보자
SELECT userID AS '사용자', SUM(amount*price) AS '총 구매액'
	FROM buytbl 
    GROUP BY userID ; -- 총구매액 산출 쿼리

SELECT userID AS '사용자', SUM(amount*price) AS '총 구매액'
	FROM buytbl
    WHERE SUM(amount*price) >= 1000
    GROUP BY userID ; -- 오류 메세지가 뜨는데, 집계 함수는 WHERE 절과 쓰일 수 없다고 한다
	-- 수정 --
SELECT userID AS '사용자', SUM(amount*price) AS '총 구매액'
	FROM buytbl
    GROUP BY userID
    HAVING SUM(amount*price) >= 1000 ; 
/*이런 상황에서 사용하는 것이 HAVING 절이다.
HAVING 절은 WHERE과 비슷하게 조건을 부여하지만 집계 함수를 조건으로 제한한다고 생각하자.
HAVING 절은 무조건 GROUP BY 다음에 작성해야 하니 참고하자. */

-- 총구매액이 적은 사용자부터 나열해보자
SELECT userID AS '사용자', SUM(amount*price) AS '총구매액'
	FROM buytbl
    GROUP BY userID
    HAVING SUM(amount*price) >= 1000
    ORDER BY SUM(amount*price) ;

 

 마지막으로 수를 집계하는 과정에서 중간 합계를 나눠 부문별 집계를 하고싶다면 쓸 수 있는 요긴한 구문이 있다. 이는 ROLLUP인데 사용법이 굉장히 간단하고 수치를 종합할 때 아주 중요하게 쓰이니 실습 내용을 따라가며 사용법을 익혀보자.

/* ROLL UP은 총합계 혹은 소합계가 필요한 경우에 쓰인다
판매 테이블에서 물건의 분류별로 합계 및 총합계를 구해보자 */

SELECT num, groupName, SUM(amount*price) AS '비용'
	FROM buytbl 
    GROUP BY groupName, num -- 분류별 총합계 이후에 각 결제별로 소합계를 나눔
    WITH ROLLUP ;

-- 각 결제회차(num)을 펼쳐 보여주는 것을 제하고 분류별 소합계와 총합계를 보려면 num을 GROUP BY에서 제외하면 된다
SELECT num, groupName, SUM(amount*price) AS '비용'
	FROM buytbl
    GROUP BY groupName
    WITH ROLLUP ;