본문 바로가기

별걸다하는 IT/데이터베이스 Database

[오라클, MySQL] 컬럼을 그룹지어 통계를 파악하자. group by와 having절 사용법, 사용 가능 대표 집계함수, 특징

[데이터베이스 SQL 완전정복 목차]

안녕하세요 양햄찌 블로거입니다.

오늘 살펴볼 쿼리는 group by와 having 절이예요.

ㅎㅎ 업무에서 자주 사용하는 기본적인 키워드죠?

GROUP BY 와 HAVING 절

사용 상황

어떤 컬럼 중 특정 값을 그룹 지어서 통계를 내고 싶을 때가 있을거예요.

예를 들어 내가 어떤 점포 CEO인데 올해 인건비로 얼마 나갔나 집계를 하고 싶다고 합시다.

알바 월급 지출액, 매니저 월급 지출액, 점장 월급 지출액 이렇게 직급별 한달 인건비를 확인하고 싶을 수 있어요! 

 

또는,

내가 판매부서 직원이라, 카테고리별로 오늘 하루 판매량을 확인하고 싶은데, 그 중 50개 미만으로 팔린 카테고리를 알고 싶을 수 있죠.

 

이런 상황처럼 특정 항목을 기준(ex 여기서는 직급/카테고리가 되겠죠?)으로 그룹을 지어

조건에 해당하는 결과만 추출하고 싶을 때 (ex 총 판매량이 50개 미만인것만)

사용하는 명령어가 'group by'와 'having'절 입니다.

 

조건에 따라 그룹짓고 싶을 땐 group by를, 그룹져진 것들 중 특정 조건을 가진 것들만 추출할 때는 having 절을 사용합니다. 간단하죠?

집계 함수 목록

group by는 집계함수와 쓰이기 때문에 집계함수를 알아두는게 좋아요 ㅎㅎ

group by 조건에 해당하는 레코드들끼리 쭉 묶어서 각 묶음이 가진 총 개수를 구한다던지, 묶음별로 모두 더한 값을 보여준다던지, 그 묶음들에서의 최대 값을 각각 보여준다던지,,등 select절에 집계함수가 사용됩니다. 

자주 사용하는 집계함수들을 알아봅시다.

집계함수 의미
COUNT 총 개수
COUNT(DISTINCT) 중복은 세지 않고 총 개수를 센다
SUM 총 합계
AVG 평균값
MAX 최대값
MIN 최소값

더 많은 집계함수가 있지만 일단 요렇게 자주 쓰입니다.

SELECT count(*) -- 테이블의 총 레코드 개수 
FROM EMPLOYEES


SELECT Max(SALARY) -- 테이블 레코드 중 가장 높은 급여값
FROM EMPLOYEES

요런식으로~

GROUP BY와 HAVING의 간단한 사용법

간단하죠? having은 where와 역할은 같은데 group by에서 결과를 필터링 하기 위해 쓰이는 조건입니다. Having절은 집계함수에 대해 조건을 제한하는 절이기 때문이예요.

SAMPLE EXAMPLE

직무를 조건으로 그룹지은 다음, 그 직무에 근무하는 직원의 수, 직무별 직원의 평균 급여 등을 뽑아볼게요.

그리고 직원수가 5명이 채 안되는 직무는 조회조건에서 제외할게요.

SELECT JOB_ID, COUNT(*) 직무별직원수, AVG(SALARY) 직무별평균급여
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING COUNT(*) >= 5

쿼리는 이렇게 되겠죠?

짠 결과가 잘 추출되었습니다.

 

이번엔 where절까지 같이 조건에 넣어 테스트해볼게요.

SELECT JOB_ID, COUNT(*) 직무별직원수, ROUND(AVG(SALARY), 2) 직무별평균급여
FROM EMPLOYEES
WHERE  TO_CHAR(HIRE_DATE, 'YYYYMMDD') >= '20050101'
GROUP BY JOB_ID
HAVING   COUNT(*) >= 5 ;

2005년 이상 근무한 사원들 한에서 직무를 조건으로 그룹지은 다음, 그 직무에 근무하는 직원의 수, 직무별 직원의 평균 급여 등을 뽑아봤습니다. 그리고 역시나 2005년도부터 직무별 근무한 직원수가 5명이 채 안되는 직무는 조회조건에서 제외했어요.

결과

2005년도부터 근무한 사원들은 애초에 그루핑 대상으로 제외를 해버렸기 때문에 결과가 많이 달라졌죠?

 

■ 그루핑 컬럼을 꼭 select절에 넣어야 하는가?

위에는 집계함수 외에도 그룹핑 컬럼을 포함시켜줬는데 그룹핑 컬럼이 꼭 조회절에 포함되어야 하는 것은 아니예요.

사실 컬럼명으로 유추할뿐 뭘 의미하는지 정확히 알 수 없죠.

그러나 포함되어 있지 않으면 결과가 이상하게 보이니 왠만하면 포함시킵니다.

 

 group by 절에는 컬럼 별칭을 사용할 수 없습니다.

SELECT COUNT(*) 직무별직원수, AVG(SALARY) 직무별평균급여
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING 직무별직원수 >= 5 ; --에러

이건 좀 아쉽죠. 요렇게 별칭을 이용해서 조건을 걸면 에러가 납니다.

 

■ select distinct와 group by

GROUP BY 조회에서 집계함수를 하나도 사용하지 않고, 조건에 해당하는 컬럼명과 넣었을 때 group by절은 select disctinct(컬럼) 과 동일하게 수행됩니다.

--집계함수를 사용하지 않은 group by
SELECT JOB_ID
FROM EMPLOYEES
GROUP BY JOB_ID;


--distinct 
SELECT DISTINCT(JOB_ID)
FROM EMPLOYEES;

 

■ 특정 값 기준으로 정렬하고 싶을 땐 order by 쓰기 

SELECT JOB_ID, COUNT(*) 직무별직원수, AVG(SALARY) 직무별평균급여
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING COUNT(*) >= 5
order by avg(salary) desc

위의 쿼리에서 직무별평균급여가 높은 순부터 차례대로 보게끔 정렬했어요.

 

 그룹바이에서 특정 개수의 행만큼 보여주기 (rownum 사용법)

그룹바이에서 rownum을 이용하고 싶다면 서브 쿼리를 이용해야 합니다.

짠 다섯개의 행만 잘 뽑힌 것을 확인할 수 있어요.

 

■ group by 컬럼은 하나가 아니라 다수일 수 있다.

SELECT JOB_ID, DEPARTMENT_ID, MAX(SALARY) 직무최대급여
FROM EMPLOYEES
GROUP BY JOB_ID, DEPARTMENT_ID

간단한 쿼리~

반대로도 group by 해보기~

이 외에도 ROLLUP, CUBE, GROUPING SETS 등의 키워들를 사용하면 

group by에서 보여주지 못하는 다양한 그룹핑 연산을 수행할 수있습니다.

추가 명령어들에 대해서는 이후 포스팅에서 알아보도록 할게요.

 

오늘은 SQL중 group by절과 having에 대해 알아보는 시간을 가졌어요.

도움이 되셨다면 공감/댓글/광고보답 중 하나는어떠신가요? 오늘 하루도 수고하셨습니다 :)