Aggregate Functions
'여러 column' 혹은 'table의 전체 column'으로부터 '하나'의 결과값을 반환하는 함수
아래의 두 table로 실습을 진행해보자.
police_station
mysql> desc police_station;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| address | varchar(128) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from police_station;
+--------------------------+----------------------------------------------------------------------------------+
| name | address |
+--------------------------+----------------------------------------------------------------------------------+
| 서울강남경찰서 | 서울특별시 강남구 테헤란로 114길 11 |
| 서울강동경찰서 | 서울특별시 강동구 성내로 33 |
| 서울강북경찰서 | 서울특별시 강북구 오패산로 406 |
| 서울강서경찰서 | 서울특별시 양천구 화곡로 73 |
| 서울관악경찰서 | 서울특별시 관악구 관악로5길 33 |
| 서울광진경찰서 | 서울특별시 광진구 광나루로 447 광진소방서 임시청사 (능동) |
31 rowscrime_status
mysql> desc crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year | year | YES | | NULL | |
| police_station | varchar(8) | YES | | NULL | |
| crime_type | varchar(16) | YES | | NULL | |
| status_type | char(2) | YES | | NULL | |
| case_number | int | YES | | NULL | |
| reference | varchar(16) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select * from crime_status;
+------+----------------+---------------+-------------+-------------+--------------------------+
| year | police_station | crime_type | status_type | case_number | reference |
+------+----------------+---------------+-------------+-------------+--------------------------+
| 2020 | 중부 | 살인 | 발생 | 1 | 서울중부경찰서 |
| 2020 | 중부 | 살인 | 검거 | 1 | 서울중부경찰서 |
| 2020 | 중부 | 강도 | 발생 | 3 | 서울중부경찰서 |
| 2020 | 중부 | 강도 | 검거 | 4 | 서울중부경찰서 |
| 2020 | 중부 | 강간,추행 | 발생 | 113 | 서울중부경찰서 |
| 2020 | 중부 | 강간,추행 | 검거 | 76 | 서울중부경찰서 |
310 rowsCOUNT
총 갯수를 연산하여 반환해주는 함수
- 문법
select count(column) from table_name where condition;
ex) police_station table에서 경찰서의 수를 조회
mysql> select count(*) from police_station;
+----------+
| count(*) |
+----------+
| 31 |
+----------+
1 row in set (0.00 sec)ex) crime_status table에서 경찰서의 수를 조회(중복 제거)
mysql> select count(distinct police_station) from crime_status;
+--------------------------------+
| count(distinct police_station) |
+--------------------------------+
| 31 |
+--------------------------------+
1 row in set (0.00 sec)SUM
'숫자' column의 합계를 연산하여 반환해주는 함수
- 문법
select sum(column) from table_name where condition;
ex) crime_status table에서 범죄 총 '발생' 건수를 조회
mysql> select sum(case_number)
-> from crime_status
-> where status_type='발생';
+------------------+
| sum(case_number) |
+------------------+
| 92679 |
+------------------+
1 row in set (0.00 sec)ex) crime_status table에서 '살인' 범죄의 '발생' 총 건수를 조회
mysql> select sum(case_number)
-> from crime_status
-> where status_type='발생' and crime_type like '살인';
+------------------+
| sum(case_number) |
+------------------+
| 141 |
+------------------+
1 row in set (0.00 sec)AVG
'숫자' column의 평균을 연산하여 반환해주는 함수
- 문법
select avg(column) from table_name where condition;
ex) crime_status table에서 평균 폭력 검거 건수 조회
mysql> select avg(case_number)
-> from crime_status
-> where crime_type like '폭력' and status_type='검거';
+------------------+
| avg(case_number) |
+------------------+
| 1323.1935 |
+------------------+
1 row in set (0.00 sec)
# 실제 데이터 확인 : 폭력 범죄 중 모든 검거 건수
mysql> select *
-> from crime_status
-> where crime_type like '폭력' and status_type='검거';
+------+----------------+------------+-------------+-------------+--------------------------+
| year | police_station | crime_type | status_type | case_number | reference |
+------+----------------+------------+-------------+-------------+--------------------------+
| 2020 | 중부 | 폭력 | 검거 | 830 | 서울중부경찰서 |
| 2020 | 종로 | 폭력 | 검거 | 792 | 서울종로경찰서 |
| 2020 | 남대문 | 폭력 | 검거 | 494 | 서울남대문경찰서 |
| 2020 | 서대문 | 폭력 | 검거 | 1029 | 서울서대문경찰서 |
| 2020 | 혜화 | 폭력 | 검거 | 628 | 서울혜화경찰서 |
| 2020 | 용산 | 폭력 | 검거 | 1327 | 서울용산경찰서 |
| 2020 | 성북 | 폭력 | 검거 | 610 | 서울성북경찰서 |
| 2020 | 동대문 | 폭력 | 검거 | 1597 | 서울동대문경찰서 |
| 2020 | 마포 | 폭력 | 검거 | 1557 | 서울마포경찰서 |
| 2020 | 영등포 | 폭력 | 검거 | 2157 | 서울영등포경찰서 |
| 2020 | 성동 | 폭력 | 검거 | 1103 | 서울성동경찰서 |
| 2020 | 동작 | 폭력 | 검거 | 1370 | 서울동작경찰서 |
| 2020 | 광진 | 폭력 | 검거 | 1407 | 서울광진경찰서 |
| 2020 | 서부 | 폭력 | 검거 | 667 | 서울서부경찰서 |
| 2020 | 강북 | 폭력 | 검거 | 1625 | 서울강북경찰서 |
| 2020 | 금천 | 폭력 | 검거 | 1310 | 서울금천경찰서 |
| 2020 | 중랑 | 폭력 | 검거 | 1724 | 서울중랑경찰서 |
| 2020 | 강남 | 폭력 | 검거 | 1911 | 서울강남경찰서 |
| 2020 | 관악 | 폭력 | 검거 | 2355 | 서울관악경찰서 |
| 2020 | 강서 | 폭력 | 검거 | 2087 | 서울강서경찰서 |
| 2020 | 강동 | 폭력 | 검거 | 1697 | 서울강동경찰서 |
| 2020 | 종암 | 폭력 | 검거 | 630 | 서울종암경찰서 |
| 2020 | 구로 | 폭력 | 검거 | 1823 | 서울구로경찰서 |
| 2020 | 서초 | 폭력 | 검거 | 1415 | 서울서초경찰서 |
| 2020 | 양천 | 폭력 | 검거 | 1270 | 서울양천경찰서 |
| 2020 | 송파 | 폭력 | 검거 | 2280 | 서울송파경찰서 |
| 2020 | 노원 | 폭력 | 검거 | 1781 | 서울노원경찰서 |
| 2020 | 방배 | 폭력 | 검거 | 365 | 서울방배경찰서 |
| 2020 | 은평 | 폭력 | 검거 | 956 | 서울은평경찰서 |
| 2020 | 도봉 | 폭력 | 검거 | 1022 | 서울도봉경찰서 |
| 2020 | 수서 | 폭력 | 검거 | 1200 | 서울수서경찰서 |
+------+----------------+------------+-------------+-------------+--------------------------+
31 rows in set (0.00 sec)MIN
'숫자' column의 최소값을 찾아 반환해주는 함수
- 문법
select min(column) from table_name where condition;
ex) crime_status table에서 강도 발생 건수가 가장 적은 경우는 몇 건인지 조회
mysql> select min(case_number)
-> from crime_status
-> where crime_type like '강도' and status_type='발생';
+------------------+
| min(case_number) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
# 실제 데이터 확인 : 모든 강도 발생 건수 조회
mysql> select *
-> from crime_status
-> where crime_type like '강도' and status_type='발생';
+------+----------------+------------+-------------+-------------+--------------------------+
| year | police_station | crime_type | status_type | case_number | reference |
+------+----------------+------------+-------------+-------------+--------------------------+
| 2020 | 중부 | 강도 | 발생 | 3 | 서울중부경찰서 |
| 2020 | 종로 | 강도 | 발생 | 4 | 서울종로경찰서 |
| 2020 | 남대문 | 강도 | 발생 | 2 | 서울남대문경찰서 |
| 2020 | 서대문 | 강도 | 발생 | 2 | 서울서대문경찰서 |
| 2020 | 혜화 | 강도 | 발생 | 3 | 서울혜화경찰서 |
| 2020 | 용산 | 강도 | 발생 | 6 | 서울용산경찰서 |
| 2020 | 성북 | 강도 | 발생 | 2 | 서울성북경찰서 |
| 2020 | 동대문 | 강도 | 발생 | 6 | 서울동대문경찰서 |
| 2020 | 마포 | 강도 | 발생 | 4 | 서울마포경찰서 |
| 2020 | 영등포 | 강도 | 발생 | 6 | 서울영등포경찰서 |
| 2020 | 성동 | 강도 | 발생 | 3 | 서울성동경찰서 |
| 2020 | 동작 | 강도 | 발생 | 1 | 서울동작경찰서 | # 최소값
| 2020 | 광진 | 강도 | 발생 | 4 | 서울광진경찰서 |
| 2020 | 서부 | 강도 | 발생 | 3 | 서울서부경찰서 |
| 2020 | 강북 | 강도 | 발생 | 5 | 서울강북경찰서 |
| 2020 | 금천 | 강도 | 발생 | 7 | 서울금천경찰서 |
| 2020 | 중랑 | 강도 | 발생 | 5 | 서울중랑경찰서 |
| 2020 | 강남 | 강도 | 발생 | 12 | 서울강남경찰서 |
| 2020 | 관악 | 강도 | 발생 | 3 | 서울관악경찰서 |
| 2020 | 강서 | 강도 | 발생 | 6 | 서울강서경찰서 |
| 2020 | 강동 | 강도 | 발생 | 15 | 서울강동경찰서 |
| 2020 | 종암 | 강도 | 발생 | 1 | 서울종암경찰서 | # 최소값
| 2020 | 구로 | 강도 | 발생 | 5 | 서울구로경찰서 |
| 2020 | 서초 | 강도 | 발생 | 5 | 서울서초경찰서 |
| 2020 | 양천 | 강도 | 발생 | 3 | 서울양천경찰서 |
| 2020 | 송파 | 강도 | 발생 | 13 | 서울송파경찰서 |
| 2020 | 노원 | 강도 | 발생 | 3 | 서울노원경찰서 |
| 2020 | 방배 | 강도 | 발생 | 1 | 서울방배경찰서 | # 최소값
| 2020 | 은평 | 강도 | 발생 | 1 | 서울은평경찰서 | # 최소값
| 2020 | 도봉 | 강도 | 발생 | 2 | 서울도봉경찰서 |
| 2020 | 수서 | 강도 | 발생 | 2 | 서울수서경찰서 |
+------+----------------+------------+-------------+-------------+--------------------------+
31 rows in set (0.00 sec)MAX
'숫자' column의 최대값을 찾아 반환해주는 함수
- 문법
select max(column) from table_name where condition;
ex) 살인이 가장 많이 검거된 건수 조회
mysql> select max(case_number)
-> from crime_status
-> where crime_type like '살인' and status_type like '검거';
+------------------+
| max(case_number) |
+------------------+
| 12 |
+------------------+
1 row in set (0.00 sec)
# 실제 데이터 확인 : 모든 살인 검거 건수 조회
mysql> select *
-> from crime_status
-> where crime_type like '살인' and status_type like '검거';
+------+----------------+------------+-------------+-------------+--------------------------+
| year | police_station | crime_type | status_type | case_number | reference |
+------+----------------+------------+-------------+-------------+--------------------------+
| 2020 | 중부 | 살인 | 검거 | 1 | 서울중부경찰서 |
| 2020 | 종로 | 살인 | 검거 | 6 | 서울종로경찰서 |
| 2020 | 남대문 | 살인 | 검거 | 1 | 서울남대문경찰서 |
| 2020 | 서대문 | 살인 | 검거 | 3 | 서울서대문경찰서 |
| 2020 | 혜화 | 살인 | 검거 | 0 | 서울혜화경찰서 |
| 2020 | 용산 | 살인 | 검거 | 3 | 서울용산경찰서 |
| 2020 | 성북 | 살인 | 검거 | 0 | 서울성북경찰서 |
| 2020 | 동대문 | 살인 | 검거 | 8 | 서울동대문경찰서 |
| 2020 | 마포 | 살인 | 검거 | 8 | 서울마포경찰서 |
| 2020 | 영등포 | 살인 | 검거 | 4 | 서울영등포경찰서 |
| 2020 | 성동 | 살인 | 검거 | 5 | 서울성동경찰서 |
| 2020 | 동작 | 살인 | 검거 | 1 | 서울동작경찰서 |
| 2020 | 광진 | 살인 | 검거 | 2 | 서울광진경찰서 |
| 2020 | 서부 | 살인 | 검거 | 3 | 서울서부경찰서 |
| 2020 | 강북 | 살인 | 검거 | 8 | 서울강북경찰서 |
| 2020 | 금천 | 살인 | 검거 | 4 | 서울금천경찰서 |
| 2020 | 중랑 | 살인 | 검거 | 9 | 서울중랑경찰서 |
| 2020 | 강남 | 살인 | 검거 | 3 | 서울강남경찰서 |
| 2020 | 관악 | 살인 | 검거 | 12 | 서울관악경찰서 | # 최대값
| 2020 | 강서 | 살인 | 검거 | 9 | 서울강서경찰서 |
| 2020 | 강동 | 살인 | 검거 | 3 | 서울강동경찰서 |
| 2020 | 종암 | 살인 | 검거 | 4 | 서울종암경찰서 |
| 2020 | 구로 | 살인 | 검거 | 6 | 서울구로경찰서 |
| 2020 | 서초 | 살인 | 검거 | 4 | 서울서초경찰서 |
| 2020 | 양천 | 살인 | 검거 | 5 | 서울양천경찰서 |
| 2020 | 송파 | 살인 | 검거 | 6 | 서울송파경찰서 |
| 2020 | 노원 | 살인 | 검거 | 3 | 서울노원경찰서 |
| 2020 | 방배 | 살인 | 검거 | 1 | 서울방배경찰서 |
| 2020 | 은평 | 살인 | 검거 | 1 | 서울은평경찰서 |
| 2020 | 도봉 | 살인 | 검거 | 0 | 서울도봉경찰서 |
| 2020 | 수서 | 살인 | 검거 | 2 | 서울수서경찰서 |
+------+----------------+------------+-------------+-------------+--------------------------+
31 rows in set (0.00 sec)GROUP BY
데이터를 그룹화(1개 이상의 그룹 집계)하여 데이터를 조회할 때 사용
group by를 진행할 때 자주 집계함수를 활용하여 사용된다.
- 문법
select column1, column2, ...
from table_name
where condition
group by column1, column2, ...
order by column1, column2, ...
ex) crime_status table에서 경찰서별로 그룹화하여 경찰서 이름을 5개 조회
mysql> select police_station
-> from crime_status
-> group by police_station
-> order by police_station
-> limit 5;
+----------------+
| police_station |
+----------------+
| 강남 |
| 강동 |
| 강북 |
| 강서 |
| 관악 |
+----------------+
5 rows in set (0.00 sec)ex) 경찰서별 총 발생 범죄 건수를 5개 조회
mysql> select police_station, sum(case_number) 발생건수
-> from crime_status
-> where status_type like '발생'
-> group by police_station
-> order by police_station
-> limit 5;
+----------------+--------------+
| police_station | 발생건수 |
+----------------+--------------+
| 강남 | 4754 |
| 강동 | 3788 |
| 강북 | 2770 |
| 강서 | 4415 |
| 관악 | 5261 |
+----------------+--------------+
5 rows in set (0.00 sec)ex) 경찰서별 범죄 평균 '발생'건수와 '검거'건수를 경찰서 이름순으로 3개를 조회
mysql> select police_station, status_type, avg(case_number)
-> from crime_status
-> group by police_station, status_type
-> order by police_station
-> limit 6;
+----------------+-------------+------------------+
| police_station | status_type | avg(case_number) |
+----------------+-------------+------------------+
| 강남 | 검거 | 674.6000 |
| 강남 | 발생 | 950.8000 |
| 강동 | 검거 | 532.4000 |
| 강동 | 발생 | 757.6000 |
| 강북 | 검거 | 451.8000 |
| 강북 | 발생 | 554.0000 |
+----------------+-------------+------------------+
6 rows in set (0.00 sec)HAVING
조건에 집계함수가 포함되는 경우 where 구문에서는 집계함수를 사용할 수 없기 때문에, having 구문에서 집계함수 사용
- 문법
select column1, column2, ...
from table_name
where condition
group by column1, column2, ...
having condition aggregate_functions
order by column1, ...
ex) 경찰서별 '발생'한 범죄 건수의 합이 4,000건보다 큰 경우를 조회
mysql> select police_station, sum(case_number) count
-> from crime_status
-> where status_type like '발생'
-> group by police_station
-> having count > 4000;
+----------------+-------+
| police_station | count |
+----------------+-------+
| 영등포 | 5217 |
| 강남 | 4754 |
| 관악 | 5261 |
| 강서 | 4415 |
| 구로 | 4175 |
| 송파 | 5410 |
+----------------+-------+
6 rows in set (0.00 sec)영등포에서 발생한 건수가 5217인지 재확인해보자.
mysql> select sum(case_number)
-> from crime_status
-> where status_type like '발생' and police_station like '영등포';
+------------------+
| sum(case_number) |
+------------------+
| 5217 |
+------------------+
1 row in set (0.00 sec)ex) 경찰서별 '발생'한 '폭력'과 '절도'의 범죄 건수 평균이 2,000건 이상인 경우 조회
mysql> select police_station, avg(case_number) avg
-> from crime_status
-> where (crime_type like '폭력' or crime_type like '절도') and status_type like '발생'
-> group by police_station
-> having avg >= 2000;
+----------------+-----------+
| police_station | avg |
+----------------+-----------+
| 영등포 | 2444.5000 |
| 강남 | 2112.0000 |
| 관악 | 2421.5000 |
| 강서 | 2067.0000 |
| 송파 | 2552.0000 |
+----------------+-----------+
5 rows in set (0.00 sec)영등포에서 발생한 건수 평균이 2444.5인지 재확인해보자.
mysql> select avg(case_number)
-> from crime_status
-> where (crime_type like '폭력' or crime_type like '절도') and status_type like '발생' and police_station like '영등포';
+------------------+
| avg(case_number) |
+------------------+
| 2444.5000 |
+------------------+
1 row in set (0.00 sec)
데이터 굽는 타자기