빡공단31기 SQL 21강

데이터 적재  cctv, 비상벨 

@저장파일경로/파일명.sql        +f5 전체스크립트 실행

@C:\study_db/cctv.sql;
@C:\study_db/비상벨.sql;
--f5누르면 전체실행

--묶은 지역을 기준으로(group by) 전체설치되어있는 지역별 카메라설치수와  + 각 목적구분별로 설치되어있는 카메라의 수를 구할거임. sum과 사용자정의함수 
SELECT distinct
    trim(replace(replace(replace(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구')) as 지역 --중복제거,정제,trim사용하여 공백제거
    ,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설치목적구분
from cctv;

교통단속
교통정보수집
기타
다목적
생활방범
시설물관리
쓰레기단속
어린이보호
재난재해
차량방범
*/
/*
SELECT DISTINCT 설치장소유형
FROM 비상벨
GROUP BY 설치장소유형;
*/

--지역별 비상벨대수
SELECT CASE WHEN INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구') > 1 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 가져오기*/
            ELSE TRIM(REPLACE(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 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 자르기*/
            ELSE TRIM(REPLACE(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'청','')) END /*구로 시작하는 경우는 정제*/
            ;

--복습꼭하기