[SQL] Aggregate Functions (집계함수)

허재훈·2023년 4월 28일
0

SQL

목록 보기
14/17
post-thumbnail
post-custom-banner

1. 실습환경 만들기

실습할 데이터베이스로 이동

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

% mysql -h database-1.dddddd-2.rdddddd -P 3306 -u
zero -p zerobase

실습할 데이터 확인

  • police_station 데이터 확인 (31 rows)
select * from police_station;
select * from police_station limit 3;

  • crime_status 데이터 확인 (310 rows)
select * from crime_status;
select * from crime_status limit 3;

2. Aggregate Functions (집계함수)

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

3. COUNT

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

  • 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;

# COUNT 없이 해보자
SELECT DISTINCT crime_type FROM crime_status;

4. SUM

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

  • 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 = '중부';

혼자서 해봅시다(COUNT, SUM)

  • 문제 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 COUNT(status_type) FROM crime_status;
SELECT DISTINCT status_type FROM crime_status;

  • 문제 3. 종로경찰서와 남대문경찰서의 강도 발생 건수의 합을 구하세요.
# 아래는 모든 범죄 발생건수
SELECT SUM(case_number) 
FROM crime_status 
WHERE police_station LIKE '종로'   # 이렇게 해도 되고 LIKE
      OR police_station = '남대문';  # 이렇게 해도 됨 =

# 아래는 강도 범죄만 
SELECT SUM(case_number) 
FROM crime_status 
WHERE crime_type = '강도'
      AND (police_station LIKE '종로' # 이렇게 해도 되고 LIKE
      OR police_station = '남대문');  # 이렇게 해도 됨 =

  • 문제 4. 폭력 범죄의 검거 건수의 합을 구하세요.
SELECT SUM(case_number) 
FROM crime_status 
WHERE crime_type = '폭력' AND status_type = '검거';

5. AVG

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

  • AVG 문법

SELECT AVG(column)
FROM tablename
WHERE condition;
  • 예제 1. 평균 폭력 검거 건수는?
SELECT AVG(case_number)
FROM crime_status
WHERE crime_type LIKE '폭력' AND status_type = '검거';

  • 확인
SELECT police_station, crime_type, status_type, 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 '발생';

  • 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE police_station LIKE '중부' AND status_type LIKE '발생';

6. MIN

  • 숫자 칼럼 중 가장 작은 값을 찾아주는 함수
  • MIN 문법
SELECT MIN(column)
FROM tablename
WHERE condition;
  • 예제 1. 강도 발생 건수가 가장 적은 경우 몇 건?
SELECT MIN(case_number)
FROM crime_status
WHERE crime_type LIKE '강도' AND status_type = '발생';

  • 확인
SELECT police_station, crime_type, status_type, 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 '검거';

  • 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE police_station LIKE '중부' AND status_type LIKE '검거';

7. MAX

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

  • MAX 문법

SELECT MAX(column)
FROM tablename
WHERE condition;
  • 예제 1. 살인이 가장 많이 검거된 건수는?
SELECT MAX(case_number)
FROM crime_status
WHERE crime_type LIKE '살인' AND status_type = '검거';

  • 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE crime_type LIKE '살인' AND status_type = '검거';

  • 예제 2. 강남 경찰서에서 가장 많이 발생한 범죄 건수는?
SELECT MAX(case_number)
FROM crime_status
WHERE police_station LIKE '강남' AND status_type LIKE '발생';

  • 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE police_station LIKE '강남' AND status_type LIKE '발생';

혼자서 해봅시다(AVG, MIN, MAX)

  • 문제 1. 살인의 평균 발생 건수를 검색하고 확인하세요.
SELECT AVG(case_number)
FROM crime_status
WHERE crime_type LIKE '살인' AND status_type = '발생';
SELECT *
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 = '검거';
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE police_station LIKE '서초' AND status_type = '검거';

  • 문제 3. 구로경찰서와 도봉경찰서의 평균 살인 검거 건수를 검색하고 확인하세요.
SELECT AVG(case_number)
FROM crime_status
WHERE (police_station LIKE '구로' OR police_station LIKE '도봉') 
      AND crime_type LIKE '살인' AND status_type = '검거';
SELECT *
FROM crime_status
WHERE (police_station LIKE '구로' OR police_station LIKE '도봉') 
      AND crime_type LIKE '살인' AND status_type = '검거';

  • 문제 4. 광진경찰서에서 가장 낮은 범죄 검거 건수를 검색하고 확인하세요.
SELECT MIN(case_number)
FROM crime_status
WHERE police_station LIKE '광진' AND status_type = '검거';
SELECT *
FROM crime_status
WHERE police_station LIKE '광진' AND status_type = '검거';

  • 문제 5. 성북경찰서에서 가장 낮은 범죄 발생 건수를 검색하고 확인하세요.
SELECT MIN(case_number)
FROM crime_status
WHERE police_station LIKE '성북' AND status_type = '발생';
SELECT *
FROM crime_status
WHERE police_station LIKE '성북' AND status_type = '발생';

  • 문제 6. 영등포경찰서의 가장 높은 범죄 발생 건수를 검색하고 확인하세요.
