Aggregate Functions (집계함수)
여러칼럼혹은테이블전체칼럼으로부터하나의결과값을반환하는함수
p7
실습환경 만들기
실습할데이터베이스로이동
AWS RDS (database-1) zerobase 에접속실습할데이터확인 - 1
police_station 데이터확인 (31 rows)
select * from police_station;실습할데이터확인 - 2
crime_status 데이터확인 (310 rows)1. Count
총갯수를계산해주는함수
COUNT 문법
SELECT COUNT(column)
FROM tablename
WHERE condition;COUNT 예제 1
police_station 테이블에서데이터는모두몇개?
SELECT COUNT(*)
FROM police_station;
=> COUNT(*) 이렇게 붙여 써야함
| COUNT(*) |
+----------+
| 31 |COUNT 예제 2
crime_status 테이블에서경찰서는총몇군데?
SELECT COUNT(DISTINCT police_station) FROM crime_status;
| COUNT(DISTINCT police_station) |
+--------------------------------+
| 31 |COUNT 예제 3
crime_type 은총몇가지?
SELECT COUNT(DISTINCT crime_type) FROM crime_status;
+----------------------------+
| COUNT(DISTINCT crime_type) |
+----------------------------+
| 6 |2. Sum
숫자 칼럼의 합계를 계산해주는함수
SUM 문법
SELECT SUM(column)
FROM tablename
WHERE condition;SUM 예제 1
범죄총발생건수는?
SELECT SUM(case_number) FROM crime_status WHERE status_type='발생';
=> 92679 SUM 예제 2
살인의총발생건수는?
SELECT SUM(case_number) FROM crime_status WHERE status_type='발생'
AND crime_type LIKE '살인';
=> 141 SUM 예제 3
중부경찰서에서검거된총범죄건수는?
SELECT SUM(case_number) FROM crime_status WHERE status_type='검거'
AND police_station='중부';
=> 1406 <문제>
-1. police_station 에서경찰서는총몇개이고, 각각경찰서이름은 무엇인지확인하세요.
SELECT * FROM police_station limit 5;
SELECT COUNT(name) FROM police_station;
=> 31개 (중복포함)
답1
SELECT COUNT(DISTINCT name) FROM police_station;
=> 31개 (중복제거, 정답 = pandas의 unique와 동일한 메서드)
답2
SELECT DISTINCT name FROM police_station;
=> 중복을 제거하고 이름 컬럼(각각의 경찰서 이름)을 보는 법, 컬럼 앞에 distinct 명시- crime_status 에서 status_type 은총몇개이고, 각각타입은무엇인지확인하세요.
SELECT * FROM crime_status limit 5;
+------+----------------+------------+-------------+-------------+----------------+
| year | police_station | crime_type | status_type | case_number | reference |
+------+----------------+------------+-------------+-------------+----------------+
| 2020 | 중부 | 살인 | 발생 | 1 | 서울중부경찰서 |
| 2020 | 중부 | 살인 | 검거 | 1 | 서울중부경찰서 |
답1
SELECT COUNT(status_type) FROM crime_status;
=> status_type 컬럼의 row 수: 310개
답1
SELECT COUNT(DISTINCT status_type) FROM crime_status;
=> status_type 은 2개
답2
SELECT DISTINCT status_type FROM crime_status;
| status_type |
+-------------+
| 발생 |
| 검거 |- 종로경찰서와남대문경찰서의강도발생건수의 합을구하세요.
답
SELECT SUM(case_number) FROM crime_status WHERE status_type='발생'
AND crime_type like '강도' AND (police_station='종로' or police_station='남대문') ;
=> 6건
※ 종로경찰서와 남대문경찰서의 총 범죄 발생건수: 5144건
SELECT SUM(case_number) FROM crime_status
where police_station like '종로' or police_station like'남대문';
※ 종로경찰서와 남대문경찰서의 총 강도 사건 : 12건
SELECT SUM(case_number) FROM crime_status
where crime_type like '강도' AND (police_station like '종로' or police_station like'남대문');- 폭력범죄의검거건수의합을구하세요.
답: 41019건
SELECT SUM(case_number) FROM crime_status
WHERE status_type='검거'
AND crime_type like '폭력' ;
※ 총 폭력범죄 건수 : 89363건
SELECT SUM(case_number) FROM crime_status
WHERE crime_type like '폭력' ;3. Avg
숫자칼럼의평균을계산해주는함수
AVG 문법
SELECT AVG(column)
FROM
WHERE AVG 예제 1
평균폭력검거건수는? 1323.1935건
SELECT AVG(case_number)
FROM crime_status
WHERE crime_type LIKE '폭력' AND status_type='검거';AVG 예제 1 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE crime_type LIKE '폭력' AND status_type='검거';
| police_station | crime_type | status_type | case_number |
+----------------+------------+-------------+-------------+
| 중부 | 폭력 | 검거 | 830 |
| 종로 | 폭력 | 검거 | 792 |
| 남대문 | 폭력 | 검거 | 494 |
...
31 rows in set (0.04 sec)
AVG 예제 2
중부경찰서범죄평균발생건수 : 411.4000
SELECT AVG(case_number)
FROM crime_status
WHERE police_station LIKE '중부' AND status_type = '발생';AVG 예제 2 확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE police_station LIKE '중부' AND status_type='발생';
| police_station | crime_type | status_type | case_number |
+----------------+------------+-------------+-------------+
| 중부 | 살인 | 발생 | 1 |
| 중부 | 강도 | 발생 | 3 |
| 중부 | 강간,추행 | 발생 | 113 |
| 중부 | 절도 | 발생 | 943 |
| 중부 | 폭력 | 발생 | 997 |4. Min
숫자칼럼중가장작은값을찾아주는함수
MIN 문법
SELECT MIN(case_number)
FROM table
WHERE MIN 예제 1
강도발생건수가가장적은경우몇건? 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='발생';
| 방배 | 강도 | 발생 | 1 |
| 은평 | 강도 | 발생 | 1 |
| 도봉 | 강도 | 발생 | 2 |
| 수서 | 강도 | 발생 | 2 |
+----------------+------------+-------------+-------------+
31 rows in set (0.04 sec)MIN 예제 2
중부경찰서에서가장낮은검거건수는? 1건
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='검거';
| police_station | crime_type | status_type | case_number |
+----------------+------------+-------------+-------------+
| 중부 | 살인 | 검거 | 1 |
| 중부 | 강도 | 검거 | 4 |
...5. Max
숫자칼럼중가장큰값을찾아주는함수
MAX 문법
SELECT MAX(case_number)
FROM table
WHERE MAX 예제 1
살인이가장많이검거된건수는? 12건
SELECT MAX(case_number)
FROM crime_status
WHERE crime_type LIKE '살인' AND status_type LIKE '검거';확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE crime_type LIKE '살인' AND status_type LIKE '검거';
=> 관악 | 살인 | 검거 | 12 |
| 강서 | 살인 | 검거 | 9 |MAX 예제 2
강남경찰서에서가장많이발생한범죄건수는? 2283건
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 '발생';
=> | 강남 | 폭력 | 발생 | 2283 |<문제>
- 살인의평균발생건수를검색하고확인하세요.
답: 4.5484 건
SELECT AVG(case_number)
FROM crime_status
WHERE crime_type LIKE '살인' AND status_type LIKE '발생';
확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE crime_type LIKE '살인' AND status_type LIKE '발생';
| police_station | crime_type | status_type | case_number |
+----------------+------------+-------------+-------------+
| 중부 | 살인 | 발생 | 1 |
| 종로 | 살인 | 발생 | 9 |
| 남대문 | 살인 | 발생 | 1 |- 서초경찰서의 범죄별 평균검거건수를검색하고확인하세요.
답: 495.2000 건
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 '검거';
| police_station | crime_type | status_type | case_number |
+----------------+------------+-------------+-------------+
| 서초 | 살인 | 검거 | 4 |
| 서초 | 강도 | 검거 | 3 |
| 서초 | 강간,추행 | 검거 | 253 |
| 서초 | 절도 | 검거 | 801 |
| 서초 | 폭력 | 검거 | 1415 |- 구로경찰서와도봉경찰서의평균살인검거건수를검색하고확인하세요.
답 : 3.0000
SELECT AVG(case_number)
FROM crime_status
WHERE (police_station LIKE '구로' or police_station LIKE '도봉') AND
crime_type LIKE '살인' and status_type LIKE '검거';
확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE (police_station LIKE '구로' or police_station LIKE '도봉') AND
crime_type LIKE '살인' and status_type LIKE '검거';
| police_station | crime_type | status_type | case_number |
+----------------+------------+-------------+-------------+
| 구로 | 살인 | 검거 | 6 |
| 도봉 | 살인 | 검거 | 0 |- 광진경찰서에서 가장 낮은 범죄검거건수를 검색하고 확인하세요.
답 : 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 '검거';
| police_station | crime_type | status_type | case_number |
+----------------+------------+-------------+-------------+
| 광진 | 살인 | 검거 | 2 |
| 광진 | 강도 | 검거 | 4 |
| 광진 | 강간,추행 | 검거 | 224 |
| 광진 | 절도 | 검거 | 874 |
| 광진 | 폭력 | 검거 | 1407 |
+----------------+------------+-------------+-------------+
5 rows in set (0.04 sec)- 성북경찰서에서가장낮은범죄발생건수를검색하고확인하세요.
답 : 0건
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 '발생';
| police_station | crime_type | status_type | case_number |
+----------------+------------+-------------+-------------+
| 성북 | 살인 | 발생 | 0 |- 영등포경찰서의가장높은범죄발생건수를검색하고확인하세요.
답 2157건
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 '검거';
| 영등포 | 절도 | 검거 | 953 |
| 영등포 | 폭력 | 검거 | 2157 |
- 절도검거가가장많은건수를검색하고확인하세요.
답: 1137건
SELECT MAX(case_number)
FROM crime_status
WHERE crime_type LIKE '절도' and status_type LIKE '검거';
확인
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE crime_type LIKE '절도' and status_type LIKE '검거';
관악 | 절도 | 검거 | 1137 |6. Group By
그룹화하여데이터를조회
GROUP BY 문법
SELECT column1, column2, ...
FROM table
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column1, column2, ...;GROUP BY 예제 1
crime_status 에서경찰서별로그룹화하여경찰서이름을조회
=> police_station별로 그룹핑해서 중복이 제외된 경찰서 이름들이 조회됨
정렬도 ㄱ부터~
SELECT police_station
FROM crime_status
GROUP BY police_station
ORDER BY police_station
LIMIT 5;
| police_station |
+----------------+
| 강남 |
| 강동 |
| 강북 |
| 강서 |
| 관악 |
경찰서종류를검색 - DISTINCT 를사용하는경우 (ORDER BY 를사용할수없음)
=> GROUP BY 대신에 DISTINCT를 사용해도 되나 DISTINCT를 사용하면 GROUP BY를 사용할 수 없음
SELECT DISTINCT police_station
FROM crime_status
LIMIT 5;GROUP BY 예제 2
경찰서별로총발생범죄건수를검색
SELECT police_station, sum(case_number) 발생건수
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station
ORDER BY 발생건수 DESC
LIMIT 5;
| police_station | 발생건수 |
+----------------+----------+
| 송파 | 5410 |
| 관악 | 5261 |
| 영등포 | 5217 |
| 강남 | 4754 |
| 강서 | 4415 |
+----------------+----------+GROUP BY 예제 3
경찰서별로평균범죄검거건수를검색
SELECT police_station, avg(case_number) 평균검거건수
FROM crime_status
WHERE status_type LIKE '검거'
GROUP BY police_station
ORDER BY 평균검거건수 DESC
LIMIT 5;
+----------------+--------------+
| police_station | 평균검거건수 |
+----------------+--------------+
| 관악 | 771.6000 |
| 송파 | 708.8000 |
| 강서 | 678.6000 |
| 강남 | 674.6000 |
| 영등포 | 674.2000 |
+----------------+--------------+GROUP BY 예제 4
경찰서별평균범죄발생건수와평균범죄검거건수를검색
SELECT police_station, status_type, avg(case_number)
FROM crime_status
GROUP BY police_station, status_type
LIMIT 6;
| police_station | status_type | avg(case_number) |
+----------------+-------------+------------------+
| 중부 | 발생 | 411.4000 |
| 중부 | 검거 | 281.2000 |
| 종로 | 발생 | 338.8000 |
| 종로 | 검거 | 235.8000 |
...7. having
Chapter 15-9.HAVING
조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용
=> select문 안에서 집계함수가 사용됐을 때에는 WHERE절 안에서는 집계함수관련 조건사용이 불가능하다.
HAVING 문법
SELECT column1, column2, ...
FROM table
WHERE condition
GROUP BY column1, column2, ...
HAVING condition (Aggregate Fuctions: 집계함수가 포함되는 부분을 having함수절에 씀)
ORDER BY column1, column2, ...;
HAVING 예제 1
경찰서별로발생한범죄건수의합이 4000 건보다보다큰경우를검색
SELECT police_station, sum(case_number) count
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station
HAVING count > 4000;
=> HAVING sum(case_number) > 4000; 로 명시해도 됨
WHERE sum(case_number) > 4000;이 불가
| police_station | count |
+----------------+-------+
| 영등포 | 5217 |
| 강남 | 4754 |
| 관악 | 5261 |
확인 : 5217
SELECT sum(case_number)
FROM crime_status
WHERE status_type LIKE '발생' and police_station like '영등포';HAVING 예제 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;
+----------------+------------------+
| police_station | AVG(case_number) |
+----------------+------------------+
| 영등포 | 2444.5000 |
| 강남 | 2112.0000 |<문제4개>
- 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;
=> distinct가 아닌 GROUP BY로 (중복제거 경찰서별)그룹핑 한뒤에 order by를 사용해 정렬할 수
있음
=> order by함수에서 집계함수 사용가능 (where절이 안됨)
=> 구문 순서 잘보기
| police_station | AVG(case_number) |
+----------------+------------------+
| 송파 | 2429.0000 |
| 관악 | 2229.0000 |
| 영등포 | 2188.0000 |
확인: 2429.0000
SELECT AVG(case_number)
FROM crime_status
WHERE police_station='송파' and status_type LIKE '발생' and crime_type like '절도';
- 2. 경찰서별로가장많이검거한범죄건수를가장적은건수순으로 5개검색하세요.
=> ex> 방배에서 가장 많이 검거한 범죄(ex>폭력)의 건수같이 경찰서별로 가장 많이 검거한 범죄의 건수를 나열한 뒤 검거건수가 적은 경찰서(와 그 건수) 순서대로 정렬해라.
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;
| police_station | MAX(case_number) |
+----------------+------------------+
| 방배 | 365 |
| 남대문 | 494 |
| 성북 | 610 |
| 혜화 | 628 |
| 종암 | 630 |
확인: 365
SELECT MAX(case_number)
FROM crime_status
WHERE police_station='방배' and status_type 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;
=> Having의 위치 잘보기 (where, group by + HAVING + order by 순)
| police_station | min(case_number) |
+----------------+------------------+
| 중랑 | 6 |
| 송파 | 6 |
| 동대문 | 5 |
| 강서 | 5 |
| 구로 | 5 |
확인: 6건
SELECT police_station, min(case_number)
FROM crime_status
WHERE police_station='중랑' and status_type LIKE '검거';
- 4. '대문' 으로끝나는이름의경찰서별범죄발생건수의평균이 500건이상인경우를검색하세요.
틀림: 범죄발생건수와 범죄검거건수의 총평균 수를 구함
SELECT police_station, avg(case_number)
FROM crime_status
WHERE police_station LIKE '%대문'
GROUP BY police_station
HAVING avg(case_number) > 500;
=>
| police_station | avg(case_number) |
| 동대문 | 586.6000 |
정답: 범죄발생건수의 평균만 구해야함 => where절 '발생'조건문 추가
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;
=>
| police_station | avg(case_number) |
+----------------+------------------+
| 서대문 | 506.6000 |
| 동대문 | 680.2000 |
확인
SELECT police_station, avg(case_number)
FROM crime_status
WHERE police_station LIKE '%대문' and status_type like '발생'
GROUP BY police_station;
+----------------+------------------+
| police_station | avg(case_number) |
+----------------+------------------+
| 남대문 | 270.8000 |
| 서대문 | 506.6000 |
| 동대문 | 680.2000 |
먼저 푼 것(범죄발생 및 검거건수의 평균에 대한)에 대한 확인
확인
SELECT police_station, avg(case_number)
FROM crime_status
WHERE police_station LIKE '%대문'
GROUP BY police_station;
| police_station | avg(case_number) |
+----------------+------------------+
| 남대문 | 227.1000 |
| 서대문 | 423.9000 |
| 동대문 | 586.6000 |
(※=> 오류1: GROUP BY를 안해주면 남대문 1건만 검색됨,
avg(case_number)와 같이 집계함수를 써줬기 때문에 group by를 해줘야함
오류2: SELECT distinct(police_station), avg(case_number) FROM crime_status
WHERE police_station LIKE '%대문';
=> 이것도 역시 남대문 1건만 검색됨, 조건절에
3: 집계함수 사용안하고 distinct만 사용시
SELECT distinct(police_station) FROM crime_status
WHERE police_station LIKE '%대문';
=> 이건 남대문, 서대문, 동대문 정상 출력됨
결론: "집계함수" 사용시 distinct가 아닌 "GROUP BY"절을 사용해줘야 한다.
"집계함수" => "GROUP BY")