시간별, 지역별 서울시 미세먼지양 분석

[15기_강민수]

1.  주제 : 서울시 미세먼지, 초미세먼지 데이터에대한 분석 및 연구


미세먼지의 발생과 영향, 관리 문제는 꾸준히 서울시에 영향을 주었다. 서울시는  미세먼지에 대한 문제들을 해결하기 위해 다양한 수단들을 활용해 왔다. 그에대한 결과 및 미세먼지 데이터를 확인해보고자 하여 서울시 미세먼지 데이터를 조사해보기로 하였다. 단순한 체감 농도가 아닌, 실제 데이터를 가져와 보며 확인한다면 인식과 실제는 어떻게 다른지 확인도 가능할것이다.
구체적으로 2008년도 부터 2021년까지 모든 날짜별, 시간별(1시간당), 지역별(구) 미세먼지 와 초미세먼지 데이터를 가져와서 분석해보기로 하였다.




2. 데이터 출처
https://www.data.go.kr/data/15089266/fileData.do


서울시 대기질 자료 제공_2008-2011.csv
서울시 대기질 자료 제공_2012-2015.csv
서울시 대기질 자료 제공_2016-2019.csv
서울시 대기질 자료 제공_2020-2021.csv




3. 컬럼정보 : 

1. 일시: 년-월-일 시간
2. 구분: 지역구
3. 미세먼지 : 미세먼지 농도 ( ㎍/m³)
4. 초미세먼지 : 초미세먼지 농도 ( ㎍/m³)


<구분>
미세먼지 : 10㎛ 이하 미세먼지
초미세먼지: 2.5㎛ 이하 미세먼지




4. SQL로 데이터 정보 확인


select count(*) from fd_0811;                  --902530
select count(*) from fd_1215;                  --907341
select count(*) from fd_1619;                  --911540
select count(*) from fd_2021;                  --322606

create table fd as
(select * from fd_0811
union all            
select * from fd_1215
union all
select * from fd_1619
union all
select * from fd_2021);

select count(*) from fd;                        --3044017




설명: 2008년~ 2011년, 2012 년~2015년, 2016년~2019년, 2020년~2021년 파일들을 union 으로 합침
fd (finedust)라는 약칭으로 2008년부터 2021년 서울시 미세먼지 데이터를 만들어냄
fd_0811,fd_1215,fd_1619,fd_2021 데이터들의 행개수를 확인하고,
 합친 fd 파일의 데이터 행개수를 확인하여, 제대로 합쳐졌는지 확인하였다.






1) 미세먼지가 가장 많았던 지역 및 시각
select * from fd
where  미세먼지 = (select max(미세먼지) from fd);

설명: 미세먼지가 가장많았던 순간을 데이터로 추출하였다. 
미세먼지가 가장많았던 순간은 2010년 11월 12일 0시 영등포구였다.


2) 초미세먼지가 가장 많았던 지역 및 시각
select * from fd
where  초미세먼지 = (select max(초미세먼지) from fd);

설명 : 초미세먼지가 가장많았던 순간을 데이터로 추출하였다.  2011년 12월 28일 10시 서대문구 및 2013년 6월 20일 1시 서대문구로 공동1등 이었다.



 
3) 지역구별 미세먼지 합 순위

select  구분, sum(미세먼지), sum(초미세먼지)
from fd
group by 구분
order by 2 desc;

설명 : 지역별로 미세먼지를 비교해보고자, 데이터를 추출했다. 영등포 서초구 관악구 순으로 미세먼지 총량이 많았다.
미세먼지가 제일 적었던곳은 도봉구로, 미세먼지 총량은 영등포구의 90% 미만이었다. 




4) 시각별 미세먼지합 순위

select 일시, sum(미세먼지), sum(초미세먼지)
from fd
group by 일시
order by 2 desc
fetch first 10 rows only;

설명: 시간별로 미세먼지를  확인해보고자 1위부터 10위까지 추출했다. 2010년 11월 12일 0시 가 독보적인 1위로 미세먼지 총량이 많았다.  2015년 2월 23일도 만만치 않은 순위들을 보여주었다.






