2023/01/25 ~ 2023/02/01

11.4.8 GROUP BY

특정 칼럼의 값으로 레코드를 그루핑하고, 그룹별로 집계된 결과를 하나의 레코드로 조회할 때 사용한다

11.4.8.1 WITH ROLLUP

그루핑된 그룹별로 소계를 가져올 수 있는 ROLLUP 기능

GROUP BY ... ROLLUP 형태로 사용
단순히 최종 합만 가져오는 것이 아니라 GROUP BY에 사용된 칼럼의 개수에 따라 소계의 레벨이 달라짐
엑셀의 피벗 테이블과 동일한 기능으로 생각하면 됨

WITH ROLLUP과 함께 사용된 GROUP BY 쿼리의 결과는 그룹별로 소계를 출력하는 레코드가 추가되어 표시됨

소계 레코드의 칼럼 값은 항상 NULL로 표시

97p dept_no 칼럼 1개만 있을 때는 소계가 1개만 존재하고 dept_no 칼럼값은 NULL로 표기

first_name, last_name 칼럼 2개로 늘어나는 예제는 소계가 2단계로 표시

first_name = Aamer 소계

first_name = Aamer, last_name = NULL, count = 228

전체 소계

first_name = NULL, last_name = NULL, count = 300024

이런식으로 표시됨

8.0 버전부터는 그룹 레코드에 표시되는 NULL을 사용자가 변경할 수 있다

GROUPING() 함수를 통해서 변경

SELECT ...
 IF(GROUPING(first_name), 'ALL first_name', first_name) AS first_name,
...

11.4.8.2 레코드를 칼럼으로 변환해서 조회

GROUP BY나 집합 함수를 통해 레코드를 그루핑할 수 있지만

하나의 레코드를 여러 개의 칼럼으로 나누거나 변환하는 SQL 문법은 없음

SUM(), COUNT() 같은 집합 함수와 CASE WHEN ... END 구문을 이용해 레코드를 칼럼으로 변환하거나

하나의 칼럼을 조건으로 구분해서 2개 이상의 칼럼으로 변환하는 것은 가능

11.4.8.2.1 레코드를 칼럼으로 변환

99p와 같이 쿼리를 작성하면

SELECT dept_no, COUNT(*) AS emp_count
FROM dept_emp
GROUP BY dept_no;

흔히 봤듯이 dept_no 칼럼의 값 별로 개수가 여러 레코드로 출력된다

레포팅 도구나 OLAP 같은 도구에서 레코드를 칼럼으로 변환해야 할 수도 있다

100p 결과처럼 부서 정보와 부서별 사원의 수를 레코드(가로)가 아닌 칼럼(세로)별로 변환됨

변환의 원리는

부서별로 9개의 레코드를 한 건의 레코드로 만들어야 하기 때문에

GROUP BY된 결과를 서브쿼리로 만든 후 SUM() 함수 적용

레코드를 칼럼으로 변환하는 작업은 위에 처럼 COUNT, MIN, MAX, AVG, SUM 등의 집합 함수를 사용하면 됨

해당 예제의 단점은 부서 번호가 쿼리의 일부로 사용되기 때문에 부서 변호 변경되면 쿼리까지도 변경해야함
- 이런 부분은 동적으로 쿼리를 생성하는 방법 등으로 보완하면 됨(???)

11.4.8.2.2 하나의 칼럼을 여러 칼럼으로 분리

SELECT dept_no, COUNT(*) AS emp_count
FROM dept_emp
GROUP BY dept_no;

앞에 99p 쿼리인데 단순히 부서별로 전체 사원의 수만 조회하는 쿼리였다

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
    ...
    ...
    COUNT(*) AS cnt_total
FROM dept_emp de, employees e
WHERE e.emp_no=de.emp_no
GROUP BY de.dept_no;

해당 쿼리 처럼 SUM(CASE WHEN...) 문장을 통해 소그룹을 특정 조건으로 나눠서 사원의 수를 구하는 용도로 사용할 수 있고 결과는 101p 처럼 1980년도, 1990년도, 2000년도의 부서별 입사 사원의 수를 볼 수 있다

