미니 프로젝트 : 사회 초년생의 전셋집 구하기 (2)

 

미니 프로젝트 : 사회 초년생의 전셋집 구하기 (1)

 

💾  데이터 전처리

1. 서울특별시 전월세가 정보 (2022)

컬럼명	데이터 타입	조건
접수연도	int
자치구 코드	int
자치구명	varchar(50)
법정동 코드	int
법정동명	varchar(50)
층	int	3층 이상
전월세 구분	varchar(50)	전세
임대면적 (m2)	int	24m2 이상
보증금 (만원)	int	2억
건축년도	int	준공 완료일로부터 10년 미만
건물용도	varchar(50)	오피스텔, 아파트

SELECT 접수년도, 건물명, 자치구명, 법정동명,
	   if(부번=0,본번,CONCAT(본번,"-",부번)) 번지수, 층,
	   DATE_FORMAT(계약일, '%Y-%m-%d') 계약일, 
	   임대면적, `보증금(만원)`, 
	   건축년도, 건물용도, 계약기간, 신규계약구분, 갱신청구권사용, 종전보증금, 종전임대료,
	   COUNT(*)
FROM Rent_2022
WHERE 전월세구분 = "전세" 
	  AND 층 >=3
	  AND 건물용도 in ("오피스텔","아파트") 
	  AND 임대면적 >= 24
	  AND `보증금(만원)` >= 20000
	  AND (접수년도 - 건축년도) < 10
GROUP BY 3,4

• column 변동
  1. 번지수 column 생성 : “본번 - 부번” 형태로 번지수 칼럼 생성
  2. 계약일 date formate을 “yyyymmdd”에서 “yyyy-mm-dd”로 변경
  3. “건물명”을 2번째 컬럼으로 순서 변경 (→ 가독성을 위함)
• 조건
  1. 전월세 구분 = “전세” : 전세 매물만 출력
  2. 건물용도 in (”오피스텔”, “아파트”) : 아파트 또는 오피스텔 매물만 출력

2. 서울시 상권분석

select 자치구명, 
	   max(if(service='스포츠 강습', sum_s, 0)) '스포츠 강습',
	   max(if(service='스포츠클럽', sum_s, 0)) '스포츠클럽',
	   max(if(service='동물병원', sum_s, 0)) '동물병원'
from
(
select 자치구명, 서비스_업종_코드_명 service, sum(점포_수) sum_s
from shop
where 자치구명 in ('동대문구', '광진구', '송파구', '서초구', '용산구', '중구', '성동구', '강남구', '관악구')
	  and 서비스_업종_코드_명 in ('스포츠 강습', '스포츠클럽', '동물병원')
	  and 기준_년분기_코드 = 20221
group by 1, 2
) t
group by 1
order by 1;

 

3. 서울시 주요 공원 현황

SELECT 지역, COUNT(*) 공원
FROM (SELECT 공원명, 지역
       FROM Park
       WHERE 공원주소 like '%서울%')a
GROUP BY 1
ORDER BY 2 DESC

 

4. 통합 데이터

 

SELECT 자치구명, `평균전세금(만원)`, 동물병원, 스포츠클럽, 스포츠강습, 공원, 매물수
FROM (SELECT 자치구명, COUNT(*) 매물수, ROUND(AVG(`보증금(만원)`),0)	"평균전세금(만원)"
 	  FROM (SELECT * FROM Rent_2022
	   		WHERE 전월세구분 = "전세" 
	   		 	  AND 자치구명 in ('동대문구', '광진구', '송파구', '서초구', '용산구', '중구', '성동구', '강남구', '관악구')
	   		 	  AND 층 >=3
	   		 	  AND 건물용도 in ("오피스텔","아파트") 
	   	   		  AND 임대면적 >= 24
	   		 	  AND `보증금(만원)` <= 20000
	   		 	  AND (접수년도 - 건축년도) < 10) r 
	   	GROUP BY 1 ) a
INNER JOIN (SELECT 시군구명,
 	   max(if(업종 = "동물병원", 점포수, NULL)) "동물병원",
 	   max(if(업종 = "스포츠클럽", 점포수, NULL)) "스포츠클럽",
 	   max(if(업종 =  "스포츠 강습", 점포수, NULL)) "스포츠강습"
	  FROM (SELECT 시군구명, 서비스_업종_코드_명 업종, sum(점포_수) 점포수
	  		FROM shop s INNER JOIN addr a ON s.행정동_코드*100 =a.행정동코드
		    WHERE 기준_년분기_코드 = 20221 and (서비스_업종_코드_명 = "동물병원" or 서비스_업종_코드_명 like '%스포츠%')
		    	  and 시군구명 in ('동대문구', '광진구', '송파구', '서초구', '용산구', '중구', '성동구', '강남구', '관악구') 
		    GROUP BY 1,2)t
	  WHERE 시군구명 in ('동대문구', '광진구', '송파구', '서초구', '용산구', '중구', '성동구', '강남구', '관악구') 
	  GROUP BY 1) b
INNER JOIN (SELECT 지역, COUNT(*) 공원
			FROM (SELECT 공원명, 지역 FROM Park WHERE 공원주소 like '%서울%' 
			AND 지역 	in ('동대문구', '광진구', '송파구', '서초구', '용산구', '중구', '성동구', '강남구', '관악구'))a
			GROUP BY 1) c
WHERE a.자치구명 = b.시군구명 and b.시군구명 = c.지역
ORDER BY 7 DESC