프로그래머스 SQL 고득점 Kit - GROUP BY

프로그래머스에서 SQL 고득점 Kit을 풀고 추가적으로 조사하여 얻은 지식을 정리하는 문서다. 문제를 풀 때는 익숙한 MySQL을 활용하였다.

GROUP BY

이전 포스트에서도 몇 번 설명했었지만 GROUP BY는 레코드들을 특정 칼럼 값들을 기준으로 묶는 명령어다. 그래서 그룹화한 후 집계 함수를 사용하는 방식으로 많이 사용되는데 COUNT() 함수를 사용하여 해당 그룹에 몇 명이 있는지 출력하는 쿼리를 작성해보면 다음과 같다.

MariaDB [vulnerable_db]> SELECT COMPANY, COUNT(NAME) FROM USER_TABLE GROUP BY COMPANY;
+-----------------------------+-------------+
| COMPANY                     | COUNT(NAME) |
+-----------------------------+-------------+
| Koreatech                   |           2 |
| Shadow Company              |           3 |
| The Fellowship of the Rings |           3 |
+-----------------------------+-------------+
3 rows in set (0.000 sec)

이렇게 GROUP BY로 그룹화된 레코드들에 대해서도 특정 조건을 적용하거나 정렬할 수 있다. 먼저 ORDER BY를 사용하면 위의 쿼리의 경우 COMPANY 칼럼의 알파벳 순서나 COUNT(NAME)의 결괏값을 기준으로 오름차순, 내림차순 정렬할 수 있다.

MariaDB [vulnerable_db]> SELECT COMPANY, COUNT(NAME) FROM USER_TABLE GROUP BY COMPANY ORDER BY COMPANY DESC;
+-----------------------------+-------------+
| COMPANY                     | COUNT(NAME) |
+-----------------------------+-------------+
| The Fellowship of the Rings |           3 |
| Shadow Company              |           3 |
| Koreatech                   |           2 |
+-----------------------------+-------------+
3 rows in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT COMPANY, COUNT(NAME) FROM USER_TABLE GROUP BY COMPANY ORDER BY COUNT(NAME) DESC;
+-----------------------------+-------------+
| COMPANY                     | COUNT(NAME) |
+-----------------------------+-------------+
| The Fellowship of the Rings |           3 |
| Shadow Company              |           3 |
| Koreatech                   |           2 |
+-----------------------------+-------------+
3 rows in set (0.000 sec)

첫 번째 쿼리는 COMPANY 칼럼을 내림차순으로 정렬했기 때문에 알파벳상으로 가장 뒤에 있는 'T'로 시작하는 'The Fellowship of the Rings' 레코드가 제일 먼저 출력되었으며 반대로 'K'로 시작하는 'Koreatech'은 가장 마지막에 출력되었다.

두 번째 쿼리에서는 COUNT(NAME) 칼럼, 즉 해당 그룹에 포함된 레코드의 개수를 내림차순으로 정렬하였으며 3, 3, 2 순으로 정렬된 것을 볼 수 있다. 이전에 언급했듯이 중복되는 레코드를 다시 정렬하려면 정렬할 칼럼을 하나 더 넘겨주기만 하면 된다.

MariaDB [vulnerable_db]> SELECT COMPANY, COUNT(NAME) FROM USER_TABLE GROUP BY COMPANY ORDER BY COUNT(NAME) DESC, COMPANY;
+-----------------------------+-------------+
| COMPANY                     | COUNT(NAME) |
+-----------------------------+-------------+
| Shadow Company              |           3 |
| The Fellowship of the Rings |           3 |
| Koreatech                   |           2 |
+-----------------------------+-------------+
3 rows in set (0.000 sec)

COMPANY 컬럼으로 한번 더 정렬했기 때문에 COUNT(NAME)이 중복되는 'Shadow Company', 'The Fellowship of the Rings' 레코드가 위의 쿼리와 달리 오름차순으로 정렬되어 'Shadow Company'가 먼저 출력되는 것을 볼 수 있다.

