[SQL] 분석 실습

※ 본 포스팅은 '패스트캠퍼스'의 '데이터 분석 Master Class' 강의 내용을 복습하고자 정리함.

 

1. 전국 캠핑장 데이터 분석

* 데이터 출처 : 공공데이터포털 - 전국야영(캠핑)장표준데이터

 

1-1) 각 지역에 위치한 캠핑장 조회

# 1) 캠핑장의 사업장명, 소재지전체주소 출력

select 사업장명 NAME, 소재지전체주소 ADDRESS from camping_info;

 

# 2) 정상영업중인 캠핑장 데이터 출력

select * from camping_info where 영업상태구분코드 = 1;

 

# 3) 양양에 위치한 캠핑장 몇 개인지 출력

select count(*) from camping_info where 소재지전체주소 like "%양양%";

 

# 4) 3번 데이터 중 폐업한 캠핑장 몇 개인지 출력

select count(*) from camping_info where 소재지전체주소 like "%양양%" and 영업상태구분코드 = 3;

 

# 5) 태안에 위치하고 2020년에 폐업한 캠핑장 출력

select * from camping_info where 소재지전체주소 like "%태안%" and year(폐업일자) = 2020;
select * from camping_info where 소재지전체주소 like "%태안%" and 폐업일자 like '2020%';

 

# 6) 제주도와 서울에 위치한 캠핑장 몇 개인지 출력

select count(*) from camping_info where 소재지전체주소 like "%제주도%" or 소재지전체주소 like "%서울%";

 

# 7) 폐업하지 않은 캠핑장 출력

select * from camping_info where 영업상태구분코드 <> 3;
select * from camping_info where 영업상태구분코드 != 3;

 

1-2) 해수욕장에 위치한 캠핑장 조회

# 1) 해수욕장에 위치한 캠핑장의 사업장명, 인허가일자 출력

select 사업장명, 인허가일자 from camping_info where 사업장명 like '%해수욕장%';

 

# 2) 제주도 해수욕장에 위치하고 인허가일자가 가장 최근인 곳의 인허가일자 출력

select max(인허가일자) from camping_info where 소재지전체주소 like '%제주%' and 사업장명 like '%해수욕장%';

 

# 3) 해수욕장에 위치한 캠핑장의 평균 시설면적 출력

select avg(시설면적) from camping_info where 사업장명 like '%해수욕장%';

 

1-3) 면적이 가장 넓은 캠핑장

# 1) 캠핑장의 사업장명, 시설면적을 시설면적이 가장 넓은 순으로 출력

select 사업장명, 시설면적 from camping_info order by 시설면적 desc;

 

# 2) 경기도 캠핑장 중에서 면적이 가장 넓은 순으로 5개만 출력 (단, 1위 제외)

select * from camping_info where 소재지전체주소 like '%경기%' order by 시설면적 desc limit 1, 4;

 

# 3) 2020년 10월 ~ 2021년 3월 사이에 폐업한 캠핑장의 사업장명, 소재지전체주소 출력

select 사업장명, 소재지전체주소, 폐업일자 from camping_info where 폐업일자 between '2020-10-01' and '2021-03-31';

 

1-4) 캠핑장이 가장 많은 지역

# 1) 각 지역별 캠핑장 수 출력 (많은 지역순)

select substr(소재지전체주소, 1, instr(소재지전체주소, ' ')) as LOCATION, count(*) as CNT
  from camping_info
group by LOCATION
order by CNT;

 

# 2) 각 지역별 영업중인 캠핑장 수 출력 (캠핑장 수가 300개 이상인 지역만)

select substr(소재지전체주소, 1, instr(소재지전체주소, ' ')) as LOCATION, count(*)
  from camping_info
 where 영업상태구분코드 = 1
group by LOCATION
having count(*) >= 300 # where 절에 집계함수 사용불가
order by count(*) desc;

 

# 3) 연도별 폐업한 캠핑장 수 출력 (연도별로 내림차순)

select substr(폐업일자, 1, 4) as YEAR, count(*)
  from camping_info
 where 영업상태구분코드 = 3
group by YEAR
order by YEAR desc;

 

---

 

 

2. 고속도로 휴게소 데이터 분석

* 데이터 출처 : 공공데이터포털 한국도로공사 - 고속도로 휴게소 데이터

 

2-1) 데이터 조인

# 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_score a right outer join rest_area_parking b
    on a.휴게소명 = b.휴게소명;

 

# 2) 고속도로 휴게소의 규모와 화장실 현황을 함께 출력

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

 

# 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.시설구분, avg(b.합계), min(b.합계), max(b.합계)
  from rest_area_score a, rest_area_parking b
 where a.휴게소명 = b.휴게소명
group by a.시설구분;

 

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

# 고속도로 휴게소 만족도별 대형 주차장 순위
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.휴게소명;
 
# 인라인 뷰로 1위인 데이터만 출력
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;

 

2-2) 휴게소 화장실 실태 조사

# 1) 노선별로 화장실의 total 변기 수가 가장 많은 곳만 출력

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

 

# 2) 노선별로 남자 변기수가 더 많은 곳, 여자 변기수가 더 많은 곳, 남녀 변기수가 동일한 곳의 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,
	count(*) as total
  from rest_area_restroom
group by 노선;

 