dept_emp 테이블 만으로는 사원의 입사 일자를 알 수 없으므로 employees 테이블을 조인했고

조인된 결과를 dept_emp 테이블의 dept_no별로 GROUP BY 를 실행 했다

그루핑된 부서별 사원의 정보에서 CASE WHEN 으로 사원의 입사 연도를 구분해서 연도별로 합계를 실행하면 원하는 결과를 얻을 수 있다

위와 같은 SQL 문장으로 상당히 많은 프로그램 코드를 줄일 수 있고 앞서나온 WITH ROLLUP 기능을 함께 사용한다면 더 유용한 결과를 만들어 낼 수 있다고 책에 나와있는데

GROUP BY 내용 매우 유익해보이고 실제로도 많이 쓸 듯?

11.4.9 ORDER BY

order by 절이 사용되지 않는 경우 select 쿼리 결과의 정렬 기준
- 인덱스를 사용한 경우 인덱스에 정렬된 순서대로 레코드를 가져옴
- 인덱스를 사용하지 못하고 풀 테이블 스캔을 하는 경우
- MyISAM 테이블은 테이블에 저장된 순서대로 가져옴. 이 순서는 데이터가 insert 된 순서와 동일함을 보장하지 않음
- InnoDB는 항상 프라이머리 키로 클러스터링돼 있기 때문에 이 순서대로 레코드를 가져옴
- 임시 테이블을 거쳐 처리되면 레코드의 순서를 예측하기 어려움
인덱스를 사용하지 못할 경우 추가 정렬 작업이 수행됨
- 이 때, 실행 계획의 Extra 칼럼에 "Using filesort" 라는 코멘트가 표시됨; 쿼리 수행 도중에 정렬 알고리즘을 수행했다는 의미
- 실제로 메모리만 이용해 정렬이 수행됐는지, 디스크의 파일을 이용했는지는 MySQL 서버의 상태 값 조회로 확인 가능 (p.102)
- Sort_merge_passes: 메모리 버퍼와 디스크에 저장된 레코드 병합 횟수
- Sort_rows: 정렬을 수행한 전체 레코드 건수 누적값
- Sort_range: 인덱스 레인지 스캔을 통해 읽은 레코드를 정렬한 횟수 누적값
- Sort_scan: 풀 테이블 스캔을 통해 읽은 레코드를 정렬한 횟수 누적값

11.4.9.1 ORDER BY 사용법 및 주의사항

일반적으로 정렬할 대상은 칼럼명이나 표현식으로 명시하지만 SELECT 되는 순번을 명시할 수도 있음
- ex. SELECT first_name, last_name .. ORDER BY 2 (= ORDER BY last_name)
문자열 상수를 ORDER BY 절에 작성하면 옵티마이저가 ORDER BY 절 자체를 무시함
- ex. SELECT first_name, last_name .. ORDER BY "last_name"
- "last_name"은 컬럼값이 아닌 문자열 리터럴 값으로 인식됨

11.4.9.2 여러 방향으로 동시 정렬

MySQL 8.0 이전 버전까지는 여러개 칼럼을 조합해서 정렬할 때 각 칼럼의 정렬 순서가 혼용되면 인덱스를 이용할 수 없음
MySQL 8.0 버전부터는 오름차순과 내림차순을 혼용해서 인덱스를 생성할 수 있게 개선됨
칼럼 하나에 대해 오름차순이든 내림차순이든 정렬된 인덱스가 있다면 적절히 이용할 수 있음
- 하지만 항상 내림차순으로만 레코드를 정렬한다면 내림차순으로 정렬된 인덱스가 생성되는 것이 좋음

11.4.9.3 함수나 표현식을 이용한 정렬

MySQL 8.0 이전 까지는 연산의 결과를 기준으로 정렬하기 위해서는 가상칼럼을 추가하고 인덱스를 생성하는 방법을 사용해야 했음
MySQL 8.0 부터는 함수 기반의 인덱스를 지원하여 연산 결과값을 기준으로 정렬하는 작업이 인덱스를 사용하도록 튜닝할 수 있게 됨
- ex. SELECT * FROM salaries ORDER BY COS(salary)
- COS: 전달받은 인자의 cosine 값을 리턴

mojh7 / real-mysql-study