백엔드 프로젝트 5주차 (SQL 첫걸음) - 5장

집계와 서브쿼리

 

🎯행 개수 구하기 - COUNT

집계함수: 인수로 집합을 지정, 집합을 특정 방법으로 계산하여 그 결과를 반환. COUNT, SUM, AVG, MIN, MAX 등

 

COUNT: COUNT(집합), 인수로 주어진 집합의 개수를 구해 반환

 

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.02 sec)

sample51 테이블 전체 불러옴

SELECT COUNT(*) FROM sample51;

+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.03 sec)

sample51에는 전부 다섯 개의 행이 있으며 COUNT의 결괏값도 5.

인수로 모든 열을 나타낼 때 사용하는 메타문자 *가 지정됨, 이때 COUNT 집계함수에서 *는 '모든 열 = 테이블 전체'라는 의미로 사용, COUNT는 인수로 지정된 집합(테이블 전체)의 개수를 세는 것

sample51에는 전부 5개의 행이 있으므로 그 결과 5가 반환됨

즉 COUNT 집계함수로 행 개수를 구할 수 있는 것

 

집계함수는 집합으로부터 하나의 값을 반환, 따라서 집계함수를 SELECT 구에 쓰면 WHERE구의 유무와 관계없이 결괏값으로 하나의 행을 반환함.

SELECT * FROM sample51 WHERE name = 'A';

+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
+------+------+----------+
2 rows in set (0.01 sec)

SELECT 구는 WHERE 구보다 나중에 내부적으로 처리, 따라서 WHERE 구로 조건 지정하면 테이블 전체가 아닌, 검색된 행이 COUNT로 넘겨짐. 이로인해 WHERE 구의 조건에 맞는 행의 개수를 구할 수 있음

 

SELECT COUNT(*) FROM sample51 WHERE name = 'A';

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

COUNT 집계함수를 넣었을 때 최종적으로 결과는 하나의 행이 됨.

WHERE로 행을 검색 >> COUNT로 행의 개수를 집계

 

집계함수와 NULL값

SELECT COUNT(no), COUNT(name) FROM sample51;

+-----------+-------------+
| COUNT(no) | COUNT(name) |
+-----------+-------------+
|         5 |           4 |
+-----------+-------------+
1 row in set (0.00 sec)

name 열에는 NULL 값을 가지는 행이 하나 존재하므로 이를 제외한 개수는 4가 됨, 따라서 COUNT(name)은 4가 되는 것

단 COUNT(*)의 경우 모든 열의 행수를 카운트하기 때문에 NULL값이 있어도 해당 정보가 무시되지 않음

즉 집계함수는 집합 안에 NULL 값이 있을 경우 이를 무시함

 

DISTINCT로 중복 제거

 SELECT ALL name FROM sample51;

+------+
| name |
+------+
| A    |
| A    |
| B    |
| C    |
| NULL |
+------+
5 rows in set (0.00 sec)

ALL을 지정하여 중복 유무와 관계없이 그대로 모든 행 반환

SELECT DISTINCT name FROM sample51;

+------+
| name |
+------+
| A    |
| B    |
| C    |
| NULL |
+------+
4 rows in set (0.01 sec)

SELECT 구에서 DISTINCT를 지정하면 중복된 데이터를 제외한 결과를 클라이언트로 반환

중복 여부는 SELECT 구에 지정된 모든 열을 비교하여 판단

DISTINCT는 예약어로, 열명이 아님

 

ALL과 DISTINCT는 중복된 값을 제거할 것인지 설정하는 스위치와 같은 역할

이들 중 어느 것도 지정하지 않은 경우 중복된 값은 제거되지 않음

 

집계함수에서 DISTINCT

SELECT COUNT(ALL name), COUNT(DISTINCT name) FROM sample51;

+-----------------+----------------------+
| COUNT(ALL name) | COUNT(DISTINCT name) |
+-----------------+----------------------+
|               4 |                    3 |
+-----------------+----------------------+
1 row in set (0.00 sec)

집계함수의 인수로 DISTINCT을 사용한 수식을 지정하여 중복 제거 후 개수 구함

이떄 DISTINCT와 ALL은 인수가 아니므로 콤마는 붙이지 않음

SELECT DISTINCT COUNT(name)라는 SELECT 명령에서는 COUNT 쪽이 먼저 계산되어버리기 때문에 불가능

WHERE 구에서는 검색할 조건을 지정하는 것밖에 할 수 없으며, 중복된 값인지 아닌지 알아보는 함수도 없어서 불가능

 

 

🎯COUNT 이외의 집계함수

SUM 집계함수: 집합의 합계 계산

 SELECT SUM(quantity) FROM sample51;

+---------------+
| SUM(quantity) |
+---------------+
|            16 |
+---------------+
1 row in set (0.01 sec)

SUM 집계함수에 지정되는 집합은 수치형 뿐. 문자열형이나 날짜시간형의 집합에서 합계 계산 불가능

COUNT와 마찬가지로 NULL값 무시(NULL값 제거한 뒤 합계를 계산)

 

AVG 집계함수: 집합의 평균 계산

SELECT AVG(quantity), SUM(quantity)/COUNT(quantity) FROM sample51;

+---------------+-------------------------------+
| AVG(quantity) | SUM(quantity)/COUNT(quantity) |
+---------------+-------------------------------+
|        4.0000 |                        4.0000 |
+---------------+-------------------------------+
1 row in set (0.01 sec)

SUM 집계함수와 COUNT 집계함수를 사용하여 평균값을 구하는 것도 가능

