[SQL] 집계

가비·2020년 12월 21일

[SQL]

목록 보기

3/13

집계함수

집계함수란? 인수로 집합을 지정하여 집합함수라고도 불린다. 집합을 특정 방법으로 계산하여 결과를 반환한다.

# 대표적인 집계함수
COUNT(집합)
SUM(집합)
AVG(집합)
MIN(집합)
MAX(집합)

하나의 행에 대하여 하나의 값을 반환하는 일반적인 함수와 다르게 복수의 값(집합)에서 하나의 값을 반환한다.
👉 집계함수를 SELECT 구에 쓰면 WHERE 구의 유무와 관계없이 결괏값으로 하나의 행을 반환한다.

COUNT

인수로 주어진 집합의 개수를 구해 반환한다.
👉 인수로 애스터리스크(*)를 사용할 수 있는 유일한 함수이다.

SELECT COUNT(집합) FROM 테이블명

# 행 개수 구하기
mysql> SELECT COUNT(*) FROM sample51;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

WHERE 구 지정

# 행 개수 구하기
mysql> SELECT * FROM sample51 WHERE name = 'a';
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
+------+------+----------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM sample51 WHERE name = 'a';
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

WHERE 구로 조건을 지정하면 검색된 행이 COUNT로 넘겨진다.
👉 SELECT 구는 WHERE 구보다 나중에 처리되기 때문이다.

NULL 값

집계함수는 집합 안에 NULL 값이 있을 경우 제외하고 처리한다.

mysql> SELECT * FROM sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.00 sec)

mysql> SELECT COUNT(no), COUNT(name) FROM sample51;
+-----------+-------------+
| COUNT(no) | COUNT(name) |
+-----------+-------------+
|         5 |           4 | 👉 NULL은 무시된다.
+-----------+-------------+
1 row in set (0.01 sec)

DISTINCT

mysql> SELECT ALL name FROM sample51;
+------+
| name |
+------+
| A    | 
| A    | 
| B    |
| C    |
| NULL |
+------+
5 rows in set (0.00 sec)

✔️ DISTINCT 지정, 인수가 아니므로 콤마는 붙이지 않는다.
mysql> SELECT DISTINCT name FROM sample51;
+------+
| name |
+------+
| A    | 👉 중복 제거
| B    |
| C    |
| NULL |
+------+
4 rows in set (0.00 sec)

DISTINCT는 예약어로 SELECT 구에 지정된 모든 열을 비교하여 판단한다.
DISTINCT를 생략할 경우 ALL로 간주되어 모든 행을 반환한다.

✔️ NULL 값을 제외하고 중복하지 않는 데이터의 개수를 구하는 경우

집계함수의 인수로 DISTINCT를 사용한 수식을 지정한다.

mysql> SELECT COUNT(ALL name), COUNT(DISTINCT name) FROM sample51;
+-----------------+----------------------+
| COUNT(ALL name) | COUNT(DISTINCT name) |
+-----------------+----------------------+
|               4 |                    3 | 👉 NULL을 제외한 값의 개수
+-----------------+----------------------+
1 row in set (0.00 sec)

SUM

# 합계 구하기
mysql> SELECT * FROM sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.00 sec)

mysql> SELECT SUM(quantity) FROM sample51;
+---------------+
| SUM(quantity) |
+---------------+
|            16 |
+---------------+
1 row in set (0.00 sec)

인수로 주어지는 집합은 수치형만 가능하다.

AVG

# 평균값 구하기
mysql> SELECT AVG(quantity), SUM(quantity)/COUNT(quantity) FROM sample51;
+---------------+-------------------------------+
| AVG(quantity) | SUM(quantity)/COUNT(quantity) |
+---------------+-------------------------------+
|        4.0000 |                        4.0000 |
+---------------+-------------------------------+

인수로 주어지는 집합은 수치형만 가능하다.

✔️ NULL 값을 포함하여 평균을 내고 싶다면 ?

CASE를 사용하여 NULL을 0으로 변환한 뒤에 계산한다.

