(SQL 포트폴리오) 대기오염이 질병에 미치는 영향

[7기_백광흠]

대기오염이 질병에 미치는 영향

                                                                                                                                                       백 광 흠

1.질문

1. 대기오염이 질병 발생 건수에 유의미한 영향을 미칠까?

2. 미세먼지가 많이 발생하는 달은 언제일까?

2.참고 Data

서울시 월별 평균 대기오염정보.csv

시군구 지역코드.csv

실제진료정보_감기_시군구.csv

실제진료정보_눈병_시군구.csv

실제진료정보_천식_시군구.csv

 +실제진료정보_피부염_시군구.csv      ( 첨부 용량 제한 ..)

   

 - 출처 : 서울 열린데이터광장  http://data.seoul.go.kr/

3.테이블 생성 스크립트

--대기정보 (ATMOSPHERE)

CREATE TABLE atmosphere

(day VARCHAR2(10), gu_name VARCHAR2(10), /*년월  ex) 201705*/

no2 number(10,4),  /*이산화질소*/

ozon NUMBER(10,4),  /*오존농도*/

co NUMBER(10,4),  /*일산화탄소*/

so2 NUMBER(10,4) ,  /*아황산가스*/

fine_dust NUMBER(10),  /*미세먼지*/

cho_fine_dust NUMBER(10)  /*초미세먼지*/ );  --- 월별, 구별 평균 대기정보. 11120건

DELETE

FROM ATMOSPHERE

    WHERE SUBSTR(day,1,4) NOT IN ('2014','2015','2016'); 

--- 2014, 2015, 2016 년도를 제외한 데이터 삭제. 1426건

DELETE

           FROM ATMOSPHERE

    WHERE gu_name NOT LIKE '%구';   --- gu_name이 ‘구’로 끝나는 데이터만 남기고 삭제. 901건

DELETE

           FROM ATMOSPHERE

    WHERE gu_name IS NULL OR day IS NULL;  --- null값을 삭제. 900건

--시군구 지역코드 (loc)

CREATE TABLE loc

(gu_num NUMBER(10), gu_name VARCHAR2(20));   --- 전국 시군구 번호, 이름 데이터. 284건

DELETE FROM loc

WHERE gu_num > 11740;   --- 서울이 데이터만 남기고 삭제. 64건

DELETE FROM loc

WHERE gu_num IS NULL OR gu_name IS NULL OR gu_name NOT LIKE '%구';  

--- null값과 비정상적 데이터 삭제. 25건

--감기 (cold)

CREATE TABLE cold2

(day VARCHAR2(10), gu_num NUMBER(10), cnt NUMBER(10));   --- 전국 일별, 구별 감기 데이터. 276793건

DELETE FROM cold2

           WHERE gu_num > 11740;     --- 서울 데이터만 남기고 삭제. 27401건

DELETE FROM cold2

           WHERE gu_num IS NULL OR day IS NULL OR cnt IS NULL;   --- null값을 삭제. 27400건

(ORA-24344 에러 발생하므로 필수)

CREATE TABLE COLD

AS

SELECT substr(day,1,6) day, gu_num, SUM(cnt) cnt

           FROM cold2

    GROUP BY substr(day,1,6), gu_num;      --- 월별, 구별 감기 데이터. 900건

--눈병 (eye)

CREATE TABLE eye2

(day VARCHAR2(10), gu_num NUMBER(10), cnt NUMBER(10)); --- 전국 일별, 구별 눈병 데이터. 275800건

DELETE FROM eye2

           WHERE gu_num > 11740;     --- 서울 데이터만 남기고 삭제. 27401건

DELETE FROM EYE2

           WHERE gu_num IS NULL OR day IS NULL OR cnt IS NULL;       --- null값을 삭제. 27400건

CREATE TABLE eye

AS

SELECT substr(day,1,6) day, gu_num, SUM(cnt) cnt

           FROM eye2

    GROUP BY substr(day,1,6), gu_num;      --- 월별, 구별 눈병 데이터. 900건

--천식 (asthma)

CREATE TABLE asthma2

(day VARCHAR2(10), gu_num NUMBER(10), cnt NUMBER(10));   --- 전국 일별, 구별 감기 데이터.  275758건

DELETE FROM asthma2

           WHERE gu_num > 11740;      --- 서울 데이터만 남기고 삭제. 27401건

DELETE FROM asthma2

           WHERE gu_num IS NULL OR day IS NULL OR cnt IS NULL;      --- null값을 삭제. 27400건

