본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
레코드를 칼럼으로 변환해서 조회
GROUP BY나 집합 함수를 통해 레코드를 그룹핑 할 수 있지만 하나의 레코드를 여러 개의 칼럼으로 나누거나 변환하는 SQL 문법은 없다. 하지만 SUSM(), COUNT()와 같은 집합 함수와 CASE WHEN .. END 구문을 이용해 레코드를 칼럼으로 변환하거나 하나의 칼럼을 조건으로 구분해서 2개 이상의 칼럼으로 변환하는 것은 가능하다.
우선 다음과 같이 dept_emp 테이블을 이용해 부서별로 사원의 수를 확인하는 쿼리를 생각해 보자.
SELECT dept_no, COUNT(*) AS emp_count FROM dept_emp GROUP BY dept_no;
레코드를 칼럼으로 변환해야 하는 경우 위의 GROUP BY 쿼리를 SUM(CASE WHEN ...) 기능을 이용해 한번 더 변환해주면 된다.
위 쿼리의 결과로 다음과 같이 부서 정보와 부서별 사원의 수가 가로(레코드가) 아니라 세로(칼럼)으로 변환되었다.
변환의 원리는 간단하다. 부서별 9개의 레코드를 한 건의 레코드로 만들어야 하기 때문에 GROUP BY된 결과를 서브 쿼리로 만든 후 SUM() 함수를 적용했다. 즉 9개의 레코드를 1건의 레코드로 변환한 것이다. 그리고 부서번호의 순서대로 CASE WHEN ... 구문을 이용해 각 칼럼에서 필요한 값만 선별해서 SUM()을 했다.
이렇게 레코드를 칼럼으로 변환하는 작업을 할 때는 목적이나 용도에 맞게 COUNT, MIN, MAX, AVG, SUM 등의 함수를 사용하면 된다. 이 예제의 단점은 부서 번호가 변경되거나 추가되면 쿼리까지도 변경돼야 한다. 이런 부분은 동적으로 쿼리를 생성해내는 방법으로 보완하면 된다.
하나의 칼럼을 여러 칼럼으로 분리
다음의 쿼리는 단순히 부서별 전체 사원의 수만 조회할 수 있는 쿼리였다.
SELECT dept_no, COUNT(*) AS emp_count
FROM dept_emp GROUP BY dept_no;
SUM(CASE WHEN...) 문장은 특정 조건으로 소그룹을 나눠서 사원의 수를 구하는 용도로 사용할 수 있다. 다음 쿼리는 전체 사원 수와 함게 입사연도별 사원수를 구하는 쿼리다.
SELECT de.dept_no,
SUM(CASE WHEN e.hire_date BETWEEN '1980-01-01' AND '1989-12-31' THEN 1 ELSE 0 END) AS cnt_1980,
SUM(CASE WHEN e.hire_date BETWEEN '1990-01-01' AND '1999-12-31' THEN 1 ELSE 0 END) AS cnt_1990,
SUM(CASE WHEN e.hire_date BETWEEN '2000-01-01' AND '2009-12-31' THEN 1 ELSE 0 END) AS cnt_2000,
COUNT(*) AS cnt_total
FROM dept_emp de, employees e
WHER e.emp_no=de.emp_no
GROUP BY de.dept_no;
위의 쿼리의 결과는 다음과 같이 1980년도, 1990년도, 그리고 2000년도의 부서별 입사자 수를 보여준다.
dept_emp 테이블만으로는 사원의 입사 일자를 알 수 없으므로 employees 테이블을 조인했으며, 조인된 결과를 dept_emp 테이블의 dept_no 별로 GROUP BY를 실행했다. 그룹핑된 부서별 사원의 정보를 CASE WHEN으로 사원의 입사 연도를 구분해서 각 연도대별로 합계(SUM 함수)를 실행하면 원하는 결과를 얻을 수 있다. 이런 간단한 SQL 문장으로 상당히 많은 프로그램 코드를 줄일 수 있을 것이다. 이러한 형태의 쿼리에 WITH ROLLUP 기능을 같이 사용한다면 더 유용한 결과를 만들어낼 수 있다.
