SQL 데이터 분석 - 서울시 미세먼지 분석

분석환경: Windows 10 Pro, Oracle DB, SQL Developer
참고 문헌: 홍형경 저 누구나 쉽게 SQL
데이터: 에어코리아(http://www.airkorea.or.kr) 2017년 1월 ~ 2018년 3월 서울시 미세먼지 데이터

1. fine_dust 테이블 생성
   : 서울시의 구, 측정소, 일자별 미세먼지와 초미세먼지 측정 데이터를 담을 테이블

   CREATE TABLE fine_dust (
   	gu_name		VARCHAR2(50)		NOT NULL, -- 구 이름
       mea_station		VARCHAR2(30)		NOT NULL, -- 측정소
       mea_date		DATE			NOT NULL, -- 측정일자
       pm10		NUMBER,				  -- 미세먼지 농도
       pm25		NUMBER				  -- 초미세먼지 농도
       );   

2. fine_dust_standard 테이블 생성
   : WHO 기준 미세먼지 농도 기준치 데이터를 담을 테이블

   CREATE TABLE fine_dust_standard (
       org_name        VARCHAR2(50)        NOT NULL, -- 기관명
       std_name        VARCHAR2(30)        NOT NULL, -- 미세먼지 기준
       pm10_start      NUMBER,                       -- 미세먼지 농도(시작 값)
       pm10_end        NUMBER,                       -- 미세먼지 농도(끝 값)
       pm25_start      NUMBER,                       -- 초미세먼지 농도(시작 값)
       pm25_end        NUMBER                        -- 초미세먼지 농도(끝 값)
       );
    ALTER TABLE fine_dust_standard
    ADD CONSTRAINT fine_dust_standard_pk PRIMARY KEY(org_name, std_name);

3. 기존 데이터 자료 입력
   : fine_dust 테이블 11,375 건, fine_dust_standard 테이블 4건.
   

4. 월간 미세먼지와 초미세먼지의 최소, 최대, 평균값

   SELECT TO_CHAR(a.mea_date, 'YYYY-MM') months -- 문자 데이터로 변환(월별 집계)
         ,ROUND(MIN(a.pm10), 0) pm10_min
         ,ROUND(MAX(a.pm10), 0) pm10_max
         ,ROUND(AVG(a.pm10), 0) pm10_avg
         ,ROUND(MIN(a.pm25), 0) pm25_min
         ,ROUND(MAX(a.pm25), 0) pm25_max
         ,ROUND(AVG(a.pm25), 0) pm25_avg	      -- ROUND 함수로 소숫점 제거
     FROM fine_dust a
    WHERE pm10 > 0
      AND pm25 > 0                               -- 결측치 제거
   GROUP BY TO_CHAR(a.mea_date, 'YYYY-MM')       -- SELECT 절에도 명시
   ORDER BY 1;                                   -- 월별 오름차순 정렬

5. 월평균 미세먼지 현황
   : 두 테이블 조인하여 활용하기

   SELECT a.months
         ,a.pm10_avg
         ,(SELECT b.std_name
             FROM fine_dust_standard b
            WHERE b.org_name = 'WHO'
              AND a.pm10_avg BETWEEN b.pm10_start
                                 AND b.pm10_end
          ) "미세먼지 상태"
         ,a.pm25_avg
         ,(SELECT b.std_name
             FROM fine_dust_standard b
            WHERE b.org_name = 'WHO'
              AND a.pm25_avg BETWEEN b.pm25_start 
                                 AND b.pm25_end
          ) "초미세먼지 상태"
     FROM ( -- 월평균 미세먼지 농도 서브쿼리
           SELECT TO_CHAR (a.mea_date, 'YYYY-MM') months 
                 ,ROUND(AVG(a.pm10), 0) pm10_avg
                 ,ROUND(AVG(a.pm25), 0) pm25_avg
             FROM fine_dust a
            WHERE a.pm10 > 0
              AND a.pm25 > 0
            GROUP BY TO_CHAR(mea_date, 'YYYY-MM')
           ) a
   ORDER BY 1;