CREATE TABLE asthma

AS

SELECT substr(day,1,6) day, gu_num, SUM(cnt) cnt

           FROM asthma2

    GROUP BY substr(day,1,6), gu_num;      --- 월별, 구별 천식 데이터. 900건

--피부염 (dermatitis)

CREATE TABLE dermatitis2

(day VARCHAR2(10), gu_num NUMBER(10), cnt NUMBER(10));    --- 전국 구별, 일별 피부염 데이터 276137건

DELETE FROM dermatitis2

           WHERE gu_num > 11740;    ---서울시 데이터만 남기고 삭제. 27401건

DELETE FROM dermatitis2

           WHERE gu_num IS NULL OR day IS NULL OR cnt IS NULL;     --- null값을 삭제. 27400건

CREATE TABLE dermatitis

AS

SELECT substr(day,1,6) day, gu_num, SUM(cnt) cnt

           FROM dermatitis2

    GROUP BY substr(day,1,6), gu_num;      --- 월별, 구별 피부염 데이터. 900건

--데이터 종합 (result)

CREATE TABLE result

AS

SELECT at.DAY, l.GU_NUM, l.GU_NAME, at.no2, at.OZON, at.co, at.SO2, at.FINE_DUST, at.CHO_FINE_DUST,

        c.CNT "감기 건수", e.cnt "눈병 건수", a.cnt "천식 건수", d.CNT "피부염 건수",

      NTILE(5) OVER (ORDER BY c.cnt desc) "감기 등급" , NTILE(5) OVER (ORDER BY e.cnt desc) "눈병 등급" ,

      NTILE(5) OVER (ORDER BY a.cnt desc) "천식 등급",

      NTILE(5) OVER (ORDER BY d.cnt desc) "피부병 등급" , NTILE(5) OVER (ORDER BY at.no2 desc) "이산화질소 등급", 

     NTILE(5) OVER (ORDER BY at.ozon DESC) "오존 등급",

      NTILE(5) OVER (order BY at.co desc) "아황산가스 등급" , NTILE(5) OVER (ORDER BY at.so2) "일산화탄소 등급",

      NTILE(5) OVER (ORDER BY at.fine_dust) "미세먼지 등급",

      NTILE(5) OVER (ORDER BY at.cho_fine_dust) "초미세먼지 등급",

      abs(NTILE(5) OVER (ORDER BY c.cnt desc)-NTILE(5) OVER (ORDER BY at.no2 desc)) "감기-이산화질소",

      abs(NTILE(5) OVER (ORDER BY c.cnt desc)-NTILE(5) OVER (ORDER BY at.ozon DESC)) "감기-오존",

      abs(NTILE(5) OVER (ORDER BY c.cnt desc)- NTILE(5) OVER (ORDER BY at.co desc)) "감기-아황산가스",

      abs(NTILE(5) OVER (ORDER BY c.cnt desc)- NTILE(5) OVER (ORDER BY at.so2)) "감기-일산화탄소",

      abs(NTILE(5) OVER (ORDER BY c.cnt desc)- NTILE(5) OVER (ORDER BY at.fine_dust)) "감기-미세먼지",

      abs(NTILE(5) OVER (ORDER BY c.cnt desc)- NTILE(5) OVER (ORDER BY at.cho_fine_dust)) "감기-초미세먼지",

      abs(NTILE(5) OVER (ORDER BY e.cnt desc)- NTILE(5) OVER (ORDER BY at.no2 desc)) "눈병-이산화질소",

      abs(NTILE(5) OVER (ORDER BY e.cnt desc)- NTILE(5) OVER (ORDER BY at.ozon DESC)) "눈병-오존",

      abs(NTILE(5) OVER (ORDER BY e.cnt desc)- NTILE(5) OVER (ORDER BY at.co desc)) "눈병-아황산가스",

      abs(NTILE(5) OVER (ORDER BY e.cnt desc)- NTILE(5) OVER (ORDER BY at.so2)) "눈병-일산화탄소",

      abs(NTILE(5) OVER (ORDER BY e.cnt desc)- NTILE(5) OVER (ORDER BY at.fine_dust)) "눈병-미세먼지",

      abs(NTILE(5) OVER (ORDER BY e.cnt desc)- NTILE(5) OVER (ORDER BY at.cho_fine_dust)) "눈병-초미세먼지",

      abs(NTILE(5) OVER (ORDER BY a.cnt desc)- NTILE(5) OVER (ORDER BY at.no2 desc)) "천식-이산화질소",

      abs(NTILE(5) OVER (ORDER BY a.cnt desc)- NTILE(5) OVER (ORDER BY at.ozon DESC)) "천식-오존",

      abs(NTILE(5) OVER (ORDER BY a.cnt desc)-NTILE(5) OVER (order BY at.co desc)) "천식-아황산가스",

      abs(NTILE(5) OVER (ORDER BY a.cnt desc)- NTILE(5) OVER (ORDER BY at.so2)) "천식-일산화탄소",

      abs(NTILE(5) OVER (ORDER BY a.cnt desc)-  NTILE(5) OVER (ORDER BY at.fine_dust)) "천식-미세먼지",

      abs(NTILE(5) OVER (ORDER BY a.cnt desc)- NTILE(5) OVER (ORDER BY at.cho_fine_dust)) "천식-초미세먼지",

      abs( NTILE(5) OVER (ORDER BY d.cnt desc)- NTILE(5) OVER (ORDER BY at.no2 desc)) "피부병-이산화질소",

      abs( NTILE(5) OVER (ORDER BY d.cnt desc)- NTILE(5) OVER (ORDER BY at.ozon DESC)) "피부병-오존",

      abs( NTILE(5) OVER (ORDER BY d.cnt desc)- NTILE(5) OVER (order BY at.co desc)) "피부병-아황산가스",

      abs( NTILE(5) OVER (ORDER BY d.cnt desc)- NTILE(5) OVER (ORDER BY at.so2)) "피부병-일산화탄소",

      abs( NTILE(5) OVER (ORDER BY d.cnt desc)- NTILE(5) OVER (ORDER BY at.fine_dust)) "피부병-미세먼지",

      abs( NTILE(5) OVER (ORDER BY d.cnt desc)-NTILE(5) OVER (ORDER BY at.cho_fine_dust)) "피부병-초미세먼지"

    FROM ATMOSPHERE at, LOC l, COLD c, EYE e, asthma a, dermatitis d

    WHERE at.GU_NAME = l.GU_NAME

          AND c.GU_NUM = l.GU_NUM AND c.day = at.DAY

        AND e.GU_NUM = l.GU_NUM AND e.DAY = at.DAY

        AND a.gu_num = l.GU_NUM AND a.day = at.day

        AND d.GU_NUM = l.GU_NUM AND d.DAY = at.day

           ORDER BY at.day;

