데이터분석MySQL 이지만, PostgreSQL로 실습해보겠습니다.
데이터셋 :
한국도로공사_휴게소 만족도 점수,
한국도로공사_휴게소 주차장 현황,
한국도로공사_휴게소별 화장실 현황,
한국도로공사_휴게소 와이파이 제공 현황,
한국도로공사_휴게소 반려동물 편의시설(놀이터) 현황

데이터 불러오기

• 인코딩 : euc-kr 으로 수정
  • 주차장 데이터만 utf-8로 수정
• Minimum column lenght : 100 으로 수정

중간중간 에러가 나긴 했지만, 인코딩을 수정해주거나 다시 불러오기를 2~3번 하면 또 정상적으로 잘 불러와집니다.

정상적으로 5개 csv파일 모두 잘 불러와집니다.

이름을 영어로 바꿔준 뒤, 불러와줍니다.

select * from rest_area_score ras ;  --만족도 점수
select * from rest_area_animal raa ; --반려동물 편의시설
select * from rest_areat_wifi raw ; -- 와이파이 제공 현황 
select * from rest_area_restroom rar ; --화장실 
select * from rest_area_parking rap ; --주차장 

데이터 병합 JOIN

1. 규모 & 주차장

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

-- 1. 고속도로 휴게소의 규모 & 주차장 현황 함께 출력 (휴게소명, 시설구분, 합계, 대형, 소형, 장애인 )
-- score (평가등급, 휴게소명, 시설구분)  & parking  (본부,노선,휴게소명,합계,대형,소형,장애인) 
select ras.휴게소명 , ras.시설구분 , rap.합계 , rap.대형 , rap.소형 , rap.장애인 
from rest_area_score ras 
left join rest_area_parking rap on rap.휴게소명  = ras.휴게소명 ; 
-- 단순히 join 만 할 경우, null값이 없어짐. 

union 함수

둘 이상의 SELECT 문의 결과 집합을 하나의 결과 집합으로 결합하는 데 사용됩니다. SELECT 문의 결과에서 중복된 행이 제거됩니다.

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

select , from, join
union
select , from, join
각각 조금씩 바꿔주면서 실행해봅니다.

2. 규모 & 화장실

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

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

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

3. 세개 테이블 병합

join on = from 과 where

n개의 테이블을 합치는데 일단 2가지 방법이 있다고 합니다.

Select 컬럼들
From A테이블
Join B테이블 on A테이블.A컬럼 = B테이블.A컬럼
join ...

Select 컬럼들
From A테이블, B테이블
Where A테이블.A컬럼 = B테이블.A컬럼
and...

-- 3. 고속도로 휴게소의 규모, 주차장, 화장실 현황 함께 출력 (휴게소명, 시설구분, 합계, 남자_변기수, 여자_변기수) 
-- score & parking & restroom (Null값 제외) 
select ras.휴게소명 , ras.시설구분 , rap.합계 , rar.남자_변기수 , rar.여자_변기수 
from rest_area_score ras 
join rest_area_parking rap on ras.휴게소명 =rap.휴게소명 
join rest_area_restroom rar on rar.시설명 = ras.휴게소명 

-- 3. 고속도로 휴게소의 규모, 주차장, 화장실 현황 함께 출력 (휴게소명, 시설구분, 합계, 남자_변기수, 여자_변기수) 
-- score & parking & restroom (Null값 제외) 
select ras.휴게소명 , ras.시설구분 , rap.합계 , rar.남자_변기수 , rar.여자_변기수 
from rest_area_score ras , rest_area_parking rap , rest_area_restroom rar 
where ras.휴게소명 = rap.휴게소명 and rap.휴게소명 = rar.시설명 

두 방식 모두 결과는 같습니다.

4. 규모별 주차장 수 평균,최소,최대

partition by(윈도우함수)와 group by

규모별? 하게되면 group by가 떠올랐는데 partition by도 가능하다고 합니다.

GROUP BY 절은 특정 기준으로 데이터를 정의하고자 할 때 사용합니다. 보통 이런 기준은 우리가 분석을 할 때 분류 기준으로 삼는 것들입니다.
기존 행에 있던 데이터는 우리가 제공한 새로운 기준에 의해 생성된 새로운 행에 입력됩니다. 그뿐만이 아닙니다. 집계 함수를 사용하여 기존 행에 있던 값들을 계산한 후 새로운 행에 입력해 줄 수도 있습니다. 집계 함수는 데이터를 하나로 합쳐줍니다. 합치는 과정에서, 우리는 기존의 상세 데이터들을 잃게 됩니다.

PARTITION BY를 통해 특정 기준에 한정하여 집계된 값을 계산해 줄 수 있습니다. 여러 행의 집계된 값을 구하고자 PARTITION BY는 OVER절과 윈도우 함수와 함께 사용됩니다. 이는 GROUP BY와 집계 함수가 하는 역할과 거의 유사하지만, 차이점이 1가지 존재합니다. 여러분이 PARTITION BY를 사용하면, GROUP BY와는 달리 기존 행의 세세한 정보들은 사라지지 않고 그대로 유지됩니다.

Group by는 이전의 기존 데이터가 하나로 합쳐지는 바람에 같이 볼 수 없습니다.
Partition by는 Group by와는 달리 기존 행의 세세한 정보들은 사라지지 않고 그대로 유지됩니다.

즉, Partition by는 기존의 데이터와 집계된 값을 함께 나란히 볼 수 있습니다.

-- 4. 고속도로 휴게소 규모별로 주차장수 합계의 평균, 최소값, 최대값 출력 
-- 규모별 주차장 수 (score 테이블과 parking 테이블 JOIN 후 Window 함수)
select ras.휴게소명 
	, ras.시설구분 
	, round(avg(rap.합계) over (partition by ras.시설구분),2) as avg_parking
	, min(rap.합계) over (partition by ras.시설구분) as min_parking
	, max(rap.합계) over (partition by ras.시설구분) as max_parking
from rest_area_score ras
join rest_area_parking rap on ras.휴게소명 = rap.휴게소명 

5. 대형주차장수 많은 휴게소 랭킹

-- 5. 고속도로 휴게소 만족도별로 대형 주차장수가 가장 많은 휴게소만 출력 (휴게소명, 평가등급, 대형, 순위)
-- 만족도별 대형 주차장 수 많은 휴게소 랭킹 (score테이블과 parking테이블을 JOIN) 
select 
	ras.휴게소명 
	, ras.평가등급 
	, rap.대형 
	, rank() over(partition by ras.평가등급 order by rap.대형 desc ) as rnk 
from rest_area_score ras, rest_area_parking rap 
where ras.휴게소명 = rap.휴게소명 

여기에서, 랭킹 top 3위만 보여줍니다.

select 
	ras.휴게소명 
	, ras.평가등급 
	, rap.대형 
	, rank() over(partition by ras.평가등급 order by rap.대형 desc ) as rnk 
from rest_area_score ras, rest_area_parking rap 
where ras.휴게소명 = rap.휴게소명 
limit 3