[SQL] 집계함수

채록·2021년 3월 19일
0

Database

목록 보기
4/11
post-thumbnail

SQL 는 어떤 언어?
SQL은 '데이터베이스'라 불리는 "데이터 집합"을 다루는 언어이다!

추가 팁!!!

SQL문에서 명령어 처리 순서는 다음과 같다
WHERE 구 => GROUP BY 구 => HAVING 구 => SELECT 구 => ORDER BY구



I. 집계 함수

집계함수 ?
일반적인 함수는 인수로 하나의 값을 지정하는데 비해 집계함수는 인수로 집합을 지정한다.



1. COUNT


1) 행 갯수 구하기

mysql> SELECT * FROM users;
+----+-----------------+----------+
| id | email           | password |
+----+-----------------+----------+
|  1 | a@gmail.com     | 1234     |
|  2 | hello@gmail.com | 777      |
|  3 | c@gmail.com     | 1234     |
|  4 | d@gmail.com     | 1234     |
+----+-----------------+----------+

mysql> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+

Null COUNT ?
NULL은 COUNT되지 않는다!
=> 5개 값 중 NULL이 1개라면 COUNT 결과 4가 나온다



2) 특정 값을 갖는 행의 갯수 파악하기 (WHERE구 사용)

mysql> SELECT * FROM users;
+----+-----------------+----------+
| id | email           | password |
+----+-----------------+----------+
|  1 | a@gmail.com     | 1234     |
|  2 | hello@gmail.com | 777      |
|  3 | c@gmail.com     | 1234     |
|  4 | d@gmail.com     | 1234     |
+----+-----------------+----------+

mysql> SELECT COUNT(password=1234) FROM users WHERE password='1234';
+----------------------+
| COUNT(password=1234) |
+----------------------+
|                    3 |
+----------------------+

COUNT의 이름 ?
COUNT뒤의 () 안에 입력하는 값은 내가 지정해 주는 값이다



3) DISTINCT : 중복 제거

mysql> SELECT password FROM users;
+----------+
| password |
+----------+
| 1234     |
| 777      |
| 1234     |
| 1234     |
+----------+

mysql> SELECT DISTINCT password FROM users;
+----------+
| password |
+----------+
| 1234     |
| 777      |
+----------+

DISTINCT 의 위치 ?
DISTINCT를 적용할 field 이름 앞에 붙여야 한다!
SELECT password DISTINCT X
SELECT DISTINCT password O



4) COUNT + DISTINCT

COUNT와 DISTINCT 처리순서 ?

=>COUNT쪽이 먼저 계산된다!
mysql> SELECT * FROM nonusers;
+----------+-------------+----------------+------------+
| ‘id’     | ‘email’     | ‘password’     | ‘name’     |
+----------+-------------+----------------+------------+
|        1 | b           | NULL           | 1          |
|        2 | c           | 111            | 1          |
|        3 | d           | 111            | 2          |
|        4 | e           | 222            | 3          |
+----------+-------------+----------------+------------+

mysql> SELECT COUNT(ALL ‘password’), COUNT(DISTINCT ‘password’) FROM nonusers;
+---------------------------+--------------------------------+
| COUNT(ALL ‘password’)     | COUNT(DISTINCT ‘password’)     |
+---------------------------+--------------------------------+
|                         3 |                              2 |
+---------------------------+--------------------------------+

4개의 password값 중 NULL인 값이 하나이고 111인 행이 3개이므로 COUNT에서 NULL이 제외되고, DISTINCT를 통해 중복을 제외하니 2개의 값이 나오게 되었다.

COUNT 이름 ?
위에서 내가 지정한 대로 설정된다고 하였는데 DISTINCT가 그대로 적용되는 모습을 참고하자!
=> 그리고 여기서 사용된 ALL 과 DISTINCT는 인수가 아니라 예약어 이다. 콤마를 붙이지 않는다!



2. SUM / AVG / MIN / MAX

요약

SUM ([ALL|DISTINCT] 집합)
AVG ([ALL|DISTINCT] 집합)
MIN ([ALL|DISTINCT] 집합)
MAX ([ALL|DISTINCT] 집합)

1) SUM / AVG

