아래에서 이어지는 글입니다.
5. 부동산 분석 테이블 만들기
실제 사용할 테이블은 아래와 같습니다.
아래 스크립트로 테이블을 생성합니다.
# T_KBLiivHouse 테이블 생성
CREATE TABLE T_KBLiivHouse
(
BaseYM VARCHAR(6) NOT NULL
,BZDongCode VARCHAR(100) NOT NULL
,AMTDiv VARCHAR(100) NOT NULL
,AMT DECIMAL(30,12) NOT NULL DEFAULT 0
,PRIMARY KEY (BaseYM ,BZDongCode ,AmtDiv)
);
이번에는 업로드 테이블의 데이터를 T_KBLiivHouse로 인서트합니다. 지난 글처럼, 업로드한 테이블은 년월이 컬럼으로 구성되어 있지만, 실제 분석에 사용할 테이블은 년월이 로우로 구성됩니다. 업로드 테이블에는 201609부터 201909까지 무려 3년치의 컬럼이 있습니다. 컬럼을 로우로 바꾸기 위해, 우선 아래 SQL을 실행해 봅니다.
# 206109부터 201909까지 로우 만들기
SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE('20160901','%Y%m%d')
, interval T1.Seq Month),'%Y%m') BaseYM
FROM C_Seq T1
WHERE DATE_ADD(STR_TO_DATE('20160901','%Y%m%d')
, interval T1.Seq Month) < STR_TO_DATE('20191001','%Y%m%d')
위 SQL을 실행하면 201609부터 201909까지 로우로 데이터가 만들어 집니다. 위 SQL의 결과와 업로드한 테이블을 크로스-조인해서 최종 분석용 테이블에 데이터를 인서트합니다. 아래와 같습니다.
# T_KBLiivHouse에 데이터 넣기
SET @@SESSION.sql_mode = REPLACE(@@SESSION.sql_mode,'STRICT_TRANS_TABLES,','');
INSERT INTO T_KBLiivHouse
(BaseYM,BZDongCode,AMTDiv,Amt)
SELECT T2.BaseYM
,T1.BZDongCode
,CASE WHEN T1.ValueDiv LIKE '%매매평균가%' THEN 'BUY' ELSE 'RENT' END AmtDiv
,CASE T2.BaseYM
WHEN '201609' THEN AMT_201609
WHEN '201610' THEN AMT_201610
WHEN '201611' THEN AMT_201611
WHEN '201612' THEN AMT_201612
WHEN '201701' THEN AMT_201701
WHEN '201702' THEN AMT_201702
WHEN '201703' THEN AMT_201703
WHEN '201704' THEN AMT_201704
WHEN '201705' THEN AMT_201705
WHEN '201706' THEN AMT_201706
WHEN '201707' THEN AMT_201707
WHEN '201708' THEN AMT_201708
WHEN '201709' THEN AMT_201709
WHEN '201710' THEN AMT_201710
WHEN '201711' THEN AMT_201711
WHEN '201712' THEN AMT_201712
WHEN '201801' THEN AMT_201801
WHEN '201802' THEN AMT_201802
WHEN '201803' THEN AMT_201803
WHEN '201804' THEN AMT_201804
WHEN '201805' THEN AMT_201805
WHEN '201806' THEN AMT_201806
WHEN '201807' THEN AMT_201807
WHEN '201808' THEN AMT_201808
WHEN '201809' THEN AMT_201809
WHEN '201810' THEN AMT_201810
WHEN '201811' THEN AMT_201811
WHEN '201812' THEN AMT_201812
WHEN '201901' THEN AMT_201901
WHEN '201902' THEN AMT_201902
WHEN '201903' THEN AMT_201903
WHEN '201904' THEN AMT_201904
WHEN '201905' THEN AMT_201905
WHEN '201906' THEN AMT_201906
WHEN '201907' THEN AMT_201907
WHEN '201908' THEN AMT_201908
WHEN '201909' THEN AMT_201909
END Amt
FROM U_KBLiivHouse T1 CROSS JOIN
(
SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE('20160901','%Y%m%d')
, interval T1.Seq Month),'%Y%m') BaseYM
FROM C_Seq T1
WHERE DATE_ADD(STR_TO_DATE('20160901','%Y%m%d')
, interval T1.Seq Month) < STR_TO_DATE('20191001','%Y%m%d')
) T2
WHERE T1.BZDongCode IS NOT NULL;
6. 비싼 동네에는 어떤 상가가 많을까?
절대!!! 부동산 권유의 글이 아닙니다. 데이터 분석을 설명하는 글입니다.
비싼 동네(?)에는 어떤 상가(M_Shop)가 많은지 분석해 보도록 하겠습니다. 이 내용이 진짜 부동산 시세와 상관이 있는지는 알 수 없습니다.(상관도는 나중에 파이썬으로 한 번 돌려보면 재미 있을거 같습니다.) 그냥 뭐가 있을까? 란 호기심의 분석입니다.
먼저 ‘법정동’ 별로 분석을 하면 데이터 종류가 너무 많아 질거 같습니다. ‘시군구’ 단위로 분석을 해보겠습니다. 부동산 데이터는 가장 최근의 2019년9월 데이터만 사용하고, 매매(AMTDiv=Buy) 데이터만 사용합니다. 분석 전에 M_Shop에 XiGoonGuCode, MktSDivCode 컬럼에 인덱스를 만듭니다. 분석 성능을 위해서죠.
# M_Shop에 인덱스 생성
CREATE INDEX X_M_Shop_2 ON M_Shop(XiGoonGuCode,MktSDivCode);
아래 SQL로 분석을 합니다. 부동산 평균 가격 Top-10 ‘시군구’에 가장 많은 상가 종류 Top-5를 찾습니다.
# Top10 시군구의 상가 종류 Top5
SELECT T2.MktSDivCode
,(SELECT A.BaseCodeName FROM C_BaseCode A
WHERE A.BaseCodeDiv = 'MktSDivCode' AND A.BaseCode = T2.MktSDivCode) MktSDivCodeName
,COUNT(*) ShopCnt
FROM (
SELECT T2.XiGoonGuCode
,MAX(T2.XiGoonGuName) XiGoonGuName
,AVG(T1.AMT)
FROM T_KBLiivHouse T1
INNER JOIN M_RegionMap2 T2
ON (T1.BZDongCode = T2.BZDongCode)
WHERE T1.BaseYM = '201909'
AND T1.AMTDiv = 'BUY'
GROUP BY T2.XiGoonGuCode
ORDER BY AVG(T1.AMT) DESC
LIMIT 10
) T1 INNER JOIN M_Shop T2
ON (T1.XiGoonGuCode = T2.XiGoonGuCode)
GROUP BY T2.MktSDivCode
ORDER BY COUNT(*) DESC
LIMIT 5;
아래와 같은 결과가 나옵니다.
# 결과
MktSDivCode MktSDivCodeName ShopCnt
=========== =============== =======
Q01A01 한식/백반/한정식 13455
Q12A01 커피전문점/카페/다방 9680
L01A01 부동산중개 8001
F01A01 여성미용실 7514
D05A01 일반의류 4790
7. 그럼, 어디가 좋을까?
절대!!! 부동산 권유의 글이 아닙니다. 데이터 분석을 설명하는 글입니다.
위의 6번 절에서, 비싼 동네에는 ‘커피전문점, 부동산중개, 여성미용실’이 많았습니다. 이 정보를 이용해서 역으로 분석을 한 번 해봅니다. 가격이 낮은 ‘시군구’ Top-10 중에, ‘커피전문점, 부동산중개, 여성미용실’가 많은 동네를 찾아보는 겁니다.
아래 SQL입니다. 서울시와 다른 시는 가격차이가 크므로, 19번 라인에서 서울특별시만 조회해서 처리했습니다. 다른 지역도 필요하면 조건을 추가하시면 됩니다.
# 어디가 좋을까?
SELECT T1.XiGoonGuCode
,MAX(T1.XiDoName)
,MAX(T1.XiGoonGuName)
,COUNT(T2.ShopNo)
,COUNT(CASE WHEN T2.MktSDivCode = 'Q12A01' THEN T2.ShopNo END) CNT_COFFEE
,COUNT(CASE WHEN T2.MktSDivCode = 'L01A01' THEN T2.ShopNo END) CNT_BUDONG
,COUNT(CASE WHEN T2.MktSDivCode = 'F01A01' THEN T2.ShopNo END) CNT_BEAUTY
,ROUND(MAX(T1.AMT)) AVG_AMT
FROM (
SELECT T2.XiGoonGuCode
,MAX(T2.XiDoName) XiDoName
,MAX(T2.XiGoonGuName) XiGoonGuName
,AVG(T1.AMT) AMT
FROM T_KBLiivHouse T1
INNER JOIN M_RegionMap2 T2
ON (T1.BZDongCode = T2.BZDongCode)
WHERE T1.BaseYM = '201909'
AND T1.AMTDiv = 'BUY'
and T2.XidoName in ('서울특별시')#,'부산광역시','경기도')
GROUP BY T2.XiGoonGuCode
ORDER BY AVG(T1.AMT) ASC
LIMIT 10
) T1 INNER JOIN M_Shop T2
ON (T1.XiGoonGuCode = T2.XiGoonGuCode
AND T2.MktSDivCode IN ('Q12A01','L01A01','F01A01') )
GROUP BY T2.XiGoonGuCode
ORDER BY COUNT(T2.ShopNo) DESC;
아래와 같은 결과를 얻었습니다.
# 결과
XiGoonGuCode MAX(T1.XiDoName) MAX(T1.XiGoonGuName) COUNT(T2.ShopNo) CNT_COFFEE CNT_BUDONG CNT_BEAUTY AVG_AMT
============ ================ ==================== ================ ========== ========== ========== ===
11380 서울특별시 은평구 2004 559 493 952 515
11620 서울특별시 관악구 1952 505 600 847 570
11290 서울특별시 성북구 1792 552 480 760 573
11350 서울특별시 노원구 1724 536 386 802 517
11230 서울특별시 동대문구 1680 512 560 608 625
11530 서울특별시 구로구 1676 543 480 653 477
11260 서울특별시 중랑구 1449 308 350 791 469
11545 서울특별시 금천구 1127 364 344 419 474
11320 서울특별시 도봉구 1064 285 257 522 415
11305 서울특별시 강북구 992 252 183 557 434
여기까지입니다. 재미로 분석한 내용입니다. 절대!!! 투자 권유가 아닙니다.!!
'데이터분석 > SQL을 이용한 데이터 분석' 카테고리의 다른 글
| 배달의 민족, 주문 총금액 어떻게 구현하지? (0) | 2020.11.11 |
|---|---|
| 부동산 분석, 비싼 동네에는 뭐가 많지? 1/2 (0) | 2020.11.09 |
| 인구 데이터 수집 - 어디에 치킨집을 열어야 할까? (0) | 2020.11.04 |
| 상가 데이터 수집 2/2 (0) | 2020.10.30 |
| 상가 데이터 수집 - 1/2 (0) | 2020.10.30 |
| 서울 지하철 경도위도 설정 2/2 (0) | 2020.10.29 |
| 서울 지하철 경도위도 설정 1/2 (0) | 2020.10.29 |
| 김밥 말러 갑시다 2/2 (0) | 2020.10.25 |