5) 시각별, 구역별 미세먼지합 순위

select 일시,구분, sum(미세먼지), sum(초미세먼지)
from fd
where 구분 <> '평균' 
group by 일시, 구분
having sum(미세먼지) is not null
order by 3 desc
fetch first 10 rows only;

설명 : 위에서 확인한 시간별 총량합에서 좀더 다가고자, 시간별, 그리고 구역 별 총1위부터 10위까지 추출했다.
그결과, 위에서 시간별 총량합이 1위였던 순간이 전부 탑10 안에 들었다.




6) 날짜별 미세먼지합 순위

select substr(일시,1,10), sum(미세먼지),sum(초미세먼지)
from fd
group by substr(일시,1,10)
order by 2 desc
fetch first 10 rows only;

설명 : 날짜별로 미세먼지 총량 1위부터 10위까지 추출했다. 그결과, 2015년 2월 23일이 압도적1위를 달성했고,  10위의 거의 3배가량 진했다. 날짜별로 미세먼지 총량차이가 매우 심한걸 확인할수 있었다.






7)년-월 별 미세먼지합 순위

select substr(일시,1,7), sum(미세먼지),sum(초미세먼지)
from fd
group by substr(일시,1,7)
order by 2 desc
fetch first 10 rows only;

설명 : 년월별로 미세먼지 총량 1위부터 10위까지 추출했다. 그결과, 2015년 2월, 2015년 3월이 1,2위를 달성하였다. 월별로 묶었을땐, 월별 미세먼지 총량차이가 덜 심하였다.





8) 연도별 미세먼지합

select substr(일시,1,4), sum(미세먼지),sum(초미세먼지)
from fd
group by substr(일시,1,4)
order by 2 desc
fetch first 10 rows only;

설명 : 년도별로 미세먼지 총량 1위부터 10위까지 추출했다. 그결과, 2008년 2009년 2010년 이 1,2,3위를 달성하였다.

년도별로 묶었을땐, 날짜별 미세먼지 총량차이가 덜 심하였다.




9)  월별 미세먼지 합

select substr(일시,6,2), sum(미세먼지),sum(초미세먼지)
from fd
group by substr(일시,6,2)
order by 2 desc

설명 : 월별로 미세먼지 총량 데이터를 추출했다. 3월,5월,1월이 강세를 보였고 7~9월이 약세를 보였다. 1위와 약세인 부분의 미세먼지 총량은 2배가 넘게 차이났다.






10)시간별 미세먼지합

select substr(일시,12,4), sum(미세먼지),sum(초미세먼지)
from fd
group by substr(일시,12,4)
order by 2 desc;

설명 : 시간별로 미세먼지 총량 데이터를 추출했다. 그결과, 늦은저녁 22시 21시 23시가 1,2,3위를 달성하였다.
반대로 5~7시의 새벽이 미세먼지가 적었다. 1위와 24위의 차이는 1.15배정도로 차이가 많이심하진 않았다.




11) 계절별 미세먼지합

select 계절, sum(미세먼지),sum(초미세먼지)
from (
select 미세먼지, 초미세먼지, 
       case   when substr(일시,6,2) in (03,04,05) then '봄' 
              when substr(일시,6,2) in (06,07,08) then '여름'
              when substr(일시,6,2) in (09,10,11) then '가을'
              when substr(일시,6,2) in (12,01,02) then '겨울'
              end as "계절"
from fd)
group by 계절
order by 2 desc;

설명 : 9 번의 데이터를 조금 변경하여,  계절별로 미세먼지 총량 데이터를 추출했다. 봄,겨울,가을,여름순으로 미세먼지 총량이 많았다. 봄과 여름의 미세먼지는 2배가 차이날정도로, 계절별 총 미세먼지량 차이가 심하였다.




12)요일별 미세먼지합
select to_char(to_date(substr(일시,1,10),'yyyy-mm-dd'), 'day') , sum(미세먼지),sum(초미세먼지)
from fd
group by to_char(to_date(substr(일시,1,10),'yyyy-mm-dd'), 'day')
order by 2 desc

