인구 데이터 수집 - 어디에 치킨집을 열어야 할까?
제목 : 인구데이터 수집 – 어디에 치킨집을?
원본 위치 : DB 전문가 네트워크 디비안 (https://cafe.naver.com/dbian)
작성자 : SweetBoss
작성일 : 2019.10.22
1. 목적 및 환경
이 글은 누구라도 데이터를 활용 할 수 있게 하는데 목적이 있습니다. 기본적으로 SELECT, UPDATE등의 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
2. 서울시 인구 데이터 수집
이 글의 주제는 치킨집을 차릴 곳을 찾는 것입니다. 시나리오는 어린 친구들(?)이 많으면서 치킨집이 적은 동네를 찾는 것입니다.
서울시 열린 데이터의 동별 인구 정보를 사용합니다. 아래 경로에서 받을 수 있습니다.
인구 정보에는 연령대별 인구수가 있습니다.
CSV 파일을 다운 받습니다. 파일을 열어보니 아래와 같습니다.
3. 업로드용 테이블 생성 및 업로드
아래 스크립트로 업로드용 테이블을 만듭니다
업로드 테이블 생성
CREATE TABLE U_Population
(
BasePeriod VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,ZCGu VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,HZDong VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,PopuDiv VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_Total VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_0_4 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_5_9 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_10_14 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_15_19 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_20_24 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_25_29 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_30_34 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_35_39 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_40_44 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_45_49 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_50_54 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_55_59 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_60_64 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_65_69 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_70_74 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_75_79 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_80_84 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_85_89 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_90_94 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_95_99 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,C_OV_100 VARCHAR(100) CHARACTER SET UTF8MB4 NULL
) ENGINE = InnoDB
;
데이터를 올리는 방법은 저번 글들에 이미 설명했습니다.
지금 올릴 파일도 UTF-8로 이미 만들어져 있습니다. UTF-8로 변환하지 않아도 됩니다. 다만 이번 파일은 CSV의 구분자가 콤마(,)가 아니라 탭(TAB)입니다. 그러므로 아래와 같디 LOAD DATA를 할 때 Field TERMINATED를 탭으로 주셔야 합니다.
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\\popu_data.txt' INTO TABLE U_Population FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
올라온 데이터 중에 불필요한 데이터(소계, 합계)를 삭제합니다.
U_Population 불필요 데이터 삭제
DELETE T1 FROM U_Population T1 WHERE HZDong in ('합계','소계');
DELETE T1 FROM U_Population T1 WHERE PopuDiv in ('계');
4. 행정동코드 찾아내기
데이터를 통합 분석하려면 연결고리가 있어야 합니다. 현재 올린 인구 데이터에는 행정동명은 있지만 행정동코드는 없습니다. 이전 글에서 상가(M_Shop)에는 행정동코드가 있습니다. 데이터를 연결하기 위해 인구 정보에도 행정동코드를 매핑할 생각입니다.
현재 시도 – 구군 – 행정동이 하이라키 구조인데, 이 구조를 관리하는 테이블이 없습니다. 아래와 같이 지역매핑 테이블을 먼저 생성해서 지역 하이라키를 관리할 예정입니다.
아래 SQL로 테이블을 생성합니다.
# M_RegionMap 생성
CREATE TABLE M_RegionMap
(
HZDongCode VARCHAR(100) NOT NULL
,XiGoonGuCode VARCHAR(100) NOT NULL
,XiDoCode VARCHAR(100) NOT NULL
,PRIMARY KEY(HZDongCode)
) ENGINE = InnoDB;
지역매핑(하이라키) 데이터는 저번 글의 U_Shop(업로드용상가정보) 테이블을 이용해 만듭니다. 데이터를 만들기 위해서는 루트 권한으로 버퍼풀을 늘려 놓아야 합니다.(저번 글 참고)
아래 SQL로 지역매핑을 만듭니다.
# M_RegionMap 데이터 생성
INSERT INTO M_RegionMap(HZDongCode, XiGoonGuCode, XiDoCode)
SELECT DISTINCT T1.HZDongCode
,T1.XiGoonGuCode
,T1.XiDoCode
FROM U_Shop T1
WHERE T1.HZDongCode IS NOT NULL
AND T1.XiGoonGuCode IS NOT NULL
AND T1.XiDoCode IS NOT NULL;
이제는 M_regionMap을 이용해서 U_Population 테이블의 행정동 코드를 업데이트합니다. 아래 SQL을 사용합니다.(서울시만 처리하면 됩니다.)
# U_Population에 행정동 코드 업데이트
ALTER TABLE U_Population ADD XiGoonGuCode VARCHAR(100);
ALTER TABLE U_Population ADD HZDongCode VARCHAR(100);
UPDATE U_Population A
INNER JOIN (
SELECT A.HZDongCode
,A.XiGoonGuCode
,C.BaseCodeName HZDongCodeName
,D.BaseCodeName XiGoonGuCodeName
FROM M_RegionMap A
INNER JOIN C_BaseCode C
ON (C.BaseCodeDiv = 'HZDongCode'
AND C.BaseCode = A.HZDongCode)
INNER JOIN C_BaseCode D
ON (D.BaseCodeDiv = 'XiGoonGuCode'
AND D.BaseCode = A.XiGoonGuCode)
WHERE A.XiDoCode
= (SELECT B.BaseCode FROM C_BaseCode B WHERE B.BaseCodeDiv = 'XiDoCode'
AND B.BaseCodeName = '서울특별시')
) B
ON (A.HZDong = B.HZDongCodeName
AND A.ZCGu = B.XiGoonGuCodeName)
SET A.XiGoonGuCode = B.XiGoonGuCode
,A.HZDongCode = B.HZDongCode;
위와 같이 업데이트를 해도, 행정동코드를 못 찾는 데이터가 있습니다. 바로 종로5,6가입니다. 상가정보의 행정동명과, 인구정보의 행정동명이 달라서 그렇습니다.
아래 SQL로 종로 5,6가는 수작업 업데이트 합니다.
# U_Population에 종로 5,6가 수작업 업데이트
UPDATE U_Population T1 SET HZDongCode = '1111063000', XiGoonGuCode = '11110' WHERE HZDong = '종로5•6가동';5. 인구 테이블 설계
아래 구조로 인구 테이블을 설계합니다.
아래 SQL로 테이블을 생성합니다.
# M_Popu생성
CREATE TABLE M_Popu(
BaseYQ VARCHAR(5) NOT NULL
,HZDongCode VARCHAR(100) NOT NULL
,PopuDiv VARCHAR(100) NOT NULL
,FromAge INT NOT NULL
,ToAge INT NOT NULL
,PopuCnt INT NOT NULL
,PRIMARY KEY(BaseYQ, HZDongCode, PopuDiv, FromAge)
);
데이터를 넣어야 하는데, 업로드용 테이블은 나이대별 인구수가 컬럼으로 되어 있습니다. 그리고 최종 사용할 테이블은 나이대가 FromAge, ToAge로 관리가 됩니다. 그러므로 업로드용 테이블의 컬럼들을 로우로 만들어야 합니다. 아래 SQL을 먼저 실행해봅니다.
# 나이대 그룹 만들기
SELECT CEIL(T1.Seq / 5) AgeGr
,MIN(T1.Seq) - 1 FromAge
,CASE WHEN MAX(T1.Seq) - 1 >= 100 THEN 999 ELSE MAX(T1.Seq) - 1 END ToAge
FROM C_Seq T1
GROUP BY CEIL(T1.Seq / 5)
HAVING MIN(T1.Seq) - 1 <= 100;
위 SQL을 실행하면, 0~4, 5~10과 같은 나이대 그룹 데이터가 만들어 집니다. 위 결과와 업로드 테이블을 CROSS JOIN해서 M_Popu에 INSERT합니다. 아래 SQL입니다. SQL을 보면 세션의 SQL_MODE에서 STRICT_TRANS_TABLES를 제거하고 있습니다. 이와 같이 해야만 데이터를 입력할 수 있습니다.(MySQL에서 형변환이 뭔가 잘 되지 않습니다…ㅜㅜ)
# M_Popu 데이터 만들기
SET @@SESSION.sql_mode = REPLACE(@@SESSION.sql_mode,'STRICT_TRANS_TABLES,','')
INSERT INTO M_Popu
(
BaseYQ
,HZDongCode
,PopuDiv
,FromAge
,ToAge
,PopuCnt
)
SELECT T0.BaseYQ
,T0.HZDongCode
,T0.PopuDiv
,T0.FromAge
,T0.ToAge
,CASE WHEN T0.PopuCnt = '-' THEN 0 ELSE ROUND(CAST(T0.PopuCnt AS UNSIGNED INT)) END PopuCnt
FROM (
SELECT '20192' BaseYQ
,HZDongCode
,CASE WHEN T1.PopuDiv = '한국인' THEN 'L' ELSE 'F' END PopuDiv
,T2.FromAge
,T2.ToAge
,CASE T2.FromAge
WHEN 0 THEN T1.C_0_4
WHEN 5 THEN T1.C_5_9
WHEN 10 THEN T1.C_10_14
WHEN 15 THEN T1.C_15_19
WHEN 20 THEN T1.C_20_24
WHEN 25 THEN T1.C_25_29
WHEN 30 THEN T1.C_30_34
WHEN 35 THEN T1.C_35_39
WHEN 40 THEN T1.C_40_44
WHEN 45 THEN T1.C_45_49
WHEN 50 THEN T1.C_50_54
WHEN 55 THEN T1.C_55_59
WHEN 60 THEN T1.C_60_64
WHEN 65 THEN T1.C_65_69
WHEN 70 THEN T1.C_70_74
WHEN 75 THEN T1.C_75_79
WHEN 80 THEN T1.C_80_84
WHEN 85 THEN T1.C_85_89
WHEN 90 THEN T1.C_90_94
WHEN 95 THEN T1.C_95_99
WHEN 100 THEN T1.C_OV_100 END PopuCnt
FROM U_Population T1
CROSS JOIN (
SELECT CEIL(T1.Seq / 5) AgeGr
,MIN(T1.Seq) - 1 FromAge
,CASE WHEN MAX(T1.Seq) - 1 >= 100 THEN 999 ELSE MAX(T1.Seq) - 1 END ToAge
FROM C_Seq T1
GROUP BY CEIL(T1.Seq / 5)
HAVING MIN(T1.Seq) - 1 <= 100
) T2
) T0;
데이터 입력까지 완료했습니다.
6. 어디에 치킨집을 열까?
15살 이하가 많이 살면서 치킨 집이 적은 동네에 치킨 집을 열도록 하겠습니다.
먼저, 상가테이블에 인덱스를 추가합니다. 데이터를 빨리 찾기 위해서죠.
# 인덱스 추가
CREATE INDEX X_M_Shop_1 ON M_Shop(HZDongCode, StndIndDivCode);
아래는 최종 SQL입니다.
# 어디에 치킨집을 열까?
SELECT T1.HZDongCode
,MAX(T1.HZDongCodeName) HZDongCodeName
,MAX(T1.XiGoonGuCodeName) XiGoonGuCodeName
,MAX(T1.PopuCnt) PopuCnt
,COUNT(T2.ShopNo) ShopCount
FROM (
SELECT T1.HZDongCode
,MAX(T3.BaseCodeName) HZDongCodeName
,MAX(T4.BaseCodeName) XiGoonGuCodeName
,SUM(T1.PopuCnt) PopuCnt
FROM M_Popu T1
INNER JOIN M_RegionMap T2
ON (T2.HZDongCode = T1.HZDongCode)
INNER JOIN C_BaseCode T3
ON (T3.BaseCodeDiv = 'HZDongCode'
AND T3.BaseCode = T2.HZDongCode)
INNER JOIN C_BaseCode T4
ON (T4.BaseCodeDiv = 'XiGoonGuCode'
AND T4.BaseCode = T2.XiGoonGuCode)
WHERE T1.ToAge <= 15
GROUP BY T1.HZDongCode
ORDER BY SUM(T1.PopuCnt) DESC
LIMIT 10
) T1
INNER JOIN M_Shop T2
ON (T2.HZDongCode = T1.HZDongCode
AND T2.StndIndDivCode = 'I56193') -- 치킨전문점
GROUP BY T1.HZDongCode
ORDER BY COUNT(T2.ShopNo) DESC;
아래와 같은 결과를 얻었습니다.
# 결과
HZDongCode HZDongCodeName XiGoonGuCodeName PopuCnt ShopCount
========== ============== ================ ======= =========
1150053500 등촌3동 강서구 2835 24
1165051000 서초1동 서초구 2645 19
1135057000 월계2동 노원구 2762 15
1121577000 중곡4동 광진구 2669 15
1132051500 창5동 도봉구 2655 15
1159056000 상도4동 동작구 2955 14
1171061000 삼전동 송파구 2931 11
1171062000 가락본동 송파구 2636 10
1132052200 도봉2동 도봉구 2726 8
1138063100 신사1동 은평구 2842 6
신사1동이 15살 이하 어린이들이 많으면서 집계된 치킨집은 6개 밖에 없습니다. 물론 이 데이터가 백퍼센트 정확하다고 생각되지는 않습니다. 그리고 다양하게 다른 것도 고민해봐야겠죠.
데이터를 이와 같이 분석할 수 있다는 것에 가치를 두셨으면 합니다!~
이상입니다. 감사합니다.!