Aggregate Functions (집계함수)
-
실습 환경 설정
C:\Users\PC\Documents\sql_ws> mysql -h "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p Enter password: ********* mysql> use zerobase Database changed mysql> show tables; +--------------------+ | Tables_in_zerobase | +--------------------+ | cctv | | celeb | | crime_status | | oil_price | | person | | police_station | | refueling | | snl_show | | sql_file | | test1 | | test2 | +--------------------+ 11 rows in set (0.15 sec) mysql> select * from police_station limit 3; +----------------+-------------------------------------+ | name | adress | +----------------+-------------------------------------+ | 서울강남경찰서 | 서울특별시 강남구 테헤란로 114길 11 | | 서울강동경찰서 | 서울특별시 강동구 성내로 33 | | 서울강북경찰서 | 서울특별시 강북구 오패산로 406 | +----------------+-------------------------------------+ 3 rows in set (0.15 sec) mysql> select * from crime_status limit 3; +------+----------------+-------------+-------------+-------------+----------------+ | year | police_station | crime_stype | status_type | case_number | REFERENCE | +------+----------------+-------------+-------------+-------------+----------------+ | 2020 | 중부 | 살인 | 발생 | 1 | 서울중부경찰서 | | 2020 | 중부 | 살인 | 검거 | 1 | 서울중부경찰서 | | 2020 | 중부 | 강도 | 발생 | 3 | 서울중부경찰서 | +------+----------------+-------------+-------------+-------------+----------------+ 3 rows in set (0.15 sec)
1. COUNT
-
crime_status에서 경찰서는 총 몇군데?
📌 모두 확인 mysql> select count(*) from police_station; +----------+ | count(*) | +----------+ | 31 | +----------+ 1 row in set (0.15 sec) 📌 중복 제거 후 확인 mysql> select 💡count(distinct💡 police_station) from crime_status; +--------------------------------+ | count(distinct police_station) | +--------------------------------+ | 31 | +--------------------------------+ 1 row in set (0.16 sec) -
crime_type은 총 몇 가지?
📌중복제거 📌column 📌TABLE name mysql> select distinct crime_stype from crime_status; +-------------+ | crime_stype | +-------------+ | 살인 | | 강도 | | 강간,추행 | | 절도 | | 폭력 | | 강간 | +-------------+ 6 rows in set (0.15 sec) mysql> select count(distinct crime_stype) from crime_status; +-----------------------------+ | count(distinct crime_stype) | +-----------------------------+ | 6 | +-----------------------------+ 1 row in set (0.15 sec)
2. SUM
-
범죄 총 발생 건수?
mysql> select * from crime_status limit 2; +------+----------------+-------------+-------------+-------------+----------------+ | year | police_station | crime_stype | status_type | case_number | REFERENCE | +------+----------------+-------------+-------------+-------------+----------------+ | 2020 | 중부 | 살인 | 발생 | 1 | 서울중부경찰서 | | 2020 | 중부 | 살인 | 검거 | 1 | 서울중부경찰서 | +------+----------------+-------------+-------------+-------------+----------------+ 2 rows in set (0.15 sec) mysql> select sum( case_number ) from crime_status -> WHERE status_type='발생' -> ; +--------------------+ | sum( case_number ) | +--------------------+ | 92679 | +--------------------+ 1 row in set (0.15 sec) -
살인 이면서 발생한 총 건수?
mysql> select sum(case_number) -> from crime_status -> where status_type = '발생' and crime_stype like '살인'; +------------------+ | sum(case_number) | +------------------+ | 141 | +------------------+ 1 row in set (0.15 sec) -
중부 경찰서에서 검거된 총 범죄 건수는?
mysql> select sum(case_number) -> from crime_status -> where status_type = '검거' and police_station = '중부'; +------------------+ | sum(case_number) | +------------------+ | 1406 | +------------------+ 1 row in set (0.16 sec)
3. AVG
mysql> select * from crime_status limit 3;
+------+----------------+-------------+-------------+-------------+----------------+
| year | police_station | crime_stype | status_type | case_number | REFERENCE |
+------+----------------+-------------+-------------+-------------+----------------+
| 2020 | 중부 | 살인 | 발생 | 1 | 서울중부경찰서 |
| 2020 | 중부 | 살인 | 검거 | 1 | 서울중부경찰서 |
| 2020 | 중부 | 강도 | 발생 | 3 | 서울중부경찰서 |
+------+----------------+-------------+-------------+-------------+----------------+-
평균 폭력 검서 건수?
mysql> select avg(case_number) -> from crime_status -> where crime_Stype like '폭력' and status_type = '검거'; +------------------+ | avg(case_number) | +------------------+ | 1323.1935 | +------------------+ 1 row in set (0.15 sec) -
중부 범죄 평균 발생 건수?
mysql> select avg(case_number) -> from crime_status -> where police_station like '중부' and status_type like '발생'; +------------------+ | avg(case_number) | +------------------+ | 411.4000 | +------------------+ 1 row in set (0.15 sec) 📌눈으로 데이터 상세히 확인 mysql> select police_station, crime_stype, status_type, case_number -> from crime_status -> where police_station like '중부' and status_type like '발생'; +----------------+-------------+-------------+-------------+ | police_station | crime_stype | status_type | case_number | +----------------+-------------+-------------+-------------+ | 중부 | 살인 | 발생 | 1 | | 중부 | 강도 | 발생 | 3 | | 중부 | 강간,추행 | 발생 | 113 | | 중부 | 절도 | 발생 | 943 | | 중부 | 폭력 | 발생 | 997 | +----------------+-------------+-------------+-------------+ 5 rows in set (0.15 sec)
4. MIN
-
강도 발생 건수가 가장 적은 경우 몇 건?
mysql> select min(case_number) -> from crime_status -> where crime_stype like '강도' and status_type = '발생'; +------------------+ | min(case_number) | +------------------+ | 1 | +------------------+ 1 row in set (0.16 sec) -
중부경찰서에서 가장 낮은 검거 건수는?
mysql> select min(case_number) -> from crime_status -> where police_station like '중부' and status_type like '검거'; +------------------+ | min(case_number) | +------------------+ | 1 | +------------------+ 1 row in set (0.15 sec)
5. MAX
-
살인이 가장 많이 검거된 건수는?
mysql> select max(case_number) -> from crime_status -> where crime_stype like '살인' and status_type like '검거'; +------------------+ | max(case_number) | +------------------+ | 12 | +------------------+ 1 row in set (0.15 sec) -
강남 경찰서에서 가장 많이 발생한 범죄 건수는?
mysql> select max(case_number) -> from crime_status -> where police_station like '강남' and status_type like '발생'; +------------------+ | max(case_number) | +------------------+ | 2283 | +------------------+ 1 row in set (0.15 sec)
6. GROUP BY
- 그룹별 데이터 조회
- order by(정렬) 사용이 가능
⭐ Distinct(중복제거)는 order by(정렬) 사용 ❌
-
crime_status에서 경찰서별로 그룹화 하여 경찰서 이름을 조회
mysql> select police_station -> from crime_status -> group by police_station -> order by police_station -> limit 5; +----------------+ | police_station | +----------------+ | 강남 | | 강동 | | 강북 | | 강서 | | 관악 | +----------------+ 5 rows in set (0.15 sec) -
경찰서 종류를 검색 - DISTINCT를 사용하는 경우 (ORDER BY 를 사용할 수 없음)
mysql> select distinct police_station -> from crime_status -> limit 5; +----------------+ | police_station | +----------------+ | 중부 | | 종로 | | 남대문 | | 서대문 | | 혜화 | +----------------+ 5 rows in set (0.15 sec) -
경찰서 별 총 발생 범죄 건수 검색
mysql> select police_station, sum(case_number) as 발생건수 -> from crime_status -> where status_type like '발생' -> group by police_station -> order by 발생건수 desc -> limit 5; +----------------+----------+ | police_station | 발생건수 | +----------------+----------+ | 송파 | 5410 | | 관악 | 5261 | | 영등포 | 5217 | | 강남 | 4754 | | 강서 | 4415 | +----------------+----------+ 5 rows in set (0.15 sec) -
경찰서 별 평균 범죄 검거 건수 검색
- group by(police_station) 로 묶어 주고
mysql> select police_station, avg(case_number) as 평균검거건수 -> 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 | +----------------+--------------+ 5 rows in set (0.15 sec) -
경찰서 별 평균 범죄 발생건수와 평균 범죄 검거 건수 검색
- 경찰서별 : group by police_station
mysql> 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 | | 남대문 | 발생 | 270.8000 | | 남대문 | 검거 | 183.4000 | +----------------+-------------+------------------+ 6 rows in set (0.15 sec)
7. HAVING
- 조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용
-
경찰서 별로 발생한 범죄 건수의 합이 4000 건보다 보다 큰 경우 검색
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.16 sec) 📌 확인 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.15 sec) -
경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우 검색
mysql> select police_station, avg(case_number) -> from crime_status -> where (crime_stype like '폭력' or crime_stype like '절도') -> and status_type like '발생' -> group by police_station -> having avg(case_number) >= 2000; +----------------+------------------+ | police_station | avg(case_number) | +----------------+------------------+ | 영등포 | 2444.5000 | | 강남 | 2112.0000 | | 관악 | 2421.5000 | | 강서 | 2067.0000 | | 송파 | 2552.0000 | +----------------+------------------+ 5 rows in set (0.15 sec)
비전공자의 데이터 공부법