본문 바로가기
데이터베이스/SQL

프로그래머스 SQL 고득점 Kit - SUM, MAX, MIN

하루히즘 2021. 2. 2. 15:41

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

집계 함수

이번 Kit에서 다루는 함수들은 집계 함수들로 테이블의 여러 행에서 하나의 결괏값을 반환하는 함수들이다. 함수 이름에서 볼 수 있듯이 합계(SUM), 최댓값(MAX), 최솟값(MIN) 등을 구하려면 당연히 여러 행을 활용해야 할 것이며 이들의 결괏값은 당연히 하나만 반환된다. 레코드를 특정 칼럼 값에 따라 그룹화하는 GROUP BY 명령어와 함께 사용된다면 더욱 효과적이다. 

 

알아둘 것은 집계 함수는 SELECT된 레코드들에 대해서 수행하는 것이기 때문에 WHERE 절에서 사용할 수 없다. 예를 들어 다음처럼 점수가 최고 점수인 레코드만 출력하는 쿼리를 작성한다고 해보자.

MariaDB [vulnerable_db]> SELECT * FROM SCORE_TABLE WHERE SCORE=MAX(SCORE);
ERROR 1111 (HY000): Invalid use of group function

레코드 중 'SCORE' 칼럼 값이 모든 레코드의 'SCORE' 칼럼 값 중 최고값과 동일한 레코드만 출력한다고 했을 때 에러가 발생하는 것을 볼 수 있다. 이는 위에서 말했듯이 MAX() 함수가 SELECT 된 결과에 대해서 동작하기 때문이다. 이를 수행하려면 괄호로 싸여있는 서브 쿼리를 활용해야 하며 다음과 같이 사용할 수 있다.

MariaDB [vulnerable_db]> SELECT * FROM SCORE_TABLE WHERE SCORE=(SELECT MAX(SCORE) FROM SCORE_TABLE);
+------+---------+-------+
| ID   | NAME    | SCORE |
+------+---------+-------+
|    5 | Saruman |   100 |
+------+---------+-------+
1 row in set (0.012 sec)

위 쿼리에서는 괄호 내의 서브쿼리가 먼저 실행되어 'SCORE_TABLE'에서 'SCORE' 칼럼 값 중 최댓값을 반환하고 메인 쿼리에서 사용하고 있다. 이는 즉 다음과 같은 쿼리와 동일하다.

MariaDB [vulnerable_db]> SELECT * FROM SCORE_TABLE WHERE SCORE=100;
+------+---------+-------+
| ID   | NAME    | SCORE |
+------+---------+-------+
|    5 | Saruman |   100 |
+------+---------+-------+
1 row in set (0.000 sec)

SUM

SUM() 함수는 말 그대로 주어진 칼럼에 대한 레코드 값의 합계를 계산한다.

MariaDB [vulnerable_db]> SELECT * FROM SCORE_TABLE;
+------+---------+-------+
| ID   | NAME    | SCORE |
+------+---------+-------+
|    1 | Kwonkyu |    55 |
|    2 | Jason   |    75 |
|    3 | Prodo   |    77 |
|    4 | Sauron  |    99 |
|    5 | Saruman |   100 |
+------+---------+-------+
5 rows in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT SUM(SCORE) FROM SCORE_TABLE;
+------------+
| SUM(SCORE) |
+------------+
|        406 |
+------------+
1 row in set (0.001 sec)

합계를 낼 수 없는 칼럼이라면 0을 반환한다.

MariaDB [vulnerable_db]> SELECT SUM(NAME) FROM SCORE_TABLE;
+-----------+
| SUM(NAME) |
+-----------+
|         0 |
+-----------+
1 row in set, 5 warnings (0.001 sec)

