[복습] SQL | Oracle 그룹별 연산을 하기 위한 group by 와 having + 복습후기(20231031)

group by는 select문의 네 번째 문법으로 성별 성적평균, 부서별 급여 평균, 시군구별 인원수 등 그룹별 연산을 수행하기 위하여 사용된다.

이렇게 작성하여 부서별 인원수를 구할 수 있으며 묶여진 그룹을 select로 보낸다.

이렇게 작성할 경우 에러가 뜬다(ORA-00979: GROUP BY 표현식이 아닙니다.)

먼저 deptno가 가능한 이유는 부서별로 요약을 했으니 부서 정보를 올릴 수 있다.

group by는 분리-적용-결합 3가지 스텝 매커니즘으로 되어있다. 그리고 분리-적용-결합 순으로 수행된다.

이 뜻은, 위의 예를 적용하여 설명하면 부서별러 먼저 자료를 구분한다. 10번 부서, 20번 부서, 30번 부서 이렇게 정리를 한다. 그 다음 그룹별로 요약을 한다. 사용자가 원하는 함수를 적용하여 요약하는 것이다.

마지막으로 그룹을 10번 부서/20번 부서/30번 부서 합쳐 출력하는 것을 결합이라고 한다.

그렇기 때문에 여기서 그룹함수를 사용한 값만이 추출되고 그룹함수가 적용이 안된 ename등은 함께 출력 할 수 없는 것이다.

즉 group by 표현식이 아닌 컬럼은 select절에 단독으로(그룹함수 없이) 사용이 불가하다.

이렇게 그룹함수를 쓴 max(ename) 자체는 올 수 있긴 하다.

 

예제를 풀어보자.

student 테이블에서 학년별 학생수와 키와 몸무게의 평균을 출력하여라.

 

student 테이블에서 성별 키와 몸무게의 최대값을 출력하여라.

계산되는 순서는 from을 읽어와서 substr 계산 후 gropu by 한 후에 select에 반영하는 것이다.

group by도 성능을 위해서라면 불필요할 시에는 굳이 사용할 필요는 없다.

 

student 테이블에서 학년별, 학과별 학생수를 출력하자.

아래는 order by를 쓰지 않았을 때 출력 결과이다. 위와 비교해보자.

이렇게 order by를 쓰면 순서가 확실하게 정리되어 출력된다. group by는 정렬된 결과를 보장해주진 않는다.

즉 group by는 내부 정렬만을 동반한다(group by 컬럼으로 정렬 후 그룹을 분리함). 하지만 출력 결과는 정렬 결과를 보장하진 않는다. 그러므로 필요시에는 order by로 정렬을 수행하여야 한다.

 

 

여기서 한번 더 쉬어서 다음 게시물에 having 절을 정리하려고 했지만 having 절은 group by와 함께 사용 가능하며 단독으로는 사용이 불가하므로 이어서 정리하도록 하겠다.

 

having절은 select문 다섯 번째에 위치한 절로써 group by와 함께 사용된다(단독 사용 불가).

왜냐하면 group by 결과에 그 조건을 전달하기 위함이기 때문이다(필터링 목적).

그럼 같은 조건 전달인 where과의 차이점은 무엇일까?

where절은 group by 절보다 먼저 사용되므로 group by에 조건을 전달하기 위해서는 having이 와야 한다.

 

예제를 풀어보며 확인해보자.

STUDENT 테이블에서 제1전공번호(deptno1)별로 학생수를 구하고 학생이 2명 이하인 학과는 생략하여 나타내어라.

풀이 전에 순서를 생각해보면

1) 테이블을 읽는다(from 절)

2) 제1전공을 그룹핑한다.

3) 학생 수를 구한다

4) 학생수 조건을 전달한다(학생수 2명 이하 학과 구하기 위하여)

그룹핑한 결과의 조건을 전달하므로 where절로는 순서를 전달할 수가 없다.

여기서 having 절의 위치는 어디로 와야 할까.

순서로 따지나, SQL 표준으로 따져보나 group by다음에 와야 하지만 group by 위에 위치해도 에러는 없다.

★ 하지만 표준은 아니므로 꼭 group by 다음에 쓰도록 하자(SQLD 시험에도 나옴)

 

 

중요한 포인트가 들어가 있는 예제를 하나 더 풀어보자.

 emp2에서 고용현황(emp_type) 별 급여 평균을 출력하되 인턴직은 생략한다.

이는 내가 처음 풀이한 결과이다.

선생님 풀이와 답은 같으나 튜닝에서 마이너스인 정답이다.

 

선생님 풀이는 다음과 같다.

★ 무조건 group by하고 having을 쓰려고 하지 말고 group by에서 요약되는 정보(평균, 카운트, 최대/최소)를 토대로 조건을 전달할 때에만 having을 쓰고 일반 조건은 당연히 where 절에서 먼저 쓰는 것이 성능에 좋다. 이것도 튜닝의 방법이다.

 

정리하자면 일반조건(그룹함수를 사용하지 않는 조건)은 where, having 절에서 모두 전달이 가능하다. 하지만 where에서 먼저 선택할 대상을 선택 후 그룹연산을 수행하는 것이 더 성능에 이점이 있다. 

where절, having절 모두 사용이 가능한 조건은 무조건 where절을 사용하자.

 

 

마지막 연습문제이다.

movie_table 데이터를 사용하여 서울특별시에 대하여 성별, 연령대별 영화 이용비율 현황(총합)을 출력하여라.

선생님께서 아래와 같은 방법도 가능하나 이 방법은 지양하라고 하셨다.

order by 1, 2 로도 쓸 수 있는데 이는 select 에 나열된 컬럼의 순서이다. 사용시 편하긴 하나 업무적으로 사용하면 나중에 문제가 되는 점이, select 순서가 바뀌게 되는 경우가 있기 때문이다.

이 문제에서는  having 자체가 불가하였다. 

성별과 연령대로 group by 하는 순간 시도 데이터는 사라지기 때문이다.

 

 

다음 시간에는 고급기법인 join과 서브쿼리를 공부하겠다고 하셨다.

즉 SQL의 파트2가 되는 것이다.

이것을 왜 배우는 것이냐,  아래 쿼리를 보자.

그래서 최대 급여를 받는 사람이 누군데? 라는 정보는 찾을 수가 없다.

다시 데이터를 훑어보면 알 수 있기는 하나 방대한 양의 데이터를 처리하는 경우에는 그마저도 힘들다.

ename도 쓸 수 없고,  max(ename)하면 가나다 순에 의해 영향받는 이름만 추출될 것이다.

즉 단일쿼리로는 max 셀에 가진 사람을 함께 올릴 수 없다.

추가적으로 쿼리 안에 쿼리가 들어가는 문법이 필요하게 되며 이것이 서브쿼리이다.

이런 것들은 다음 시간부터 배우도록 하겠다.

 

 

 

복습후기

선생님께서 다음 시간부터는 어려워질 것이라고 하셨다. 

안그래도 긴장된 상태인데 더 긴장이 될 것 같다. 더 열심히 해야겠네.