SQL STUDY(교통사고 데이터)
Data 출처
https://www.ktdb.go.kr/www/index.do
1. 연대별 발생한 사고 건수
2. 연대별 발생한 사고 건수와 사고별 사망자 수 JOIN
* (해양 사고는 사망자 수 집계가 안되었습니다.)
3. 분석 결론
<해양 사고>
- 지속적으로 증가(선박 수의 증가가 원인으로 생각됨)
<항공 사고>
- 지속적으로 증가(항공편의 증가가 원인으로 생각됨)
- 사망자 수는 감소(감소의 원인은 안전 설비 고도화로 추정)
<도로 교통사고>
- 90년대까지 증가 추세에서 2000년대 넘어오면서 감소(차량의 안전장치 및 도로 교통 설비의 고도화로 추정)
- 사망자 수도 발생건수와 대체로 비례
<철도 사고>
- 2000년대까지 증가 추세에서 2010년대 들어 감소(철도 안전 설비 증가로 추정)
- 사망자 수는 2000년대부터 감소 추세(여러 사건 사고가 생기며 경각심을 갖고 안전 설비 점검 등을 원인으로 추정)
Check Point
* join후 결과에 null값 확인
* 다양한 data를 들여다보면 어떤 피처 값이 주요하고 가설을 세울때 유의미한 인사이트 도출 가능
-> EDA의 중요성
------------------------------------------------------------------------------------------------------
사용된 쿼리
create table sea_accident(
year varchar2(30) not null, -- 사고 연도
total number not null, -- 총 해양 사고수
type_passeneger number, -- 여객선 사고수
type_cargo number, -- 화물선 사고수
type_fishing number, -- 어선 사고수
type_tanker number, -- 유조선 사고수
type_tugboat number, -- 예선 사고수
type_barge number, -- 부선 사고수
type_leisure number, -- 레저 기구 사고수
type_etc number -- 기타 사고수
);
ALTER TABLE sea_accident
ADD CONSTRAINT sea_accident_pk PRIMARY KEY (year);
---------------------------------------------------------
create table aviation_accident(
year number not null, -- 사고 연도
total number, -- 발생 건수
died_num number -- 사망자 수
);
ALTER TABLE aviation_accident
ADD CONSTRAINT aviation_accident_pk PRIMARY KEY (year);
-----------------------------------------------------------------
create table road_accident(
year number not null, -- 사고 연도
nationwide_num number, -- 전국 발생 건수
nationwide_died_num number, -- 전국 사망자 수
nationwide_injured_num number, -- 전국 부상자 수
highway_num number, -- 고속도로 발생 건수
highway_died_num number, -- 고속도로 사망자 수
highway_injured_num number -- 고속도로 부상자 수
); -- 2004년 까지는 전국 수치에 고속도로 포함, 2005년부터 고속도로 미포함
ALTER TABLE road_accident
ADD CONSTRAINT road_accident_pk PRIMARY KEY (year);
-----------------------------------------------------------------
create table rail_accident(
year number not null, -- 사고 연도
total_accident number, -- 총 사고 건수
train_accident number, -- 열차 사고
crosswalk_accident number, -- 건널목 사고
railway_accident number, -- 철도교통사상사고
etc_accident number, -- 기타 사고
total_casualties number, -- 총 사상자 수
total_died number, -- 총 사망자 수
total_wounded number, -- 총 부상자 수
train_casualties number, -- 여객 사상사고 총 사상자 수
train_died number, -- 여객 사상사고 총 사망자 수
train_wounded number, -- 여객 사상사고 총 부상자 수
nonpassenger_casualties number, -- 공중 사상사고 총 사상자 수
nonpassenger_died number, -- 공중 사상사고 총 사망자 수
nonpassenger_wounded number, -- 공중 사상사고 총 부상자 수
work_casualties number, -- 직무 사상사고 총 사상자 수
work_died number, -- 직무 사상사고 총 사상자 수
work_wounded number -- 직무 사상사고 총 사상자 수
);
ALTER TABLE rail_accident
ADD CONSTRAINT rail_accident_pk PRIMARY KEY(year);
-----------------------------------------------------------------
select S.year, S.total Sea_num, A.total Aviation_num from sea_accident S
left join aviation_accident A on S.year = A.year;
select R.R_ACC, RA.total_accident from
(select year, case
when year between 2005 and 2018 then nationwide_num + highway_num
when year between 1980 and 2004 then nationwide_num
end R_ACC
from road_accident) R
left join rail_accident RA on R.year = RA.year;
select year, case
when year between 2005 and 2018 then nationwide_num + highway_num
when year between 1980 and 2004 then nationwide_num
end R_ACC
from road_accident;
-- 2004년 까지는 전국 수치에 고속도로 포함, 2005년부터 고속도로 미포함.
------------------------------------------------------------------------------
select
aa.year, aa.sea_num, aa.aviation_num, bb.road_num, bb.rail_num
from
(
select S.year, S.total Sea_num, A.total Aviation_num from sea_accident S
left join aviation_accident A on S.year = A.year
) AA
left join
(
select R.year, R.R_ACC road_num, RA.total_accident rail_num
from
(select year, case
when year between 2005 and 2018 then nationwide_num + highway_num
when year between 1980 and 2004 then nationwide_num
end R_ACC
from road_accident) R
left join rail_accident RA on R.year = RA.year
) BB on AA.year = BB.year;
------------해당 자료로 traffic_accident 테이블 생성--------------------
create table traffic_accident(
year varchar2(10) not null, -- 사고 연도
sea_num number, -- 해양 사고 건수
aviation_num number, -- 항공 사고 건수
road_num number, -- 도로 교통사고 건수
rail_num number -- 철도 사고 건수
);
ALTER TABLE traffic_accident
ADD CONSTRAINT traffic_accident_pk PRIMARY KEY(year);
-----------------------------------------------------------------
select case
when year between 1980 and 1989 then '1980년대'
when year between 1990 and 1999 then '1990년대'
when year between 2000 and 2009 then '2000년대'
when year between 2010 and 2019 then '2010년대'
end years
, sum(aviation_num) AS "항공 사고"
, sum(sea_num) AS "해양 사고"
, sum(road_num) AS "도로 교통사고"
, sum(rail_num) AS "철도 사고"
from traffic_accident
where 1=1
group by case
when year between 1980 and 1989 then '1980년대'
when year between 1990 and 1999 then '1990년대'
when year between 2000 and 2009 then '2000년대'
when year between 2010 and 2019 then '2010년대'
end
order by years asc;
-----------------------------------------------------------------
select
A.year, A.die_num aviation_DIED , bb.road_died, bb.rail_died
from
aviation_accident A
left join
(
select R.year, R.R_DIED road_died, RA.total_died rail_died
from
(select year, case
when year between 2005 and 2018 then nationwide_died_num + highway_died_num
when year between 1980 and 2004 then nationwide_died_num
end R_DIED
from road_accident) R
left join rail_accident RA on R.year = RA.year
) BB on A.year = BB.year;
---------------- 해양 사고 제외 연도별 사망자 수 ------------------------
create table traffic_died(
year varchar2(10) not null, -- 사고 연도
aviation_died number, -- 항공 사고 사망자 수
road_died number, -- 도로 교통사고 사망자 수
rail_died number -- 철도 사고 사망자 수
);
ALTER TABLE traffic_died
ADD CONSTRAINT traffic_died_PK PRIMARY KEY(year);
-------------------------------------------------------------------
select A.year, A.sea_num, a.aviation_num, d.aviation_died,
a.rail_num, d.rail_died, a.road_num, d.road_died
from traffic_accident A
left join traffic_died D
on trim(A.year) = D.year
order by a.year;
------------ null 값 발생, year에 공백 확인, trim 사용----------
select case
when year between 1980 and 1989 then '1980년대'
when year between 1990 and 1999 then '1990년대'
when year between 2000 and 2009 then '2000년대'
when year between 2010 and 2019 then '2010년대'
end years
, sum(sea_num) AS "해양 사고"
, sum(aviation_num) AS "항공 사고"
, sum(aviation_died) AS "항공 사고 사망자"
, sum(road_num) AS "도로 교통사고"
, sum(road_died) AS "도로 교통사고 사망자"
, sum(rail_num) AS "철도 사고"
, sum(rail_died) AS "철도 사고 사망자"
from
(
select A.year, A.sea_num, a.aviation_num, d.aviation_died,
a.rail_num, d.rail_died, a.road_num, d.road_died
from traffic_accident A
left join traffic_died D
on trim(A.year) = D.year
order by a.year
)
where 1=1
group by case
when year between 1980 and 1989 then '1980년대'
when year between 1990 and 1999 then '1990년대'
when year between 2000 and 2009 then '2000년대'
when year between 2010 and 2019 then '2010년대'
end
order by years asc;