빡공단31기 SQL 25강

--21강에서 만들었던 sql문을 테이블ㄹ ㅗ만ㄷ르어줄거임. 
create table cctv설치현황 as
--묶은 지역을 기준으로(group by) 전체설치되어있는 지역별 카메라설치수와  + 각 목적구분별로 설치되어있는 카메라의 수를 구할거임. sum과 사용자정의함수 
SELECT 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(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구'))
;



/* 비상벨 설치현황*/
--지역별 비상벨대수
create table 비상벨설치현황 as
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 /*구로 시작하는 경우는 정제*/
            ;


select *
from 비상벨설치현황;

select *
from cctv설치현황;

--두개 조인해서 지역벌로 어디가 가장많이 설치되어 있는지 확인할것임. 각 지역별 카메라설치율,비상벨설치율 구하기.
select A.지역
    , A.전체카메라설치수
    , B.설치대수
    , ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 전체카메라설치율
    , ROUND(B.설치대수 / (A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 비상벨설치율
from cctv설치현황 A full outer join 비상벨설치현황 B
 on A.지역 = B.지역
 ;
 
 --위에꺼 카메라설치비율 가지고 구간나눠서 해당되는 평균설치 카메라수 구할거임. 
select CASE WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 50 THEN '설치미비'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 80 THEN '설치보통'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 95 THEN '설치우수'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) > 95 THEN '완전설치'
    ELSE 'CHK' END AS 설치구분
    ,TO_CHAR(SUM(A.전체카메라설치수+B.설치대수),'999,999') AS 전체설치건수
    ,TO_CHAR(ROUND(AVG(A.전체카메라설치수+B.설치대수)),'999,999') AS 평균건수
    ,COUNT(*) AS 지역수
    ,TO_CHAR(SUM(A.전체카메라설치수),'999,999') AS 카메라수
    ,TO_CHAR(SUM(B.설치대수),'999,999') AS 비상벨수
    ,ROUND(SUM(A.전체카메라설치수) / SUM(A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 카메라설치비율
    ,ROUND(SUM(B.설치대수) / SUM(A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 비상벨설치비율
from cctv설치현황 A INNER join 비상벨설치현황 B
 on A.지역 = B.지역
 GROUP BY CASE WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 50 THEN '설치미비'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 80 THEN '설치보통'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 95 THEN '설치우수'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) > 95 THEN '완전설치'
    ELSE 'CHK' END
ORDER BY 카메라설치비율 DESC
 ;