설명: 요일별로 미세먼지 총량 데이터를 추출했다. 토요일, 금요일 순으로  미세먼지 총량이 많았다. 하지만, 바로옆에 붙어있는 일요일이 총량은 제일적었으며, 토요일 미세먼지 총량은 일요일 미세먼지 총량의 1.1배 수준이었으며, 미세먼지 총량은 요일별로 크게 차이가 나진않았다.






5. 머신러닝
위에서 추출해보았던 여러 데이터를 바탕으로 linear한 미세먼지 총량 패턴이 있지않을까 의구심이 들어, 학습 머신러닝을 구성하여 보았다. 주어진 데이터 내에서 여러 요소들을 나누어서 분석을 시도하였다.


1) 테이블 구성


create table fd2 as
select rownum as id, f.*                                                        
from fd f;


alter table fd2
add year varchar2(10);

merge into fd2 f                                                         
using (select id, substr(일시,1,4) as r1
from fd2) s                                                       
on( f.id=s.id)                                         
when matched then                                                  
update set f.year = s.r1;   



alter table fd2
add month varchar2(10);

merge into fd2 f                                                         
using (select id, substr(일시,6,2) as r1
from fd2) s                                                       
on( f.id=s.id)                                         
when matched then                                                  
update set f.month = s.r1;  



alter table fd2
add day varchar2(10);

merge into fd2 f                                                         
using (select id, substr(일시,9,2) as r1
from fd2) s                                                       
on( f.id=s.id)                                         
when matched then                                                  
update set f.day = s.r1;  



alter table fd2
add hour varchar2(10);

merge into fd2 f                                                         
using (select id, substr(일시,12, instr(일시,':')-12) as r1
from fd2) s                                                       
on( f.id=s.id)                                         
when matched then                                                  
update set f.hour = s.r1;  



alter table fd2
add 계절 varchar2(10);

merge into fd2 f                                                         
using (select id,  case when substr(일시,6,2) in (03,04,05) then '봄' 
              when substr(일시,6,2) in (06,07,08) then '여름'
              when substr(일시,6,2) in (09,10,11) then '가을'
              when substr(일시,6,2) in (12,01,02) then '겨울'
              end as r1 from fd2) s
on( f.id=s.id)                                         
when matched then                                                  
update set f.계절 = s.r1;  



alter table fd2
add 요일 varchar2(10);

merge into fd2 f                                                         
using (select id, to_char(to_date(substr(일시,1,10),'yyyy-mm-dd'), 'day' ) as r1 from fd2) s                                                       
on( f.id=s.id)                                         
when matched then                                                  
update set f.요일 = s.r1;  



alter table fd2
rename column 미세먼지 to finedust;

alter table fd2
rename column 초미세먼지 to ultrafinedust;

alter table fd2 drop column 일시;

select * from fd2 
order by 1 ;


설명 : 일시 테이블을 삭제하고 대신, 각각 연,월,일 계절, 요일 등으로 나누었으며  연,월,일 계절, 요일등을 숫자로 인식하여, 데이터값으로 계산하는 오류를 피하기 위해 전부 문자형으로 바꾸어 저장하였다.








 2) training, test용 table 생성

drop table fd_training; 

create table fd_training
as
   select *
     from fd2
     where year <2021;                                --2021년전까지 데이터 예측

drop table fd_test;

create table fd_test
as
   select *
     from fd2
     where year >= 2021;                             --2021년 테스트용

select count(*) from fd_training;                                --3000000
select count(*) from fd_test;                                    --94224

3). 연습할 table 생성

drop table settings_reg3;

create table settings_reg3                                                     
as
select *
  from table (dbms_data_mining.get_default_settings)
  where setting_name like '%GLM%';    


4) 머신러닝 작동

begin

insert into settings_reg3
 values (dbms_data_mining.algo_name,'ALGO_GENERALIZED_LINEAR_MODEL');       

