1. 주제 : CCTV를 많이 설치하면 범죄율이 감소하는지 비교 분석
관련 기사:
- 충주 범죄취약지역에 방범 CCTV 138대 추가 설치: https://newsis.com/view/?id=NISX20220620_0001913002&cID=10806&pID=10800
- 국토부, 2024년까지 철도역사에 AI CCTV 5000대 설치 : https://newsis.com/view/?id=NISX20220608_0001899807&cID=10401&pID=10400
위와 같은 기사들을 읽고 정말 CCTV를 많이 설치하면 범죄율이 감소하는지 수도권(서울, 경기도, 인천)의 CCTV데이터를 가지고 분석을 해보았습니다.
2. 데이터 출처
https://www.data.go.kr/data/15085726/fileData.do : 대검찰청_범죄발생지
https://www.localdata.go.kr/lif/lifeCtacDataView.do : 생활밀착데이터 / CCTV정보
3. 데이터 분석
1 ) 데이터 임포트
1-1 ) 서울의 CCTV 데이터 임포트 :
1-2 ) 경기도의 CCTV 데이터 임포트 :
1-3 ) 인천의 CCTV 데이터 임포트 :
1-4 ) 2019년도 범죄분류와 범죄장소 데이터:
1-5 ) 2021년도의 범죄분류와 범죄장소 데이터:
2 ) CCTV 관련 테이블들을 합치기 전 구별을 위해 도시 column을 추가
3 ) union all을 이용하여 cctv_seoul, cctv_gyeonggi, cctv_incheon 테이블들을 합치는데 설치연월이 기재되지 않은 데이터들은 데이터기준일자로 대체함
4 ) 범죄 장소 데이터를 기준으로 CCTV데이터를 2019년도와 2021년도로 분리
5 ) rollup함수를 이용하여 2019년도까지의 CCTV갯수와, 신규 CCTV갯수의 총합을 확인 기존의 CCTV에서 약 80%정도의 CCTV가 추가 설치됨
6 ) 범죄 장소 테이블과 join하기 전 관리기관명을 맞춰줘야해서 2019년도 CCTV테이블과 2021년도 CCTV테이블의 관리기관명을 변경해준 뒤 새로운 테이블을 생성
/*2019*/
create table cctv_2019_pretreatment
as
select 번호, case when 관리기관명 like '%강남구%' then '서울_강남'
when 관리기관명 like '%강동구%' then '서울_강동'
when 관리기관명 like '%강북%' then '서울_강북'
when 관리기관명 like '%강서구%' then '서울_강서'
when 관리기관명 like '%관악구%' then '서울_관악'
when 관리기관명 like '%광진구%' then '서울_광진'
when 관리기관명 like '%구로구%' then '서울_구로'
when 관리기관명 like '%금천구%' then '서울_금천'
when 관리기관명 like '%노원구%' then '서울_노원'
when 관리기관명 like '%도봉구%' then '서울_도봉'
when 관리기관명 like '%동대문구%' then '서울_동대문'
when 관리기관명 like '%동작구%' then '서울_동작'
when 관리기관명 like '%마포구%' then '서울_마포'
when 관리기관명 like '%서대문구%' then '서울_서대문'
when 관리기관명 like '%서초구%' then '서울_서초'
when 관리기관명 like '%성동구%' then '서울_성동'
when 관리기관명 like '%성북구%' then '서울_성북'
when 관리기관명 like '%송파구%' then '서울_송파'
when 관리기관명 like '%양천구%' then '서울_양천'
when 관리기관명 like '%영등포구%' then '서울_영등포'
when 관리기관명 like '%용산구%' then '서울_용산'
when 관리기관명 like '%은평구%' then '서울_은평'
when 관리기관명 like '%종로구%' then '서울_종로'
when 관리기관명 like '%서울%중구%' then '서울_중구'
when 관리기관명 like '%중랑구%' then '서울_중랑'
when 관리기관명 like '%인천%중구%' then '인천_중구'
when 관리기관명 like '%동구%' then '인천_동구'
when 관리기관명 like '%미추홀구%' then '인천_미추홀'
when 관리기관명 like '%연수구%' then '인천_연수'
when 관리기관명 like '%남동구%' then '인천_남동'
when 관리기관명 like '%부평구%' then '인천_부평'
when 관리기관명 like '%계양구%' then '인천_계양'
when 관리기관명 like '%서구%' then '인천_서구'
when 관리기관명 like '%강화군%' then '인천_강화군'
when 관리기관명 like '%옹진군%' then '인천_옹진군'
when 관리기관명 like '%여성정책과%' then '인천_동구'
when 관리기관명 like '%복지정책과%' then '인천_동구'
when 관리기관명 like '%송림%' then '인천_동구'
when 관리기관명 like '%화수2동%' then '인천_동구'
when 관리기관명 like '%금창동%' then '인천_동구'
when 관리기관명 like '%수원%' then '수원'
when 관리기관명 like '%용인%' then '용인'
when 관리기관명 like '%성남%' then '성남'
when 관리기관명 like '%부천%' then '부천'
when 관리기관명 like '%화성%' then '화성'
when 관리기관명 like '%안산%' then '안산'
when 관리기관명 like '%안양%' then '안양'
when 관리기관명 like '%평택%' then '평택'
when 관리기관명 like '%시흥%' then '시흥'
when 관리기관명 like '%김포%' then '김포'
when 관리기관명 like '%광주%' then '광주'
when 관리기관명 like '%광명%' then '광명'
when 관리기관명 like '%군포%' then '군포'
when 관리기관명 like '%하남%' then '하남'
when 관리기관명 like '%오산%' then '오산'
when 관리기관명 like '%이천%' then '이천'
when 관리기관명 like '%안성%' then '안성'
when 관리기관명 like '%의왕%' then '의왕'
when 관리기관명 like '%양평%' then '양평'
when 관리기관명 like '%여주%' then '여주'
when 관리기관명 like '%과천%' then '과천'
when 관리기관명 like '%고양%' then '고양'
when 관리기관명 like '%남양주%' then '남양주'
when 관리기관명 like '%파주%' then '파주'
when 관리기관명 like '%의정부%' then '의정부'
when 관리기관명 like '%양주%' then '양주'
when 관리기관명 like '%구리%' then '구리'
when 관리기관명 like '%포천%' then '포천'
when 관리기관명 like '%동두천%' then '동두천'
when 관리기관명 like '%가평%' then '가평'
when 관리기관명 like '%연천%' then '연천'
else 관리기관명
end as 관리기관명, 카메라대수, 설치연월, location
from cctv_2019;
/*2021*/
create table cctv_2021_pretreatment
as
select 번호, case when 관리기관명 like '%강남구%' then '서울_강남'
when 관리기관명 like '%강동구%' then '서울_강동'
when 관리기관명 like '%강북%' then '서울_강북'
when 관리기관명 like '%강서구%' then '서울_강서'
when 관리기관명 like '%관악구%' then '서울_관악'
when 관리기관명 like '%광진구%' then '서울_광진'
when 관리기관명 like '%구로구%' then '서울_구로'
when 관리기관명 like '%금천구%' then '서울_금천'
when 관리기관명 like '%노원구%' then '서울_노원'
when 관리기관명 like '%도봉구%' then '서울_도봉'
when 관리기관명 like '%동대문구%' then '서울_동대문'
when 관리기관명 like '%동작구%' then '서울_동작'
when 관리기관명 like '%마포구%' then '서울_마포'
when 관리기관명 like '%서대문구%' then '서울_서대문'
when 관리기관명 like '%서초구%' then '서울_서초'
when 관리기관명 like '%성동구%' then '서울_성동'
when 관리기관명 like '%성북구%' then '서울_성북'
when 관리기관명 like '%송파구%' then '서울_송파'
when 관리기관명 like '%양천구%' then '서울_양천'
when 관리기관명 like '%영등포구%' then '서울_영등포'
when 관리기관명 like '%용산구%' then '서울_용산'
when 관리기관명 like '%은평구%' then '서울_은평'
when 관리기관명 like '%종로구%' then '서울_종로'
when 관리기관명 like '%서울%중구%' then '서울_중구'
when 관리기관명 like '%중랑구%' then '서울_중랑'
when 관리기관명 like '%인천%중구%' then '인천_중구'
when 관리기관명 like '%동구%' then '인천_동구'
when 관리기관명 like '%미추홀구%' then '인천_미추홀'
when 관리기관명 like '%연수구%' then '인천_연수'
when 관리기관명 like '%남동구%' then '인천_남동'
when 관리기관명 like '%부평구%' then '인천_부평'
when 관리기관명 like '%계양구%' then '인천_계양'
when 관리기관명 like '%서구%' then '인천_서구'
when 관리기관명 like '%강화군%' then '인천_강화군'
when 관리기관명 like '%옹진군%' then '인천_옹진군'
when 관리기관명 like '%여성정책과%' then '인천_동구'
when 관리기관명 like '%복지정책과%' then '인천_동구'
when 관리기관명 like '%송림%' then '인천_동구'
when 관리기관명 like '%화수2동%' then '인천_동구'
when 관리기관명 like '%금창동%' then '인천_동구'
when 관리기관명 like '%수원%' then '수원'
when 관리기관명 like '%용인%' then '용인'
when 관리기관명 like '%성남%' then '성남'
when 관리기관명 like '%부천%' then '부천'
when 관리기관명 like '%화성%' then '화성'
when 관리기관명 like '%안산%' then '안산'
when 관리기관명 like '%안양%' then '안양'
when 관리기관명 like '%평택%' then '평택'
when 관리기관명 like '%시흥%' then '시흥'
when 관리기관명 like '%김포%' then '김포'
when 관리기관명 like '%광주%' then '광주'
when 관리기관명 like '%광명%' then '광명'
when 관리기관명 like '%군포%' then '군포'
when 관리기관명 like '%하남%' then '하남'
when 관리기관명 like '%오산%' then '오산'
when 관리기관명 like '%이천%' then '이천'
when 관리기관명 like '%안성%' then '안성'
when 관리기관명 like '%의왕%' then '의왕'
when 관리기관명 like '%양평%' then '양평'
when 관리기관명 like '%여주%' then '여주'
when 관리기관명 like '%과천%' then '과천'
when 관리기관명 like '%고양%' then '고양'
when 관리기관명 like '%남양주%' then '남양주'
when 관리기관명 like '%파주%' then '파주'
when 관리기관명 like '%의정부%' then '의정부'
when 관리기관명 like '%양주%' then '양주'
when 관리기관명 like '%구리%' then '구리'
when 관리기관명 like '%포천%' then '포천'
when 관리기관명 like '%동두천%' then '동두천'
when 관리기관명 like '%가평%' then '가평'
when 관리기관명 like '%연천%' then '연천'
else 관리기관명
end as 관리기관명, 카메라대수, 설치연월, location
from cctv_2021;
7 ) 2019년도 CCTV 테이블과 2021년도 CCTV 테이블을 with절을 사용하여 합쳐서 전체 all_cctv테이블을 생성
- 2019년도 이전 카메라의 대수가 null로 나오는 관리기관명은 정확하지 않은 데이터로 생각되어 제외했습니다.
8 ) crime_location_2021 테이블과 crime_location_2019의 범죄 건수 합계 출력
create table crime_sum_2019
as
select sum(서울_종로) as 서울_종로, sum(서울_중구) as 서울_중구, sum(서울_용산) as 서울_용산, sum(서울_성동) as 서울_성동, sum(서울_광진) as 서울_광진, sum(서울_동대문) as 서울_동대문, sum(서울_중랑) as 서울_중랑, sum(서울_성북) as 서울_성북, sum(서울_강북) as 서울_강북, sum(서울_도봉) as 서울_도봉, sum(서울_노원) as 서울_노원, sum(서울_은평) as 서울_은평, sum(서울_서대문) as 서울_서대문, sum(서울_마포) as 서울_마포, sum(서울_양천) as 서울_양천, sum(서울_강서) as 서울_강서,
sum(서울_구로) as 서울_구로, sum(서울_금천) as 서울_금천, sum(서울_영등포) as 서울_영등포, sum(서울_동작) as 서울_동작, sum(서울_관악) as 서울_관악, sum(서울_서초) as 서울_서초, sum(서울_강남) as 서울_강남, sum(서울_송파) as 서울_송파,
sum(서울_강동) as 서울_강동, sum(인천_중구) as 인천_중구, sum(인천_동구) as 인천_동구, sum(인천_서구) as 인천_서구,
sum(인천_미추홀) as 인천_미추홀, sum(인천_연수) as 인천_연수, sum(인천_남동) as 인천_남동, sum(인천_부평) as 인천_부평,sum(인천_계양) as 인천_계양, sum(인천_강화군) as 인천_강화군, sum(인천_옹진군) as 인천_옹진군, sum(부천) as 부천, sum(수원) as 수원, sum(성남) as 성남, sum(안양) as 안양, sum(광명) as 광명, sum(안산) as 안산, sum(고양) as 고양, sum(의정부) as 의정부, sum(군포) as 군포, sum(남양주) as 남양주, sum(용인) as 용인, sum(시흥) as 시흥, sum(파주) as 파주, sum(이천) as 이천, sum(평택) as 평택, sum(구리) as 구리
from crime_location_2019;
create table crime_sum_2021
as
select sum(서울_종로) as 서울_종로, sum(서울_중구) as 서울_중구, sum(서울_용산) as 서울_용산, sum(서울_성동) as 서울_성동, sum(서울_광진) as 서울_광진, sum(서울_동대문) as 서울_동대문, sum(서울_중랑) as 서울_중랑, sum(서울_성북) as 서울_성북, sum(서울_강북) as 서울_강북, sum(서울_도봉) as 서울_도봉, sum(서울_노원) as 서울_노원, sum(서울_은평) as 서울_은평, sum(서울_서대문) as 서울_서대문, sum(서울_마포) as 서울_마포, sum(서울_양천) as 서울_양천, sum(서울_강서) as 서울_강서,
sum(서울_구로) as 서울_구로, sum(서울_금천) as 서울_금천, sum(서울_영등포) as 서울_영등포, sum(서울_동작) as 서울_동작, sum(서울_관악) as 서울_관악, sum(서울_서초) as 서울_서초, sum(서울_강남) as 서울_강남, sum(서울_송파) as 서울_송파,
sum(서울_강동) as 서울_강동, sum(인천_중구) as 인천_중구, sum(인천_동구) as 인천_동구, sum(인천_서구) as 인천_서구,
sum(인천_미추홀) as 인천_미추홀, sum(인천_연수) as 인천_연수, sum(인천_남동) as 인천_남동, sum(인천_부평) as 인천_부평,sum(인천_계양) as 인천_계양, sum(인천_강화군) as 인천_강화군, sum(인천_옹진군) as 인천_옹진군, sum(부천) as 부천, sum(수원) as 수원, sum(성남) as 성남, sum(안양) as 안양, sum(광명) as 광명, sum(안산) as 안산, sum(고양) as 고양, sum(의정부) as 의정부, sum(군포) as 군포, sum(남양주) as 남양주, sum(용인) as 용인, sum(시흥) as 시흥, sum(파주) as 파주, sum(이천) as 이천, sum(평택) as 평택, sum(구리) as 구리
from crime_location_2021;
9 ) 가로로 출력된 데이터를 cctv데이터와 join해야하기 때문에 unpivot함수를 이용해 세로데이터로 변경create table crime_2019
create
as
select *
from crime_sum_2019
unpivot (
crime_year
for crime_area
in ("서울_종로", "서울_중구", "서울_용산", "서울_성동", "서울_광진", "서울_동대문", "서울_중랑", "서울_성북", "서울_강북", "서울_도봉", "서울_노원", "서울_은평", "서울_서대문", "서울_마포", "서울_양천", "서울_강서", "서울_구로", "서울_금천",
"서울_영등포", "서울_동작", "서울_관악", "서울_서초", "서울_강남", "서울_송파", "서울_강동", "인천_중구", "인천_동구", "인천_서구", "인천_미추홀", "인천_연수", "인천_남동", "인천_부평", "인천_계양", "인천_강화군", "인천_옹진군", "수원", "용인", "성남", "부천", "안산", "안양", "평택", "시흥", "광명", "군포", "이천", "고양", "남양주", "파주", "의정부", "구리")
);
create table crime_2021
as
select *
from crime_sum_2021
unpivot (
crime_year
for crime_area
in ("서울_종로", "서울_중구", "서울_용산", "서울_성동", "서울_광진", "서울_동대문", "서울_중랑", "서울_성북", "서울_강북", "서울_도봉", "서울_노원", "서울_은평", "서울_서대문", "서울_마포", "서울_양천", "서울_강서", "서울_구로", "서울_금천",
"서울_영등포", "서울_동작", "서울_관악", "서울_서초", "서울_강남", "서울_송파", "서울_강동", "인천_중구", "인천_동구", "인천_서구", "인천_미추홀", "인천_연수", "인천_남동", "인천_부평", "인천_계양", "인천_강화군", "인천_옹진군", "수원", "용인", "성남", "부천", "안산", "안양", "평택", "시흥", "광명", "군포", "이천", "고양", "남양주", "파주", "의정부", "구리")
);
10 ) 2019범죄 건수 테이블과, 2021범죄 건수 테이블 JOIN 후 새로운 테이블 all_crime 생성
11 ) all_cctv테이블과 all_crime테이블의 JOIN을 위해 이름 변경
12 ) all_cctv테이블과 all_crime테이블을 조인 후 assignment_fin이라는 새로운 테이블을 생성하였고, CCTV 한 대당 범죄율 증감을 확인하기위해 범죄율증감에서 증가카메라대수를 나눈 뒤 새 컬럼으로 추가
13 ) 총 합계를 구해보니 CCTV한 대당 범죄율이 -9.01건 정도 감소되는 것이 확인됨
+ 추가 :
좀 더 정확한 결과를 얻기 위해 CCTV설치와 관련된다고 생각하는 범죄 분류만 뽑아서 다시 실행해보았습니다.
CCTV와 관련된다고 생각하는 범죄 분류:
절도, 장물, 횡령, 배임, 손괴, 살인, 강도, 방화, 성폭력, 폭행, 상해, 협박, 공갈, 약취와 유인, 체포와 감금, 폭력행위등(손괴,강요,주거침입등), 폭력행위 등(단체 등의 구성,활동) , 직무유기, 수뢰, 증뢰, 도박과 복표, 기타 음란행위, 주거 침입, 유기, 교통방해, 공무방해, 도주와 범인 은닉, 위증과 증거인멸, 도로교통법, 도로교통법(무면허운전), 도로교통법(사고후미조치), 도로교통법(음주운전),도로교통법(음주측정거부), 특가법(도주차량)
/*2019 cctv관련 범죄분류만 뽑아서 합계*/
create table crime_sum_2019_ex
as
select sum(서울_종로) as 서울_종로, sum(서울_중구) as 서울_중구, sum(서울_용산) as 서울_용산, sum(서울_성동) as 서울_성동, sum(서울_광진) as 서울_광진, sum(서울_동대문) as 서울_동대문, sum(서울_중랑) as 서울_중랑, sum(서울_성북) as 서울_성북, sum(서울_강북) as 서울_강북, sum(서울_도봉) as 서울_도봉, sum(서울_노원) as 서울_노원, sum(서울_은평) as 서울_은평,
sum(서울_서대문) as 서울_서대문, sum(서울_마포) as 서울_마포, sum(서울_양천) as 서울_양천, sum(서울_강서) as 서울_강서, sum(서울_구로) as 서울_구로, sum(서울_금천) as 서울_금천, sum(서울_영등포) as 서울_영등포, sum(서울_동작) as 서울_동작, sum(서울_관악) as 서울_관악, sum(서울_서초) as 서울_서초, sum(서울_강남) as 서울_강남, sum(서울_송파) as 서울_송파,
sum(서울_강동) as 서울_강동, sum(인천_중구) as 인천_중구, sum(인천_동구) as 인천_동구, sum(인천_서구) as 인천_서구, sum(인천_미추홀) as 인천_미추홀, sum(인천_연수) as 인천_연수, sum(인천_남동) as 인천_남동, sum(인천_부평) as 인천_부평, sum(인천_계양) as 인천_계양, sum(인천_강화군) as 인천_강화군, sum(인천_옹진군) as 인천_옹진군, sum(부천) as 부천, sum(수원) as 수원, sum(성남) as 성남, sum(안양) as 안양, sum(광명) as 광명, sum(안산) as 안산, sum(고양) as 고양, sum(의정부) as 의정부, sum(군포) as 군포, sum(남양주) as 남양주, sum(용인) as 용인, sum(시흥) as 시흥, sum(파주) as 파주, sum(이천) as 이천, sum(평택) as 평택, sum(구리) as 구리
from (select *
from crime_2019_ex
where 범죄분류 = '절도' or 범죄분류 = '장물' or 범죄분류 = '횡령' or 범죄분류 = '배임' or 범죄분류 = '손괴' or 범죄분류 = '살인' or 범죄분류 = '강도' or 범죄분류 = '방화' or 범죄분류 = '성폭력' or 범죄분류 = '폭행' or 범죄분류 = '상해' or 범죄분류 = '협박' or 범죄분류 = '공갈' or 범죄분류 = '약취와 유인' or 범죄분류 = '체포와 감금' or 범죄분류 = '폭력행위등(손괴,강요,주거침입등)' or 범죄분류 = '폭력행위 등(단체 등의 구성,활동)' or 범죄분류 = '직무유기' or 범죄분류 = '수뢰' or 범죄분류 = '증뢰' or 범죄분류 = '도박과 복표' or 범죄분류 = '기타 음란행위' or 범죄분류 = '주거 침입' or 범죄분류 = '유기' or 범죄분류 = '교통방해' or 범죄분류 = '공무방해' or 범죄분류 = '도주와 범인 은닉' or 범죄분류 = '위증과 증거인멸' or 범죄분류 = '도로교통법' or 범죄분류 = '도로교통법(무면허운전)' or 범죄분류 = '도로교통법(사고후미조치)' or 범죄분류 = '도로교통법(음주운전)' or 범죄분류 = '도로교통법(음주측정거부)' or 범죄분류 = '특가법(도주차량)'
);
/*2021 cctv관련 범죄분류만 뽑아서 합계*/
create table crime_sum_2021_ex
as
select sum(서울_종로) as 서울_종로, sum(서울_중구) as 서울_중구, sum(서울_용산) as 서울_용산, sum(서울_성동) as 서울_성동, sum(서울_광진) as 서울_광진, sum(서울_동대문) as 서울_동대문, sum(서울_중랑) as 서울_중랑, sum(서울_성북) as 서울_성북, sum(서울_강북) as 서울_강북, sum(서울_도봉) as 서울_도봉, sum(서울_노원) as 서울_노원, sum(서울_은평) as 서울_은평,
sum(서울_서대문) as 서울_서대문, sum(서울_마포) as 서울_마포, sum(서울_양천) as 서울_양천, sum(서울_강서) as 서울_강서, sum(서울_구로) as 서울_구로, sum(서울_금천) as 서울_금천, sum(서울_영등포) as 서울_영등포, sum(서울_동작) as 서울_동작, sum(서울_관악) as 서울_관악, sum(서울_서초) as 서울_서초, sum(서울_강남) as 서울_강남, sum(서울_송파) as 서울_송파,
sum(서울_강동) as 서울_강동, sum(인천_중구) as 인천_중구, sum(인천_동구) as 인천_동구, sum(인천_서구) as 인천_서구, sum(인천_미추홀) as 인천_미추홀, sum(인천_연수) as 인천_연수, sum(인천_남동) as 인천_남동, sum(인천_부평) as 인천_부평, sum(인천_계양) as 인천_계양, sum(인천_강화군) as 인천_강화군, sum(인천_옹진군) as 인천_옹진군, sum(부천) as 부천, sum(수원) as 수원, sum(성남) as 성남, sum(안양) as 안양, sum(광명) as 광명, sum(안산) as 안산, sum(고양) as 고양, sum(의정부) as 의정부, sum(군포) as 군포, sum(남양주) as 남양주, sum(용인) as 용인, sum(시흥) as 시흥, sum(파주) as 파주, sum(이천) as 이천, sum(평택) as 평택, sum(구리) as 구리
from (select *
from crime_2021_ex
where 범죄분류 = '절도' or 범죄분류 = '장물' or 범죄분류 = '횡령' or 범죄분류 = '배임' or 범죄분류 = '손괴' or 범죄분류 = '살인' or 범죄분류 = '강도' or 범죄분류 = '방화' or 범죄분류 = '성폭력' or 범죄분류 = '폭행' or 범죄분류 = '상해' or 범죄분류 = '협박' or 범죄분류 = '공갈' or 범죄분류 = '약취와 유인' or 범죄분류 = '체포와 감금' or 범죄분류 = '폭력행위등(손괴,강요,주거침입등)' or 범죄분류 = '폭력행위 등(단체 등의 구성,활동)' or 범죄분류 = '직무유기' or 범죄분류 = '수뢰' or 범죄분류 = '증뢰' or 범죄분류 = '도박과 복표' or 범죄분류 = '기타 음란행위' or 범죄분류 = '주거 침입' or 범죄분류 = '유기' or 범죄분류 = '교통방해' or 범죄분류 = '공무방해' or 범죄분류 = '도주와 범인 은닉' or 범죄분류 = '위증과 증거인멸' or 범죄분류 = '도로교통법' or 범죄분류 = '도로교통법(무면허운전)' or 범죄분류 = '도로교통법(사고후미조치)' or 범죄분류 = '도로교통법(음주운전)' or 범죄분류 = '도로교통법(음주측정거부)' or 범죄분류 = '특가법(도주차량)'
);
/*2021 범죄 테이블을 가로 데이터 세로로 바꾸기위해 unpivot사용하여 새로운 테이블 생성*/
create table crime_2021_ex
as
select *
from crime_sum_2021_ex
unpivot (
crime_year
for crime_area
in ("서울_종로", "서울_중구", "서울_용산", "서울_성동", "서울_광진", "서울_동대문", "서울_중랑", "서울_성북", "서울_강북","서울_도봉", "서울_노원", "서울_은평", "서울_서대문", "서울_마포", "서울_양천", "서울_강서", "서울_구로", "서울_금천","서울_영등포", "서울_동작", "서울_관악", "서울_서초", "서울_강남", "서울_송파", "서울_강동","인천_중구", "인천_동구", "인천_서구", "인천_미추홀", "인천_연수", "인천_남동", "인천_부평", "인천_계양", "인천_강화군", "인천_옹진군", "수원", "용인", "성남", "부천", "안산", "안양", "평택", "시흥", "광명", "군포", "이천", "고양", "남양주", "파주", "의정부", "구리")
);
/*2019 범죄 테이블을 가로 데이터 세로로 바꾸기위해 unpivot사용하여 새로운 테이블 생성*/
create table crime_2019_ex
as
select *
from crime_sum_2019_ex
unpivot (
crime_year
for crime_area
in ("서울_종로", "서울_중구", "서울_용산", "서울_성동", "서울_광진", "서울_동대문", "서울_중랑", "서울_성북", "서울_강북", "서울_도봉", "서울_노원", "서울_은평", "서울_서대문", "서울_마포", "서울_양천", "서울_강서", "서울_구로", "서울_금천", "서울_영등포", "서울_동작", "서울_관악", "서울_서초", "서울_강남", "서울_송파", "서울_강동", "인천_중구", "인천_동구", "인천_서구", "인천_미추홀", "인천_연수", "인천_남동", "인천_부평", "인천_계양", "인천_강화군", "인천_옹진군", "수원", "용인", "성남", "부천", "안산", "안양", "평택", "시흥", "광명", "군포", "이천", "고양", "남양주", "파주", "의정부", "구리")
);
/*2019범죄 테이블과 2021범죄 테이블 JOIN*/
create table all_crime_ex
as
select c2019.crime_area, c2019.crime_year as crime_2019, c2021.crime_year as crime_2021, c2021.crime_year - c2019.crime_year as 범죄율증감
from crime_2019_ex c2019, crime_2021_ex c2021
where c2019.crime_area = c2021.crime_area
order by 범죄율증감 desc;
/*범죄테이블과 CCTV테이블을 JOIN하기위해 컬럼명 변경*/
alter table all_crime_ex
rename column crime_area to area;
/*CCTV 한 대당 범죄율 증감을 확인하기위해 범죄율증감에서 증가카메라대수를 나눈 뒤 새 컬럼으로 추가 */
create table assignment_fin_ex
as
select cctv.area, cctv.총카메라대수2019, cctv.총카메라대수2021, cctv.증가카메라대수, crime.crime_2019, crime.crime_2021, crime.범죄율증감
from all_cctv cctv, all_crime_ex crime
where cctv.area = crime.area
order by 범죄율증감 asc;
select af.*, case when 범죄율증감 > 0 then round(범죄율증감/증가카메라대수, 2) || '▲'
when 범죄율증감 < 0 then round(범죄율증감/증가카메라대수, 2) || '▽'
else '-' || 범죄율증감
end as "CCTV 한 대당 범죄율 증감"
from assignment_fin_ex af
order by 범죄율증감 asc;
합계 재확인 :
최종 결과:
2019년 이후 수도권에서의 CCTV 카메라 설치 대수는 총 20074개 이고 2019년 이전의 CCTV갯수에서 약 80%정도의 CCTV가 추가 설치되었다는 것을 확인 할 수 있었습니다. 범죄예방을 위해 CCTV의 설치비율이 많아진 것으로 보입니다.
추가 테이블 분석 전 CCTV와 전체 범죄 건수의 테이블을 비교하였을 때는 범죄율이 줄어들어 CCTV와 범죄율이 관련이 있다고 생각하였지만, CCTV와 관련 있는 범죄들을 뽑아 다시 분석하였을 때 위와 같이 범죄율이 증가(CCTV 한 대당 4.3건 증가)하였다는 결과를 얻었습니다. 이를 토대로 현재까지는 CCTV와 범죄율은 깊게 관련이 없는 것으로 확인할 수 있었습니다.
하지만 현재 AI가 탑재된 지능형 CCTV가 개발되었고, 이 지능형 CCTV들이 범죄 발생을 즉각적으로 인지해 경찰에게 알리는 시스템이 도입 된다고 하니 앞으로는 CCTV와 범죄율의 관련이 깊어지고 범죄율도 점차 줄어들 것이라 생각됩니다.
관련 기사 : https://www.hankyung.com/society/article/2022060866237 범죄 자동으로 경찰 통보…'AI CCTV' 5000대 생긴다