11.4.8 GROUP BY

11.4.8.1 WITH ROULLUP

• ROLLUP 기능을 사용해 GROUP BY 가 사용된 쿼리에서 그루핑된 그룹별로 소계 가져오기 가능
• WITH ROLLUP 이 선언된 GROUP BY 쿼리 결과는 그룹별 소계 출력 레코드가 추가되어 표시되고, 칼럼값은 NULL 이다.

-- GROUP BY의 전체 합계 조회
SELECT dept_no, COUNT(*)
FROM dept_emp
GROUP BY dept_no WITH ROLLUP;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d001    |    20211 |
| d002    |    17346 |
| d003    |    17786 |
| d004    |    73485 |
| d005    |    85707 |
| d006    |    20117 |
| d007    |    52245 |
| d008    |    21126 |
| d009    |    23580 |
| NULL    |   331603 |
+---------+----------+

-- GROUP BY 칼럼 2개
-- first_name 그룹별로 소계 레코드 출력
-- 마지막 라인에 전체 총계 레코드 출력
SELECT first_name, last_name, COUNT(*)
FROM employees 
GROUP BY first_name, last_name WITH ROLLUP;
+----------------+------------------+----------+
|   first_name   |    last_name     | count(*) |
+----------------+------------------+----------+
...
| Zhonghua       | Zwicker          |        1 |
| Zhonghua       | NULL             |      203 |
...
| Zissis         | Zeleznik         |        1 |
| Zissis         | NULL             |      241 |
...
| Zvonko         | Zuberek          |        1 |
| Zvonko         | NULL             |      258 |
| NULL           | NULL             |   300024 |
+----------------+------------------+----------+

• MySQL 8.0 부터는 GROUPING 함수를 통해 NULL 대신 명시적인 값 사용 가능

SELECT IF(GROUPING(dept_no), 'Total', dept_no) AS dept_no, COUNT(*)
FROM dept_emp
GROUP BY dept_no WITH ROLLUP;
+---------+----------+
| dept_no | COUNT(*) |
+---------+----------+
| d001    |    20211 |
| d002    |    17346 |
...
| d008    |    21126 |
| d009    |    23580 |
| Total   |   331603 |
+---------+----------+

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

• 하나의 레코드를 여러 개의 칼럼으로 나누거나 변환하는 SQL 문법은 없음. 집합 함수 와 CASE WHEN 구문 이용해 변환 가능.

-- 부서번호, 부서별 사원 수
mysql> select dept_no, count(*) as emp_count 
         from dept_emp 
        group by dept_no;
+---------+-----------+
| dept_no | emp_count |
+---------+-----------+
| d001    |     20211 |
| d002    |     17346 |
| d003    |     17786 |
| d004    |     73485 |
| d005    |     85707 |
| d006    |     20117 |
| d007    |     52245 |
| d008    |     21126 |
| d009    |     23580 |
+---------+-----------+

-- 레코드를 칼럼으로 변환
SELECT  
     SUM(CASE WHEN dept_no='d001' THEN emp_count ELSE 0 END) AS count_d001,
     SUM(CASE WHEN dept_no='d002' THEN emp_count ELSE 0 END) AS count_d002,
	...
     SUM(CASE WHEN dept_no='d008' THEN emp_count ELSE 0 END) AS count_d008,
     SUM(CASE WHEN dept_no='d009' THEN emp_count ELSE 0 END) AS count_d009,
     SUM(emp_count) AS count_total
  FROM (
        SELECT dept_no, count(*) AS emp_count FROM dept_emp GROUP BY dept_no
       ) tb_derived;
+------------+------------+------------+-----+------------+-------------+
| count_d001 | count_d002 | count_d003 | ... | count_d009 | count_total |
+------------+------------+------------+-----+------------+-------------+
|      20211 |      17346 |      17786 | ... |      23580 |      331603 |
+------------+------------+------------+-----+------------+-------------+

-- 하나의 칼럼을 여러 칼럼으로 분리
-- employees > hire_date 이용하여 입사 연도별 사원 수 조회 가능
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
WHERE e.emp_no = de.emp_no
GROUP BY de.dept_no;
+---------+----------+----------+----------+-----------+
| dept_no | cnt_1980 | cnt_1990 | cnt_2000 | cnt_total |
+---------+----------+----------+----------+-----------+
| d001    |    11038 |     9171 |        2 |     20211 |
| d002    |     9580 |     7765 |        1 |     17346 |
| d003    |     9714 |     8068 |        4 |     17786 |
| d004    |    40418 |    33065 |        2 |     73485 |
| d005    |    47007 |    38696 |        3 |     85707 |
| d006    |    11057 |     9059 |        1 |     20117 |
| d007    |    28673 |    23570 |        1 |     52245 |
| d008    |    11601 |     9523 |        0 |     21126 |
| d009    |    12979 |    10600 |        1 |     23580 |
+---------+----------+----------+----------+-----------+

11.4.9 ORDER BY

• ORDER BY 절이 사용되지 않는 경우 다음 순서로 정렬 됨
  • 인덱스를 사용한 SELECT 쿼리의 경우 인덱스에 정렬된 순서대로 레코드 조회
  • 인덱스 사용 x, 풀 테이블 스캔 실행하는 SELECT 쿼리의 경우
    • MyISAM 테이블은 테이블에 저장된 순서
    • InnoDB는 항상 프라이머리 키로 클러스터링 되어 있기 때문에 프라이머리 키 순서대로 조회
  • SELECT 쿼리가 임시 테이블을 거쳐 처리되면 레코드 순서 예측 어려움
• • 어떤 DBMS도 ORDER BY 절이 명시되지 않은 쿼리에 대해서는 어떠한 정렬도 보장하지 않는다.
  • 따라서 정렬이 필요한 쿼리에는 꼭 ORDER BY 절을 명시하는 것이 좋음.
• ORDER BY 에서 인덱스 사용 못한 경우에는 추가 정렬 작업이 수행
  • 쿼리 실행 계획 Extra 칼럼에 "Using Filesort" 표시
  • SHOW STATUS LIKE 'Sort_% 로 디스크 파일 이용 여부 확인 가능

11.4.9.1 ORDER BY 사용법 및 주의사항~

• ORDER BY 절에 문자 리터럴은 무시된다.
• MySQL 8.0 부터 정렬 순서를 혼용해서 인덱스를 생성할 수 있다.
  • 인덱스를 ASC 방향으로 생성하면 ASC(정순 스캔)든 DESC(역순 스캔)든 비용 차이가 거의 없다.
  • 인덱스를 DESC 방향으로 생성하면 DESC(역순 스캔)가 ASC(정순 스캔)보다 더 빠르다.