SELECT MAX(case_number)
FROM crime_status
WHERE police_station LIKE '영등포' AND status_type = '발생';
SELECT *
FROM crime_status
WHERE police_station LIKE '영등포' AND status_type = '발생';

  • 문제 7. 절도 검거가 가장 많은 건수를 검색하고 확인하세요.
SELECT MAX(case_number)
FROM crime_status
WHERE crime_type LIKE '절도' AND status_type = '검거';
SELECT *
FROM crime_status
WHERE crime_type LIKE '절도' AND status_type = '검거';

8. GROUP BY

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

  • GROUP BY 문법

  • GROUP BY 와 ORDER BY에 쓰인 컬럼이 SELECT에 무조건 있어야 함.
SELECT column1, column2, ...
FROM table
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;

  • 경찰서 종류를 검색 - DISTINCT 를 사용하는 경우 (ORDER BY 를 사용할 수 없음)
SELECT DISTINCT police_station
FROM crime_status
LIMIT 5;

  • 예제 2. 경찰서 별로 총 발생 범죄 건수를 검색
SELECT police_station, sum(case_number) 발생건수
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station
ORDER BY 발생건수 DESC
LIMIT 5;

  • 예제 3. 경찰서 별로 평균 범죄 검거 건수를 검색
SELECT police_station, avg(case_number) 평균검거건수
FROM crime_status
WHERE status_type LIKE '검거'
GROUP BY police_station
ORDER BY 평균검거건수 DESC
LIMIT 5;

  • 예제 4. 경찰서 별 평균 범죄 발생건수와 평균 범죄 검거 건수를 검색
SELECT police_station, status_type, avg(case_number)
FROM crime_status
GROUP BY police_station, status_type
LIMIT 6;

9. HAVING

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

  • HAVING 문법

  • GROUP BY 와 ORDER BY에 쓰인 컬럼이 SELECT에 무조건 있어야 함.
SELECT column1, column2, ...
FROM table
WHERE condition
GROUP BY column1, column2, ...
HAVING condition (Aggregate Functions)
ORDER BY column1, column2, ...;
  • 예제 1. 경찰서 별로 발생한 범죄 건수의 합이 4000 건보다 보다 큰 경우를 검색
SELECT police_station, sum(case_number) count
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station
HAVING count > 4000;

  • 확인
SELECT sum(case_number)
FROM crime_status
WHERE status_type LIKE '발생' AND police_station LIKE '영등포';

  • 예제 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
SELECT AVG(case_number)
FROM crime_status
WHERE (crime_type LIKE '폭력' OR crime_type LIKE '절도')
 AND status_type LIKE '발생' AND police_station LIKE '영등포';

  • 확인 2
SELECT *
FROM crime_status
WHERE (crime_type LIKE '폭력' OR crime_type LIKE '절도')
 AND status_type LIKE '발생' AND police_station LIKE '영등포';

혼자서 해봅시다(GROUP BY, HAVING)

  • 문제 1. 경찰서 별로 절도 범죄 평균 발생 건수를 가장 많은 건수 순으로 10개 검색하고 확인하세요.
SELECT police_station, avg(case_number)
FROM crime_status
WHERE status_type LIKE '발생' AND crime_type LIKE '절도'
GROUP BY police_station
ORDER BY avg(case_number) DESC
LIMIT 10;
SELECT avg(case_number)
FROM crime_status
WHERE police_station LIKE '송파' 
      AND status_type LIKE '발생' 
      AND crime_type LIKE '절도';

  • 문제 2. 경찰서 별로 가장 많이 검거한 범죄 건수를 가장 적은 건수 순으로 5개 검색하세요.
SELECT police_station, MAX(case_number)
FROM crime_status
WHERE status_type LIKE '검거'
GROUP BY police_station
ORDER BY MAX(case_number) ASC
LIMIT 5;
SELECT MAX(case_number)
FROM crime_status
WHERE status_type LIKE '검거'
      AND police_station LIKE '방배';

  • 문제 3. 경찰서 별로 가장 적게 검거한 건수 중 4건보다 큰 경우를 건수가 큰 순으로 정렬하여 검색하세요.
SELECT police_station, MIN(case_number)
FROM crime_status
WHERE status_type LIKE '검거'
GROUP BY police_station
HAVING MIN(case_number) > 4
ORDER BY MIN(case_number) DESC;
SELECT MIN(case_number)
FROM crime_status
WHERE status_type LIKE '검거'
      AND police_station LIKE '중랑';

  • 문제 4. '대문' 으로 끝나는 이름의 경찰서 별 범죄발생 건수의 평균이 500건 이상인 경우를 검색하세요.
SELECT police_station, AVG(case_number)
FROM crime_status
WHERE police_station LIKE '%대문' AND status_type LIKE '발생'
GROUP BY police_station
HAVING AVG(case_number) >= 500;
SELECT AVG(case_number)
FROM crime_status
WHERE police_station LIKE '서대문' 
      AND status_type LIKE '발생';
SELECT *
FROM crime_status
WHERE police_station LIKE '동대문' 
      AND status_type LIKE '발생';


위 글은 제로베이스 데이터 취업 스쿨의 강의자료를 참고하여 작성되었습니다.

profile
허재
post-custom-banner

0개의 댓글