mysql> SELECT AVG(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS avgnull0
+----------+
| avgnull0 |
+----------+
|   3.2000 |
+----------+
1 row in set (0.00 sec)

MIN / MAX

# 최솟값 / 최댓값 구하기
mysql> SELECT MIN(quantity), MAX(quantity), MIN(name), MAX(name) FROM sample51;
+---------------+---------------+-----------+-----------+
| MIN(quantity) | MAX(quantity) | MIN(name) | MAX(name) |
+---------------+---------------+-----------+-----------+
|             1 |            10 | A         | C         |
+---------------+---------------+-----------+-----------+
1 row in set (0.01 sec)

그룹화

GROUP BY

# GROUP BY 구에 name 열 지정하여 그룹화
✔️ SELECT 구에 name 열을 지정하였으므로 그룹화된 name 열의 데이터가 클라이언트로 반환된다.
mysql> SELECT name FROM sample51 GROUP BY name;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| NULL |
+------+
4 rows in set (0.00 sec)

지정된 열의 값이 같은 행이 하나의 그룹으로 묶이므로 각 그룹으로 묶인 값들은 서로 동일하다.
👉 DISTINCT와 같이 중복을 제거하는 효과가 있지만 집계함수와 함께 사용하지 않으면 의미가 없다. (각 그룹이 하나의 집합으로 집계함수의 인수로 넘겨지기 때문)

집계함수와 GROUP BY

mysql> SELECT * FROM sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.00 sec)

mysql> SELECT name, COUNT(name), SUM(quantity)
    -> FROM sample51 GROUP BY name;
+------+-------------+---------------+
| name | COUNT(name) | SUM(quantity) |
+------+-------------+---------------+
| A    |           2 |             3 |
| B    |           1 |            10 |
| C    |           1 |             3 |
| NULL |           0 |          NULL |
+------+-------------+---------------+
4 rows in set (0.00 sec)

HAVING 구로 조건 지정

mysql> SELECT name, COUNT(name) FROM sample51 GROUP BY name;
+------+-------------+
| name | COUNT(name) |
+------+-------------+
| A    |           2 |
| B    |           1 |
| C    |           1 |
| NULL |           0 |
+------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT name, COUNT(name) FROM sample51
    -> GROUP BY name HAVING COUNT(name) = 1;
+------+-------------+
| name | COUNT(name) |
+------+-------------+
| B    |           1 |
| C    |           1 |
+------+-------------+
2 rows in set (0.01 sec)

집계함수는 WHERE 구의 조건식에서는 사용할 수 없기 때문에 HAVING 구를 사용한다.
👉 WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY 순으로 처리되기 때문
GROUP BY 구 뒤에 기술하며 조건식에는 그룹별로 집계된 열의 값이나 집계합수의 계산결과가 전달된다.
결과적으로 WHERE 구와 HAVING 구에 지정한 조건으로 검색하는 2단 구조가 된다.

복수열의 그룹화

mysql> SELECT no, name, quantity FROM sample51 GROUP BY name;
ERROR 1055 (42000)         ❗️

mysql> SELECT * FROM sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 | 👉 name 열 값이 A인 그룹의 quantity 열 값은 2개
|    2 | A    |        2 | ✔️ 어느 값을 출력해야 하는지 모른다.
|    3 | B    |       10 |
|    4 | C    |        3 |
|    5 | NULL |     NULL |
+------+------+----------+
5 rows in set (0.00 sec)

GROUP BY에 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT 구에 기술해서는 안된다.
👉 클라이언트로 반환되는 결과는 그룹당 하나의 행이므로 어느 것을 반환하면 좋을지 몰라 에러가 발생한다.

mysql> SELECT MIN(no), name, SUM(quantity) FROM sample51 GROUP BY name;
+---------+------+---------------+
| MIN(no) | name | SUM(quantity) |
+---------+------+---------------+
|       1 | A    |             3 |
|       3 | B    |            10 |
|       4 | C    |             3 |
|       5 | NULL |          NULL |
+---------+------+---------------+
4 rows in set (0.00 sec)

이때 집계함수를 사용하면 집합은 하나의 값으로 계산되므로 그룹마다 하나의 행을 출력할 수 있다.

결과값 정렬

# name 열로 그룹화하여 합계를 구하고 내림차순으로 정렬
mysql> SELECT name, COUNT(name), SUM(quantity)
    -> FROM sample51 GROUP BY name ORDER BY SUM(quantity) DESC;
+------+-------------+---------------+
| name | COUNT(name) | SUM(quantity) |
+------+-------------+---------------+
| B    |           1 |            10 |
| A    |           2 |             3 |
| C    |           1 |             3 |
| NULL |           0 |          NULL |
+------+-------------+---------------+
4 rows in set (0.00 sec)