SQL 데이터 분석 - 교통사고 분석

분석환경: Windows 10 Pro, Oracle DB, SQL Developer
참고 문헌: 홍형경 저 누구나 쉽게 SQL
데이터: 국가교통 DB(www.ktdb.go.kr) 1980년~2016년까지 자동차, 선박, 지하철, 철도, 항공기별 전체 사고 건수 및 사망자 수 외

1. traffic_accident 테이블 생성

   CREATE TABLE traffic_accident
   	     (
                 year		NUMBER		NOT NULL, -- 연도
                 trans_type	VARCHAR(30)	NOT NULL, -- 교통수단
                 total_acct_num	NUMBER,			  -- 사고 건수
                 death_person_num	NUMBER  		  -- 사망자 수
                );
    ALTER TABLE traffic_accident
    ADD CONSTRAINT traffic_accident_pk PRIMARY KEY(year, trans_type);

2. 데이터 입력(기존 실습 자료 다운로드 후 실행)
   

SELECT *
  FROM traffic_accident;

5개의 교통수단, 총 185 건 조회됨.

3. 연대, 교통수단별 총 사고 건수 조회
   : 10년 단위로 데이터를 집계해 보겠다.

   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 AS YEARS -- 별칭 선언
         ,trans_type
         ,SUM(total_acct_num)   AS 사고건수
         ,SUM(death_person_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, trans_type /* SELECT 절에 명시한 CASE 문을 
             		     GROUP BY 절에도 명시함. 
                      	     여기서 별칭을 선언하는 'AS YEARS'는 
                       	     GROUP BY 절에 명시하지 않는다. 
                                다른 컬럼식도 마찬가지. */
    ORDER BY 1, 2;

   

4. 연대별 추이 분석
   : 위 테이블처럼 연대를 로우 형태가 아닌 컬럼 형태로 조회해 보겠다.

   SELECT trans_type
         ,SUM(CASE WHEN year >= 1980 AND year <= 1989 
                   THEN total_acct_num ELSE 0 END) "1980년대"
         ,SUM(CASE WHEN year >= 1990 AND year <= 1999 
                   THEN total_acct_num ELSE 0 END) "1990년대"
         ,SUM(CASE WHEN year >= 2000 AND year <= 2009 
                   THEN total_acct_num ELSE 0 END) "2000년대"    
         ,SUM(CASE WHEN year >= 2010 AND year <= 2019
                   THEN total_acct_num ELSE 0 END) "2010년대" 
    -- 위 구문에서 별칭을 ""로 감싼 이유는 별칭은 숫자로 시작할 수 없기 때문.
     FROM traffic_accident
    WHERE 1 = 1
    GROUP BY trans_type
    ORDER BY trans_type;

5. 교통수단별 가장 많은 사망자 수가 발생한 연도 구하기

   SELECT a.*
     FROM traffic_accident a
         ,( SELECT trans_type
                  ,MAX(death_person_num) death_per
              FROM traffic_accident
             GROUP BY trans_type
          ) b
    WHERE a.trans_type       = b.trans_type
      AND a.death_person_num = b.death_per;

1991년도에 가장 많은 자동차 사고와 사망자가 발생했음을 알 수 있다.
특이한 점은, 지하철의 경우 2003년도에 가장 많은 사망자 수가 나타났는데 이는 2003년도에 대구 지하철 참사가 일어난 해이다.
항공기의 경우, 1983년도에 대한항공 여객기가 구 소련 전투기에 피격되어 탑승자 전원이 사망한 사건이 있었다.

[응용문제]
: 각 교통수단별 사고 건수 대비 사망자수 비율의 평균 구하기

SELECT trans_type
     , ROUND(AVG(death_person_num/total_acct_num)*100, 0) death_per
  FROM traffic_accident
 WHERE total_acct_num > 0 
 -- ↑ 분모가 0일 경우를 대비해 사고 건수가 없는 연도는 제외
 GROUP BY trans_type;