insert into settings_reg3
 values (dbms_data_mining.prep_auto, 'ON');                                   


commit;
end;
/

begin
  dbms_data_mining.drop_model('MD_REG_MODEL2');                                 
end;
/


begin 

   dbms_data_mining.create_model(
      model_name            => 'MD_REG_MODEL2',                                 --모델이름
      mining_function       => dbms_data_mining.regression,                     --기계를 학습시킬방법(함수)
      data_table_name       => 'FD_TRAINING',                                   --훈련테이블 이름
      case_id_column_name   => 'ID',                                            --id열 
      target_column_name    => 'FINEDUST',                                      --target열 (결론) 
      settings_table_name   => 'SETTINGS_REG3');                                --테이블 이름
end;
/

SELECT * FROM FD_TRAINING;


5) 머신러닝 모델확인(생략가능) 

select model_name,
          algorithm,
          mining_function
  from all_mining_models
  where model_name = 'MD_REG_MODEL2';                                           --머신러닝 모델확인
  
  
select setting_name, setting_value
  from all_mining_model_settings
  where model_name = 'MD_REG_MODEL2';                                           


6) 결과확인
select attribute_name, attribute_value, round(coefficient,10),round(p_value,10)as p값   
  from table (dbms_data_mining.get_model_details_glm ('MD_REG_MODEL2'))
  order by abs(round(coefficient,10)) desc;

안타깝게도, 선형식을 세우긴했지만, 신뢰도가 낮았다. 그로인해 p값을 출력하지 않았다.
그래도 coefficient 의 절댓값을 기준으로 나열해보면, 달의 영향이 컸음을 알수있고,
시간대의 영향력도 있음을 확인할수 있었다.






select corr(finedust,model_predict_response)
from (select id,finedust,round(prediction (md_reg_model2 using *),2) model_predict_response  
from fd_test t);

학습된 3백만 데이터를 바탕으로, 44017개의 예측을 하였고, 상관관계는 0.892의 수치를 보여주었다. 어느정도 상관관계가 있게 테스트를 보았다고 할수있다.






select global_detail_name, round(global_detail_value,3)
  from
  table(dbms_data_mining.get_model_details_global(model_name =>'MD_REG_MODEL2'))
  where  global_detail_name in ('R_SQ','ADJUSTED_R_SQUARE'); 

결정계수는 낮게 나왔고, 신뢰도가 낮다고 볼수밖에없었다.


머신러닝 결론) 시각과 지역만으로 , 미세먼지양을 linear한 수식으로 표현하는데 실패했다고 볼수있다. 
미래의 총미세먼지양을 구하기 위해서는,  대기오염물질 농도, 배출량, 기상기후, 사회지리, 중국 대기질 등과 같은 여러 데이터를 추가로 구해서 학습을 해야, 정확한 학습을 할수 있다고 생각한다.


6. 파이썬 그래프 시각화

1) 지역구별 미세먼지

2) 연도별 미세먼지

전반적으로 줄어드는 추세이나,
2021년 데이터는 5월까지 밖에없어서, 2021년 데이터는 따로 들여다 보기로 하였다.
 
3) 2021년 미세먼지

1월부터 5월까지 , 일별 평균 미세먼지 및 초미세먼지 데이터를 점으로 표시하였고, 위와같은 그래프를 얻었다.

1,2 월에는 미세먼지가 적었으나, 3월 및 5월에는 심한 날들이 보였다.

흥미로운점은, 미세먼지와 초미세먼지가 비례하게 분포 하였는데, 2가지 비례 계수 추세가 보였다는 것이다.

4) 계절별 미세먼지

봄이 미세먼지가 가장많았고, 초미세먼지는 겨울이 더 많았다.

5) 월별 미세먼지

초미세먼지는 1월에 가장 심했고, 미세먼지는 3월에 가장 심했다.

9월에 초미세먼지와 미세먼지 둘다 약하였다.

6) 시간별 미세먼지

24시 근처와 12시 근처에 미세먼지가 강하게 나타나였으나,  큰차이를 보이지 않았다.