노랭이 98번 group by 와 order by

사원 ID	직급	월급
1	사원	100
2	사원	120
3	대리	200
4	대리	200
5	과장	400
6	부장	600

기본적으로 DBMS상에서 ORDERBY절을 수행하려 할 때, ORDER BY절은 SELECT이후에 실행이 된다.

즉, SELECT까지 완료되어 추출된 컬럼을 기준으로 ORDER BY를 수행하는데,

Oracle과 일부 DBMS에서는 SELECT절에 선언하는 Scalra 서브쿼리 혹은, GROUP BY를 하지않은 일반적인 쿼리일 경우

From절에서 DBMS에 의해 메모리에 전체 컬럼을 로드하여 적재시킨다.
이로 인해 SELECT절에 테이블에 존재하는 컬럼을 명시하지 않더라도 ORDER BY절에서 명시하지 않은 컬럼을 기준으로 정렬 수행이 가능하다. (메모리상에 접근?)

 

하지만 GROUP BY나 Scalra 서브쿼리에서는 말이 달라진다.

 

 

코드 실행의 순서상으로는 FROM → GROUP BY → SELECT → ORDER BY순이기 때문에 

사원 테이블로 부터 직급을 기준으로 그룹핑을 수행하게 되면 DBMS 메모리 상에는 테이블의 전체 데이터가 올라가지 않고 GROUP BY절에 의해 그루핑된 직급 컬럼이 메모리상에 올라가게 된다.

 

이 때 주의할 점은 GROUP BY로 인해 직급으로 그룹핑이 된다면 월급으로 ORDER BY를 할 수가 없다.

GROUP BY절을 사용할 때 그룹핑 하지 않고 명시하는 컬럼값(SELECT 혹은 ORDER BY)은 반드시 집계함수와 함께 사용되어야만 한다.

 

아래 예제 쿼리를 실행해본다

  select 직급
    from 사원
group by 직급
order by 월급;

 

 다음과 같은 오류를 출력한다.

 

 Error Code: 1055. Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'sqld.사원.월급'

which is not functionally dependent on columns in GROUP BY clause;

this is incompatible with sql_mode=only_full_group_by 0.000 sec

 

해석

오류 코드: 1055. ORDER BY 절의 식 1번이 GROUP BY 절에 없고 집계되지 않은 열 'sqld.사원.월급'을 포함합니다.
GROUP BY 절의 열에 기능적으로 종속되지 않습니다.
이것은 sql_mode=only_full_group_by 0.000초와 호환되지 않습니다.

 

GROUP BY를 하게 되면 그룹핑이 되면서 컬럼의 값이 단일 로우로 유일해 지게 되는데, 그룹핑하지 않은 컬럼의 경우 다중행으로 출력되는 값들 중 어떤(?) 값을 기준으로 출력을 할 수 없기 때문이다.

 

예를들어 사원테이블을 직급 컬럼으로 그룹핑 할때 아래의 표를 보자

사원ID	직급	월급
(1 or 2) ?	사원	(100 or 120) ?
(2 or 3) ?	대리	200
4	과장	400
5	부장	600

괄호의 값과 물음표로 출력된 값의 의미는 그룹핑시 두 값중 어떤 값을 출력해야 할 지 논리적으로 선택이 불가능하며, DBMS의 선택 기준이 따로 없기 때문이다.

 

따라서 GROUP BY절에 명시되지 않은 컬럼 (그룹핑 기준이 아닌 컬럼)의 경우 ORDER BY를 하기 위해서는 집계함수로 계산된 단일 값을 기준으로 아래와 같은 쿼리문을 작성하여 정렬을 해야 한다.

 

  select 직급
    from 사원
group by 직급
order by MAX(월급);

MAX함수는 그룹별로 데이터를 계산하고, ORDER BY 절은 결과를 정렬하는 데 사용된다.
MAX함수는 SELECT 문의 결과가 그룹별로 정렬되기 전에 계산됩니다.
따라서 MAX함수의 결과는 ORDER BY에 의해 정렬되기 전에 메모리에 로드된다.

(그룹핑 후 계산한 뒤 메모리에 로드함 = GROUP BY -> 집계함수 계산 -> 계산된 컬럼 메모리에 로드) 

 

쿼리 유형	메모리 적재 방식
GROUP BY 하지 않은 쿼리	테이블에 있는 전체 컬럼을 로드
GROUP BY가 있는 쿼리	GROUP BY에 지정한 컬럼 SELECT에 지정한 컬럼 ORDER BY에 명시한 집계함수의 결과값