실습환경

• AWS RDS (database-1) zerobase 에 접속

mysql -h "엔드포인트" -P 3306 -u zero -p zerobase

Aggregate Functions (집계함수)

• 여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수
  

(1) COUNT

• 총 갯수를 계산해 주는 함수

# 문법
select count(column)
from tablename
where condition;

예제

1. police_station 테이블에서 데이터는 모두 몇 개?

select count(*) from police_station;

2. crime_status 테이블에서 경찰서는 총 몇군데?

select count(distinct police_station)
from crime_status;

3. crime_type 은 총 몇 가지?

select count(distinct crime_type)
from crime_status;

(2) SUM

• 숫자 칼럼의 합계를 계산해주는 함수

# 문법
select sum(column)
from tablename
where condition;

예제

1. 범죄 총 발생건수는?

select sum(case_number)
from crime_status
where status_type = '발생';

2. 살인의 총 발생건수는?

select sum(case_number)
from crime_status
where status_type = '발생' and crime_type like '살인';

3. 중부 경찰서에서 검거된 총 범죄 건수는?

select sum(case_number)
from crime_status
where status_type = '검거' and police_station = '중부';

(3) AVG

• 숫자 칼럼의 평균을 계산해주는 함수

# 문법
select avg(column)
from tablename
where condition;

예제

1. 평균 폭력 검거 건수는?

select avg(case_number)
from crime_status
where crime_type like '폭력' and status_type = '검거';

2. 중부경찰서 범죄 평균 발생 건수

select avg(case_number)
from crime_status
where police_station like '중부' and status_type like '발생';

(4) MIN

• 숫자 칼럼 중 가장 작은 값을 찾아주는 함수

# 문법
select min(column)
from tablename
where condition;

예제

1. 강도 발생 건수가 가장 적은 경우 몇 건?

select min(case_number)
from crime_status
where crime_type like '강도' and status_type = '발생';

2. 중부경찰서에서 가장 낮은 검거 건수는?

select min(case_number)
from crime_status
where police_station like '중부' and status_type like '검거';

(5) MAX

• 숫자 칼럼 중 가장 큰 값을 찾아주는 함수

# 문법
select max(column)
from tablename
where condition;

예제

1. 살인이 가장 많이 검거된 건수는?

select max(case_number)
from crime_status
where crime_type like '살인' and status_type like '검거';

2. 강남 경찰서에서 가장 많이 발생한 범죄 건수는?

select max(case_number)
from crime_status
where police_station like '강남' and status_type like '발생';

(6) GROUP BY

• 그룹화하여 데이터를 조회

# 문법
select column1, column2, ...
from tablename
where condition
group by column1, column2, ...
order by column1, column2, ...;

예제

1. crime_status에서 경찰서별로 그룹화 하여 경찰서 이름을 조회

select police_station
from crime_status
group by police_station
order by police_station
limit 5;

2. 경찰서 종류를 검색 - DISTINCT를 사용하는 경우 (ORDER BY 를 사용할 수 없음)

select distinct police_station
from crime_status
limit 5;

3. 경찰서 별 총 발생 범죄 건수 검색

select police_station, sum(case_number) as 발생건수
from crime_status
where status_type like '발생'
group by police_station
order by 발생건수 desc
limit 5;

4. 경찰서 별 평균 범죄 검거 건수 검색

select police_station, avg(case_number) as 평균검거건수
from crime_status
where status_type like '검거'
group by police_station
order by 평균검거건수 desc
limit 5;

5. 경찰서 별 평균 범죄 발생건수와 평균 범죄 검거 건수 검색

select police_station, status_type, avg(case_number)
from crime_status
group by police_station, status_type
limit 6;

(7) HAVING

• 조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용

# 문법
select column1, column2, ...
from tablename
where condition
group by column1, column2, ...
having condition (aggregate functions)
order by column1, column2, ...;

예제

1. 경찰서 별로 발생한 범죄 건수의 합이 4000 건보다 보다 큰 경우 검색

select police_station, sum(case_number) as count
from crime_status
where status_type like '발생'
group by police_station
having count > 4000;

2. 경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우 검색

select police_station, avg(case_number)
from crime_status
where (crime_type like '폭력' or crime_type like '절도')
	and status_type like '발생'
group by police_station
having avg(case_number) >= 2000;

문제풀이

1. police_station에서 경찰서는 총 몇개이고, 각각 경찰서 이름음 무엇인지 확인하세요.

select count(distinct name) from police_station;
select distinct name from police_station;

2. crime_status에서 status_type은 총 몇개이고, 각각 타입은 무엇인지 확인하세요.

select count(distinct status_type) from crime_status;
select distinct status_type from crime_status;

3. 종로경찰서와 남대문경찰서의 강도 발생 건수의 합을 구하세요.

select sum(case_number)
from crime_status
where (police_station like '종로' or police_station like '남대문')
	and crime_type = '강도';

4. 폭력 범죄의 검거 건수의 합을 구하세요.

select sum(case_number)
from crime_status
where crime_type like '폭력' and status_type = '검거';

5. 살인의 평균 발생 건수를 검색하고 확인하세요.

select avg(case_number)
from crime_status
where crime_type like '살인' and status_type like '발생';

6. 서초경찰서의 범죄 별 평균 검거 건수를 검색하고 확인하세요.

select avg(case_number)
from crime_status
where police_station like '서초' and status_type like '검거';

7. 구로경찰서와 도봉경찰서의 평균 살인 검거 건수를 검색하고 확인하세요.

select avg(case_number)
from crime_status
where (police_station like '구로' or police_station like '도봉')
	and (crime_type like '살인' and status_type like '검거');

8. 광진경찰서에서 가장 낮은 범죄 검거 건수를 검색하고 확인하세요.

select min(case_number)
from crime_status
where police_station like '광진' and status_type like '검거';

9. 성북경찰서에서 가장 낮은 범죄 발생 건수를 검색하고 확인하세요.

select min(case_number)
from crime_status
where police_station like '성북' and status_type like '발생';

10. 영등포경찰서의 가장 높은 범죄 발생 건수를 검색하고 확인하세요.

select max(case_number)
from crime_status
where police_station like '영등포' and status_type like '발생';

11. 절도 검거가 가장 많은 건수를 검색하고 확인하세요.

select max(case_number)
from crime_status
where crime_type like '절도' and status_type like '검거';

12. 경찰서 별로 절도 범죄 평균 발생 건수를 가장 많은 건수 순으로 10개 검색하고 확인하세요.

select police_station, avg(case_number) as 평균발생건수
from crime_status
where crime_type like '절도' and status_type like '발생'
group by police_station
order by 평균발생건수 desc
limit 10;

13. 경찰서 별로 가장 많이 검거한 범죄 건수를 가장 적은 건수 순으로 5개 검색하세요.

select police_station, max(case_number) as 최대검거건수
from crime_status
where status_type like '검거'
group by police_station
order by 최대검거건수
limit 5;

14. 경찰서 별로 가장 적게 검거한 건수 중 4건보다 큰 경우를 건수가 큰 순으로 정렬하여 검색하세요.

select police_station, min(case_number) as 최소검거건수
from crime_status
where status_type like '검거'
group by police_station
having 최소검거건수 > 4
order by 최소검거건수 desc;

15. '대문'으로 끝나는 이름의 경찰서 별 범죄발생 건수의 평균이 500건 이상인 경우를 검색하세요.

select police_station, avg(case_number) as 평균발생건수
from crime_status
where police_station like '%대문' and status_type like '발생'
group by police_station
having 평균발생건수 >= 500;