SQL 6장 실습문제

소스파일 : https://github.com/bjpublic/postgresql

1

교차로에서 사고자(다치거나 사망한 사람)가 5명 이상인 대형 사고는 어느 시도, 시군구에서 발생했는지 사고발생 수로 내림차순 출력

# 사고자가 5명 이상인 대형 사고는 어느 시도, 시군구에서 발생했는지 사고자 수로 내림차순 출력    
  select 시도, 시군구, count(*) as 사고횟수
  from accident
  where (사망자수+부상자수+중상자수+경상자수+부상신고자수)>=5
  group by 1,2
  order by 3 desc;

 시도 |  시군구  | 사고횟수
------+----------+----------
 경기 | 용인시   |        9
 경기 | 화성시   |        7
 경기 | 평택시   |        7
 강원 | 원주시   |        6
 충북 | 청주시   |        6
 경기 | 이천시   |        6
         (이하생략)

# 교차로에서 + 위 테이블 조건 조인 출력
select 시도, 시군구, count(*) as 사고횟수
from accident join road_type using (도로형태id)
where road_type.대분류 = '교차로'
    and (사망자수+부상자수+중상자수+경상자수+부상신고자수)>=5
group by 1,2
order by 3 desc;

 시도 |  시군구  | 사고횟수
------+----------+----------
 경기 | 평택시   |        4
 경기 | 용인시   |        4
 서울 | 송파구   |        3
 충남 | 아산시   |        3
 경기 | 성남시   |        3
 경남 | 창원시   |        3
         (이하생략)

2

세대수 대비 사망자수가 많은 순으로 시도, 시군구를 출력

# 시도, 시군구별 사망자수 출력
select 시도,시군구,sum(사망자수) as 사망자수
from accident
group by 1,2
order by 3 desc;

 시도 |  시군구  | 사망자수
------+----------+----------
 경남 | 창원시   |       62
 경기 | 평택시   |       51
 충북 | 청주시   |       49
 충남 | 천안시   |       42
 제주 | 제주시   |       40
 경기 | 성남시   |       39
         (이하생략)

# 인구수테이블 + 위 테이블 조건 조인 출력
select population.시도, population.시군구,
        round(시군구별_사고.사망자수::decimal/population.세대수,7) as 세대당_사망자수
from population
join (
    select 시군구, 시도, sum(사망자수) as 사망자수
    from accident
    group by 1,2
    order by 3 desc
) 시군구별_사고
using(시도,시군구)
order by 3 desc;

 시도 |  시군구  | 세대당_사망자수
------+----------+-----------------
 경북 | 군위군   |       0.0013844
 전북 | 장수군   |       0.0009622
 충북 | 보은군   |       0.0007738
 전남 | 진도군   |       0.0007354
 전북 | 무주군   |       0.0007179
 강원 | 화천군   |       0.0007130
        (이하생략)

3

광역단체(시도)의 각 시군구 평균 사고 횟수 대비 각 시군구가 얼만큼 사고가 더 나는지 증감을 표로 표시

문제를 해석하는것 자체가 어려웠다.

우리나라 행정구역 체계는 광역단체(시도) 안에 시군구로 구성되어 있고,
각각의 광역단체(시도)의 각각의 시군구 별로 사고횟수를 구하고 이를 이용하여
각 광역단체(시도)의 평균 사고횟수를 구한 후
각각의 시군구가 해당 시군구가 속한 광역단체(시도)의 평균사고횟수 대비 증감을 출력하라는 문제다.

# 각 시군구의 사고횟수 출력
select 시군구, 시도, count(*) as 사고횟수
from accident
group by 1, 2
order by 3 desc;

 시군구 | 시도 | 사고횟수
--------+------+----------
 창원시 | 경남 |       62
 청주시 | 충북 |       48
 평택시 | 경기 |       47
 천안시 | 충남 |       42
 제주시 | 제주 |       40
 성남시 | 경기 |       39
         (이하 생략)

# 각 시도의 평균 사고횟수 출력(시도의 모든 사고횟수 / 시군구 개수)
select 시도, round(avg(사고횟수),5) as 평균_사고횟수
from (
    select 시군구, 시도, count(*) as 사고횟수
    from accident
    group by 1, 2
) 시군구_사고횟수
group by 1
order by 2 desc;

 시도 | 평균_사고횟수
------+---------------
 제주 |      32.00000
 충남 |      19.93333
 경기 |      19.61290
 전북 |      16.28571
 충북 |      16.18182
     (이하 생략)

# 각 시군구의 평균대비 사고횟수 증감 출력
select 시군구_사고횟수.시도, 시군구_사고횟수.시군구, 시군구_사고횟수.사고횟수,
    (시군구_사고횟수.사고횟수 - 시도_평균_사고횟수.평균_사고횟수)
        as 평균대비_사고횟수_증감
from (
    select 시군구, 시도, count(*) as 사고횟수
    from accident
    group by 1, 2
) 시군구_사고횟수 join
(
    select 시도, avg(사고횟수) as 평균_사고횟수
    from (
        select 시군구, 시도, count(*) as 사고횟수
        from accident
        group by 1, 2
    ) 시군구_사고횟수
    group by 1
) 시도_평균_사고횟수
using (시도)
order by 평균대비_사고횟수_증감 desc;    

 시도 |  시군구  | 사고횟수 | 평균대비_사고횟수_증감
------+----------+----------+------------------------
 경남 | 창원시   |       62 |    45.9444444444444444
 충북 | 청주시   |       48 |    31.8181818181818182
 경기 | 평택시   |       47 |    27.3870967741935484
 강원 | 원주시   |       32 |    22.4444444444444444
 충남 | 천안시   |       42 |    22.0666666666666667
 경북 | 경주시   |       35 |    19.5454545454545455
             (이하 생략)