사용하기!!

  • 수치형만 사용 가능하다 => 문자열형/날짜시간형 X
  • NULL은 무시한다 => AVG에서 NULL=0 취급을 하고 싶다면 변환해주어야 한다.
SELECT AVE(CASE WHEN num is NULL THEN 0 ELSE num END)
  AS numavg FROM products;

2) MIN / MAX

사용하기!!

  • NULL은 무시한다
  • 문자열형과 날짜시간형에도 사용 가능하다
SELECT MIN(num), MAX(num), MIN(price), MAX(price) FROM products;
+----------+-------------+----------------+----+
| MIN(num) | MAX(num) | MIN(price) | MAX(price) |
+----------+-------------+----------------+----+
|        1 | 10       | 500        | 10000      |
+----------+-------------+----------------+----+



II. Group by

그룹화를 통해 집계함수의 활용 범위를 넓힐 수 있다.

test용 table (nonusers)

mysql> SELECT * FROM nonusers;
+----------+-------------+----------------+------------+
| ‘id’     | ‘email’     | ‘password’     | ‘name’     |
+----------+-------------+----------------+------------+
|        1 | b           | NULL           | 1          |
|        2 | c           | 111            | 1          |
|        3 | d           | 111            | 2          |
|        4 | e           | 222            | 3          |
|        5 | f           | 110            | 2          |
+----------+-------------+----------------+------------+

1) 하나의 열 GROUP BY

password

mysql> SELECT ‘password’ FROM nonusers GROUP BY ‘password’;
+----------------+
| ‘password’     |
+----------------+
| NULL           |
| 111            |
| 222            |
| 110            |
+----------------+

name

mysql> SELECT ‘name’ FROM nonusers GROUP BY ‘name’;
+------------+
| ‘name’     |
+------------+
| 1          |
| 2          |
| 3          |
+------------+

+) 두개의 열 GROUP BY (안됨)

mysql> SELECT ‘password’, ‘name’ FROM nonusers GROUP BY ‘password’, ‘name’;
+----------------+------------+
| ‘password’     | ‘name’     |
+----------------+------------+
| NULL           | 1          |
| 111            | 1          |
| 111            | 2          |
| 222            | 3          |
| 110            | 2          |
+----------------+------------+

DISTINCT vs GROUP BY
집계함수를 사용할때 GROUP BY의 진가가 드러난다!
=> 그룹화된 각각의 그룹이 하나의 집합으로서 집계함수의 인수로 넘겨질 수 있다.


2) 집계함수 + GROUP BY

mysql> select * from products;
+----+--------+-------+----------+
| id | name   | price | quantity |
+----+--------+-------+----------+
|  1 | book   |  1000 |        3 |
|  2 | pencil |   500 |       10 |
|  3 | cd     |  3000 |      100 |
|  4 | book   |  2000 |        5 |
+----+--------+-------+----------+

mysql> select name, COUNT(name), SUM(quantity) from products GROUP BY name;
+--------+-------------+---------------+
| name   | COUNT(name) | SUM(quantity) |
+--------+-------------+---------------+
| book   |           2 |             8 |
| pencil |           1 |            10 |
| cd     |           1 |           100 |
+--------+-------------+---------------+

이해가 잘 안되는 부분!!!!!
SELECT구문에서 사용된 field는 'name'이고, GROUP BY에 사용된 field도 'name'이다.
이에 따라서 name에 관련된 값만 table에 나오는데.. 이의 id를 찾고 싶어 추가하면

mysql> select id, name, COUNT(name), SUM(quantity) from products GROUP BY name;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'brandi_pr.products.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

이런 에러가 뜬다!!

생각해보니 COUNT를 사용함으로써 기존에 두 instance로 존재한 name=book 의 id가 꼬였을 것이다. 이 때문인가??

확인과정