--대기오염~질병 연관관계 (result2)  [ abs( 질병건수등급 – 오염등급 ) ]

CREATE TABLE result2

AS

SELECT ROUND(AVG("감기-이산화질소"),3)"감기-이산화질소", ROUND(AVG("감기-오존"),3)"감기-오존",

ROUND(AVG("감기-아황산가스"),3)"감기-아황산가스",ROUND(AVG("감기-일산화탄소"),3)"감기-일산화탄소",

ROUND(AVG("감기-미세먼지"),3)"감기-미세먼지", ROUND(AVG("감기-초미세먼지"),3)"감기-초미세먼지",

ROUND(AVG("눈병-이산화질소"),3)"눈병-이산화질소", ROUND(AVG("눈병-오존"),3)"눈병-오존",

ROUND(AVG("눈병-아황산가스"),3)"눈병-아황산가스", ROUND(AVG("눈병-일산화탄소"),3)"눈병-일산화탄소",

ROUND(AVG("눈병-미세먼지"),3)"눈병-미세먼지", ROUND(AVG("눈병-초미세먼지"),3)"눈병-초미세먼지",

ROUND(AVG("천식-이산화질소"),3)"천식-이산화질소", ROUND(AVG("천식-오존"),3)"천식-오존",

ROUND(AVG("천식-아황산가스"),3)"천식-아황산가스", ROUND(AVG("천식-일산화탄소"),3)"천식-일산화탄소",

ROUND(AVG("천식-미세먼지"),3)"천식-미세먼지", ROUND(AVG("천식-초미세먼지"),3)"천식-초미세먼지",

ROUND(AVG("피부병-이산화질소"),3)"피부병-이산화질소", ROUND(AVG("피부병-오존"),3)"피부병-오존",

ROUND(AVG("피부병-아황산가스"),3)"피부병-아황산가스",ROUND(AVG("피부병-일산화탄소"),3)"피부병-일산화탄소",

ROUND(AVG("피부병-미세먼지"),3)"피부병-미세먼지", ROUND(AVG("피부병-초미세먼지"),3)"피부병-초미세먼지"