GROUP BY 명령어로 레코드를 그룹화하면 해당 그룹에 대해서만 합계를 낼 수 있다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE;
+------+---------+-----------------------------+-------+
| id   | name    | company                     | SCORE |
+------+---------+-----------------------------+-------+
|    1 | Kwnokyu | Koreatech                   |    50 |
|    2 | Jason   | Shadow Company              |    55 |
|    3 | Prodo   | The Fellowship of the Rings |    35 |
|    4 | Sauron  | The Fellowship of the Rings |    80 |
|    5 | Saruman | The Fellowship of the Rings |    76 |
|    6 | John    | Shadow Company              |    70 |
|    7 | Tyler   | Shadow Company              |    77 |
|    8 | Jaemin  | Koreatech                   |    90 |
+------+---------+-----------------------------+-------+
8 rows in set (0.000 sec)

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

그렇기 때문에 집계 함수는 GROUP BY 명령어와 유용하게 쓰이는 것이다.

MAX

MAX() 함수는 주어진 컬럼에서 최댓값을 반환한다.

MariaDB [vulnerable_db]> SELECT MAX(SCORE) FROM USER_TABLE;
+------------+
| MAX(SCORE) |
+------------+
|         90 |
+------------+
1 row in set (0.000 sec)

SUM() 함수와는 달리 합산이 불가능한 칼럼이라도 최댓값을 낼 수 있다. 이는 문자열이나 날짜 자료형(DATE)에도 사용할 수 있다는 것을 의미한다.

MariaDB [vulnerable_db]> SELECT MAX(NAME) FROM USER_TABLE;
+-----------+
| MAX(NAME) |
+-----------+
| Tyler     |
+-----------+
1 row in set (0.010 sec)

MariaDB [vulnerable_db]> SELECT NAME, MAX(REGISTER) FROM REGISTER_DATE;
+---------+---------------+
| NAME    | MAX(REGISTER) |
+---------+---------------+
| Kwonkyu | 2077-12-25    |
+---------+---------------+
1 row in set (0.000 sec)

이는 MySQL에서 문자열의 경우 알파벳 순으로, 날짜 자료형의 경우 날짜로 비교할 수 있기 때문에 내부적으로 비교 연산을 통해 최댓값을 계산할 수 있기 때문이다. 반대로 최솟값을 구하는 연산도 가능할 것이다.

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

GROUP BY와 활용하면 각 그룹에서 최고 점수(혹은 가장 빠른 알파벳을 가진 이름, 날짜 등)를 출력하는 것도 가능하다.

MIN

MAX() 함수와 동일하지만 반대의 역할을 한다는 것 말고는 별다른 차이점이 없다. 무엇이든 최솟값을 반환한다.

MariaDB [vulnerable_db]> SELECT MIN(NAME), MIN(COMPANY), MIN(SCORE) FROM USER_TABLE;
+-----------+--------------+------------+
| MIN(NAME) | MIN(COMPANY) | MIN(SCORE) |
+-----------+--------------+------------+
| Jaemin    | Koreatech    |         35 |
+-----------+--------------+------------+
1 row in set (0.000 sec)

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

AVG

AVG() 함수는 주어진 컬럼의 평균값을 구한다.

MariaDB [vulnerable_db]> SELECT AVG(SCORE) FROM USER_TABLE;
+------------+
| AVG(SCORE) |
+------------+
|    66.6250 |
+------------+
1 row in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT AVG(NAME) FROM USER_TABLE;
+-----------+
| AVG(NAME) |
+-----------+
|         0 |
+-----------+
1 row in set, 8 warnings (0.000 sec)

MariaDB [vulnerable_db]> SELECT AVG(REGISTER) FROM REGISTER_DATE;
+---------------+
| AVG(REGISTER) |
+---------------+
| 20324219.3333 |
+---------------+
1 row in set (0.000 sec)

MAX(), MIN() 과는 달리 문자열에는 적용할 수 없는데 특이하게 DATE 칼럼에는 평균값을 구할 수 있는 것을 볼 수 있다. 이것은 실제로 해당 날짜 사이의 가운데 날짜를 구하는 게 아니라 날짜 문자열을 정수형으로 치환해서 평균값을 계산한 것에 불과하기 때문에 의미 있는 레코드라 보기 어렵다. 확인을 위해 날짜의 최댓값과 최솟값의 차이를 구해보면 그냥 날짜 문자열을 정수로 치환해서 계산한 것과 동일한 것을 볼 수 있다.