mysql> select id, name, COUNT(name), SUM(quantity) from products GROUP BY id, name;
+----+--------+-------------+---------------+
| id | name   | COUNT(name) | SUM(quantity) |
+----+--------+-------------+---------------+
|  1 | book   |           1 |             3 |
|  2 | pencil |           1 |            10 |
|  3 | cd     |           1 |           100 |
|  4 | book   |           1 |             5 |
+----+--------+-------------+---------------+
mysql> select name, COUNT(name), SUM(quantity) from products GROUP BY name;
+--------+-------------+---------------+
| name   | COUNT(name) | SUM(quantity) |
+--------+-------------+---------------+
| book   |           2 |             8 |
| pencil |           1 |            10 |
| cd     |           1 |           100 |
+--------+-------------+---------------+
mysql> select quantity, COUNT(quantity), SUM(price) from products group by quantity;
+----------+-----------------+------------+
| quantity | COUNT(quantity) | SUM(price) |
+----------+-----------------+------------+
|        3 |               1 |       1000 |
|       10 |               2 |       1300 |
|      100 |               1 |       3000 |
|        5 |               1 |       2000 |
+----------+-----------------+------------+

select 문과 group by문에 id를 추가하니 제대로 GROUP BY 되지 않았다! group by 되지 않는 id 조건때문에 무시되었는거라 생각된다.

해결하기

그렇다면 중복되는 값이 없도록 둘중에 하나만 나오도록 하면된다!!!! 이때 쓰는게 또다시 집계함수

mysql> select min(id), name, COUNT(name), SUM(quantity) from products GROUP BY name;
+---------+--------+-------------+---------------+
| min(id) | name   | COUNT(name) | SUM(quantity) |
+---------+--------+-------------+---------------+
|       1 | book   |           2 |             8 |
|       2 | pencil |           1 |            10 |
|       3 | cd     |           1 |           100 |
|       5 | rular  |           1 |            10 |
+---------+--------+-------------+---------------+

여러개가 나올수 있는 id 중에서 최소값만을 반환하게 했다. 이때 MIN / MAX / SUM / AVG 모두 가능하다. (대신 id 값이니까 제멋대로의 가공은 금지...!)



III. 조건에 맞는 값만 출력하기 (HAVING 구)

HAVING 구 ?
이것을 사용하면 집계함수를 사용해서 조건식을 지정할 수 있다!

1. WHERE구에서는 집계함수를 사용할 수 없다!

mysql> select quantity, COUNT(quantity) from products where COUNT(quantity)=1 group by quantity;
ERROR 1111 (HY000): Invalid use of group function


2. HAVING 구를 사용하자!

mysql> select quantity, COUNT(quantity) from products group by quantity;
+----------+-----------------+
| quantity | COUNT(quantity) |
+----------+-----------------+
|        3 |               1 |
|       10 |               2 |
|      100 |               1 |
|        5 |               1 |
+----------+-----------------+
mysql> select quantity, COUNT(quantity) from products group by quantity having count(quantity)=1;
+----------+-----------------+
| quantity | COUNT(quantity) |
+----------+-----------------+
|        3 |               1 |
|      100 |               1 |
|        5 |               1 |
+----------+-----------------+

HAVING 구 사용 법!

  • HAVING 구는 GROUP BY 구의 뒤에 기술한다.
  • WHERE구와 동일하게 조건식을 지정할 수 있다.
    - 조건식에는 그룹별로 집계된 열의 값이나 집계함수의 계산결과가 전달된다!
  • 이때 조건식이 참인 그룹값만 client에게 반환된다!

+) GROUP BY => ORDER BY 정렬하기 GOOD!!!


mysql> select quantity, COUNT(quantity) from products group by quantity;
+----------+-----------------+
| quantity | COUNT(quantity) |
+----------+-----------------+
|        3 |               1 |
|       10 |               2 |
|      100 |               1 |
|        5 |               1 |
+----------+-----------------+

mysql> select quantity, COUNT(quantity) from products group by quantity order by COUNT(quantity);
+----------+-----------------+
| quantity | COUNT(quantity) |
+----------+-----------------+
|        3 |               1 |
|      100 |               1 |
|        5 |               1 |
|       10 |               2 |
+----------+-----------------+

내림차순 / 오름차순 ?
ORDER BY의 기본값은 오름차순이다 (ASC) 반대로 출력하고 싶으면 DESC(내림차순)으로 지정해 주어야 한다!
ORDER BY COUNT(quantity) DESC

profile
채록
🍎 🍊 🍋 🍏 🍇
이전 포스트

[SQL] : CRUD

다음 포스트

[SQL] 서브쿼리

0개의 댓글