파트 4-3. Group By, Having 절

in MySQL on Theory

1. GROUP BY절

말 그대로 그룹으로 묶어주는 역할을 한다. 만약 구매 테이블에서 사용자가 구매한 물품의 개수를 보려고 다음과 같이 쿼리를 보냈다고 하자.

SELECT userid, amount from buytbl ORDER BY userid;

사용자별로 여러 번의 물건 구매가 이루어져서 각각의 행이 별도로 출력되게 된다. 그렇다면 만약 특정 사용자의 경우 몇 개를 구매했는가는 확인하려면 일일이 합계를 내야하는데 이렇게 하면 MySQL을 사용할 이유가 없다.
이럴 때는 집계 함수를 사용하면 된다. 집계 함수는 주로 GROUP BY절과 함께 쓰이며 데이터를 그룹화 해주는 기능을 한다. 집계 함수를 사용해서 조회하게 되면 함수그대로가 열이름으로 나오게 되는데 별칭을 사용해서 보기 좋게 바꿀 수 있다.

-- userid에 해당하는 amount의 총합을 하나의 열로 만들어서 조회
SELECT userid, SUM(amount) FROM buytbl GROUP BY userid;

-- 별칭이용
SELECT userid AS '사용자 아이디', SUM(amount) AS '총 구매 개수' FROM buytbl GROUP BY userid;

-- 구매액의 총합 -> 각 물건의 구매액의 총합을 사용자아이디 기준으로 묶어서 조회
SELECT userid AS '사용자 아이디', SUM(price*amount) AS '총 구매 개수' FROM buytbl GROUP BY userid;


SUM() 외의 집계 함수

그림1

주의할 점은 집계 함수는 WHERE절 조건으로 사용할 수 없다. 하지만 where절 조건에 서브쿼리안에 집계함수는 사용할 수 있다.

-- 전체 구매자가 구매한 물품의 개수의 평균
SELECT AVG(amount) AS '평균 구매 개수' FROM buytbl;

-- 각 사용자 별로 한 번 구매시 물건을 평균 몇 개 구매하는지
SELECT userid, AVG(amount) AS '평균 구매 개수' FROM buytbl GROUP BY userid;

-- 가장 큰 키와 가장 작은 키의 회원 이름과 키를 출력
SELECT name, height FROM usertbl WHERE height = (SELECT MAX(height) FROM usertbl) OR height = (SELECT MIN(height) FROM usertbl);

-- 휴대폰이 있는 사용자의 수를 카운트
SELECT COUNT(mobile1) AS '휴대폰이 있는 사용자 수' FROM usertbl;


2. HAVING 절

총 구매액이 1,000 이상인 사용자에게만 사은품을 증정하고 싶다고 하면 아래와 같이 작성했을 것이다.

SELECT userid AS '사용자', SUM(amount*price) AS ' 총 구매액' 
FROM usertbl 
WHERE SUM(amount*price) >1000
GROUP BY userid;

하지만 이렇게 작성하면 안된다. 집계 함수는 WHERE의 조건으로 사용할 수 없기 때문이다. 이때 사용하는 것이 HAVING은 WHERE과 비슷한 개념으로 조건을 제한하는 것이지만, 집계 함수에 대해서 조건을 제한하는 것이라고 생각하면 된다. 그리고 반드시 HAVING절은 GROUP BY절 뒤에 나와야한다.

SELECT userid AS '사용자', SUM(amount*price) AS ' 총 구매액' 
FROM usertbl 
GROUP BY userid;
HAVING SUM(price*amount) > 1000
ORDER BY SUM(price*amount); -- 총 구매액이 적은 사용자부터


3. ROLLUP

총합 또는 중간 합계가 필요하다면 GROUP BY절과 함께 WITH ROLLUP문을 사용하면 된다.

SELECT num, groupname, SUM(price*amount) AS '비용'
FROM buytbl
GROUP BY groupname, num
WITH ROLLUP;

그림2

그림3

중간에 num열이 NULL로 되어 있는 추가된 행이 각 그룹의 소합계를 의미한다. 마지막 행은 각 소합계의 합계, 즉 총합계의 결과가 나타난다.
위 구문에서 num은 PK이며, 각 항목이 보이는 효과를 위해 넣어준 것이다. 만약 소합계 및 총합계만 필요하다면 num을 빼면 된다.

SELECT groupname, SUM(price*amount) AS '비용'
FROM buytbl
GROUP BY groupname
WITH ROLLUP;

그림4


이로써 기본적인 SELECT문의 틀을 살펴봤는데 이제 위와 같은 순서정도는 외워두도록 하자.

그림5


4. SQL의 분류

SQL문은 크게 DML, DDL, DCL로 분류한다.

DML

DML(Data Manipulation Language)은 데이터를 조작(선택, 삽입, 수정, 삭제)하는 데 사용되는 언어다. SQL문 중에 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당한다.

DDL

DDL(Data Definition Language)는 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할을 한다. CREATE, DROP, ALTER 등이다.

DCL

DCL(Data Control Language)는 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문으로 GRANT/REVOKE/DENY 등이 이에 해당한다.


© 2021. By Backtony