[SQL] 데이터 분석 실습2 : 고속도로 휴게소 데이터 분석

고속도로 휴게소 정보 테이블 및 데이터

• 고속도로 휴게소 만족도 점수 데이터
• 휴게소별 화장실 현황 데이터
• 휴게소 와이파이 제공 현황 데이터
• 휴게소 주차장 현황 데이터
• 휴게소 반려동물 편의시설 데이터

https://www.data.go.kr/data/15043718/fileData.do

한국도로공사_휴게소 만족도 점수_20171231

https://www.data.go.kr/data/15043712/fileData.do

한국도로공사_휴게소별 화장실 현황_20240630

https://www.data.go.kr/data/15043713/fileData.do

한국도로공사_휴게소 와이파이 제공 현황_20240701

https://www.data.go.kr/data/15043716/fileData.do

https://www.data.go.kr/data/15064250/fileData.do

한국도로공사_휴게소 반려동물 편의시설(놀이터) 현황_20240630

 

전국 휴게소의 화장실 실태 조사

1. 고속도로 휴게소의 규모와 주차장 현황을 함께 출력 (휴게소명, 시설구분, 합계, 대형, 소형, 장애인)

select a.휴게소명, a.시설구분, b.합계, b.대형, b.소형, b.장애인
from rest_area_score a left outer join rest_area_parking b on a.휴게소명 = b.휴게소명
union 
select b.휴게소명, a.시설구분, b.합계, b.대형, b.소형, b.장애인
from rest_area_parking b left outer join rest_area_score a on a.휴게소명 = b.휴게소명;

2. 고속도로 휴게소의 규모와 화장실 현황을 함께 출력 (휴게소명, 시설구분, 남자_변기수, 여자_변기수)

select a.휴게소명, a.시설구분, c.남자_변기수, c.여자_변기수
from rest_area_score a left outer join rest_area_restroom c on a.휴게소명 = c.시설명
union
select c.시설명, a.시설구분, c.남자_변기수, c.여자_변기수
from rest_area_restroom c left outer join rest_area_score a on a.휴게소명 = c.시설명;

3. 고속도록 휴게소의 규모, 주차장, 화장실 현황을 함께 출력 (휴게소명, 시설구분, 합계, 남자_변기수, 여자_변기수)

select a.휴게소명, a.시설구분, b.합계, c.남자_변기수, c.여자_변기수
from rest_area_score a, rest_area_parking b, rest_area_restroom c
where a.휴게소명 = b.휴게소명 and b.휴게소명 = c.시설명;

4. 고속도로 휴게소 규모별로 주차장수 합계의 평균, 최소값, 최대값 출력

select a.휴게소명, a.시설구분,
		avg(b.합계) over(partition by a.시설구분) as avg_parking,
		min(b.합계) over(partition by a.시설구분) as min_parking,
		max(b.합계) over(partition by a.시설구분) as max_parking
	from rest_area_score a inner join rest_area_parking b
	on a.휴게소명 = b.휴게소명;

5. 고속도로 휴게소 만족도별로 대형 주차장수가 가장 많은 휴게소만 출력

select t.휴게소명, t.평가등급, t.대형
from(select a.휴게소명, a.평가등급, b.대형,
		rank() over(partition by a.평가등급 order by b.대형 desc) as rnk
	from rest_area_score a, rest_area_parking b
	where a.휴게소명 = b.휴게소명
	)t
where rnk = 1;

 

 

전국 휴게소의 화장실 실태 조사

1. 노선별 남자 변기수, 여자 변기수 최대값 출력

select 노선, max(남자_변기수), max(여자_변기수) 
from rest_area_restroom 
group by 노선;

2. 휴게소 중  total 변기수가 가장 많은 휴게소가 어디인지 출력

select 시설명, 남자_변기수+여자_변기수 as total
from rest_area_restroom
order by total desc
limit 1;

3. 노선별로 남자_변기수, 여자_변기수의 평균값 출력

select 노선, round(avg(남자_변기수)), round(avg(여자_변기수))
from rest_area_restroom 
group by 노선;

4. 노선별로 total 변기수가 가장 많은 곳만 출력

select t.노선, t.total, t.시설명
from (select 노선, 시설명, 남자_변기수+여자_변기수 as total,
		rank() over(partition by 노선 order by 남자_변기수+여자_변기수 desc) as rnk
	from rest_area_restroom) t
	where t.rnk = 1;