2-3) 만족도가 높은 휴게소의 편의시설 현황

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

# 평가등급이 우수인 휴게소의 휴게소명, 시설구분 출력
select 휴게소명, 시설구분
  from rest_area_score
 where 평가등급 = '우수'
 
# 인라인 뷰
select s.휴게소명, s.시설구분, round(p.장애인/p.합계*100, 2) as 장애인_주차장수_비율
  from (select 휴게소명, 시설구분
		from rest_area_score
		where 평가등급 = '우수'
		) s left outer join rest_area_parking p
  	on s.휴게소명 = p.휴게소명
order by 장애인_주차장수_비율 desc;

 

# 2) 노선별로 대형차를 가장 많이 주차할 수 있는 휴게소 top 3

# 노선별 대형 주차장 순위
select r.노선, p.대형, p.휴게소명,
		rank() over(partition by r.노선 order by p.대형 desc) as rnk
  from rest_area_restroom r, rest_area_parking p
 where r.시설명 = p.휴게소명;
 
# 인라인 뷰
select t.노선, t.대형, t.휴게소명, t.rnk
  from (
	select r.노선, p.대형, p.휴게소명,
		rank() over(partition by r.노선 order by p.대형 desc) as rnk
	from rest_area_restroom r, rest_area_parking p
	where r.시설명 = p.휴게소명
	) t
 where t.rnk <= 3;

 

2-4) 반려동물, 와이파이 휴게소 현황

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

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

 

# 2) 본부별로 wifi 사용이 가능한 휴게소가 몇군데인지 출력 (휴게소가 많은 순서대로, 휴게소 수가 25보다 많은 곳만)

select trim(본부), count(*) # 본부 컬럼에 공백이 포함된 데이터가 있음.
  from rest_area_wifi
 where 가능여부 = 'O'
group by trim(본부)
having count(*) > 25
order by count(*) desc;

 

---

 

3. 편의점 구매상품 분석

* 데이터 출처 : 패스트캠퍼스 - 편의점 구매상품 데이터, 기상자료개방포털 - 종관기상관측 - 자료

 

3-1) 편의점에서 월별로 가장 많이 팔린 상품 카테고리

# 1) 월별 커피음료 카테고리중 가장 많이 팔린 카테고리의 총판매수 출력

select substr(일자, 1, 7) as month,
	greatest(sum(커피음료_페트), sum(커피음료_병), sum(커피음료_중캔), sum(커피음료_소캔)) as greatest
  from store_order
group by month;

 

 

# 2) 월별 커피음료_페트 총판매수 데이터 pivot

select '커피음료_페트' as product,
	sum(case when substring(일자, 1, 7)='2020-07' then 커피음료_페트 end) as '2020-07',
	sum(case when substring(일자, 1, 7)='2020-08' then 커피음료_페트 end) as '2020-08',
	sum(case when substring(일자, 1, 7)='2020-09' then 커피음료_페트 end) as '2020-09',
	sum(case when substring(일자, 1, 7)='2020-10' then 커피음료_페트 end) as '2020-10'
  from store_order;

 

# 3) 월별 탄산수와 생수의 평균 판매수 출력

 select substring(일자, 1, 7) as month,
 	floor(avg(탄산수)), floor(avg(생수))
   from store_order
 group by substring(일자, 1, 7);

 

3-2) 편의점 월별 매출액

# 1) 월별 커피음료 매출 구하기

# 스칼라 서브쿼리를 반복적으로 사용하는 것이 좋진 않으나 카테고리가 컬럼명으로 되어있기 때문에 사용
select substr(일자, 1, 7) as month,
	sum(커피음료_페트)*(select price from price_info where product = '커피음료_페트') as '커피음료_페트_매출',
	sum(커피음료_병)*(select price from price_info where product = '커피음료_병') as '커피음료_병_매출',
	sum(커피음료_중캔)*(select price from price_info where product = '커피음료_중캔') as '커피음료_중캔_매출',
	sum(커피음료_소캔)*(select price from price_info where product = '커피음료_소캔') as '커피음료_소캔_매출'
 from store_order
group by month;

 

# 2) 8월의 이온음료 매출 구하기

select sum(이온음료_대페트)*(select price from price_info where product = '이온음료_대페트') as '이온음료_대페트_매출',
	sum(이온음료_중페트)*(select price from price_info where product = '이온음료_중페트') as '이온음료_중페트_매출',
	sum(이온음료_캔)*(select price from price_info where product = '이온음료_캔') as '이온음료_캔_매출'
  from store_order 
 where 일자 like '2020-08%';

 

3-3) 날씨와 편의점 구매상품의 관련성

# 1) 최고기온이 30도 이상이었던 날의 아이스음료 판매수 출력

select w.일시, w.최고기온, o.아이스음료 
  from weather w left outer join store_order o
    on w.일시 = o.일자
 where w.최고기온 >= 30;

 

# 2) 비가 왔던 날의 숙취해소음료 판매수, 매출 출력

select w.일시, w.일강수량,
	ifnull(o.숙취해소음료, 0) as 판매수,
	ifnull(o.숙취해소음료*(select price from price_info where product = '숙취해소음료'), 0) as 숙취해소음료_매출액
  from weather w left outer join store_order o
    on w.일시 = o.일자 
 where 일강수량 > 0;