HAVING

GROUP BY로 정렬된 레코드들에도 조건을 한번 더 적용할 수 있다. 그룹화 이전에 WHERE 명령어로 출력할 레코드의 조건을 적용했다면 그룹화 이후에는 HAVING 명령어로 출력할 레코드의 조건을 적용할 수 있다. 예를 들어 그룹화된 레코드 중에서 S로 시작하는 레코드만 출력하고 싶다면 다음처럼 HAVING 조건을 지정할 수 있다.

MariaDB [vulnerable_db]> SELECT COMPANY, COUNT(NAME) FROM USER_TABLE
    -> GROUP BY COMPANY HAVING COMPANY LIKE 'S%';
+----------------+-------------+
| COMPANY        | COUNT(NAME) |
+----------------+-------------+
| Shadow Company |           3 |
+----------------+-------------+
1 row in set (0.001 sec)

위의 쿼리와 달리 S로 시작하는 'Shadow Company' 레코드만 출력된 것을 볼 수 있다. 이때 HAVING 조건에는 SELECT 로 출력하는 칼럼에 대한 조건만을 지정할 수 있다. 예를 들어 ID가 3인 레코드만 포함하고 싶어서 HAVING에 다음처럼 SELECT 되지 않은 칼럼을 넘기면 에러가 발생한다.

MariaDB [vulnerable_db]> SELECT COMPANY, COUNT(NAME) AS PEOPLE FROM USER_TABLE
    -> GROUP BY COMPANY HAVING ID=3;
ERROR 1054 (42S22): Unknown column 'ID' in 'having clause'

이 경우에는 그룹화하기 이전에 적용되는 WHERE 조건에 해당 조건을 포함해야 할 것이다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE WHERE ID=3;
+------+-------+-----------------------------+-------+
| id   | name  | company                     | SCORE |
+------+-------+-----------------------------+-------+
|    3 | Prodo | The Fellowship of the Rings |    35 |
+------+-------+-----------------------------+-------+
1 row in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT COMPANY, COUNT(NAME) AS PEOPLE FROM USER_TABLE
    -> WHERE ID=3
    -> GROUP BY COMPANY;
+-----------------------------+--------+
| COMPANY                     | PEOPLE |
+-----------------------------+--------+
| The Fellowship of the Rings |      1 |
+-----------------------------+--------+
1 row in set (0.000 sec)

쿼리 결과 USER_TABLE에서 ID가 3인 레코드에 대해서만 그룹화가 이루어졌기 때문에 'Prodo' 레코드만 그룹화되었으며 COMPANY 컬럼으로 그룹화한 결과 'Prodo' 레코드 혼자기 때문에 1명밖에 없는 'The Fellowship of the Rings' 그룹만 출력된 것을 볼 수 있다.

 

다른 예제로 COMPANY 컬럼으로 그룹화된 레코드 중 레코드가 3개 포함된 그룹만 출력하고 싶다면 다음처럼 쿼리를 작성할 수 있다.

MariaDB [vulnerable_db]> SELECT COMPANY, COUNT(NAME) AS PEOPLE FROM USER_TABLE
    -> GROUP BY COMPANY HAVING PEOPLE=3;
+-----------------------------+--------+
| COMPANY                     | PEOPLE |
+-----------------------------+--------+
| Shadow Company              |      3 |
| The Fellowship of the Rings |      3 |
+-----------------------------+--------+
2 rows in set (0.001 sec)

이때 눈여겨볼 점은 COUNT(NAME) 칼럼을 AS 명령어를 이용하여 PEOPLE 칼럼으로 바꾼 후 HAVING 조건에 적용했다는 점이다. 꼭 이렇게 이름을 바꾸지 않아도 HAVING에서는 사용할 수 있지만 가독성과 쿼리 수정의 편리함을 위해 함수 실행 결과 같은 칼럼은 AS 명령어로 이름을 바꿔주는 게 편하다.