부동산 분석, 비싼 동네에는 뭐가 많지? 1/2

제목 : 부동산 분석, 비싼 동네에는 뭐가 많지?

원본 위치 : DB 전문가 네트워크 디비안 (https://cafe.naver.com/dbian)

작성자 : SweetBoss

작성일 : 2019.10.23

 

1. 목적 및 환경

이 글은 누구라도 데이터를 활용 할 수 있게 하는데 목적이 있습니다. 기본적으로 SELECT, UPDATE등의 SQL은 사용할 줄 아는 사람들을 대상으로 합니다. 이 글을 통해 SQL 실력도 향상할 수 있습니다.

사용 DBMS : MySQL 5.7 Windows

사용 Tool : MySQL Workbench, MySQL Command Line Client

활용 주제 : 비싼 동네에는 뭐가 많지?

선행 주제 : 차례대로입니다.

1.     https://cafe.naver.com/dbian/2331

2.     https://cafe.naver.com/dbian/2335

3.     https://cafe.naver.com/dbian/2337

4.     https://cafe.naver.com/dbian/2338

 

 

2. KB부동산 데이터 수집

이 글의 주제는 비싼 동네(?)에는 어떤 상가가 많은지 분석해 보는 것입니다. 본격적으로 설명을 하기에 앞서, 이 글은 절대 투자 권유 글이 아니라는 것을 명시해주시기 바랍니다. 데이터를 분석하고 SQL 실력을 향상하기 위한 글입니다.

‘KB의 월간 주택가격 동향’ 데이터를 사용합니다. 아래 사이트의 메뉴에서 데이터를 얻을 수 있습니다.

- https://onland.kbstar.com/

- 메뉴: 뉴스/자료실 > 월간KB주택가격동향 > 리스트 제일 위에, ‘Liiv ON 아파트 시세’ 통계

- 위의 항목에 들어가서 아래 쪽에 ‘시세통계_면적당평균가_201909기준(2010버전).xlsx’

 

받은 엑셀 파일을 열어보면 아래와 같습니다.

위 엑셀 파일을 약간 변환합니다. 불필요한 1~4번 라인을 삭제하고 CSV 파일로 저장합니다. CSV로 저장된 파일을 메모장에서 열어보면 아래 그림과 같습니다.

데이터를 올릴 때, 한글이 깨지므로 메모장에서 UTF-8로 변환해서 저장하도록 합니다.

 

 

3. 업로드 테이블 설계 및 생성

아래와 같이 업로드 테이블을 설계합니다.

 

아래 SQL로 업로드용 테이블을 생성합니다.

# 업로드 테이블 생성
CREATE TABLE U_KBLiivHouse
(
XiDo VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
GuXiGun VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
Gu VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
DongYM VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
ValueDiv VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201609 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201610 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201611 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201612 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201701 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201702 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201703 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201704 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201705 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201706 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201707 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201708 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201709 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201710 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201711 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201712 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201801 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201802 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201803 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201804 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201805 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201806 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201807 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201808 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201809 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201810 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201811 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201812 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201901 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201902 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201903 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201904 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201905 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201906 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201907 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201908 VARCHAR(100) CHARACTER SET UTF8MB4 NULL,
AMT_201909 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
) ENGINE = InnoDB;

 

생성된 테이블에 CSV(UTF-8로 변환된) 파일을 올립니다. 아래와 같습니다.

# U_Population 업로드 (아래 SQL은 ROOT계정으로 MySQL Command Line Client에서 실행해야 합니다.)
USE SWEET_DATA;

SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_database = utf8;
SET character_set_results = utf8;
SET character_set_server = utf8;
LOAD DATA LOCAL INFILE 'C:\\upload\\up_budong_UTF8.CSV' INTO TABLE U_KBLiivHouse FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

 

 

4. 법정동코드 찾아내기

업로드된 데이터를 조회해보면 아래와 같습니다.

KB Liiv의 부동산 데이터의 ‘동’ 정보는 ‘법정동’이라고 합니다. ‘동’ 정보는 ‘행정동’과 ‘법정동’이 있는거 같습니다.

법정동은 법적으로 나누어진 동이고, 행정동은 실질적인 지역을 통제(?)하는 단위인듯 합니다. (제 짧은 생각입니다. 정확한지 모르겠습니다.)

어쨌든, 저번 글에서는 행정동을 기준으로 지역매핑을 만들었는데, 이번에는 법정동을 기준으로 지역매핑을 만들어야 합니다. 아래와 같이 테이블을 설계합니다.

테이블 명을 정하는데 많은 시간을 할애하기 힘들어서 그냥 M_RegionMap2로 했습니다. (이와 같은 방법은 다양한 장단점이 있습니다.) 그리고 테이블명에 지역 명칭도 모두 같이 넣었습니다. 위의 테이블 구조는 정규화를 위배하는 모델입니다. 하지만 분석을 편하게 하기 위해서, 또는 SQL을 간단화하기 위해서 예외적으로 위와 같이 설계를 할 수 도 있습니다. (이것 역시 장단점이 다양합니다.)

아래 SQL로 테이블을 생성합니다.

# M_RegionMap2(법정동 기준) 생성
CREATE TABLE M_RegionMap2
(
    BZDongCode VARCHAR(100) NOT NULL
    ,XiGoonGuCode VARCHAR(100) NOT NULL
    ,XiDoCode VARCHAR(100) NOT NULL
    ,BZDongName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
    ,XiGoonGuName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
    ,XiDoName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
    ,PRIMARY KEY(BZDongCode)
) ENGINE = InnoDB;

 

테이블을 생성한 후에는, 업로드상가(U_Shop) 테이블을 이용해 법정동 매핑 데이터를 만들어 냅니다. 실제로는 법정동 매핑이 관리되는 마스터 데이터가 별도로 필요합니다. 하지만 상가 정보를 이용해서 구성해도 큰 문제는 없어 보입니다. 아래 SQL을 이용합니다. SQL 실행전에 루트 권한으로 Buffer Pool을 늘려주셔야 합니다.(데이터활용3번 글 참고)

# 법정동 기준 데이터 생성
INSERT INTO M_RegionMap2
    (BZDongCode,XiGoonGuCode,XiDoCode
    ,BZDongName,XiGoonGuName,XiDoName)
 SELECT DISTINCT
     BZDongCode,XiGoonGuCode,XiDoCode
    ,BZDongName,XiGoonGuName,XiDoName
 FROM U_Shop
 WHERE BZDongCode IS NOT NULL
 AND XiGoonGuCode IS NOT NULL
 AND XiDoCode IS NOT NULL;

 

업로드한 부동산 테이블(U_KBLiivHouse)에는 법정동명칭만 있습니다. 법정동 코드 컬럼을 추가하고 방금 생성한 M_RegionMap2를 이용해 법정동 코드를 업데이트 하도록 합니다.

아래 SQL로 법정동코드를 추가합니다. (법정동 코드를 추가하면서 M_RegionMap2에 BZDongName과 XiGoonGuName 컬럼에 인덱스도 만들어 놓도록 합니다. 업데이트 성능을 위해서입니다.)

# 법정동코드 추가
ALTER TABLE U_KBLiivHouse ADD BZDongCode VARCHAR(100);

CREATE INDEX X_M_RegionMap2_1 ON M_RegionMap2(BZDongName, XiGoonGuName);

 

아래 SQL로 U_KBLiivHouse테이블의 법정동코드(BZDongCode)를 업데이트합니다.

# 법정동코드 업데이트
UPDATE U_KBLiivHouse A
       INNER JOIN M_RegionMap2 B
       ON (A.DongYM = B.BZDongName
          AND A.XiDo = B.XiDoName
          AND A.GuXiGun = B.XiGoonGuName)
SET    A.BZDongCode = B.BZDongCode;

 

위와 같이 업데이트 한 후에도, 법정동코드를 찾지 못하는 경우가 있습니다. 확인해 보면, U_KBLiivHouse의 ‘시군구’명과 M_RegionMap2의 ‘시군구’명의 구성이 차이가 있어서입니다. 아래와 같이 추가 업데이트를 합니다.(위에서 업데이트 되지 않은 대상만 업데이트 처리합니다.)

# 법정동코드 업데이트2
# 구시랑 구가 합쳐진 데이터 존재.
UPDATE U_KBLiivHouse A
       INNER JOIN M_RegionMap2 B
       ON (A.DongYM = B.BZDongName
          AND A.XiDo = B.XiDoName
          AND CONCAT(A.GuXiGun,A.Gu) = REPLACE(B.XiGoonGuName,' ',''))
SET    A.BZDongCode = B.BZDongCode
WHERE	A.BZDongCode IS NULL;

다시 한번 U_KBLiivHouse 에서 법정동코드(BZDongCode)가 NULL인 데이터를 조회해봅니다. 여전히 ‘충청북도 덕산읍’이 업데이트 되지 못했습니다. 이런 경우 수작업으로 찾아서 업데이트를 해야 하지만, 단 두 건이므로 무시하고 진행하도록 하겠습니다.

BZDongCode가 있는 데이터만 실제 분석용 테이블에 올린 후 사용하도록 하겠습니다