FROM result;

 

4.분석쿼리

--1. 대기오염이 질병 발생 건수에 유의미한 영향을 미칠까?

--1.1 질병-대기오염 전체 연관도 순위 출력

SELECT item "질병-대기오염", cnt "연관도", RANK() OVER (ORDER BY cnt asc) 순위

FROM result2

unpivot(cnt FOR item IN(

"감기-이산화질소", "감기-오존", "감기-아황산가스", "감기-일산화탄소", "감기-미세먼지", "감기-초미세먼지",

"눈병-이산화질소", "눈병-오존", "눈병-아황산가스", "눈병-일산화탄소", "눈병-미세먼지", "눈병-초미세먼지",

"천식-이산화질소", "천식-오존", "천식-아황산가스", "천식-일산화탄소", "천식-미세먼지", "천식-초미세먼지",

"피부병-이산화질소", "피부병-오존", "피부병-아황산가스", "피부병-일산화탄소", "피부병-미세먼지", "피부병-초미세먼지"));

--1.2 질병 별 가장 영향을 끼치는 요인 2위까지 출력

SELECT substr("질병-대기오염",1,INSTR("질병-대기오염",'-')-1) "질병"

,substr("질병-대기오염",INSTR("질병-대기오염",'-')+1,length("질병-대기오염")) "대기오염", "연관 순위"

FROM(SELECT "질병-대기오염",RANK() OVER(PARTITION BY substr("질병-대기오염",1,INSTR("질병-대기오염",'-')-1) ORDER BY 연관도 ) "연관 순위"

           FROM (SELECT item "질병-대기오염", cnt "연관도", RANK() OVER (ORDER BY cnt asc) 순위

FROM result2

unpivot(cnt FOR item IN(

"감기-이산화질소", "감기-오존", "감기-아황산가스", "감기-일산화탄소", "감기-미세먼지", "감기-초미세먼지",

"눈병-이산화질소", "눈병-오존", "눈병-아황산가스", "눈병-일산화탄소", "눈병-미세먼지", "눈병-초미세먼지",

"천식-이산화질소", "천식-오존", "천식-아황산가스", "천식-일산화탄소", "천식-미세먼지", "천식-초미세먼지",

"피부병-이산화질소", "피부병-오존", "피부병-아황산가스", "피부병-일산화탄소", "피부병-미세먼지", "피부병-초미세먼지"))))

WHERE "연관 순위" <= 2 ;

--2. 미세먼지가 많이 발생하는 달은 언제일까?

SELECT SUBSTR(day,5,2)월, ROUND(AVG(fine_dust),2) "평균미세먼지",
   round((AVG(fine_dust)-NVL(LAG(AVG(fine_dust),1) OVER (ORDER BY SUBSTR(day,5,2)),47.28))/
   NVL(LAG(AVG(fine_dust),1) OVER (ORDER BY SUBSTR(day,5,2)),47.28)*100,2) "전월대비 상승률",
   RANK() OVER(ORDER BY ROUND(AVG(fine_dust),2) desc) 순위
      FROM result
      group BY SUBSTR(day,5,2)
      ORDER BY 월;

   

5.결과화면

--1. 대기오염이 질병 발생 건수에 유의미한 영향을 미칠까?

1.1 질병-대기오염 전체 연관도 순위 출력

          

             * 연관도 : [대기오염 등급 – 질병 발생건수 등급]의 절대값. (최소 0 ~ 최대 4)

0에 가까울수록 대기오염의 정도와 질병 발생건수가 비례함.

    1.2 질병 별 가장 영향을 끼치는 요인 2위까지 출력               

--2. 미세먼지가 많이 발생하는 달은 언제일까?

           

6.결론

--1. 대기오염이 질병 발생 건수에 유의미한 영향을 미칠까?

1.     질병과 대기오염의 연관도는 0~4의 값을 갖는데 모든 연관도가 2 이하의 값을 가졌다. (0에 가까울수록 영향↑)

이는 대기오염 농도와 질병 발생건수가 비례하고, 대기오염이 질병 발생 건수에 유의미한 영향을 끼친다고 볼 수

있다

2.     분석 전 미세먼지와 천식, 감시의 연관관계가 높을 것으로 예상했지만, 의외로 (초)미세먼지가 감기, 천식에 많은 영향을 미치지 않는 결과가 나타났다. 이 결과를 통해 서울 시민들이 미세먼지 관련 이슈에 반응하여 미세먼지 농도가 높은 날은 야외활동 자제 및 마스크 착용과 같은 대비를 했을 수도 있다는 생각을 해볼 수 있다.

      (대기오염 흡입에 대한 데이터가 아닌, 대기중의 오염 농도를 활용한 결과이므로.) 