MariaDB [vulnerable_db]> SELECT (SELECT MAX(REGISTER) FROM REGISTER_DATE)-(SELECT MIN(REGISTER) FROM REGISTER_DATE) AS GAP_BETWEEN_MAX_AND_MIN;
+-------------------------+
| GAP_BETWEEN_MAX_AND_MIN |
+-------------------------+
|                  779994 |
+-------------------------+
1 row in set (0.000 sec)

만약 DATETIME같은 시간을 비교하고 싶다면 TIMESTAMPDIFF()라는 함수를 사용할 수 있다. 이는 두 날짜 간 간격을 초, 분, 시 같은 단위로 나타낼 수 있다.

MariaDB [vulnerable_db]> SELECT MAX(REGISTER), MIN(REGISTER) FROM REGISTER_DATE;
+---------------+---------------+
| MAX(REGISTER) | MIN(REGISTER) |
+---------------+---------------+
| 2077-12-25    | 1999-12-31    |
+---------------+---------------+
1 row in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT TIMESTAMPDIFF(SECOND,
    -> (SELECT MIN(REGISTER) FROM REGISTER_DATE),
    -> (SELECT MAX(REGISTER) FROM REGISTER_DATE)
    -> ) AS 'GAP_BETWEEN_MAX_AND_MIN';
+-------------------------+
| GAP_BETWEEN_MAX_AND_MIN |
+-------------------------+
|              2461017600 |
+-------------------------+
1 row in set (0.000 sec)

확인을 위해 외부 사이트에서 비교해보면 일치하는 것을 볼 수 있다.

COUNT

COUNT() 함수는 조건에 부합하는 레코드의 개수를 출력한다. 이때 레코드 자체는 출력되지 않는다.

MariaDB [vulnerable_db]> SELECT COUNT(NAME) FROM USER_TABLE;
+-------------+
| COUNT(NAME) |
+-------------+
|           8 |
+-------------+
1 row in set (0.000 sec)

단순히 테이블에 몇 개의 레코드가 있는지 뿐 아니라 특정 조건에 해당하는 레코드의 개수나 GROUP BY로 그룹화된 레코드의 개수들을 출력할 수 있다.

MariaDB [vulnerable_db]> SELECT COUNT(NAME) FROM USER_TABLE WHERE SCORE > 70;
+-------------+
| COUNT(NAME) |
+-------------+
|           4 |
+-------------+
1 row in set (0.000 sec)

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)

DISTINCT

집계함수에서는 중복을 허용하지 않는 DISTINCT 키워드를 적용할 수 있다.

MariaDB [vulnerable_db]> SELECT COUNT(COMPANY) FROM USER_TABLE;
+----------------+
| COUNT(COMPANY) |
+----------------+
|              8 |
+----------------+
1 row in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT COUNT(DISTINCT COMPANY) FROM USER_TABLE;
+-------------------------+
| COUNT(DISTINCT COMPANY) |
+-------------------------+
|                       3 |
+-------------------------+
1 row in set (0.000 sec)

위의 쿼리처럼 총 8개의 레코드가 존재하는 테이블일지라도 칼럼 값을 기준으로 COUNT() 할 때 DISTINCT 옵션이 설정된다면 중복된 'COMPANY' 칼럼 값을 가지는 레코드는 세지 않는다. 그래서 총 세 종류의 'COMPANY' 칼럼 값이 존재한다는 것을 알 수 있다.

 

 

집계 함수는 그 자체로도 유용하지만 특히 GROUP BY와 함께 사용되었을 때 빛을 발한다. 이를 잘 활용할 수 있도록 해보자.

'데이터베이스 > SQL' 카테고리의 다른 글

프로그래머스 SQL 고득점 Kit - GROUP BY  (0) 2021.02.02
WITH (Common Table Expressions)  (0) 2021.02.02
프로그래머스 SQL 고득점 Kit - SELECT  (0) 2021.02.02

'데이터베이스/SQL' Related Articles

티스토리툴바