[빡공단 28기] 베어유 하루 10분 챌린지_SQL 2일차

21강. 실전 데이터 분석 - 우리 집 주변은 안전할까?! - 2

공공데이터를 활용하여 우리 동네 주변은 안전한지 동네 중에서 가장 안전한 곳이 어딘지 확인해보자

---

먼저 다음을 통해 공공데이터를 불러온다

@파일경로/cctv.sql;
@파일경로/비상벨.sql;

 

정형화가 되어있지 않기 때문에 관리기관을 정재하기 위해 다음과 같이 작성한다.

SELECT DISTINCT
		TRIM(REPLACE(REPLACE(REPLACE (관리기관명, '서울특별시', ''), '구청', '구'), '경찰서', '구'))
FROM cctv
;

SELECT TRIM(REPLACE(REPLACE(REPLACE (관리기관명, '서울특별시', ''), '구청', '구'), '경찰서', '구')) AS 지역
	, SUM(카메라대수) AS 전체카메라설치수
    , SUM(CASE WHEN 설치목적구분 = '어린이보호' THEN 카메라대수 ELSE 0 END) AS 어린이보호시설
    , SUM(CASE WHEN 설치목적구분 = '시설물관리' THEN 카메라대수 ELSE 0 END) AS 시설물관리
	, SUM(CASE WHEN 설치목적구분 = '쓰레기단속' THEN 카메라대수 ELSE 0 END) AS 쓰레기단속
	, SUM(CASE WHEN 설치목적구분 = '재난재해' THEN 카메라대수 ELSE 0 END) AS 재난재해
	, SUM(CASE WHEN 설치목적구분 = '기타' THEN 카메라대수 ELSE 0 END) AS 기타
	, SUM(CASE WHEN 설치목적구분 = '생활방범' THEN 카메라대수 ELSE 0 END) AS 생활방범
	, SUM(CASE WHEN 설치목적구분 = '교통정보수집' THEN 카메라대수 ELSE 0 END) AS 교통정보수집
	, SUM(CASE WHEN 설치목적구분 = '교통단속' THEN 카메라대수 ELSE 0 END) AS 교통단속
    , SUM(CASE WHEN 설치목적구분 = '다목적' THEN 카메라대수 ELSE 0 END) AS 다목적
FROM cctv
GROUP BY TRIM(REPLACE(REPLACE(REPLACE (관리기관명, '서울특별시', ''), '구청', '구'), '경찰서', '구'))
;

 

 

다음은 비상벨이다

SELECT DISTINCT
/* 정제대상 */		REPLACE(REPLACE (관리기관명, '서울시', '')관리기관명, '서울특별시', '') 정제대상
/* STEP 1 */	, INSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '구') 최초구의 위치
/* STEP 2 */
			, CASE WHEN INSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '구') > 1
            	THEN TRIM(SUBSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', '')
                		, 1, INSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '구')))
                ELSE TRIM(SUBSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '청', '') END AS 지역chk
                FROM 비상벨;

 

다시 쿼리를 잘 다듬어보자

SELECT CASE WHEN INSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '구') > 1    -- 구가 시작하는 최초의 위치 > 1
            THEN TRIM(SUBSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', '')	       
                	, 1, INSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '구')))  -- 처음부터 구의 위치까지 문자열 자르기
            ELSE TRIM(SUBSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '청', '') END AS 지역  -- 구로 시작하는 경우는 정체
		, COUNT(*) AS 설치대수
        , SUM(CASE WHEN 설치장소유형 = '가로변' THEN 1 ELSE 0 END) AS 설치위치_가로변
        , SUM(CASE WHEN 설치장소유형 = '기타' THEN 1 ELSE 0 END) AS 설치위치_기타
        , SUM(CASE WHEN 설치장소유형 = '공원' THEN 1 ELSE 0 END) AS 설치위치_공원
        , SUM(CASE WHEN 설치장소유형 = '화장실' THEN 1 ELSE 0 END) AS 설치위치_화장실
        , SUM(CASE WHEN 설치장소유형 = '건물' THEN 1 ELSE 0 END) AS 설치위치_건물
        , SUM(CASE WHEN 설치장소유형 = '주차장' THEN 1 ELSE 0 END) AS 설치위치_주차장
FROM 비상벨
GROUP BY CASE WHEN INSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '구') > 1
            THEN TRIM(SUBSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', '')
                	, 1, INSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '구')))
            ELSE TRIM(SUBSTR(REPLACE(REPLACE (관리기관명, '서울시', ''), '서울특별시', ''), '청', '') END

 

---