3.   서울 시민들이 비교적 많은 이슈가 된 미세먼지에 대한 대비는 하지만, 별로 이슈가 되지 않는 아황산가스 및 이

            산화질소, 오존 등에 대한 대비가 잘 되어있는지 생각해 볼 필요가 있다.

 

        #참고

대기오염	인체에 미치는 영향
이산화질소	눈, 코 등의 점막에서 만성 기관지염, 호흡기 질환, 천식, 폐렴, 폐출혈, 폐수종의 발병으로까지 발전가능.
오존	가슴의 통증, 기침, 메스꺼움, 목 자극, 소화 불량 등의 증상을 유발하고 기관지염, 심장질환, 폐기종 및 천식을 악화, 폐활량을 감소 시키고 눈의 기능에 영향을 미친다. 오존에 의해 각질층 속의 불포화지방산이 산화되고 차단 기능이 손상돼 피부염을 일으킬 가능성이 있다
아황산가스	인체의 점막을 자극하며, 고농도를 흡입하면 콧물, 담, 기침 등이 나오고 호흡곤란을 초래한다. 이산화황 흡입을 통해 기관지염, 폐수종, 폐렴 등에 걸릴 가능성이 있다.
일산화탄소	혈액순환 기능 중 산소의 운반기능을 저하시키며, 고농도의 일산화탄소는 유독성이 있어 건강한 사람에게도 치명적인 해를 입힌다.
미세먼지	천식과 같은 호흡기계 질병을 악화시키고, 폐 기능의 저하를 초래한다
초미세먼지	감기나 천식, 기관지염 등의 호흡기 질환을 비롯하여 심혈관 질환, 안구 질환, 피부 질환 등 각종 질병의 발병 확률을 높인다.

                                                                 -출처 : 환경부 http://www.me.go.kr/mamo/web/index.do?menuId=587

  --2. 미세먼지가 많이 발생하는 달은 언제일까?

1.     6~10월은 미세먼지 농도가 30대를 유지하며 비교적 쾌적한 상태를 유지한다.

2.     11월부터 1월까지 8~12%의 미세먼지 상승률을 보여주며 43~52의 미세먼지 농도를 보여준다.

3.     2월은 전월 대비 19%라는 높은 상승률을 보여주며 3월달까지 올랐다가 9월달까지 꾸준히 감소한다. 특히 6월에는 전월 대비 27%의 급격한 감소율을 보여준다.

4.     분석결과를 통해 미세먼지는 봄, 겨울, 가을, 여름 순으로 높다는 것을 알 수 있다.

5.     여름인 6~9월까지는 꾸준한 미세먼지 감소율을 보여주지만 10월부터는 3월까지는 꾸준한 상승률을 보인다.

이는 여름에 발생하는 태풍의 영향과 남동계절풍이 주된 원인이라고 추론할 수 있다.

6.     미세먼지를 발생시키는 국내요인(교통,공사,시설물)의 급격한 변화가 없음에도 불구하고 계절별로 많은 차이를   

보이는 것은 “봄-겨울에 부는 북서계절풍, 편서풍으로 인해 중국의 영향을 많이 받는다”는 추론할 수 있다.

7.     즉, 미세먼지는 봄, 겨울, 가을, 여름 순으로 높고 계절풍으로 인한 국외요인의 영향을 상당히 받는다.

 

       #참고

 

  

  

7.연습문제

    문제1. 월,월별 감기 발생건수, 전월대비 상승률, 감기건수 순위를 출력 하시오.
  

     

  SELECT SUBSTR(day,5,2)월, ROUND(AVG("감기 건수"),2) "평균 감기건수", 

       round((AVG("감기 건수") - NVL(LAG(AVG("감기 건수"),1) OVER (ORDER BY SUBSTR(day,5,2)),107540.05))

       /NVL(LAG(AVG("감기 건수"),1) OVER (ORDER BY SUBSTR(day,5,2)),107540.05),3)*100 "상승률",

       RANK() OVER(ORDER BY AVG("감기 건수") desc) 순위

  FROM result

  group BY SUBSTR(day,5,2)

  ORDER BY 월;

(SQL 포트폴리오) 대기오염이 질병에 미치는 영향_7기 백광흠.docx