5. 노선별로 남자 변기수가 더 많은 곳, 여자 변기수가 더 많은 곳, 남녀 변기수가 동일한 곳의 count를 각각 구하여 출력

select 노선,
	count(case when 남자_변기수 > 여자_변기수 then 1 end) as male,
	count(case when 남자_변기수 < 여자_변기수 then 1 end) as female,
	count(case when 남자_변기수 = 여자_변기수 then 1 end) as equal
from rest_area_restroom
group by 노선;

 

만족도가 높은 휴게소의 편의시설 현황

1. 평가등급이 최우수인 휴게소의 장애인 주차장수 출력(휴게소명, 시설구분, 장애인 주차장수 내림차순으로 출력)

<방법1>
select a.휴게소명, a.평가등급, b.장애인
from(select 휴게소명, 평가등급
	from rest_area_score 
	where 평가등급 = '최우수') a left outer join rest_area_parking b on a.휴게소명 = b.휴게소명
order by b.장애인 desc;

<방법2>
select a.휴게소명, a.평가등급, b.장애인
from rest_area_score a left outer join rest_area_parking b on a.휴게소명 = b.휴게소명
where a.평가등급 = '최우수'
order by b.장애인 desc;

2. 평가등급이 우수인 휴게소의 장애인 주차장수 비율 출력(휴게소명, 시설구분, 장애인 주차장수 비율 내림차순으로 출력)

<방법1>
select a.휴게소명, a.평가등급, round((b.장애인/b.합계)*100,2) as 장애인_주차장수_비율
from(select 휴게소명, 평가등급
	from rest_area_score 
	where 평가등급 = '최우수') a 
	left outer join rest_area_parking b on a.휴게소명 = b.휴게소명
order by 장애인_주차장수_비율 desc;

<방법2>
select a.휴게소명, a.평가등급, b.장애인, (b.장애인/b.합계)*100 as 장애인_주차장수_비율 
from rest_area_score a left outer join rest_area_parking b on a.휴게소명 = b.휴게소명
where a.평가등급 = '우수'
order by 장애인_주차장수_비율 desc;

3. 휴게소의 시설구분별 주차장수 합계의 평균 출력

select a.시설구분, avg(b.합계)
from rest_area_score a, rest_area_parking b 
where a.휴게소명 = b.휴게소명
group by a.시설구분;

4. 노선별로 대형차를 가장 많이 주차할 수 있는 휴게소 TOP3

select t.노선, t.대형, t.휴게소명
from ( select a.노선, b.대형, b.휴게소명,
		rank() over(partition by a.노선 order by b.대형 desc) as rnk
	from rest_area_restroom a, rest_area_parking b
	where a.시설명 = b.휴게소명 ) t
where t.rnk < 4;

5. 본부별로 소형차를 가장 많이 주차할 수 있는 휴게소 TOP3

select t.본부, t.휴게소명
from ( select 본부, 휴게소명, rank() over(partition by 본부 order by 소형 desc) as rnk
	from rest_area_parking) t
where t.rnk < 4;

 

반려동물을 데리고 와이파이 빵빵한 휴게소에 가보기

1. 반려동물 놀이터가 있는 휴게소 중 wifi 사용이 가능한 곳 출력

select *
from rest_area_animal a left outer join rest_area_wifi b on a.휴게소명 = b.휴게소명
where 가능여부 = 'O';

 2. 반려동물 놀이터가 있는 휴게소 중 운영시간이 24시간인 곳이 몇군데인지 출력

select count(a.휴게소명)
from rest_area_animal a
where 운영시간 = '24시간';

3. 본부별로 wifi 사용이 가능한 휴게소가 몇군데인지 출력

select 본부, count(*)
from rest_area_wifi
where 가능여부 = 'O'
group by 본부;

4. 3번 데이터를 휴게소가 많은 순서대로 정렬하여 출력

select 본부, count(*)
from rest_area_wifi
where 가능여부 = 'O'
group by 본부
order by count(*) desc;

5. 4번 데이터에서 휴게소 수가 25보다 많은 곳만 출력

select 본부, count(*)
from rest_area_wifi
where 가능여부 = 'O'
group by 본부
having count(*) > 25
order by count(*) desc;