AVG 집계함수에 주어지는 집합은 SUM과 동일하게 수치형만 가능

AVG 집계함수 또한 NULL값 무시(NULL 값 제거한 뒤 평균값 계산)

 

SELECT AVG(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) 
AS avgnull0 FROM sample51;

+----------+
| avgnull0 |
+----------+
|   3.2000 |
+----------+
1 row in set (0.00 sec)

만약 NULL을 0으로 간주해서 평균을 내고자 한다면, CASE를 사용해 NULL을 0으로 변환한 뒤 AVG 함수로 계산 가능

 

MIN, MAX: 최솟값&최댓값 구하기

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.00 sec)

문자열형과 날짜시간형에도 사용 가능

NULL 값을 무시하는 기본 규칙은 다른 집계함수와 동일

 

 

🎯그룹화 - GROUP BY

GROUP BY: SELECT * FROM 테이블명 GROUP BY 열1, 열2, ...

• GROUP BY 구를 사용해 집계함수로 넘겨줄 집합을 그룹으로 나눔
• 그룹화를 통해 집계함수의 활용 범위 넓힘

SELECT name FROM sample51 GROUP BY name;

+------+
| name |
+------+
| A    |
| B    |
| C    |
| NULL |
+------+
4 rows in set (0.01 sec)

name 열에서 같은 값을 가진 행끼리 묶어 그룹화한 집합을 집계함수로 넘겨줄 수 있음

지정된 열에서 값이 같으면 하나의 그룹으로 묶임

위의 경우 SELECT 구에서 name 열을 지정하였으므로 그룹화된 name 열의 데이터가 클라이언트로 반환됨

 

각 그룹으로 묶인 값들은 서로 동일, 따라서 GROUP BY를 지정해 그룹화하면 DISTINCT와 같이 중복 제거 효과가 존재

단 DISTINCT와 달리 GROUP BY 구를 지정하는 경우 집계함수와 함께 사용하지 않으면 큰 의미가 없음. GROUP BY 구로 그룹화된 각각의 그룹이 하나의 집합으로서 집계함수의 인수로 넘겨지기 때문.

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.01 sec)

GROUP BY name에 의해 name 열 값이 A, B, C 그리고 NULL의 네 개 그룹으로 나뉨

A 그룹에는 두 개의 행이 있어 COUNT는 2가 됨(행의 개수 반환)

 

실무에서 GROUP BY가 사용되는 경우

• 점포별 매출실적 집계 
• 점포별, 상품별, 월별, 일별 등 특정 단위로 집계할 때 GROUP BY를 자주 사용
• SUM 집계함수로 합계를, COUNT로 건수를 집계하는 경우도 존재

SELECT name, COUNT(name) FROM sample51 
 WHERE COUNT(name)=1 GROUP BY name;

ERROR 1111 (HY000): Invalid use of group function

WHERE 구로 행을 검색하는 처리가 GROUP BY로 그룹화하는 처리보다 순서상 앞서기 때문에, WHERE 구에서는 그룹화가 필요한 집계함수를 사용할 수 없음.

집계한 결과에서 조건에 맞는 값을 따로 걸러낼 때는 SELECT 명령의 HAVING 구를 사용.

• GROUP BY 구의 뒤에 기술, WHERE 구와 동일하게 조건식 지정 가능
• 조건식에는 그룹별로 집계된 열의 값이나 집계함수의 계산결과가 전달됨
• 조건식이 참인 그룹값만 클라이언트에게 반환
• WHERE 구와 HAVING 구의 2단 구조(WHER로 검색 >> 검색한 뒤 그룹화 >> HAVING 구로 조건 지정)

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)

 

 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.00 sec)

HAVING 구로 name 개수가 1인 행들만 검색

집계함수를 사용할 경우 HAVING 구로 검색조건 지정

 

그룹화보다 나중에 처리되는 ORDER BY 구에서는 문제없이 집계함수 사용 가능. ORDER BY COUNT(name)과 같이 지정 가능

 

내부 처리 순서를 정리하자면

1.WHERE 구 >> 2.GROUP BY 구 >> 3.HAVING 구 >> 4.SELECT 구 >> 5.ORDER 구

 

다만 SELECT 구보다도 먼저 처리되므로 별명을 사용할 수는 없음. COUNT(SELECT 구)에 별명을 붙이면 ORDER BY 구에서는 사용 가능하지만 GROUP BY 구나 HAVING 구에서는 사용 불가.

 

GROUP BY에 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT 구에 기술해는 안됨.

SELECT no, name, quantity FROM sample51 GROUP BY name;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'sample.sample51.no' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

name은 GROUP BY에서 지정하므로 괜찮지만 no, quantity의 경우 지정 불가.

 

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

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)

집계함수를 사용하면 복수열의 그룹화 가능

단, GROUP BY에서 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT 구에 지정할 수 없음

 

SELECT no, quantity FROM sample51 GROUP BY no, quantity;

+------+----------+
| no   | quantity |
+------+----------+
|    1 |        1 |
|    2 |        2 |
|    3 |       10 |
|    4 |        3 |
|    5 |     NULL |
+------+----------+
5 rows in set (0.00 sec)

no, quantity로 그룹화한다면 GROUP BY no, quantity로 지정

GROUP BY에서 지정한 열이라면 SELECT 구에 그대로 지정 가능

 

 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.01 sec)

ORDER BY 구를 사용해 결과 정렬

합계를구한 뒤 분석할 때 값이 큰 순서대로 정렬

기본값이 ASC이므로 DESC를 따로 지정해야 내림차순 정렬 수행