이 글은 누구라도 데이터를 활용 할 수 있게 하는데 목적이 있습니다. 기본적으로 SELECT, UPDATE등의 SQL은 사용할 줄 아는 사람들을 대상으로 합니다. 이 글을 통해 SQL 실력도 향상할 수 있습니다. 이번 글은 특히 MySQL의 성능을 다양하게 측정해보는 과정에 목적이 있습니다.
사용 DBMS : MySQL 5.7 Windows
사용 Tool : MySQL Workbench, MySQL Command Line Client
오늘은 데이터 활용과는 약간 어긋난 주제가 될지도 모르겠습니다. 최근에 이슈가 된 ‘배달의 민족’의 ‘나의 총 주문금액’을 구하는 과정을 구현해보려고 합니다. 어쩌면 뻔한 이야기가 될 수도 있습니다. 아직은 DB를 잘 모르거나, MySQL을 많이 접해보지 않은 분들을 대상으로 합니다. 디비에 능통하신 ‘디비안’ 회원님들은 스킵 부탁드립니다. 먼저, 저는 ‘배달의 민족’과 아무 관련이 없습니다.!
먼저, SQL 테스트를 위해 여기서는 사용자 테이블과 주문 테이블, 일자(C_BaseDate) 테이블을 신규로 생성합니다. 아래와 같습니다.
주문 테이블은 기존에 과정에서 만들었던 M_Shop을 참조합니다. 다만, FK를 설정하지는 않을 예정입니다.
(1) 일자 테이블 생성 및 데이터 생성
아래 SQL로 일자 테이블을 만들고 데이터를 생성합니다. 기존의 ‘데이터활용6’ 과정에서 이미 만들었던 테이블입니다. 해당 과정을 진행하신 분은 추가로 수행하실 필요 없습니다.
# 일자 테이블 생성 및 데이터 생성
# C_BaseDate
CREATE TABLE C_BaseDate
(
BaseDT DATE NOT NULL
,BaseDTSeq INT NOT NULL
, PRIMARY KEY(BaseDT)
, UNIQUE KEY(BaseDTSeq)
) ENGINE = InnoDB;
INSERT INTO C_BaseDate (BaseDT, BaseDTSeq)
SELECT '2015-01-01', 1 FROM DUAL;
-- 결과 건수가 0건이 될때까지 반복 실행한다.(2015-01-01부터 2030-12-31까지 데이터 생성)
INSERT INTO C_BaseDate (BaseDT, BaseDTSeq)
SELECT DATE_ADD(T1.BaseDT, interval T2.MAX_Seq day) BaseDT
,T1.BaseDTSeq + T2.MAX_Seq
FROM C_BaseDate T1
CROSS JOIN (SELECT MAX(BaseDT) MAX_DT, MAX(BaseDTSeq) MAX_Seq FROM C_BaseDate) T2
WHERE DATE_ADD(T1.BaseDT, interval T2.MAX_Seq day) <= '2030-12-31';
(2) 사용자 테이블 생성 및 데이터 생성
M_User 테이블을 만들고 데이터를 생성합니다. 총 10,000명의 사용자를 생성합니다. M_Shop 테이블에서 10000건의 데이터를 가져와서 사용자 데이터를 생성합니다. 만 건의 데이터가 필요해서 M_Shop을 임의로 사용했습니다.
# M_User 테이블과 데이터 생성
CREATE TABLE M_User
( UserNo INT NOT NULL
,UserName VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,PRIMARY KEY(UserNo)
) ENGINE = InnoDB;
INSERT INTO M_User(UserNo, UserName)
SELECT @RNO := @RNO + 1 UserNo
,CONCAT('U_',@RNO) UserNamr
FROM (SELECT T1.ShopNo FROM M_Shop T1 ORDER BY ShopNo LIMIT 10000 ) A
CROSS JOIN (SELECT @RNO := 0 FROM DUAL) B
;
SQL의 8번과 11번 라인의 @RNO를 이용해서 순번을 부여하는 패턴은 유용하니 익혀두도록 합니다. 하지만 해당 패턴은 절대 남발하지 않습니다. 임시성이나 일회성 SQL에만 적절한 패턴입니다. 실제 서비스되는 SQL에서는 사용하지 않기를 가이드합니다. (이유는 성능상, 불리할 수 있기 때문입니다.)
(3) 상장매핑 임시 테이블 생성
M_Shop의 ShopNo는 1부터 시작하지 않습니다. 주문 데이터를 생성할 때, 실제 M_Shop의 ShopNo를 물리기 위해서, ShopNo에 1부터 시퀀스한 숫자를 매핑해서 2,000개 데이터를 만듭니다. 실전에서 ID를 완전히 새로 부여할 때 자주 사용하는 패턴입니다.
# 상장매핑 임시 테이블 생성
CREATE TEMPORARY TABLE TMP_MAPSHOP AS
SELECT @RNO := @RNO + 1 MapNo
,A.ShopNo
FROM (SELECT T1.ShopNo FROM M_Shop T1 ORDER BY ShopNo LIMIT 2000 ) A
CROSS JOIN (SELECT @RNO := 0 FROM DUAL) B
;
ALTER TABLE TMP_MAPSHOP ADD INDEX X_TMP_MAPSHOP(MapNo,ShopNo);
성능에 문제 없도록 인덱스도 잡아줍니다.
(4) 주문 테이블 생성 및 데이터 생성
아래 SQL로 T_Order를 생성하고, 가상의 주문 데이터를 만들어 냅니다.
# 주문 테이블 생성 및 데이터 생성
# ROOT 권한으로 다음 SQL을 실행해 버퍼를 늘려 놓는 것이 좋습니다.
SET GLOBAL innodb_buffer_pool_size = 402653184;
CREATE TABLE T_Order
(
OrderNo BIGINT AUTO_INCREMENT NOT NULL
,OrderDTM DATETIME NOT NULL
,OrderAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
,DeliveryAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
,PayAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
,UserNo INT NOT NULL
,ShopNo INT NOT NULL
,PRIMARY KEY(OrderNO)
) ENGINE = InnoDB;
INSERT INTO T_Order (OrderDTM,OrderAMT,DeliveryAMT,PayAMT,UserNo,ShopNo)
SELECT T1.OrderDTM
,10000 OrderAMT
,1000 DeliveryAMT
,11000 PayAMT
,T1.UserNo
,(SELECT A.ShopNo FROM TMP_MAPSHOP A WHERE A.MapNo = T1.MapNo) ShopNo
FROM (
#일반 사용자
SELECT DATE_ADD(T1.BaseDT, interval MOD(T2.UserNo, 240) minute) OrderDTM
,T2.UserNo
,MOD(T1.BaseDTSeq + T2.UserNo ,2000) + 1 MapNo
FROM C_BaseDate T1
CROSS JOIN ( SELECT * FROM M_User A WHERE UserNo >= 30) T2
WHERE T1.BaseDT <= STR_TO_DATE('2019-10-31','%Y-%m-%d')
AND WEEKDAY(T1.BaseDT) = 6 #일요일만 배달.
UNION ALL
#우수(?) 사용자 매일 배달.
SELECT DATE_ADD(T1.BaseDT, interval MOD(T2.UserNo, 240) minute) OrderDTM
,T2.UserNo
,MOD(T1.BaseDTSeq + T2.UserNo ,2000) + 1 MapNo
FROM C_BaseDate T1
CROSS JOIN ( SELECT * FROM M_User A WHERE UserNo < 30) T2
WHERE T1.BaseDT <= STR_TO_DATE('2019-10-31','%Y-%m-%d')
) T1
ORDER BY T1.OrderDTM, T1.UserNo
;
주문을 만들 때, UserNo가 30 미만인 경우는 매일 주문을 한 우수(?) 사용자처럼 주문을 만들어 줍니다. 30 이상은 일주일에 한번 주문한 것으로 만들어줍니다. 총 2,563,877 건의 주문 데이터가 만들어 집니다.
3. 총 주문 금액 조회하기 – 테이블 전체 스캔
총 주문 금액을 조회해보도록 합니다.
SHOW STATUS를 실행하면 innodb에서 읽어들인 row 건수를 확인할 수 있습니다. 아래와 같이 100번 사용자의 총 주문금액을 조회합니다.
# 100번 사용자 주문금액 확인
SHOW STATUS LIKE 'innodb_rows_read';
SELECT SUM(T1.OrderAMT) , COUNT(*) FROM T_Order T1 WHERE T1.UserNo = 100;
SHOW STATUS LIKE 'innodb_rows_read';
제 환경에서 ‘Innodb rows’는 SQL 실행 전에 251 실행 후에 2,564,128이 나옵니다. 총 2,563,877(=2,564,128-251) 건을 읽은 것을 알 수 있습니다. 2,563,877은 테이블의 전체 건수입니다. 100번 사용자의 주문총금액을 구하기 위해 테이블의 전체 데이터를 읽은 것입니다.
이번에는 주문이 많은 10번 사용자의 총 주문금액을 조회합니다.
# 10번 사용자 주문금액 확인
EXPLAIN
SELECT SUM(T1.OrderAMT) FROM T_Order T1 WHERE T1.UserNo = 10;
아래와 같은 실행계획이 나옵니다. Type이 ALL이고 key값이 비어 있으면 테이블 전체를 읽은 것입니다. 당연히 T_Order에는 OrderNo로 구성된 PK 밖에 없으니, 원하는 데이터를 찾으려면 테이블 전체를 뒤질 수 밖에 없습니다.
4. 총 주문 금액 조회하기 – UserNo 인덱스 사용
UserNo 컬럼에 인덱스를 만들어 3번에서 수행한 테스트를 동일하게 해보도록 하겠습니다. 인덱스에 대해서는 추가적인 공부가 필요합니다. 친절한SQL튜닝책을 추천드립니다.
아래와 같이 UserNo 로 구성된 인덱스를 만듭니다.
# UserNo 인덱스 생성
CREATE INDEX X_T_Order_1 ON T_Order(UserNo);
인덱스가 잘 만들어졌는지는 아래 SQL로 확인합니다.
# 인덱스 확인
show index from T_Order;
인덱스가 만들어 졌다면 다시 innodb_rows_read를 확인하면서, 100번 사용자와 10번 사용자의 총 주문금액을 조회해봅니다.
100번 사용자의 총 주문금액을 조회합니다.
# 100번 사용자 주문금액 확인
SHOW STATUS LIKE 'innodb_rows_read';
SELECT SUM(T1.OrderAMT) , COUNT(*) FROM T_Order T1 WHERE T1.UserNo = 100;
SHOW STATUS LIKE 'innodb_rows_read';
100번 사용자 SQL 실행전과 후과 rows_read를 비교해 보면 251건의 데이터만 읽은 것을 알 수 있습니다.
이번에는 10번 사용자를 테스트해봅니다.
# 10번 사용자 주문금액 확인
SHOW STATUS LIKE 'innodb_rows_read';
SELECT SUM(T1.OrderAMT) , COUNT(*) FROM T_Order T1 WHERE T1.UserNo = 10;
SHOW STATUS LIKE 'innodb_rows_read';
10번 사용자는 1765건의 rows_read가 발생했습니다. 실제 해당 사용자가 주문한 건수만큼만 접근했습니다. UserNo에 인덱스를 만들어서 성능을 향상했습니다.
5. 총 주문 금액 조회하기 – UserNo + OrderAMT 인덱스 사용
이번에는 UserNo와 OrderAMT에 인덱스를 만들어서 테스트해보도록 합니다.
인덱스를 만들기 전에, EXPLAIN FORMAT=JSON을 사용해, 10번 사용자의 실행계획을 JSON형태로 조회해봅니다. EXPLAIN보다 자세한 정보들이 있습니다.
# 10번 사용자 주문금액 확인 - UserNo인덱스 – 실행계획 얻기
EXPLAIN FORMAT = JSON
SELECT SUM(T1.OrderAMT) , COUNT(*) FROM T_Order T1 WHERE T1.UserNo = 10;
이전 SQL과 비교해보면, cost가 현저히 낮아졌습니다. 인덱스에 OrderAMT가 추가되어서 인덱스만으로 모든 문제를 해결할 수 있기 때문이다. 사실,,, 이러한 부분이 실행계획에 명확히 표시되면 좋은데 MySQL은 그렇지가 않습니다. 그러므로 오라클을 사용하던 분들이 MySQL을 쓰면 답답해서 속이 터집니다. 그래도 현실에 적응해야 합니다. 그냥 무료로 사용할 수 있음에 감사해야겠습니다.
실제, 실행시간도 측정해보도록 합니다. 아래 SQL들을 실행해봅니다.
# 2번 사용자, 1번 사용자 주문금액 확인(각각 다른 인덱스 사용)
SET PROFILING = ON;
SELECT SQL_NO_CACHE SUM(T1.OrderAMT),COUNT(*) FROM T_Order T1 FORCE INDEX(X_T_Order_2)
WHERE T1.UserNo = 2;
SELECT SQL_NO_CACHE SUM(T1.OrderAMT),COUNT(*) FROM T_Order T1 FORCE INDEX(X_T_Order_1)
WHERE T1.UserNo = 1;
일부러, 기존의 버퍼캐시의 데이터를 읽지 않도록 다른 UserNo를 조회합니다. 첫 번쨰 SQL은 UserNo+OrderAMT 인덱스를 사용하게 했고, 두 번째 SQL은 UserNo 인덱스를 사용하도록 힌트를 주었습니다.
SHOW PROFILES로 실행 성능을 확인해봅니다.
SHOW PROFILES;
저의 환경에서는 UserNo+OrderAMT 인덱스를 사용한 경우에는 0.008초로 측정되었고, UserNo 인덱스를 사용한 경우에는 0.2초로 측정되었습니다. 실제 시간으로도 UserNo+OrderAMT가 월등한걸 알 수 있습니다. (너무 당연한걸 길게 설명하고 있습니다.ㅜ.ㅜ)
추가로.. 개인적으로 OrderAMT와 같은 금액, 단가와 같은 컬럼이 인덱스에 들어가는 것을 별로 추천하지는 않습니다. 꼭 어쩔 수 없을 때만 사용합니다.
6. 총 주문 금액 – 집계 테이블 전략
UserNo+OrderAMT 인덱스를 만들었어도, 10번 사용자의 총 주문 금액을 조회하려면 1,765건의 데이터를 읽어야 하는 것은 피할 수 없습니다. (물론, 인덱스의 리프 블록만 읽으므로 매우 빠릅니다.) 한 명이 사용하는 거라면 큰 이슈가 없겠지만, 배달의 민족처럼 매우 많은 사용자가 한 순간에 조회를 요청하면 이 또한 심한 부하가 발생될 수 있습니다. 이를 해결하기 위해 집계(배치) 테이블 전략을 시도해보겠습니다. 사실 집계 테이블을 하면 성능이 월등히 좋을거야란 추측으로 시도했으나 현실은 그렇지 않네요!!!!!. 어쨌든, SQL에 대한 공부로 생각해주시면 감사하겠습니다.
아래와 같은 집계 테이블을 설계합니다.
테이블을 보면, 누적주문금액 컬럼을 가지고 있습니다. 사용자의 주문년월까지의 누적주문금액을 저장합니다. 예를 들어 2018년1월이라면 최초부터 2018년1월까지의 주문금액 합계가, 2019년2월이라면 최초부터 2019년2월까지의 주문금액을 누적해서 저장합니다. 마지막 월의 해당 값만 읽어와서 총주문금액을 처리하는 전략입니다.
아래 스크립트로 테이블을 생성합니다.
# 사용자월별주문 집계 테이블 생성
CREATE TABLE S_UserOrderYM
(
OrderYM VARCHAR(6) CHARACTER SET UTF8MB4 NOT NULL
,UserNo INT NOT NULL
,OrderAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
,DeliveryAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
,PayAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
,SUMOrderAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
,PRIMARY KEY(OrderYM,UserNo)
) ENGINE = InnoDB;
아래 SQL로 SUMOrderAMT를 제외한 2019년9월까지의 주문을 S_UserOrderAMT에 입력합니다.
# 사용자월별주문 초기 데이터 생성
INSERT INTO S_UserOrderYM
(OrderYM,UserNo,OrderAMT,DeliveryAMT,PayAMT)
SELECT DATE_FORMAT(OrderDTM,'%Y%m')
,UserNo
,SUM(T1.OrderAMT)
,SUM(T1.DeliveryAMT)
,SUM(T1.PayAMT)
FROM T_Order T1
WHERE OrderDTM < '2019-10-01'
GROUP BY DATE_FORMAT(OrderDTM,'%Y%m'), UserNo;
아직, 10월달 데이터와 SUMOrderAMT는 입력하지 않았습니다. 입력된 9월까지의 SUMOrderAMT는 아래 업데이트 SQL로 처리합니다. MySQL 5.7은 분석함수 기능이 없습니다. 분석함수 기능이 있다면, 분석 함수 한 줄로 깔끔하게 총 주문금액을 처리할 수 있는데 안타깝습니다. 어쩔 수 없이 아래와 같이 업데이트 합니다. 업데이트 전에 루트 계정으로 버퍼풀 사이즈도 늘려주고 추가로 인덱스도 만들어줍니다. 업데이트 처리시에 성능 때문입니다.
# 사용자월별주문 누적주문금액 업데이트
SET GLOBAL innodb_buffer_pool_size = 402653184;
CREATE INDEX X_S_UserOrderYM ON S_UserOrderYM(UserNo,OrderYM,OrderAMT);
UPDATE S_UserOrderYM T1
right outer join (
SELECT A.UserNo, A.OrderYM , SUM(B.OrderAMT) SUMOrderAMT
FROM S_UserOrderYM A
INNER JOIN S_UserOrderYM B
ON (A.UserNO = B.UserNo AND A.OrderYM >= B.OrderYM)
GROUP BY A.UserNo, A.OrderYM
) T2
ON (T1.UserNo = T2.UserNo AND T1.OrderYM = T2.OrderYM)
SET T1.SUMOrderAMT = T2.SUMOrderAMT;
업데이트를 보시면 S_UserOrderYM과 인라인-뷰(7~12)의 결과를 아우터-조인(6번 라인) 하고 있습니다. 업데이트 처리가, 인라인-뷰=>S_UserOrderYM 순으로 해야 성능이 좋을거 같습니다. 그냥 INNER JOIN을 하면 이처럼 안되기 때문에 일부러 RIGHT OUTER JOIN을 사용했습니다. 아주,… 힌트가 안되니 어렵습니다. (오라클이 그립습니다.)
이제 주문총금액을 조회해봅니다. 그런데, 집계 테이블에는 9월 주문까지만 있습니다. 그러므로 10월 주문금액 정보는 T_Order에서 읽어야 합니다. T_Order의 효율적인 접근을 위해 아래와 같이 인덱스를 추가합니다.(인덱스가 계속 늘어납니다. 좋지 않습니다. 나중에 불필요한 인덱스는 제거해야 합니다.)
# 집계 테이블을 이용한 사용자 총주문금액 조회 – T_Order 인덱스 추가
CREATE INDEX X_T_Order_3 ON T_Order(UserNo,OrderDTM);
아래는 실제 조회 SQL입니다.
집계 테이블을 이용한 사용자 총주문금액 조회
SHOW STATUS LIKE 'innodb_rows_read';
SELECT MAX(T1.SUMOrderAMT) + IFNULL(SUM(T2.OrderAMT),0)
FROM (
SELECT T1.SUMOrderAMT
,DATE_ADD(STR_TO_DATE(CONCAT(T1.OrderYM,'01'),'%Y%m%d'),interval 1 month) FromDate
FROM S_UserOrderYM T1
WHERE T1.UserNo = 10
ORDER BY T1.OrderYM DESC
LIMIT 1
) T1
LEFT OUTER JOIN T_Order T2
ON (T2.UserNo = 10 AND T2.OrderDTM >= T1.FromDate);
SHOW STATUS LIKE 'innodb_rows_read';
위 SQL에서 주의 깊게 볼 만한 부분은 T1과 T2를 조인 처리하는 부분입니다.보시고 무릎을 탁 치신다면 이미 SQL은 저만큼 하시는 겁니다.
Rows_read를 확인해보면, 이전에는 1,765건을 읽었지만 이번에는 33건만 읽어서 결과를 처리했습니다. 집계 테이블이라는 불편한 작업이 있었지만, 확실한 성능 개선 효과가 있었다고 단정할 수 있습니다. JSON 실행계획도 확인해 보시기 바랍니다. 전체 실행계획은 훨씬 길고 복잡해졌지만 query cost가 44로 확실하게 좋아졌습니다.
이제 실제 실행시간도 측정해 봅니다. 실제 실행시간을 측정하기 전에는 당연히 집계 전략이 유리할거라 생각했습니다.!!!!! 그러나…
실제 실행시간을 측정해 봅니다. 각각 다른 사용자를 사용합니다.
# 집계 테이블을 이용한 사용자 총주문금액 조회 – 시간 측정
SET PROFILING = ON;
SELECT SQL_NO_CACHE
MAX(T1.SUMOrderAMT) + IFNULL(SUM(T2.OrderAMT),0)
FROM (
SELECT T1.SUMOrderAMT
,DATE_ADD(STR_TO_DATE(CONCAT(T1.OrderYM,'01'),'%Y%m%d'),interval 1 month) FromDate
FROM S_UserOrderYM T1
WHERE T1.UserNo = 23
ORDER BY T1.OrderYM DESC
LIMIT 1
) T1
LEFT OUTER JOIN T_Order T2
ON (T2.UserNo = 23
AND T2.OrderDTM >= T1.FromDate);
SELECT SQL_NO_CACHE SUM(T1.OrderAMT),COUNT(*) FROM T_Order T1 FORCE INDEX(X_T_Order_2)
WHERE T1.UserNo = 29;
SHOW PROFILES;
실제 실행시간을 확인해 보면, 사용자의 T_Order를 그냥 모두 읽은 것이 더 좋습니다. SHOW PROFILES에서 얻은 각 쿼리의 ID를 이용해 실행타임을 자세하게 분석해 봅니다.
# 쿼리ID를 이용해 자세한 단계별 실행시간 확인
#집계 테이블 사용+실적 테이블 사용
SHOW PROFILE FOR QUERY 135;
#실적 테이블만 사용
SHOW PROFILE FOR QUERY 136;
비교 결과는 아래와 같습니다.
비교해보면, ‘집계 테이블과 조인’은 두 번의 executing과 sending data가 발생합니다. 아마도 조인을 위해 스토리지 엔진을 나누어서 다녀오기 때문인 듯 합니다.
지금의 상황으로는 집계 테이블이 유리한 상황은 아닌 거 같습니다. (하지만 데이터가 더 많고, 많은 사용자가 몰린다면 집계 전략이 훨씬 유리할거라 여전히 추측은 됩니다.) 추가로 해보고 싶은 전략도 많고, 데이터 분포 변경에 따른 테스트도 해보고 싶지만 여기까지 쓰고 줄이도록 하겠습니다.
감사합니다.
이처럼, 데이터를 분석하는 과정을 공부해보고 싶으신 분은 아래의 '평생 필요한 데이터 분석'의 교육 과정을 추천합니다. 교육을 통해 SQL을 배운다면, 위 내용을 좀 더 보강할 수도 있고, 자신만의 스타일로 분석을 할 수 있습니다. SQL을 완전히 자신의 것으로 만들 수 있는 교육이니 관심 가져보시기 바랍니다. 감사합니다.~!
# 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;
위의 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
- 메뉴: 뉴스/자료실 > 월간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인 데이터를 조회해봅니다. 여전히 ‘충청북도 덕산읍’이 업데이트 되지 못했습니다. 이런 경우 수작업으로 찾아서 업데이트를 해야 하지만, 단 두 건이므로 무시하고 진행하도록 하겠습니다.
업로드 테이블 생성
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;
-- 기준 코드 테이블 생성
CREATE TABLE C_BaseCodeDiv
( BaseCodeDiv VARCHAR(100) NOT NULL
,BaseCodeDivName VARCHAR(500) CHARACTER SET UTF8MB4 NULL
,PRIMARY KEY(BaseCodeDiv)
);
CREATE TABLE C_BaseCode
( BaseCodeDiv VARCHAR(100) NOT NULL
,BaseCode VARCHAR(100) NOT NULL
,BaseCodeName VARCHAR(500) CHARACTER SET UTF8MB4 NULL
,SortOrder INT NULL
,PRIMARY KEY(BaseCodeDiv, BaseCode)
);
ALTER TABLE C_BaseCode
ADD CONSTRAINT FK_C_BaseCode_1 FOREIGN KEY(BaseCodeDiv) REFERENCES C_BaseCodeDiv(BaseCodeDiv);
기준 코드 데이터를 만들기 전에 아래와 같이 MySQL의 버퍼풀의 크기를 늘려줍니다. 만약에 버퍼가 충분하다면 상관 없는데, 버퍼가 모자라면 많은 데이터를 읽어서 INSERT하는 중에 에러가 발생합니다.
-- ROOT계정으로 접속해서 실행, 버퍼사이즈 업
SET GLOBAL innodb_buffer_pool_size = 402653184;
버퍼를 늘렸으면, 아래 SQL로 기준코드 데이터를 만듭니다.
(타임아웃이 날 경우, My.ini에 [mysqld] skip-name-resolve 설정이 필요합니다.)
-- 기준코드 데이터 만들기
INSERT INTO C_BaseCodeDiv(BaseCodeDiv, BaseCodeDivName)
VALUES('MktLDivCode','상권업종대분류코드'),
('MktMDivCode','상권업종중분류코드'),
('MktSDivCode','상권업종소분류코드'),
('StndIndDivCode','표준산업분류코드'),
('XiDoCode','시도코드'),
('XiGoonGuCode','시군구코드'),
('HZDongCode','행정동코드'),
('BZDongCode','법정동코드'),
('DaeJiDivCode','대지구분코드'),
('DoroCode','도로명코드');
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT DISTINCT 'DaeJiDivCode', T1.DaeJiDivCode, T1.DaeJiDivName FROM U_Shop T1;
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT 'DoroCode', T1.DoroCode, MAX(T1.DoroName) FROM U_Shop T1 GROUP BY T1.DoroCode;
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT DISTINCT 'HZDongCode', T1.HZDongCode, T1.HZDongName FROM U_Shop T1;
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT DISTINCT 'MktLDivCode', T1.MktLDivCode, T1.MktLDivName FROM U_Shop T1;
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT DISTINCT 'MktMDivCode', T1.MktMDivCode, T1.MktMDivName FROM U_Shop T1;
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT DISTINCT 'MktSDivCode', T1.MktSDivCode, T1.MktSDivName FROM U_Shop T1;
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT DISTINCT 'StndIndDivCode', T1.StndIndDivCode, T1.StndIndDivName FROM U_Shop T1;
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT DISTINCT 'XiDoCode', T1.XiDoCode, T1.XiDoName FROM U_Shop T1;
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT DISTINCT 'XiGoonGuCode', T1.XiGoonGuCode, T1.XiGoonGuName FROM U_Shop T1;
INSERT INTO C_BaseCode(BaseCodeDiv, BaseCode, BaseCodeName)
SELECT DISTINCT 'BZDongCode', T1.BZDongCode, T1.BZDongName FROM U_Shop T1;
여기서 기준코드의 기준코드구분(BaseCodeDiv)의 값을 보면, 컬럼명을 그대로 사용합니다. 찾아보기 쉽게 하기 위해서입니다. 이와 같이 사용하는 방법에는 장단점이 있습니다.(여기서 설명하지는 않습니다.) 그리고 기준코드라고 하기에는 부적합해 보이는 코드들도 있습니다. 도로코드와 같은 데이터입니다. 별도의 지역마스터를 구성하는 것이 가장 좋다고 생각됩니다. 하지만 그렇게 하려면 공수가 많이 들어가므로 우선은 모두 기준 코드로 처리하도록 하겠습니다.
6. 상가 테이블 설계
드디어, 상가 테이블을 만들고 데이터를 넣을 차례입니다. 상가 테이블은 아래와 같은 구조입니다.
아래 SQL로 상가(M_Shop) 테이블을 생성합니다.
-- 상가 테이블 생성하기
CREATE TABLE M_Shop(
ShopNo INT NOT NULL
,ShopName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BranchName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,MktLDivCode VARCHAR(100)
,MktMDivCode VARCHAR(100)
,MktSDivCode VARCHAR(100)
,StndIndDivCode VARCHAR(100)
,XiDoCode VARCHAR(100)
,XiGoonGuCode VARCHAR(100)
,HZDongCode VARCHAR(100)
,BZDongCode VARCHAR(100)
,JiBunCode VARCHAR(100)
,DaeJiDivCode VARCHAR(100)
,JiBunPrimNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,JiBunSecnNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,JiBunAddress VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,DoroCode VARCHAR(100)
,BuildingPrimNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BuildingSecnNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BuildingMngNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BuildingName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,DoroAddress VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,OldZipCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,NewZipCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,DongInfo VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,CengInfo VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,HoInfo VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,PosXY POINT NOT NULL
,PRIMARY KEY(ShopNo)
) ENGINE = InnoDB;
아래 SQL로 상가(M_Shop) 테이블에 데이터를 생성합니다. 여기서, JiBunCode를 넣을 때, 데이터 때문에 오류가 납니다. 일단 사용안할 예정이므로 ‘’(빈값)으로 처리해서 값을 입력합니다.
-- 상가 데이터 생성
INSERT INTO M_Shop
(ShopNo,ShopName,BranchName,MktLDivCode,MktMDivCode,MktSDivCode
,StndIndDivCode,XiDoCode,XiGoonGuCode,HZDongCode,BZDongCode
,JiBunCode,DaeJiDivCode,JiBunPrimNumber,JiBunSecnNumber,JiBunAddress,DoroCode
,BuildingPrimNumber,BuildingSecnNumber,BuildingMngNumber,BuildingName,DoroAddress
,OldZipCode,NewZipCode,DongInfo,CengInfo,HoInfo,PosXY)
SELECT ShopNo,ShopName,BranchName,MktLDivCode,MktMDivCode,MktSDivCode
,StndIndDivCode,XiDoCode,XiGoonGuCode,HZDongCode,BZDongCode
,'' JiBunCode,DaeJiDivCode,JiBunPrimNumber,JiBunSecnNumber,JiBunAddress,DoroCode
,BuildingPrimNumber,BuildingSecnNumber,BuildingMngNumber,BuildingName,DoroAddress
,OldZipCode,NewZipCode,DongInfo,CengInfo,HoInfo,POINT(T1.PosX, T1.PosY)
FROM U_Shop T1;
7. 우리 동네에서 가장 많은 업종은?
간단히 몸풀기 분석을 해봅니다. 우리 동네에서 가장 많은 업종 10개를 찾아보겠습니다. 저희 동네는 중계동입니다.
-- 우리 동네에서 가장 많은 업종은?
SELECT T2.BaseCodeName BZDongCodeName
,T1.MktMDivCode
,T3.BaseCodeName MktMDivCodeName
,COUNT(*)
FROM M_Shop T1
LEFT OUTER JOIN C_BaseCode T2
ON (T2.BaseCodeDiv = 'BZDongCode' AND T2.BaseCode = T1.BZDongCode)
LEFT OUTER JOIN C_BaseCode T3
ON (T3.BaseCodeDiv = 'MktMDivCode' AND T3.BaseCode = T1.MktMDivCode)
WHERE T1.XiDoCode
= (SELECT A.BaseCode FROM C_BaseCode A WHERE A.BaseCodeDiv = 'XiDoCode' AND A.BaseCodeName = '서울특별시')
AND T1.BZDongCode
= (SELECT A.BaseCode FROM C_BaseCode A WHERE A.BaseCodeDiv = 'BZDongCode' AND A.BaseCodeName = '중계동')
GROUP BY T1.MktMDivCode
ORDER BY COUNT(*) DESC
LIMIT 10;
아래와 같은 결과가 나옵니다. 음..!! 그렇습니다. 아래 업종들은 저희 동네에서 쉽게 접근하면 안되겠습니다.
저번글에서 올렸던 지하철 승하차 정보와, 지하철 역의 위치(경도, 위도) 정보를 구축했습니다. 그리고 오늘은 상가 정보를 올렸습니다.
하차 Top 10 지하철 역 근처에 제일 많은 업종을 찾아보겠습니다.(승하차 중에 하차만 사용하겠습니다.) 먼저 하차 Top 10 지하철 역과 위치 정보를 조회해봅니다. 아래 SQL입니다.
-- 우리 동네에서 가장 많은 업종은
SELECT T1.*
,T2.StationName
,CAST(X(T2.PosXY) as CHAR) PosX
,CAST(Y(T2.PosXY) as CHAR) PosY
FROM (
SELECT T1.StationNo, SUM(UseCount) UseCount
FROM T_StationUse T1
WHERE T1.GetOnOffType = 'OFF'
GROUP BY T1.StationNo
ORDER BY SUM(UseCount) DESC
LIMIT 10
) T1
INNER JOIN M_Station T2
ON (T1.StationNo = T2.StationNo)
이제, 위치 정보를 이용해, 상가 정보와 조인을 합니다. 위치 정보 조인을 위해서, M_Shop의 위치 값에 SPATIAL INDEX인덱스를 만듭니다.
-- 인덱스 생성
CREATE SPATIAL INDEX SX_M_Shop_1 ON M_Shop(PosXY);
최종 SQL은 아래와 같습니다. (역 근처의 기준은 반경 1키로입니다.)
-- Top 10 하차 지하철 역 근처 상권 정보.
SELECT T3.MktMDivCode
,(SELECT A.BaseCodeName FROM C_BaseCode A
WHERE A.BaseCodeDiv = 'MktMDivCode' AND A.BaseCode = T3.MktMDivCode) MktMDivCodeName
,COUNT(*)
FROM (
SELECT T1.*
,T2.StationName
,ufn_getDiagonal(CAST(ST_X(T2.PosXY) as CHAR),CAST(ST_Y(T2.PosXY) as CHAR),1000) diag
FROM (
SELECT T1.StationNo, SUM(UseCount) UseCount
FROM T_StationUse T1
WHERE T1.GetOnOffType = 'OFF'
GROUP BY T1.StationNo
ORDER BY SUM(UseCount) DESC
LIMIT 10
) T1
INNER JOIN M_Station T2
ON (T1.StationNo = T2.StationNo)
LIMIT 10
) T1
INNER JOIN M_Shop T3
ON (MBRCONTAINS(ST_LINESTRINGFROMTEXT(T1.diag), T3.PosXY))
GROUP BY T3.MktMDivCode
ORDER BY COUNT(*) DESC
LIMIT 10;
파일이 제법 큽니다. 압축을 풀어보면, 4개의 파일이 있습니다. 4개의 파일을 모두 DB로 올려야 합니다. 우선은 업로드할 테이블을 정의해야 하니, 엑셀 하나를 열어봅니다. 아래와 같습니다.
3. 업로드용 테이블 설계 및 생성
업로드할 임시 테이블을 먼저 설계합니다. 앞에서 열었던 파일의 컬럼명들을 모두 나열해주면 됩니다.
컬럼이 너무 많아서 설계가 귀찮습니다. 컬럼명을 컬럼1, 2, 3, 4 처럼 하고 싶었으나… 그러면 안되니까.. 귀찮지만 적당히 컬럼명을 정했습니다. 순수 영어로 번역해서 컬럼명을 하고 싶었지만, 한국어를 100% 영문명으로 변환하기가 쉽지 않습니다. 어쩔 수 없이, DoroCode같은 한글을 발음 그대로 영문으로 사용한 컬럼들도 있습니다. 이해 부탁드립니다.
아래 스크립트로 테이블을 생성합니다.
-- 업로드 테이블 생성
CREATE TABLE U_Shop(
ShopNo VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,ShopName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BranchName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,MktLDivCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,MktLDivName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,MktMDivCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,MktMDivName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,MktSDivCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,MktSDivName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,StndIndDivCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,StndIndDivName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,XiDoCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,XiDoName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,XiGoonGuCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,XiGoonGuName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,HZDongCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,HZDongName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BZDongCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BZDongName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,JiBunCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,DaeJiDivCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,DaeJiDivName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,JiBunPrimNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,JiBunSecnNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,JiBunAddress VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,DoroCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,DoroName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BuildingPrimNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BuildingSecnNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BuildingMngNumber VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,BuildingName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,DoroAddress VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,OldZipCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,NewZipCode VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,DongInfo VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,CengInfo VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,HoInfo VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,PosX VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,PosY VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
) ENGINE = InnoDB
4. 상가정보 업로드하기.
CSV파일을 MySQL에 업로드 하기 위해서는 아래와 같은 과정이 필요합니다. 참고해주세요.
이전 글(서울지하철승하차분석)에서 ‘5. 테이블 업로드’편에서 이와 같이 작업을 했습니다. 여기서도 동일하게 작업을 할 예정입니다. 그런데 다행히도, 상가정보의 CSV파일은 이미 UTF-8로 되어 있습니다. CSV파일을 UTF-8로 변환할 필요가 없습니다. UTF-8로 올려준 공공데이터 포털에 감사합니다~!
아래 스크립트로 CSV파일 네 개를 T_Shop에 업로드합니다. 빨간색은 각자 폴더와 파일명으로 변경해 주세요. (바꿀때, 윈도우 환경에서 \\ 와 같이 \를 두개 사용하셔야 합니다.)
ð한글 파일명이 안되서, 영어로 파일명 바꾸어야 하네.. mysql 8에서는요.
ðC:\upload\ 폴더로 파일 옮겨서 했음.
-- U_StationUse 업로드 (아래 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:\\Users\\sweetboss\\Desktop\\데이터분석\\0030_상점데이터수집\\상가(상권)정보_201909\\소상공인시장진흥공단_상가업소정보_201909_01.CSV' INTO TABLE U_Shop FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\Users\\sweetboss\\Desktop\\데이터분석\\0030_상점데이터수집\\상가(상권)정보_201909\\소상공인시장진흥공단_상가업소정보_201909_02.CSV' INTO TABLE U_Shop FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\Users\\sweetboss\\Desktop\\데이터분석\\0030_상점데이터수집\\상가(상권)정보_201909\\소상공인시장진흥공단_상가업소정보_201909_03.CSV' INTO TABLE U_Shop FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\Users\\sweetboss\\Desktop\\데이터분석\\0030_상점데이터수집\\상가(상권)정보_201909\\소상공인시장진흥공단_상가업소정보_201909_04.CSV' INTO TABLE U_Shop FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
위 과정이 정상적으로 수행되면, U_Shop에는 1,090,952 건의 상가 정보가 만들어집니다. 상가가 참 많습니다.
업로드 완료한 후에는 아래와 같이 U_Shop테이블에 PK를 잡도록 하겠습니다. 아마도, PK 조건으로 필요한 정보를 몇 건 찾아야 할 필요가 있을거 같습니다.
경도 위도 값을 구할 차례입니다. 저는 카카오 API를 사용했습니다. (SQL과 데이터 분석 중심 설명이기 때문에 카카오API에 대한 자세한 설명은 제외합니다.)
카카오API를 호출해서 위도, 경도 값을 업데이트하기 위해서 파이썬을 사용합니다. 파이썬 소스코드는 아래와 같습니다.(아나콘다3, 파이참 환경입니다. 파이썬 설치가 불가능한 분은 다음 아래의 UPDATE 스크립트를 그대로 카피해서 사용하셔도 됩니다.)
-- 경도, 위도 가져오는 파이썬
import requests
import json
import pymysql
def find_gps():
#DB LINK
conn = pymysql.connect(user='ADMIN_SWEET', passwd='1q2w3e4r', host='127.0.0.1', port=13306, db='SWEET_DATA',charset='utf8')
cur = conn.cursor()
sql_text = "SELECT T1.StationNo, T1.Address FROM M_Station T1"
cur.execute(sql_text);
rows = cur.fetchall();
url = "https://dapi.kakao.com/v2/local/search/address.json";
header = {'Authorization': 'KakaoAK 각자키를 사용'}
for row in rows:
print(row)
_addr = str(row[1]);
_key = row[0]
query = "query=" + _addr;
r = requests.get(url, headers=header, params=query)
json_data = json.loads(r.text)
for i in json_data['documents'] :
# X 좌표값 => longitude
# Y 좌표값 => latitude
sql_update_text = """UPDATE M_Station SET PosXY = POINT(""" +i['x'] + """,""" + i['y'] + """) WHERE StationNo = '""" + str(_key) + """';""";
print(sql_update_text)
cur.execute(sql_update_text)
cur.execute('commit')
find_gps()
아래는 경도, 위도를 바로 업데이트하는 스크립트입니다.
-- 경도, 위도 바로 업데이트 하기 (너무 길어서 줄 번호는 생략합니다.)
UPDATE M_Station SET PosXY = POINT(126.97255256952047,37.557158852433425) WHERE StationNo = '150';
UPDATE M_Station SET PosXY = POINT(126.97698199254849,37.565438157005836) WHERE StationNo = '151';
UPDATE M_Station SET PosXY = POINT(126.98323798089602,37.57021448838956) WHERE StationNo = '152';
UPDATE M_Station SET PosXY = POINT(126.99203100001637,37.570428044296825) WHERE StationNo = '153';
UPDATE M_Station SET PosXY = POINT(127.00191528898299,37.57090762722926) WHERE StationNo = '154';
UPDATE M_Station SET PosXY = POINT(127.01116585737377,37.571779278087966) WHERE StationNo = '155';
UPDATE M_Station SET PosXY = POINT(127.02456540747554,37.57612364917105) WHERE StationNo = '156';
UPDATE M_Station SET PosXY = POINT(127.03469139400376,37.57820060253574) WHERE StationNo = '157';
UPDATE M_Station SET PosXY = POINT(127.04505339058124,37.58022614096847) WHERE StationNo = '158';
UPDATE M_Station SET PosXY = POINT(127.01674914262058,37.573371577168444) WHERE StationNo = '159';
UPDATE M_Station SET PosXY = POINT(126.97538891079977,37.563578179426656) WHERE StationNo = '201';
UPDATE M_Station SET PosXY = POINT(126.98237414667892,37.566051762475745) WHERE StationNo = '202';
UPDATE M_Station SET PosXY = POINT(126.99030643210962,37.56626531530101) WHERE StationNo = '203';
UPDATE M_Station SET PosXY = POINT(126.99785391218619,37.56663690408433) WHERE StationNo = '204';
UPDATE M_Station SET PosXY = POINT(127.0091320641583,37.56558781104745) WHERE StationNo = '205';
UPDATE M_Station SET PosXY = POINT(127.01954091431038,37.56565502277837) WHERE StationNo = '206';
UPDATE M_Station SET PosXY = POINT(127.0290302021847,37.564467359880524) WHERE StationNo = '207';
UPDATE M_Station SET PosXY = POINT(127.0366574155161,37.56122165079314) WHERE StationNo = '208';
UPDATE M_Station SET PosXY = POINT(127.04358539961328,37.55557905450867) WHERE StationNo = '209';
UPDATE M_Station SET PosXY = POINT(127.04736461242203,37.54718750384358) WHERE StationNo = '210';
UPDATE M_Station SET PosXY = POINT(127.05608956590137,37.54450230873498) WHERE StationNo = '211';
UPDATE M_Station SET PosXY = POINT(127.0691712451776,37.54041924881689) WHERE StationNo = '212';
UPDATE M_Station SET PosXY = POINT(127.08618081290898,37.53715364857336) WHERE StationNo = '213';
UPDATE M_Station SET PosXY = POINT(127.09466422420185,37.535164952269845) WHERE StationNo = '214';
UPDATE M_Station SET PosXY = POINT(127.10381323936149,37.52068718042725) WHERE StationNo = '215';
UPDATE M_Station SET PosXY = POINT(127.10031473197408,37.513319997745555) WHERE StationNo = '216';
UPDATE M_Station SET PosXY = POINT(127.0863730771204,37.51156865152352) WHERE StationNo = '217';
UPDATE M_Station SET PosXY = POINT(127.0733561309303,37.51096837383229) WHERE StationNo = '218';
UPDATE M_Station SET PosXY = POINT(127.06307527215017,37.50886235664123) WHERE StationNo = '219';
UPDATE M_Station SET PosXY = POINT(127.0489436033093,37.50450458903165) WHERE StationNo = '220';
UPDATE M_Station SET PosXY = POINT(127.03661876920279,37.50071762069987) WHERE StationNo = '221';
UPDATE M_Station SET PosXY = POINT(127.02830790088069,37.498164651039694) WHERE StationNo = '222';
UPDATE M_Station SET PosXY = POINT(127.0141576077151,37.49357566392058) WHERE StationNo = '223';
UPDATE M_Station SET PosXY = POINT(127.00758070181062,37.491824710937784) WHERE StationNo = '224';
UPDATE M_Station SET PosXY = POINT(126.9976935425379,37.481438109455894) WHERE StationNo = '225';
UPDATE M_Station SET PosXY = POINT(126.9814093612868,37.47652436655696) WHERE StationNo = '226';
UPDATE M_Station SET PosXY = POINT(126.96341086398692,37.47706437373934) WHERE StationNo = '227';
UPDATE M_Station SET PosXY = POINT(126.95264544509422,37.48115113161296) WHERE StationNo = '228';
UPDATE M_Station SET PosXY = POINT(126.94149651644463,37.48255531496967) WHERE StationNo = '229';
UPDATE M_Station SET PosXY = POINT(126.92980421555579,37.48421761465397) WHERE StationNo = '230';
UPDATE M_Station SET PosXY = POINT(126.91317513329555,37.48757633464119) WHERE StationNo = '231';
UPDATE M_Station SET PosXY = POINT(126.9014956225072,37.48531465452861) WHERE StationNo = '232';
UPDATE M_Station SET PosXY = POINT(126.89494460557201,37.49330995650912) WHERE StationNo = '233';
UPDATE M_Station SET PosXY = POINT(126.89108197001643,37.508771317645575) WHERE StationNo = '234';
UPDATE M_Station SET PosXY = POINT(126.89476532263198,37.51792164597545) WHERE StationNo = '235';
UPDATE M_Station SET PosXY = POINT(126.89662780191244,37.52570074348017) WHERE StationNo = '236';
UPDATE M_Station SET PosXY = POINT(126.90272923847847,37.534957406012765) WHERE StationNo = '237';
UPDATE M_Station SET PosXY = POINT(126.91463716307454,37.55011421506363) WHERE StationNo = '238';
UPDATE M_Station SET PosXY = POINT(126.92367442251489,37.556888697557625) WHERE StationNo = '239';
UPDATE M_Station SET PosXY = POINT(126.9361204389213,37.555440075383814) WHERE StationNo = '240';
UPDATE M_Station SET PosXY = POINT(126.94575729679175,37.55679639200007) WHERE StationNo = '241';
UPDATE M_Station SET PosXY = POINT(126.95603329132891,37.557359284038256) WHERE StationNo = '242';
UPDATE M_Station SET PosXY = POINT(126.96447506227653,37.559838065855615) WHERE StationNo = '243';
UPDATE M_Station SET PosXY = POINT(127.05072874381338,37.562005716412244) WHERE StationNo = '244';
UPDATE M_Station SET PosXY = POINT(127.04682212731294,37.56999015446607) WHERE StationNo = '245';
UPDATE M_Station SET PosXY = POINT(127.02456540747554,37.57612364917105) WHERE StationNo = '246';
UPDATE M_Station SET PosXY = POINT(126.88270348752339,37.51444322325958) WHERE StationNo = '247';
UPDATE M_Station SET PosXY = POINT(126.86606566370143,37.51269920666508) WHERE StationNo = '248';
UPDATE M_Station SET PosXY = POINT(126.85298994738748,37.51979797507366) WHERE StationNo = '249';
UPDATE M_Station SET PosXY = POINT(127.03803112066214,37.573939672123046) WHERE StationNo = '250';
UPDATE M_Station SET PosXY = POINT(126.90582369800002,37.65085626552864) WHERE StationNo = '309';
UPDATE M_Station SET PosXY = POINT(126.9187436123905,37.63684295896786) WHERE StationNo = '310';
UPDATE M_Station SET PosXY = POINT(126.9211598597772,37.618961821472446) WHERE StationNo = '311';
UPDATE M_Station SET PosXY = POINT(126.92998497034971,37.610206176235664) WHERE StationNo = '312';
UPDATE M_Station SET PosXY = POINT(126.93601831026713,37.60074200017985) WHERE StationNo = '313';
UPDATE M_Station SET PosXY = POINT(126.94424412257759,37.588708926043225) WHERE StationNo = '314';
UPDATE M_Station SET PosXY = POINT(126.95032164535576,37.58238306587019) WHERE StationNo = '315';
UPDATE M_Station SET PosXY = POINT(126.95774846781482,37.57441035829069) WHERE StationNo = '316';
UPDATE M_Station SET PosXY = POINT(126.97306191821602,37.575860041203214) WHERE StationNo = '317';
UPDATE M_Station SET PosXY = POINT(126.98582213739954,37.57672001005511) WHERE StationNo = '318';
UPDATE M_Station SET PosXY = POINT(126.99199470091352,37.571141630115804) WHERE StationNo = '319';
UPDATE M_Station SET PosXY = POINT(126.99265172209068,37.566276296521835) WHERE StationNo = '320';
UPDATE M_Station SET PosXY = POINT(127.00545974413328,37.55902878277881) WHERE StationNo = '322';
UPDATE M_Station SET PosXY = POINT(127.01082377161588,37.55455946560875) WHERE StationNo = '323';
UPDATE M_Station SET PosXY = POINT(127.01589250869206,37.54811134857417) WHERE StationNo = '324';
UPDATE M_Station SET PosXY = POINT(127.01833055740015,37.54104716078602) WHERE StationNo = '325';
UPDATE M_Station SET PosXY = POINT(127.02848148490598,37.526430913588165) WHERE StationNo = '326';
UPDATE M_Station SET PosXY = POINT(127.02032892209975,37.51643146679401) WHERE StationNo = '327';
UPDATE M_Station SET PosXY = POINT(127.01122040598548,37.51279983559945) WHERE StationNo = '328';
UPDATE M_Station SET PosXY = POINT(127.00444243570533,37.50464799363718) WHERE StationNo = '329';
UPDATE M_Station SET PosXY = POINT(127.0141576077151,37.49357566392058) WHERE StationNo = '330';
UPDATE M_Station SET PosXY = POINT(127.01620479414689,37.48514196837421) WHERE StationNo = '331';
UPDATE M_Station SET PosXY = POINT(127.03454374096688,37.48393428252617) WHERE StationNo = '332';
UPDATE M_Station SET PosXY = POINT(127.04661648311405,37.48696112783275) WHERE StationNo = '333';
UPDATE M_Station SET PosXY = POINT(127.05532122481543,37.49086414202696) WHERE StationNo = '334';
UPDATE M_Station SET PosXY = POINT(127.06342962689939,37.49456140410784) WHERE StationNo = '335';
UPDATE M_Station SET PosXY = POINT(127.07171829171698,37.49671547492159) WHERE StationNo = '336';
UPDATE M_Station SET PosXY = POINT(127.07949534292234,37.4937227596509) WHERE StationNo = '337';
UPDATE M_Station SET PosXY = POINT(127.0844509601109,37.4840749361128) WHERE StationNo = '338';
UPDATE M_Station SET PosXY = POINT(127.10185851088313,37.48738773253322) WHERE StationNo = '339';
UPDATE M_Station SET PosXY = POINT(127.11848381630814,37.49274223205612) WHERE StationNo = '340';
UPDATE M_Station SET PosXY = POINT(127.12406558337433,37.495601702768255) WHERE StationNo = '341';
UPDATE M_Station SET PosXY = POINT(127.12788105549203,37.50226507641803) WHERE StationNo = '342';
UPDATE M_Station SET PosXY = POINT(127.07901229284572,37.67029255404396) WHERE StationNo = '409';
UPDATE M_Station SET PosXY = POINT(127.07357594636086,37.66090424876206) WHERE StationNo = '410';
UPDATE M_Station SET PosXY = POINT(127.06303203591438,37.65626849637051) WHERE StationNo = '411';
UPDATE M_Station SET PosXY = POINT(127.04771291366652,37.65321595105881) WHERE StationNo = '412';
UPDATE M_Station SET PosXY = POINT(127.03488286973543,37.6488488732005) WHERE StationNo = '413';
UPDATE M_Station SET PosXY = POINT(127.02535610786357,37.63774397254115) WHERE StationNo = '414';
UPDATE M_Station SET PosXY = POINT(127.0261044533112,37.62644184231728) WHERE StationNo = '415';
UPDATE M_Station SET PosXY = POINT(127.0301002308641,37.61328646943786) WHERE StationNo = '416';
UPDATE M_Station SET PosXY = POINT(127.02482344269079,37.60318219863949) WHERE StationNo = '417';
UPDATE M_Station SET PosXY = POINT(127.0164771967841,37.592717746005185) WHERE StationNo = '418';
UPDATE M_Station SET PosXY = POINT(127.00595101268726,37.588393983179365) WHERE StationNo = '419';
UPDATE M_Station SET PosXY = POINT(127.00197896746458,37.58180245102366) WHERE StationNo = '420';
UPDATE M_Station SET PosXY = POINT(127.00954468675879,37.57041351149444) WHERE StationNo = '421';
UPDATE M_Station SET PosXY = POINT(127.00755189864863,37.56507976088089) WHERE StationNo = '422';
UPDATE M_Station SET PosXY = POINT(126.99411000772092,37.561173134797826) WHERE StationNo = '423';
UPDATE M_Station SET PosXY = POINT(126.98594737006931,37.560909354360476) WHERE StationNo = '424';
UPDATE M_Station SET PosXY = POINT(126.97823812825729,37.558536761057404) WHERE StationNo = '425';
UPDATE M_Station SET PosXY = POINT(126.97280752153232,37.5532017297038) WHERE StationNo = '426';
UPDATE M_Station SET PosXY = POINT(126.97187348361902,37.54519343558292) WHERE StationNo = '427';
UPDATE M_Station SET PosXY = POINT(126.97301340873837,37.53459433761754) WHERE StationNo = '428';
UPDATE M_Station SET PosXY = POINT(126.96821830742816,37.529616013993916) WHERE StationNo = '429';
UPDATE M_Station SET PosXY = POINT(126.97368292469619,37.52252106548174) WHERE StationNo = '430';
UPDATE M_Station SET PosXY = POINT(126.98026736778657,37.502869646133625) WHERE StationNo = '431';
UPDATE M_Station SET PosXY = POINT(126.98219812464853,37.487552836479765) WHERE StationNo = '432';
UPDATE M_Station SET PosXY = POINT(126.9815946871435,37.476863175208685) WHERE StationNo = '433';
UPDATE M_Station SET PosXY = POINT(126.98865563982616,37.46485899919096) WHERE StationNo = '434';
UPDATE M_Station SET PosXY = POINT(126.8126306092129,37.57700464071319) WHERE StationNo = '2511';
UPDATE M_Station SET PosXY = POINT(126.80599997709584,37.572269120979904) WHERE StationNo = '2512';
UPDATE M_Station SET PosXY = POINT(126.80157133547586,37.56214539460694) WHERE StationNo = '2513';
UPDATE M_Station SET PosXY = POINT(126.81105778784394,37.56147620063578) WHERE StationNo = '2514';
UPDATE M_Station SET PosXY = POINT(126.82554810761461,37.56019746267116) WHERE StationNo = '2515';
UPDATE M_Station SET PosXY = POINT(126.83731235802402,37.55902854821429) WHERE StationNo = '2516';
UPDATE M_Station SET PosXY = POINT(126.83641074062257,37.549001008797624) WHERE StationNo = '2517';
UPDATE M_Station SET PosXY = POINT(126.84049622173754,37.5414346084945) WHERE StationNo = '2518';
UPDATE M_Station SET PosXY = POINT(126.84661226903779,37.53195698666088) WHERE StationNo = '2519';
UPDATE M_Station SET PosXY = POINT(126.85729175247246,37.52520926282623) WHERE StationNo = '2520';
UPDATE M_Station SET PosXY = POINT(126.86474281159968,37.52619832069603) WHERE StationNo = '2521';
UPDATE M_Station SET PosXY = POINT(126.87535270115818,37.524451239657594) WHERE StationNo = '2522';
UPDATE M_Station SET PosXY = POINT(126.88686737317903,37.52551159691278) WHERE StationNo = '2523';
UPDATE M_Station SET PosXY = POINT(126.8952904240174,37.52422552622088) WHERE StationNo = '2524';
UPDATE M_Station SET PosXY = POINT(126.90495781244745,37.52273443371112) WHERE StationNo = '2525';
UPDATE M_Station SET PosXY = POINT(126.91433478893137,37.517667159567864) WHERE StationNo = '2526';
UPDATE M_Station SET PosXY = POINT(126.92448389420136,37.52184392588269) WHERE StationNo = '2527';
UPDATE M_Station SET PosXY = POINT(126.9328638413941,37.52708561146302) WHERE StationNo = '2528';
UPDATE M_Station SET PosXY = POINT(126.94591912537975,37.53958378800099) WHERE StationNo = '2529';
UPDATE M_Station SET PosXY = POINT(126.95136518782198,37.54452723838077) WHERE StationNo = '2530';
UPDATE M_Station SET PosXY = POINT(126.95661507072609,37.553362673930344) WHERE StationNo = '2531';
UPDATE M_Station SET PosXY = POINT(126.96337438660855,37.560994609486485) WHERE StationNo = '2532';
UPDATE M_Station SET PosXY = POINT(126.96663637960322,37.565857352826356) WHERE StationNo = '2533';
UPDATE M_Station SET PosXY = POINT(126.97678991701194,37.571622553368286) WHERE StationNo = '2534';
UPDATE M_Station SET PosXY = POINT(126.99006110477345,37.57256865945283) WHERE StationNo = '2535';
UPDATE M_Station SET PosXY = POINT(126.99805763167915,37.56752348754336) WHERE StationNo = '2536';
UPDATE M_Station SET PosXY = POINT(127.00591742504709,37.564575296239745) WHERE StationNo = '2537';
UPDATE M_Station SET PosXY = POINT(127.0138080439052,37.560293101373034) WHERE StationNo = '2538';
UPDATE M_Station SET PosXY = POINT(127.02006312784418,37.554533029207484) WHERE StationNo = '2539';
UPDATE M_Station SET PosXY = POINT(127.02923112964419,37.55727735595999) WHERE StationNo = '2540';
UPDATE M_Station SET PosXY = POINT(127.0366574155161,37.56122165079314) WHERE StationNo = '2541';
UPDATE M_Station SET PosXY = POINT(127.04316151445926,37.56628305149315) WHERE StationNo = '2542';
UPDATE M_Station SET PosXY = POINT(127.05318118517143,37.566444746492174) WHERE StationNo = '2543';
UPDATE M_Station SET PosXY = POINT(127.0644189574387,37.561501690379174) WHERE StationNo = '2544';
UPDATE M_Station SET PosXY = POINT(127.07901512810203,37.557362654799554) WHERE StationNo = '2545';
UPDATE M_Station SET PosXY = POINT(127.08937161621444,37.55239978385352) WHERE StationNo = '2546';
UPDATE M_Station SET PosXY = POINT(127.10368438183367,37.54527740465725) WHERE StationNo = '2547';
UPDATE M_Station SET PosXY = POINT(127.12374300170981,37.53857988247639) WHERE StationNo = '2548';
UPDATE M_Station SET PosXY = POINT(127.13353161821865,37.535520236233765) WHERE StationNo = '2549';
UPDATE M_Station SET PosXY = POINT(127.13999863971951,37.53781209828301) WHERE StationNo = '2550';
UPDATE M_Station SET PosXY = POINT(127.14296914789105,37.54573012263343) WHERE StationNo = '2551';
UPDATE M_Station SET PosXY = POINT(127.14400274117364,37.55126099421607) WHERE StationNo = '2552';
UPDATE M_Station SET PosXY = POINT(127.1539511120498,37.55504704640015) WHERE StationNo = '2553';
UPDATE M_Station SET PosXY = POINT(127.16582876743654,37.556663567935374) WHERE StationNo = '2554';
UPDATE M_Station SET PosXY = POINT(127.1363739058897,37.528135991320916) WHERE StationNo = '2555';
UPDATE M_Station SET PosXY = POINT(127.13060127102182,37.51608710743359) WHERE StationNo = '2556';
UPDATE M_Station SET PosXY = POINT(127.12585594283948,37.50855626435071) WHERE StationNo = '2557';
UPDATE M_Station SET PosXY = POINT(127.12788105549203,37.50226507641803) WHERE StationNo = '2558';
UPDATE M_Station SET PosXY = POINT(127.13516074116417,37.49781321258245) WHERE StationNo = '2559';
UPDATE M_Station SET PosXY = POINT(127.14403142719337,37.49320759944486) WHERE StationNo = '2560';
UPDATE M_Station SET PosXY = POINT(127.15232084931651,37.49467124867699) WHERE StationNo = '2561';
UPDATE M_Station SET PosXY = POINT(126.91551472523976,37.59874332069344) WHERE StationNo = '2611';
UPDATE M_Station SET PosXY = POINT(126.92296737989116,37.60603923327726) WHERE StationNo = '2612';
UPDATE M_Station SET PosXY = POINT(126.92998497034971,37.610206176235664) WHERE StationNo = '2613';
UPDATE M_Station SET PosXY = POINT(126.93280900514041,37.6183996051419) WHERE StationNo = '2614';
UPDATE M_Station SET PosXY = POINT(126.91718136927429,37.61121059714101) WHERE StationNo = '2616';
UPDATE M_Station SET PosXY = POINT(126.91391017297514,37.591826152095116) WHERE StationNo = '2617';
UPDATE M_Station SET PosXY = POINT(126.91005600501946,37.584193674614255) WHERE StationNo = '2618';
UPDATE M_Station SET PosXY = POINT(126.90192720232601,37.57716305593089) WHERE StationNo = '2619';
UPDATE M_Station SET PosXY = POINT(126.89903434554247,37.56994184911615) WHERE StationNo = '2620';
UPDATE M_Station SET PosXY = POINT(126.90335776672575,37.56342586335406) WHERE StationNo = '2621';
UPDATE M_Station SET PosXY = POINT(126.9100355097372,37.5560573836446) WHERE StationNo = '2622';
UPDATE M_Station SET PosXY = POINT(126.91354286903871,37.54912232079213) WHERE StationNo = '2623';
UPDATE M_Station SET PosXY = POINT(126.92241177988097,37.5477733797523) WHERE StationNo = '2624';
UPDATE M_Station SET PosXY = POINT(126.93194025779533,37.54748733268153) WHERE StationNo = '2625';
UPDATE M_Station SET PosXY = POINT(126.94246411919256,37.54766952522553) WHERE StationNo = '2626';
UPDATE M_Station SET PosXY = POINT(126.95136518782198,37.54452723838077) WHERE StationNo = '2627';
UPDATE M_Station SET PosXY = POINT(126.9613166204824,37.53933395166948) WHERE StationNo = '2628';
UPDATE M_Station SET PosXY = POINT(126.97408110462513,37.53564334162721) WHERE StationNo = '2629';
UPDATE M_Station SET PosXY = POINT(126.98702937858346,37.534830968963725) WHERE StationNo = '2630';
UPDATE M_Station SET PosXY = POINT(126.99372290017801,37.53448192611647) WHERE StationNo = '2631';
UPDATE M_Station SET PosXY = POINT(127.00174704299799,37.54029533464754) WHERE StationNo = '2632';
UPDATE M_Station SET PosXY = POINT(127.00688478144716,37.54799328551464) WHERE StationNo = '2633';
UPDATE M_Station SET PosXY = POINT(127.01012201494936,37.55389278946013) WHERE StationNo = '2634';
UPDATE M_Station SET PosXY = POINT(127.0138080439052,37.560293101373034) WHERE StationNo = '2635';
UPDATE M_Station SET PosXY = POINT(127.01615443943429,37.56627541998749) WHERE StationNo = '2636';
UPDATE M_Station SET PosXY = POINT(127.01568028457561,37.57220043049817) WHERE StationNo = '2637';
UPDATE M_Station SET PosXY = POINT(127.01521096796488,37.580017519973765) WHERE StationNo = '2638';
UPDATE M_Station SET PosXY = POINT(127.01938754998206,37.58532916538653) WHERE StationNo = '2639';
UPDATE M_Station SET PosXY = POINT(127.0362868728223,37.590648244448744) WHERE StationNo = '2641';
UPDATE M_Station SET PosXY = POINT(127.04118420104847,37.60142249412873) WHERE StationNo = '2642';
UPDATE M_Station SET PosXY = POINT(127.04832616862042,37.606263508926446) WHERE StationNo = '2643';
UPDATE M_Station SET PosXY = POINT(127.05642453926866,37.61055222857029) WHERE StationNo = '2644';
UPDATE M_Station SET PosXY = POINT(127.06611877103421,37.61504853500426) WHERE StationNo = '2645';
UPDATE M_Station SET PosXY = POINT(127.07499697577263,37.61790476490005) WHERE StationNo = '2646';
UPDATE M_Station SET PosXY = POINT(127.08374349284219,37.61989547071212) WHERE StationNo = '2647';
UPDATE M_Station SET PosXY = POINT(127.09075903378347,37.61765223007443) WHERE StationNo = '2648';
UPDATE M_Station SET PosXY = POINT(127.05313592571325,37.70011859930464) WHERE StationNo = '2711';
UPDATE M_Station SET PosXY = POINT(127.04652958563797,37.6891222729532) WHERE StationNo = '2712';
UPDATE M_Station SET PosXY = POINT(127.05537345041247,37.67762557714484) WHERE StationNo = '2713';
UPDATE M_Station SET PosXY = POINT(127.05768986266521,37.66508643213007) WHERE StationNo = '2714';
UPDATE M_Station SET PosXY = POINT(127.0605194502251,37.65469489527157) WHERE StationNo = '2715';
UPDATE M_Station SET PosXY = POINT(127.06424615612472,37.64489022881391) WHERE StationNo = '2716';
UPDATE M_Station SET PosXY = POINT(127.06798672392947,37.63645131422817) WHERE StationNo = '2717';
UPDATE M_Station SET PosXY = POINT(127.07300633481204,37.625477910674306) WHERE StationNo = '2718';
UPDATE M_Station SET PosXY = POINT(127.07499697577263,37.61790476490005) WHERE StationNo = '2719';
UPDATE M_Station SET PosXY = POINT(127.07776672574502,37.6106265895414) WHERE StationNo = '2720';
UPDATE M_Station SET PosXY = POINT(127.07946954444269,37.60157951886236) WHERE StationNo = '2721';
UPDATE M_Station SET PosXY = POINT(127.08756682062385,37.58838327587009) WHERE StationNo = '2723';
UPDATE M_Station SET PosXY = POINT(127.08844098507846,37.580796263833484) WHERE StationNo = '2724';
UPDATE M_Station SET PosXY = POINT(127.0870383398057,37.57402903933762) WHERE StationNo = '2725';
UPDATE M_Station SET PosXY = POINT(127.0840719599339,37.565525794437484) WHERE StationNo = '2726';
UPDATE M_Station SET PosXY = POINT(127.07901512810203,37.557362654799554) WHERE StationNo = '2727';
UPDATE M_Station SET PosXY = POINT(127.07455105814755,37.547872644193866) WHERE StationNo = '2728';
UPDATE M_Station SET PosXY = POINT(127.07104541845995,37.540839804010105) WHERE StationNo = '2729';
UPDATE M_Station SET PosXY = POINT(127.06671932273433,37.53158725107477) WHERE StationNo = '2730';
UPDATE M_Station SET PosXY = POINT(127.0520390352376,37.51914278253384) WHERE StationNo = '2731';
UPDATE M_Station SET PosXY = POINT(127.0413007514142,37.51719004644419) WHERE StationNo = '2732';
UPDATE M_Station SET PosXY = POINT(127.031462969986,37.51420534847362) WHERE StationNo = '2733';
UPDATE M_Station SET PosXY = POINT(127.02165763527358,37.511198199421855) WHERE StationNo = '2734';
UPDATE M_Station SET PosXY = POINT(127.0114956748719,37.50812539767412) WHERE StationNo = '2735';
UPDATE M_Station SET PosXY = POINT(127.00444243570533,37.50464799363718) WHERE StationNo = '2736';
UPDATE M_Station SET PosXY = POINT(126.99318861355258,37.487532357103085) WHERE StationNo = '2737';
UPDATE M_Station SET PosXY = POINT(126.98102282962365,37.485015414425256) WHERE StationNo = '2738';
UPDATE M_Station SET PosXY = POINT(126.97170632193176,37.48444411335394) WHERE StationNo = '2739';
UPDATE M_Station SET PosXY = POINT(126.95406103853256,37.4958128895882) WHERE StationNo = '2740';
UPDATE M_Station SET PosXY = POINT(126.94770059895008,37.503209307157675) WHERE StationNo = '2741';
UPDATE M_Station SET PosXY = POINT(126.93874232967946,37.504664630119066) WHERE StationNo = '2742';
UPDATE M_Station SET PosXY = POINT(126.92822455086642,37.499721196936385) WHERE StationNo = '2743';
UPDATE M_Station SET PosXY = POINT(126.92048905018001,37.49991086100452) WHERE StationNo = '2744';
UPDATE M_Station SET PosXY = POINT(126.90984030067348,37.50011585114009) WHERE StationNo = '2745';
UPDATE M_Station SET PosXY = POINT(126.89660991849864,37.4927419893405) WHERE StationNo = '2746';
UPDATE M_Station SET PosXY = POINT(126.8873428283549,37.48616337072886) WHERE StationNo = '2747';
UPDATE M_Station SET PosXY = POINT(126.8825603874058,37.481596008752796) WHERE StationNo = '2748';
UPDATE M_Station SET PosXY = POINT(126.86801218606675,37.47611693573971) WHERE StationNo = '2749';
UPDATE M_Station SET PosXY = POINT(126.85463844176687,37.47936649568283) WHERE StationNo = '2750';
UPDATE M_Station SET PosXY = POINT(126.83871283951193,37.48682059028633) WHERE StationNo = '2751';
UPDATE M_Station SET PosXY = POINT(126.82338161808453,37.492150645572) WHERE StationNo = '2752';
UPDATE M_Station SET PosXY = POINT(126.81139624459261,37.50619860942323) WHERE StationNo = '2753';
UPDATE M_Station SET PosXY = POINT(126.79742821318048,37.505483510808496) WHERE StationNo = '2754';
UPDATE M_Station SET PosXY = POINT(126.78686559116848,37.50364489731701) WHERE StationNo = '2755';
UPDATE M_Station SET PosXY = POINT(126.77649905200991,37.50297341171852) WHERE StationNo = '2756';
UPDATE M_Station SET PosXY = POINT(126.76406251971743,37.504643113710536) WHERE StationNo = '2757';
UPDATE M_Station SET PosXY = POINT(126.75318785519286,37.505810217122445) WHERE StationNo = '2758';
UPDATE M_Station SET PosXY = POINT(126.74190705378871,37.506582594807355) WHERE StationNo = '2759';
UPDATE M_Station SET PosXY = POINT(126.73176253798594,37.50707897171457) WHERE StationNo = '2760';
UPDATE M_Station SET PosXY = POINT(126.72085774246696,37.50760511901552) WHERE StationNo = '2761';
UPDATE M_Station SET PosXY = POINT(127.1275418321658,37.550141060814475) WHERE StationNo = '2811';
UPDATE M_Station SET PosXY = POINT(127.12374300170981,37.53857988247639) WHERE StationNo = '2812';
UPDATE M_Station SET PosXY = POINT(127.12057570947756,37.53067237616664) WHERE StationNo = '2813';
UPDATE M_Station SET PosXY = POINT(127.11242844575287,37.517623092550025) WHERE StationNo = '2814';
UPDATE M_Station SET PosXY = POINT(127.1042170589561,37.51497806972679) WHERE StationNo = '2815';
UPDATE M_Station SET PosXY = POINT(127.10634432937081,37.50594811233585) WHERE StationNo = '2816';
UPDATE M_Station SET PosXY = POINT(127.11217989255323,37.4996861342522) WHERE StationNo = '2817';
UPDATE M_Station SET PosXY = POINT(127.11848381630814,37.49274223205612) WHERE StationNo = '2818';
UPDATE M_Station SET PosXY = POINT(127.1222907853981,37.48626191297808) WHERE StationNo = '2819';
UPDATE M_Station SET PosXY = POINT(127.12642443090823,37.478119655330296) WHERE StationNo = '2820';
UPDATE M_Station SET PosXY = POINT(127.12676975735933,37.47105537138374) WHERE StationNo = '2821';
UPDATE M_Station SET PosXY = POINT(127.14994228203592,37.456525568472756) WHERE StationNo = '2822';
UPDATE M_Station SET PosXY = POINT(127.15980099129938,37.451567893801) WHERE StationNo = '2823';
UPDATE M_Station SET PosXY = POINT(127.15668641533487,37.445142434318456) WHERE StationNo = '2824';
UPDATE M_Station SET PosXY = POINT(127.14810817259715,37.44118542933686) WHERE StationNo = '2825';
UPDATE M_Station SET PosXY = POINT(127.14035387124626,37.43746108672792) WHERE StationNo = '2826';
UPDATE M_Station SET PosXY = POINT(127.12954981193997,37.4338513401459) WHERE StationNo = '2827';
카카오 API로 경도, 위도를 찾았지만, 두 군데(안암역, 상봉역)는 카카오 API로 찾을 수가 없었습니다. 아마도 주소가 약간 안 맞아서 그런 것 아닐까 생각이 듭니다. 구글 지도로 직접 경도와 위도를 찾아서 업데이트합니다.
-- 못 찾은 경도, 위도 수작업 업데이트.
UPDATE M_Station SET PosXY = POINT(127.029137,37.586296) WHERE StationNo = 2640;
UPDATE M_Station SET PosXY = POINT(127.085739,37.595627) WHERE StationNo = 2722;
5. 검색해보기
POINT 자료형에서 데이터를 빨리 찾기 위해서는 SPATIAL 인덱스를 만들어서 활용합니다. 그런데 SPATIAL 인덱스를 만들려면 해당 컬럼이 NOT NULL이어야 합니다. 아래와 같이 PosXY를 NOT NULL로 변경하고 인덱스를 만듭니다.
-- SPATIAL INDEX 생성
ALTER TABLE M_Station MODIFY PosXY POINT NOT NULL;
CREATE SPATIAL INDEX SX_M_Station_1 ON M_Station(PosXY);
경도와 위도 위치 정보입니다. 위치 정보를 저장하고 관리하기 위해서는 MySQL의 POINT 자료형을 사용합니다.
(보통은 경도, 위도 보다는 위도, 경도(위경도) 순으로 말하는 것이 익숙합니다. 그런데 경도는 X값, 위도는 Y값이므로, 이 후 사용의 통일성을 위해서 경도, 위도 순으로 쓰고 있습니다.)
아래 스크립트로 M_Station 테이블을 변경합니다.
-- 테이블 컬럼 추가
ALTER TABLE M_Station ADD COLUMN Address VARCHAR(500) CHARACTER SET UTF8MB4 NOT NULL;
ALTER TABLE M_Station ADD COLUMN PhoneNo VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL;
ALTER TABLE M_Station ADD COLUMN PosXY POINT;
건수가 279건 밖에 안됩니다. 이런 경우에는 업로드용 테이블을 만들고 따로 업로드 하는 것보다, 엑셀 수식을 이용해 바로 올리면서 테이블을 만드는 것이 좋습니다.
엑셀로 CSV 파일을 열어보면 아래와 같습니다.
엑셀의 2번 로우, H 컬럼에 아래 수식을 적어줍니다.
-- 엑셀의 1-H 셀에 수식 입력
="SELECT '"&B2&"' LINE_NO,'"&C2&"' ST_NM,'"&E2&"' ADDR, '"&F2&"' PH_NO FROM DUAL UNION ALL"
위 수식을 엑셀에 279라인까지 카피를 합니다. 카피된 내용을 SQL 창으로 옮겨서 U_StationAddInfo 테이블을 CREATE합니다. (엑셀에서 SQL창으로 카피한 다음에, 마지막 줄에 UNION ALL은 제거해야 합니다.)
-- 지하철역 추가정보 업로드 테이블 생성 U_StationAddInfo (너무 길어서 줄 번호는 생략합니다.)
CREATE TABLE U_StationAddInfo AS
SELECT '1' LINE_NO,'서울역' ST_NM,'서울특별시 중구 세종대로 지하 2 (남대문로 5가)' ADDR, '02-6110-1331' PH_NO FROM DUAL UNION ALL
SELECT '1' LINE_NO,'시청' ST_NM,'서울특별시 중구 세종대로 지하 101 (정동)' ADDR, '02-6110-1321' PH_NO FROM DUAL UNION ALL
SELECT '1' LINE_NO,'종각' ST_NM,'서울특별시 종로구 종로 지하 55 (종로1가)' ADDR, '02-6110-1311' PH_NO FROM DUAL UNION ALL
SELECT '1' LINE_NO,'종로3가' ST_NM,'서울특별시 종로구 종로 지하 129 (종로3가)' ADDR, '02-6110-1301' PH_NO FROM DUAL UNION ALL
SELECT '1' LINE_NO,'종로5가' ST_NM,'서울특별시 종로구 종로 지하 216 (종로5가)' ADDR, '02-6110-1291' PH_NO FROM DUAL UNION ALL
SELECT '1' LINE_NO,'동대문' ST_NM,'서울특별시 종로구 종로 지하 302 (창신동)' ADDR, '02-6110-1281' PH_NO FROM DUAL UNION ALL
SELECT '1' LINE_NO,'신설동' ST_NM,'서울특별시 동대문구 왕산로 지하 1 (신설동)' ADDR, '02-6110-1261' PH_NO FROM DUAL UNION ALL
SELECT '1' LINE_NO,'제기동' ST_NM,'서울특별시 동대문구 왕산로 지하 93 (제기동)' ADDR, '02-6110-1251' PH_NO FROM DUAL UNION ALL
SELECT '1' LINE_NO,'청량리(서울시립대입구)' ST_NM,'서울특별시 동대문구 왕산로 지하 205 (전농동)' ADDR, '02-6110-1241' PH_NO FROM DUAL UNION ALL
SELECT '1' LINE_NO,'동묘앞' ST_NM,'서울특별시 종로구 종로 359 (숭인동)' ADDR, '02-6110-1271' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'시청' ST_NM,'서울특별시 중구 서소문로 지하 127 (서소문동)' ADDR, '02-6110-2011' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'을지로입구' ST_NM,'서울특별시 중구 을지로 지하 42 (을지로1가)' ADDR, '02-6110-2021' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'을지로3가' ST_NM,'서울특별시 중구 을지로 지하 106 (을지로3가)' ADDR, '02-6110-2031' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'을지로4가' ST_NM,'서울특별시 중구 을지로 지하 178 (을지로4가)' ADDR, '02-6110-2041' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'동대문역사문화공원' ST_NM,'서울특별시 중구 을지로 지하 279 (을지로7가)' ADDR, '02-6110-2051' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'신당' ST_NM,'서울특별시 중구 퇴계로 지하 431-1 (신당동)' ADDR, '02-6110-2061' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'상왕십리' ST_NM,'서울특별시 성동구 왕십리로 지하 374 (하왕십리동)' ADDR, '02-6110-2071' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'왕십리(성동구청)' ST_NM,'서울특별시 성동구 왕십리로 지하300 (행당동)' ADDR, '02-6110-2081' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'한양대' ST_NM,'서울특별시 성동구 왕십리로 206 (행당동)' ADDR, '02-6110-2091' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'뚝섬' ST_NM,'서울특별시 성동구 아차산로 18 (성수동1가)' ADDR, '02-6110-2101' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'성수' ST_NM,'서울특별시 성동구 아차산로 100 (성수동2가)' ADDR, '02-6110-2111' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'건대입구' ST_NM,'서울특별시 광진구 아차산로 243 (화양동)' ADDR, '02-6110-2121' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'구의(광진구청)' ST_NM,'서울특별시 광진구 아차산로 384-1 (구의동)' ADDR, '02-6110-2131' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'강변(동서울터미널)' ST_NM,'서울특별시 광진구 강변역로 53 (구의동)' ADDR, '02-6110-2141' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'잠실나루' ST_NM,'서울특별시 송파구 오금로 20 (신천동)' ADDR, '02-6110-2151' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'잠실(송파구청)' ST_NM,'서울특별시 송파구 올림픽로 지하 265 (잠실동)' ADDR, '02-6110-2161' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'잠실새내' ST_NM,'서울특별시 송파구 올림픽로 지하 140 (잠실동)' ADDR, '02-6110-2171' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'종합운동장' ST_NM,'서울특별시 송파구 올림픽로 지하 23 (잠실동)' ADDR, '02-6110-2181' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'삼성(무역센터)' ST_NM,'서울특별시 강남구 테헤란로 지하 538 (삼성동)' ADDR, '02-6110-2191' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'선릉' ST_NM,'서울특별시 강남구 테헤란로 지하 340 (삼성동)' ADDR, '02-6110-2201' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'역삼' ST_NM,'서울특별시 강남구 테헤란로 지하 156 (역삼동)' ADDR, '02-6110-2211' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'강남' ST_NM,'서울특별시 강남구 강남대로 지하 396 (역삼동)' ADDR, '02-6110-2221' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'교대(법원•검찰청)' ST_NM,'서울특별시 서초구 서초대로 지하 294 (서초동)' ADDR, '02-6110-2231' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'서초' ST_NM,'서울특별시 서초구 서초대로 지하 233 (서초동)' ADDR, '02-6110-2241' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'방배' ST_NM,'서울특별시 서초구 방배로 지하 80 (방배동)' ADDR, '02-6110-2251' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'사당' ST_NM,'서울특별시 동작구 남부순환로 지하 2089 (사당동)' ADDR, '02-6110-2261' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'낙성대' ST_NM,'서울특별시 관악구 남부순환로 지하 1928 (봉천동)' ADDR, '02-6110-2271' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'서울대입구(관악구청)' ST_NM,'서울특별시 관악구 남부순환로 지하 1822 (봉천동)' ADDR, '02-6110-2281' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'봉천' ST_NM,'서울특별시 관악구 남부순환로 지하 1721 (봉천동)' ADDR, '02-6110-2291' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'신림' ST_NM,'서울특별시 관악구 남부순환로 지하 1614 (신림동)' ADDR, '02-6110-2301' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'신대방' ST_NM,'서울특별시 동작구 대림로 2 (신대방동)' ADDR, '02-6110-2311' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'구로디지털단지' ST_NM,'서울특별시 구로구 도림천로 477 (구로동)' ADDR, '02-6110-2321' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'대림(구로구청)' ST_NM,'서울특별시 구로구 도림천로 351 (구로동)' ADDR, '02-6110-2331' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'신도림' ST_NM,'서울특별시 구로구 새말로 지하 117-21 (신도림동)' ADDR, '02-6110-2341' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'문래' ST_NM,'서울특별시 영등포구 당산로 지하 28 (문래동3가)' ADDR, '02-6110-2351' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'영등포구청' ST_NM,'서울특별시 영등포구 당산로 지하 121 (당산동3가)' ADDR, '02-6110-2361' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'당산' ST_NM,'서울특별시 영등포구 당산로 229 (당산동 6가)' ADDR, '02-6110-2371' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'합정' ST_NM,'서울특별시 마포구 양화로 지하 55 (서교동)' ADDR, '02-6110-2381' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'홍대입구' ST_NM,'서울특별시 마포구 양화로 지하160 (동교동)' ADDR, '02-6110-2391' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'신촌' ST_NM,'서울특별시 마포구 신촌로 지하 90 (노고산동)' ADDR, '02-6110-2401' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'이대' ST_NM,'서울특별시 마포구 신촌로 지하 180 (염리동)' ADDR, '02-6110-2411' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'아현' ST_NM,'서울특별시 마포구 신촌로 지하 270 (아현동)' ADDR, '02-6110-2421' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'충정로(경기대입구)' ST_NM,'서울특별시 서대문구 서소문로 지하 17 (충정로3가)' ADDR, '02-6110-2431' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'용답' ST_NM,'서울특별시 성동구 용답길 86 (용답동)' ADDR, '02-6110-1341' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'신답' ST_NM,'서울특별시 성동구 천호대로 232 (용답동)' ADDR, '02-6110-1351' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'용두(동대문구청)' ST_NM,'서울특별시 동대문구 천호대로 지하 129 (용두동)' ADDR, '02-6110-1361' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'신설동' ST_NM,'서울특별시 동대문구 왕산로 지하 1 (신설동)' ADDR, '02-6110-1371' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'도림천' ST_NM,'서울특별시 구로구 경인로 67길 160 (신도림동)' ADDR, '02-6110-2441' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'양천구청' ST_NM,'서울특별시 양천구 목동로3길 지하 33(신정동)' ADDR, '02-6110-2451' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'신정네거리' ST_NM,'서울특별시 양천구 중앙로 지하 261 (신정동)' ADDR, '02-6110-2461' PH_NO FROM DUAL UNION ALL
SELECT '2' LINE_NO,'까치산' ST_NM,'서울특별시 강서구 강서로 지하54(화곡동)' ADDR, '02-6110-5180' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'지축' ST_NM,'경기도 고양시 덕양구 삼송로 300 (지축동)' ADDR, '02-6110-3191' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'구파발' ST_NM,'서울특별시 은평구 진관2로 지하 15-25 (진관동)' ADDR, '02-6110-3201' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'연신내' ST_NM,'서울특별시 은평구 통일로 지하 849 (갈현동)' ADDR, '02-6110-3211' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'불광' ST_NM,'서울특별시 은평구 통일로 지하 723-1 (대조동)' ADDR, '02-6110-3221' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'녹번' ST_NM,'서울특별시 은평구 통일로 지하 602-1 (녹번동)' ADDR, '02-6110-3231' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'홍제' ST_NM,'서울특별시 서대문구 통일로 지하 440-1 (홍제동)' ADDR, '02-6110-3241' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'무악재' ST_NM,'서울특별시 서대문구 통일로 지하 361 (홍제동)' ADDR, '02-6110-3251' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'독립문' ST_NM,'서울특별시 서대문구 통일로 지하 247 (현저동)' ADDR, '02-6110-3261' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'경복궁(정부서울청사)' ST_NM,'서울특별시 종로구 사직로 지하 130 (적선동)' ADDR, '02-6110-3271' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'안국' ST_NM,'서울특별시 종로구 율곡로 지하 62 (안국동)' ADDR, '02-6110-3281' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'종로3가' ST_NM,'서울특별시 종로구 돈화문로 지하 30 (묘동)' ADDR, '02-6110-3291' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'을지로3가' ST_NM,'서울특별시 중구 을지로 지하 129 (을지로3가)' ADDR, '02-6110-3301' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'충무로' ST_NM,'서울특별시 중구 퇴계로 지하 199 (필동2가)' ADDR, '02-6110-4231' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'동대입구' ST_NM,'서울특별시 중구 동호로 지하 256 (장충동2가)' ADDR, '02-6110-3321' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'약수' ST_NM,'서울특별시 중구 다산로 지하 122 (신당동)' ADDR, '02-6110-3331' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'금호' ST_NM,'서울특별시 성동구 동호로 지하 104 (금호동4가)' ADDR, '02-6110-3341' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'옥수' ST_NM,'서울특별시 성동구 동호로 21 (옥수동)' ADDR, '02-6110-3351' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'압구정' ST_NM,'서울특별시 강남구 압구정로 지하 172 (신사동)' ADDR, '02-6110-3361' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'신사' ST_NM,'서울특별시 강남구 도산대로 지하 102 (신사동)' ADDR, '02-6110-3371' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'잠원' ST_NM,'서울특별시 서초구 잠원로 4길 지하 46 (잠원동)' ADDR, '02-6110-3381' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'고속터미널' ST_NM,'서울특별시 서초구 신반포로 지하 188 (반포동)' ADDR, '02-6110-3391' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'교대(법원•검찰청)' ST_NM,'서울특별시 서초구 서초대로 지하 294 (서초동)' ADDR, '02-6110-3401' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'남부터미널(예술의전당)' ST_NM,'서울특별시 서초구 서초중앙로 지하 31 (서초동)' ADDR, '02-6110-3411' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'양재(서초구청)' ST_NM,'서울특별시 서초구 남부순환로 지하 2585 (서초동)' ADDR, '02-6110-3421' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'매봉' ST_NM,'서울특별시 강남구 남부순환로 지하 2744 (도곡동)' ADDR, '02-6110-3431' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'도곡' ST_NM,'서울특별시 강남구 남부순환로 지하 2814 (도곡동)' ADDR, '02-6110-3441' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'대치' ST_NM,'서울특별시 강남구 남부순환로 지하 2952 (대치동)' ADDR, '02-6110-3451' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'학여울' ST_NM,'서울특별시 강남구 남부순환로 지하 3104 (대치동)' ADDR, '02-6110-3461' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'대청' ST_NM,'서울특별시 강남구 일원로 지하 2 (일원동)' ADDR, '02-6110-3471' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'일원' ST_NM,'서울특별시 강남구 일원로 지하 121 (일원동)' ADDR, '02-6110-3481' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'수서' ST_NM,'서울특별시 강남구 광평로 지하 270 (수서동)' ADDR, '02-6110-3491' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'가락시장' ST_NM,'서울특별시 송파구 송파대로 지하 257 (가락동)' ADDR, '02-6110-3501' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'경찰병원' ST_NM,'서울특별시 송파구 중대로 지하 149 (가락동)' ADDR, '02-6110-3511' PH_NO FROM DUAL UNION ALL
SELECT '3' LINE_NO,'오금' ST_NM,'서울특별시 송파구 오금로 지하 321 (오금동)' ADDR, '02-6110-3521' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'당고개' ST_NM,'서울특별시 노원구 상계로 305 (상계동)' ADDR, '02-6110-4091' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'상계' ST_NM,'서울특별시 노원구 상계로 182 (상계동)' ADDR, '02-6110-4101' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'노원' ST_NM,'서울특별시 노원구 상계로 69-1 (상계동)' ADDR, '02-6110-4111' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'창동' ST_NM,'서울특별시 도봉구 마들로 11길 77 (창동)' ADDR, '02-6110-4121' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'쌍문' ST_NM,'서울특별시 도봉구 도봉로 지하 486-1 (창동)' ADDR, '02-6110-4131' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'수유(강북구청)' ST_NM,'서울특별시 강북구 도봉로 지하 338 (수유동)' ADDR, '02-6110-4141' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'미아(서울사이버대학)' ST_NM,'서울특별시 강북구 도봉로 지하 198 (미아동)' ADDR, '02-6110-4151' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'미아사거리' ST_NM,'서울특별시 강북구 도봉로 지하 50 (미아동)' ADDR, '02-6110-4161' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'길음' ST_NM,'서울특별시 성북구 동소문로 지하 248 (길음동)' ADDR, '02-6110-4171' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'성신여대입구(돈암)' ST_NM,'서울특별시 성북구 동소문로 지하 102 (동선동4가)' ADDR, '02-6110-4181' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'한성대입구(삼선교)' ST_NM,'서울특별시 성북구 삼선교로 지하 1 (삼선동1가)' ADDR, '02-6110-4191' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'혜화' ST_NM,'서울특별시 종로구 대학로 지하 120 (명륜4가)' ADDR, '02-6110-4201' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'동대문' ST_NM,'서울특별시 종로구 율곡로 지하 308 (종로6가)' ADDR, '02-6110-4211' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'동대문역사문화공원' ST_NM,'서울특별시 중구 장충단로 지하 230 (광희동2가)' ADDR, '02-6110-4221' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'충무로' ST_NM,'서울특별시 중구 퇴계로 지하 199 (필동2가)' ADDR, '02-6110-4231' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'명동' ST_NM,'서울특별시 중구 퇴계로 지하 126 (충무로2가)' ADDR, '02-6110-4241' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'회현(남대문시장)' ST_NM,'서울특별시 중구 퇴계로 지하 54 (남창동)' ADDR, '02-6110-4251' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'서울역' ST_NM,'서울특별시 용산구 한강대로 지하 392 (동자동)' ADDR, '02-6110-4261' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'숙대입구(갈월)' ST_NM,'서울특별시 용산구 한강대로 지하 306 (갈월동)' ADDR, '02-6110-4271' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'삼각지' ST_NM,'서울특별시 용산구 한강대로 지하 180 (한강로1가)' ADDR, '02-6110-4281' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'신용산' ST_NM,'서울특별시 용산구 한강대로 지하 112 (한강로2가)' ADDR, '02-6110-4291' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'이촌(국립중앙박물관)' ST_NM,'서울특별시 용산구 서빙고로 지하 83 (용산동5가)' ADDR, '02-6110-4301' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'동작(현충원)' ST_NM,'서울특별시 동작구 현충로 257 (동작동)' ADDR, '02-6110-4311' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'총신대입구(이수)' ST_NM,'서울특별시 동작구 동작대로 지하 117 (사당동)' ADDR, '02-6110-4321' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'사당' ST_NM,'서울특별시 동작구 동작대로 지하 3 (사당동)' ADDR, '02-6110-4331' PH_NO FROM DUAL UNION ALL
SELECT '4' LINE_NO,'남태령' ST_NM,'서울특별시 서초구 과천대로 지하 816 (방배동)' ADDR, '02-6110-4341' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'방화' ST_NM,'서울특별시 강서구 금낭화로 지하132 (방화동)' ADDR, '02-6311-5100' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'개화산' ST_NM,'서울특별시 강서구 양천로 22(방화동)' ADDR, '02-6311-5110' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'김포공항' ST_NM,'서울특별시 강서구 하늘길 지하77 (방화동)' ADDR, '02-6311-5120' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'송정' ST_NM,'서울특별시 강서구 공항대로 지하33 (공항동)' ADDR, '02-6311-5130' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'마곡' ST_NM,'서울특별시 강서구 공항대로 지하163 (가양동)' ADDR, '02-6311-5140' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'발산' ST_NM,'서울특별시 강서구 공항대로 지하267 (가양동)' ADDR, '02-6311-5150' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'우장산' ST_NM,'서울특별시 강서구 강서로 지하262 (화곡동)' ADDR, '02-6311-5160' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'화곡' ST_NM,'서울특별시 강서구 화곡로 지하168 (화곡동)' ADDR, '02-6311-5170' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'까치산' ST_NM,'서울특별시 강서구 강서로 지하54(화곡동)' ADDR, '02-6311-5180' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'신정(은행정)' ST_NM,'서울특별시 양천구 오목로 지하179 (신정동)' ADDR, '02-6311-5190' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'목동' ST_NM,'서울특별시 양천구 오목로 지하245 (목동)' ADDR, '02-6311-5200' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'오목교(목동운동장앞)' ST_NM,'서울특별시 양천구 오목로 지하342 (목동)' ADDR, '02-6311-5210' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'양평' ST_NM,'서울특별시 영등포구 양산로 지하21(양평동2가)' ADDR, '02-6311-5220' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'영등포구청' ST_NM,'서울특별시 영등포구 양산로 지하116(당산동3가)' ADDR, '02-6311-2361' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'영등포시장' ST_NM,'서울특별시 영등포구 양산로 지하200 (영등포동5가)' ADDR, '02-6311-5240' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'신길' ST_NM,'서울특별시 영등포구 경인로114가길 지하9 (영등포동1가)' ADDR, '02-6311-5250' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'여의도' ST_NM,'서울특별시 영등포구 여의나루로 지하40 (여의도동)' ADDR, '02-6311-5260' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'여의나루' ST_NM,'서울특별시 영등포구 여의동로 지하343 (여의도동)' ADDR, '02-6311-5270' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'마포' ST_NM,'서울특별시 마포구 마포대로 지하33 (도화동)' ADDR, '02-6311-5280' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'공덕' ST_NM,'서울특별시 마포구 마포대로 지하100(공덕동)' ADDR, '02-6311-5290' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'애오개' ST_NM,'서울특별시 마포구 마포대로 지하210 (아현동)' ADDR, '02-6311-5300' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'충정로(경기대입구)' ST_NM,'서울특별시 서대문구 충정로 지하28-1 (충정로3가)' ADDR, '02-6311-5310' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'서대문' ST_NM,'서울특별시 종로구 통일로 지하126 (평동)' ADDR, '02-6311-5320' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'광화문(세종문화회관)' ST_NM,'서울특별시 종로구 세종대로 지하172 (세종로)' ADDR, '02-6311-5330' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'종로3가(탑골공원)' ST_NM,'서울특별시 종로구 돈화문로11길 지하26 (돈의동)' ADDR, '02-6311-5340' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'을지로4가' ST_NM,'서울특별시 중구 창경궁로 지하51 (주교동)' ADDR, '02-6311-5350' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'동대문역사문화공원' ST_NM,'서울특별시 중구 마른내로 지하162 (광희동1가)' ADDR, '02-6311-5360' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'청구' ST_NM,'서울특별시 중구 청구로 지하77(신당동)' ADDR, '02-6311-5370' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'신금호' ST_NM,'서울특별시 성동구 금호로 지하154 (금호동2가)' ADDR, '02-6311-5380' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'행당' ST_NM,'서울특별시 성동구 행당로 지하89 (행당동)' ADDR, '02-6311-5390' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'왕십리(성동구청)' ST_NM,'서울특별시 성동구 왕십리로 지하300(행당동)' ADDR, '02-6311-5400' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'마장' ST_NM,'서울특별시 성동구 마장로 지하296 (마장동)' ADDR, '02-6311-5410' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'답십리' ST_NM,'서울특별시 성동구 천호대로 지하300(용답동)' ADDR, '02-6311-5420' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'장한평' ST_NM,'서울특별시 동대문구 천호대로 지하405 (장안동)' ADDR, '02-6311-5430' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'군자(능동)' ST_NM,'서울특별시 광진구 천호대로 지하550 (능동) ' ADDR, '02-6311-5440' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'아차산(어린이대공원후문)' ST_NM,'서울특별시 광진구 천호대로 지하657 (능동)' ADDR, '02-6311-5450' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'광나루(장신대)' ST_NM,'서울특별시 광진구 아차산로 지하571 (광장동)' ADDR, '02-6311-5460' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'천호(풍납토성)' ST_NM,'서울특별시 강동구 천호대로 지하997 (천호동)' ADDR, '02-6311-5470' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'강동' ST_NM,'서울특별시 강동구 천호대로 지하1097 (천호동)' ADDR, '02-6311-5480' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'길동' ST_NM,'서울특별시 강동구 양재대로 지하1480 (길동)' ADDR, '02-6311-5490' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'굽은다리(강동구민회관앞)' ST_NM,'서울특별시 강동구 양재대로 지하1572 (명일동)' ADDR, '02-6311-5500' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'명일' ST_NM,'서울특별시 강동구 양재대로 지하1632 (명일동)' ADDR, '02-6311-5510' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'고덕' ST_NM,'서울특별시 강동구 고덕로 지하253 (고덕동)' ADDR, '02-6311-5520' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'상일동' ST_NM,'서울특별시 강동구 고덕로 지하359 (상일동)' ADDR, '02-6311-5530' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'둔촌동' ST_NM,'서울특별시 강동구 양재대로 지하1369 (둔촌동)' ADDR, '02-6311-5540' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'올림픽공원(한국체대)' ST_NM,'서울특별시 송파구 양재대로 지하1233 (방이동)' ADDR, '02-6311-5550' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'방이' ST_NM,'서울특별시 송파구 양재대로 지하1127 (방이동)' ADDR, '02-6311-5560' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'오금' ST_NM,'서울특별시 송파구 오금로 지하321(오금동)' ADDR, '02-6311-3521' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'개롱' ST_NM,'서울특별시 송파구 오금로 지하402 (가락동)' ADDR, '02-6311-5580' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'거여' ST_NM,'서울특별시 송파구 오금로 지하499(거여동)' ADDR, '02-6311-5590' PH_NO FROM DUAL UNION ALL
SELECT '5' LINE_NO,'마천' ST_NM,'서울특별시 송파구 마천로57길 지하7(마천동)' ADDR, '02-6311-5600' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'응암' ST_NM,'서울특별시 은평구 증산로 지하477 (역촌동)' ADDR, '02-6311-6100' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'역촌' ST_NM,'서울특별시 은평구 서오릉로 지하63 (녹번동)' ADDR, '02-6311-6110' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'불광' ST_NM,'서울특별시 은평구 통일로 지하723-1 (대조동)' ADDR, '02-6311-6120' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'독바위' ST_NM,'서울특별시 은평구 불광로 지하129-1 (불광동)' ADDR, '02-6311-6130' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'연신내' ST_NM,'서울특별시 은평구 통일로 지하849 (갈현동)' ADDR, '02-6311-3211' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'구산' ST_NM,'서울특별시 은평구 연서로 지하137-1(구산동)' ADDR, '02-6311-6150' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'새절(신사)' ST_NM,'서울특별시 은평구 증산로 지하400 (신사동' ADDR, '02-6311-6160' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'증산(명지대앞)' ST_NM,'서울특별시 은평구 증산로 지하306(증산동)' ADDR, '02-6311-6170' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'디지털미디어시티' ST_NM,'서울특별시 은평구 수색로 지하175 (증산동)' ADDR, '02-6311-6180' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'월드컵경기장(성산)' ST_NM,'서울특별시 마포구 월드컵로 지하240 (성산동)' ADDR, '02-6311-6190' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'마포구청' ST_NM,'서울특별시 마포구 월드컵로 지하190 (성산동)' ADDR, '02-6311-6200' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'망원' ST_NM,'서울특별시 마포구 월드컵로 지하77 (망원동)' ADDR, '02-6311-6210' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'합정' ST_NM,'서울특별시 마포구 양화로 지하45 (합정동)' ADDR, '02-6311-6220' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'상수' ST_NM,'서울특별시 마포구 독막로 지하85 (상수동)' ADDR, '02-6311-6230' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'광흥창(서강)' ST_NM,'서울특별시 마포구 독막로 지하165 (창전동)' ADDR, '02-6311-6240' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'대흥(서강대앞)' ST_NM,'서울특별시 마포구 대흥로 지하85(대흥동)' ADDR, '02-6311-6250' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'공덕' ST_NM,'서울특별시 마포구 마포대로 지하100(공덕동)' ADDR, '02-6311-5290' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'효창공원앞' ST_NM,'서울특별시 용산구 백범로 지하287 (효창동)' ADDR, '02-6311-6270' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'삼각지' ST_NM,'서울특별시 용산구 한강대로 지하185 (한강로1가)' ADDR, '02-6311-6280' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'녹사평(용산구청)' ST_NM,'서울특별시 용산구 녹사평대로 지하195 (용산동4가)' ADDR, '02-6311-6290' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'이태원' ST_NM,'서울특별시 용산구 이태원로 지하177(이태원동)' ADDR, '02-6311-6300' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'한강진' ST_NM,'서울특별시 용산구 이태원로 지하287 (한남동)' ADDR, '02-6311-6310' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'버티고개' ST_NM,'서울특별시 중구 다산로 지하38 (신당동)' ADDR, '02-6311-6320' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'약수' ST_NM,'서울특별시 중구 다산로 지하115 (신당동)' ADDR, '02-6311-6330' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'청구' ST_NM,'서울특별시 중구 청구로 지하77(신당동)' ADDR, '02-6311-5370' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'신당' ST_NM,'서울특별시 중구 다산로 지하260 (흥인동)' ADDR, '02-6311-6350' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'동묘앞' ST_NM,'서울특별시 종로구 지봉로 지하24 (숭인동)' ADDR, '02-6311-6360' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'창신' ST_NM,'서울특별시 종로구 지봉로 지하112 (창신동)' ADDR, '02-6311-6370' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'보문' ST_NM,'서울특별시 성북구 보문로 지하116 (보문동1가)' ADDR, '02-6311-6380' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'안암(고대병원앞)' ST_NM,'서울특별시 성북구 인촌로 지하89 (안암동5가)' ADDR, '02-6311-6390' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'고려대(종암)' ST_NM,'서울특별시 성북구 종암로 지하1 (종암동)' ADDR, '02-6311-6400' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'월곡(동덕여대)' ST_NM,'서울특별시 성북구 월곡로 지하107 (하월곡동)' ADDR, '02-6311-6410' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'상월곡(한국과학기술연구원)' ST_NM,'서울특별시 성북구 화랑로 지하157 (상월곡동)' ADDR, '02-6311-6420' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'돌곶이' ST_NM,'서울특별시 성북구 화랑로 지하243 (석관동)' ADDR, '02-6311-6430' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'석계' ST_NM,'서울특별시 노원구 화랑로 지하347-1 (월계동)' ADDR, '02-6311-6440' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'태릉입구' ST_NM,'서울특별시 노원구 동일로 지하992-1 (공릉동)' ADDR, '02-6311-7170' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'화랑대(서울여대입구)' ST_NM,'서울특별시 노원구 화랑로 지하510 (공릉동)' ADDR, '02-6311-6460' PH_NO FROM DUAL UNION ALL
SELECT '6' LINE_NO,'봉화산(서울의료원)' ST_NM,'서울특별시 중랑구 신내로 지하232 (신내동)' ADDR, '02-6311-6470' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'장암' ST_NM,'경기도 의정부시 동일로 121 (장암동)' ADDR, '02-6311-7090' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'도봉산' ST_NM,'서울특별시 도봉구 도봉로 964-40 (도봉동)' ADDR, '02-6311-7100' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'수락산' ST_NM,'서울특별시 노원구 동일로 지하1662 (상계동)' ADDR, '02-6311-7110' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'마들' ST_NM,'서울특별시 노원구 동일로 지하1530-1 (상계동)' ADDR, '02-6311-7120' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'노원' ST_NM,'서울특별시 노원구 동일로 지하1409 (상계동)' ADDR, '02-6311-7130' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'중계' ST_NM,'서울특별시 노원구 동일로 지하1308-1 (중계동)' ADDR, '02-6311-7140' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'하계' ST_NM,'서울특별시 노원구 동일로 지하1196(하계동)' ADDR, '02-6311-7150' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'공릉(서울과학기술대)' ST_NM,'서울특별시 노원구 동일로 지하1074 (공릉동)' ADDR, '02-6311-7160' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'태릉입구' ST_NM,'서울특별시 노원구 동일로 지하992-1 (공릉동)' ADDR, '02-6311-7170' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'먹골' ST_NM,'서울특별시 중랑구 동일로 지하901(묵동)' ADDR, '02-6311-7180' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'중화' ST_NM,'서울특별시 중랑구 동일로 지하797 (중화동)' ADDR, '02-6311-7190' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'상봉(시외버스터미널)' ST_NM,'서울특별시 중랑구 망우로 지하297 (상봉동)' ADDR, '02-6311-7200' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'면목' ST_NM,'서울특별시 중랑구 면목로 지하407(면목동)' ADDR, '02-6311-7210' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'사가정' ST_NM,'서울특별시 중랑구 사가정로 지하393 (면목동)' ADDR, '02-6311-7220' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'용마산' ST_NM,'서울특별시 중랑구 용마산로 지하227(면목동)' ADDR, '02-6311-7230' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'중곡' ST_NM,'서울특별시 광진구 능동로 지하417 (중곡동)' ADDR, '02-6311-7240' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'군자(능동)' ST_NM,'서울특별시 광진구 천호대로 지하550 (능동) ' ADDR, '02-6311-5440' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'어린이대공원(세종대)' ST_NM,'서울특별시 광진구 능동로 지하210(화양동)' ADDR, '02-6311-7260' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'건대입구' ST_NM,'서울특별시 광진구 능동로 지하110 (화양동)' ADDR, '02-6311-7270' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'뚝섬유원지' ST_NM,'서울특별시 광진구 능동로 10 (자양동)' ADDR, '02-6311-7280' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'청담' ST_NM,'서울특별시 강남구 학동로 지하508 (청담동)' ADDR, '02-6311-7290' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'강남구청' ST_NM,'서울특별시 강남구 학동로 지하346 (삼성동)' ADDR, '02-6311-7300' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'학동' ST_NM,'서울특별시 강남구 학동로 지하180(논현동)' ADDR, '02-6311-7310' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'논현' ST_NM,'서울특별시 강남구 학동로 지하102(논현동)' ADDR, '02-6311-7320' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'반포' ST_NM,'서울특별시 서초구 신반포로 지하241(잠원동)' ADDR, '02-6311-7330' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'고속터미널' ST_NM,'서울특별시 서초구 신반포로 지하188 (반포동)' ADDR, '02-6311-7340' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'내방' ST_NM,'서울특별시 서초구 서초대로 지하103 (방배동)' ADDR, '02-6311-7350' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'이수' ST_NM,'서울특별시 동작구 사당로 지하310 (사당동)' ADDR, '02-6311-7360' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'남성' ST_NM,'서울특별시 동작구 사당로 지하218 (사당동)' ADDR, '02-6311-7370' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'숭실대입구(살피재)' ST_NM,'서울특별시 동작구 상도로 지하378(상도동)' ADDR, '02-6311-7380' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'상도' ST_NM,'서울특별시 동작구 상도로 지하272(상도1동)' ADDR, '02-6311-7390' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'장승배기' ST_NM,'서울특별시 동작구 상도로 지하188(상도동)' ADDR, '02-6311-7400' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'신대방삼거리' ST_NM,'서울특별시 동작구 상도로 지하76 (대방동)' ADDR, '02-6311-7410' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'보라매' ST_NM,'서울특별시 동작구 상도로 지하2(대방동)' ADDR, '02-6311-7420' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'신풍' ST_NM,'서울특별시 영등포구 신풍로 지하27(신길동)' ADDR, '02-6311-7430' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'대림(구로구청)' ST_NM,'서울특별시 영등포구 도림로 지하137(대림동)' ADDR, '02-6311-7440' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'남구로' ST_NM,'서울특별시 구로구 도림로 지하7(구로동)' ADDR, '02-6311-7450' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'가산디지털단지' ST_NM,'서울특별시 금천구 벚꽃로 309 (가산동)' ADDR, '02-6311-7460' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'철산' ST_NM,'경기도 광명시 철산로 지하13 (철산동)' ADDR, '02-6311-7470' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'광명사거리' ST_NM,'경기도 광명시 오리로 지하980(광명동)' ADDR, '02-6311-7480' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'천왕' ST_NM,'서울특별시 구로구 오리로 지하1154(오류동)' ADDR, '02-6311-7490' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'온수(성공회대입구)' ST_NM,'서울특별시 구로구 경인로3길 지하64 (온수동)' ADDR, '02-6311-7500' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'까치울' ST_NM,'경기도 부천시 원미구 길주로 지하626(춘의동)' ADDR, '02-6311-7510' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'부천종합운동장' ST_NM,'경기도 부천시 원미구 길주로 지하502(춘의동)' ADDR, '02-6311-7520' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'춘의' ST_NM,'경기도 부천시 원미구 길주로 지하406 (춘의동)' ADDR, '02-6311-7530' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'신중동' ST_NM,'경기도 부천시 원미구 길주로 지하314 (중동)' ADDR, '02-6311-7540' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'부천시청' ST_NM,'경기도 부천시 원미구 길주로 지하202(중동)' ADDR, '02-6311-7550' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'상동' ST_NM,'경기도 부천시 원미구 길주로 지하104(상동)' ADDR, '02-6311-7560' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'삼산체육관' ST_NM,'인천광역시 부평구 길주로 지하713 (삼산동)' ADDR, '02-6311-7570' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'굴포천' ST_NM,'인천광역시 부평구 길주로 지하623 (삼산동)' ADDR, '02-6311-7580' PH_NO FROM DUAL UNION ALL
SELECT '7' LINE_NO,'부평구청' ST_NM,'인천광역시 부평구 길주로 지하527(갈산동)' ADDR, '02-6311-7590' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'암사' ST_NM,'서울특별시 강동구 올림픽로 지하776 (암사동)' ADDR, '02-6311-8100' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'천호(풍납토성)' ST_NM,'서울특별시 강동구 천호대로 지하997 (천호동)' ADDR, '02-6311-5470' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'강동구청' ST_NM,'서울특별시 강동구 올림픽로 지하550 (성내동)' ADDR, '02-6311-8120' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'몽촌토성(평화의문)' ST_NM,'서울특별시 송파구 올림픽로 지하383 (신천동)' ADDR, '02-6311-8130' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'잠실(송파구청)' ST_NM,'서울특별시 송파구 올림픽로 305 (신천동)' ADDR, '02-6311-8140' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'석촌' ST_NM,'서울특별시 송파구 송파대로 지하439(석촌동)' ADDR, '02-6311-8150' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'송파' ST_NM,'서울특별시 송파구 송파대로 지하354 (가락동)' ADDR, '02-6311-8160' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'가락시장' ST_NM,'서울특별시 송파구 송파대로 지하257(가락동)' ADDR, '02-6311-8170' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'문정' ST_NM,'서울특별시 송파구 송파대로 지하179 (문정동)' ADDR, '02-6311-8180' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'장지' ST_NM,'서울특별시 송파구 송파대로 지하82(장지동)' ADDR, '02-6311-8190' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'복정' ST_NM,'서울특별시 송파구 송파대로 지하6 (장지동)' ADDR, '02-6311-8200' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'산성' ST_NM,'경기도 성남시 수정구 수정로 지하365(신흥동)' ADDR, '02-6311-8210' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'남한산성입구(성남법원,검찰청)' ST_NM,'경기도 성남시 수정구 산성대로 지하445 (단대동) ' ADDR, '02-6311-8220' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'단대오거리' ST_NM,'경기도 성남시 수정구 산성대로 지하365 (신흥동)' ADDR, '02-6311-8230' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'신흥' ST_NM,'경기도 성남시 수정구 산성대로 지하280 (신흥동)' ADDR, '02-6311-8240' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'수진' ST_NM,'경기도 성남시 수정구 산성대로 지하200 (수진동)' ADDR, '02-6311-8250' PH_NO FROM DUAL UNION ALL
SELECT '8' LINE_NO,'모란' ST_NM,'경기도 성남시 수정구 산성대로 100(수진동)' ADDR, '02-6311-8260' PH_NO FROM DUAL ;
가져온 주소, 전화번호 정보를 M_Station 테이블에 업데이트해줍니다. 그런데, 라인명(호선)이 이전에 승하차 정보에서 가져온 정보와 일치하지 않습니다. 아래와 같이 업로드한 테이블의 라인명 뒤에 ‘호선’을 붙여서 업데이트 합니다.
-- 주소, 전화번호 업데이트 1단계
UPDATE M_Station T1
INNER JOIN U_StationAddInfo T2
ON (T1.StationName = T2.ST_NM
AND T1.LineName = CONCAT(T2.LINE_NO,'호선'))
SET T1.Address = T2.ADDR
,T1.PhoneNo = T2.PH_NO;
위와 같이 업데이트를 하면 4건을 제외한 모든 지하철역의 주소가 들어갑니다. 나 머지 네 건은 지하철역 이름이 서로 같지 않기 때문입니다. 아래 SQL로 추가 업데이트를 합니다
-- 주소, 전화번호 업데이트 2단계
UPDATE M_Station T1
INNER JOIN U_StationAddInfo T2
ON (SUBSTRING(T1.StationName,1,2) = SUBSTRING(T2.ST_NM,1,2)
AND T1.LineName = CONCAT(T2.LINE_NO,'호선')
AND T1.Address = '')
SET T1.Address = T2.ADDR
,T1.PhoneNo = T2.PH_NO;
!! 데이터를 올리고 살펴보다 보니, 빠진 지하철 역이 있습니다. 1호선의 경우에는 영등포역, 창동역 등 많이 누락되어 있습니다. 어떤 이유인지 모르겠습니다. 정확한 분석을 위해서 마스터는 완벽하게 맞추어져 있어야 하는데.. 그렇지가 않네요. 그래도 감사하고 일단은 쓰도록 합니다. 혹시 모르니 공공 데이터 포털에 오류신고를 해났습니다.!!!
먼저 다운 받은 CSV 파일을 UTF8로 변환해서 저장해야 합니다. 그렇지 않으면 필자 환경에서는 한글이 깨져서 DB에 올라가게 됩니다. 아래와 같이 메모장으로 파일을 연 다음에 다른 이름으로 저장을 하도록 합니다. 다른 이름으로 저장할 때 아래와 같이 해야 합니다.
- 인코딩 부분을 ‘UTF-8’로 변경한다.
- 파일 형식을 ‘모든 파일’로 변경한다.
- 파일명을 UP_LOAD_GETONOFF.CSV로 변경한다.
이젠, U_StationUse에 올릴 파일명은 UP_LOAD_GETONOFF.CSV 입니다.
인코딩을 변환해서 저장했으면 파일을 올려보도록 합니다. 저는 'MySQL 5.7 Command Line Client'를 Root 계정으로 접속해서 아래 스크립트를 실행했습니다. 한글 파일이 깨지는 경우가 있어서 아래와 같이 캐릭터 셋을 변경한 후 파일을 업로드 해야 합니다.
(MySQL8 에서는 C:\ProgramData\MySQL\MySQL Server 8.0 위치의 my.ini 를 열어서 작업 필요.
[mysql] , [mysqld] 밑에 local-infile = 1을 표시. My.ini 저장할때 ansi변환필요한듯함.)
-- U_StationUse 업로드
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:\\Users\\sweetboss\\Desktop\\201910_디비안_서울지하철분석\\download\\UP_LOAD_GETONOFF.CSV' INTO TABLE U_StationUse FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\upload\\UP_LOAD_GETONOFF.CSV' INTO TABLE U_StationUse FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
실제 파일을 올리는 스크립트는 9~11번 라인의 SQL입니다. 해당 SQL이 한 줄로 실행되어야 합니다. 빨간색으로 표시된 부분은 각자 자신이 가지고 있는 파일 경로와 파일명을 사용해야 합니다.
데이터를 올린 후에 MySQL WorkBench에서 조회를 해보면 아래와 같이 데이터가 입력되어 있습니다.
6. 실제 사용할 테이블에 데이터 입력하기
아래 SQL로 M_Station 데이터를 생성합니다.
-- M_Station 업로드
INSERT INTO M_Station
(StationNo ,StationName ,LineName)
SELECT DISTINCT StationNo ,StationName ,LineName FROM U_StationUse;
이제, 승하차 정보를 만들어야 하는데, 업로드 테이블에는 시간이 컬럼으로 구성되어 있습니다. 우리가 실제 사용할 테이블은 시간이 로우로 구성되어야 합니다. 로우로 구성하기 위해 아래와 같이 C_Seq 테이블을 만들도록 합니다. C_는 Common의 약자다. C_Seq 테이블을 이용해서 컬럼을 로우로 바꿀것입니다.
-- 시퀀스 테이블 만들기
CREATE TABLE C_Seq
( Seq INT NOT NULL
,PRIMARY KEY(Seq)
);
INSERT INTO C_Seq (Seq)
SELECT (SELECT COUNT(*) FROM M_Station A WHERE A.StationNo <= T1.StationNo) Seq
FROM M_Station T1
아래 SQL로 역 승하차 정보를 만듭니다. C_Seq와 U_StationUse을 CROSS JOIN합니다.
-- T_StationUse
INSERT INTO T_StationUse
(UseDateTime
,GetOnOffType
,StationNo
,UseCount)
SELECT DATE_ADD(STR_TO_DATE(T2.UseDate, '%Y-%m-%d'), interval T1.Seq - 1 hour) UseDate
,CASE WHEN T2.GetOnOffType = '승차' THEN 'ON' ELSE 'OFF' END GetOnOffType
,T2.StationNo
,CASE T1.Seq - 1
WHEN 0 THEN T2.H00
WHEN 1 THEN T2.H01
WHEN 2 THEN T2.H02
WHEN 3 THEN T2.H03
WHEN 4 THEN T2.H04
WHEN 5 THEN T2.H05
WHEN 6 THEN T2.H06
WHEN 7 THEN T2.H07
WHEN 8 THEN T2.H08
WHEN 9 THEN T2.H09
WHEN 10 THEN T2.H10
WHEN 11 THEN T2.H11
WHEN 12 THEN T2.H12
WHEN 13 THEN T2.H13
WHEN 14 THEN T2.H14
WHEN 15 THEN T2.H15
WHEN 16 THEN T2.H16
WHEN 17 THEN T2.H17
WHEN 18 THEN T2.H18
WHEN 19 THEN T2.H19
WHEN 20 THEN T2.H20
WHEN 21 THEN T2.H21
WHEN 22 THEN T2.H22
WHEN 23 THEN T2.H23 END UseCount
FROM (SELECT * FROM C_Seq A WHERE A.Seq <= 24) T1
CROSS JOIN U_StationUse T2;
409,200 건 데이터가 입력됩니다. 제대로 데이터가 올라갔는지 확인이 필요합니다. 항상 확인이 필요하죠.
-- 적용된 데이터 검증
SELECT SUM(UseCount)
FROM T_StationUse T1
WHERE UseDateTime >= '2019-07-01'
AND UseDateTime < '2019-07-02'
AND StationNo = 150
AND GetOnOffType = 'ON';
위 SQL을 실행해보면 58,362가 나옵니다. 엑셀에서도 150번 역의 ON(승차) 7월1일 전체 승차 값을 보면 58,362 입니다. 문제 없이 승하차 정보가 반영되었습니다.
7. 데이터를 분석해보자.
매일 아침 김밥을 파는 건 너무 뻔합니다. 퇴근 시간에 지하철 역 앞에서 김밥을 팔아보시죠.
평일 저녁 19시대를 퇴근 시간으로 잡습니다. 해당 시간대에 하차가 많은 전철역을 골라 냅니다. 그리고 승차는 적어야 합니다. 하차와 승차가 같이 있다는 것은 전철을 갈아타고 있는 것으로 추측할 수 있습니다.
먼저 7월 중에 평일 19시만 조회해봅니다. 우선 C_Seq 테이블을 사용해서 아래와 같이 시간 집합을 만듭니다.
-- 7월 중에 평일 19시만 조회
SELECT DATE_ADD(DATE_ADD(STR_TO_DATE('2019-07-01','%Y-%m-%d'), interval T1.seq day)
, interval 19 hour) UseDate
,WEEKDAY(DATE_ADD(STR_TO_DATE('2019-07-01','%Y-%m-%d'), interval T1.seq day)
) WeekDay
FROM C_Seq T1
WHERE DATE_ADD(STR_TO_DATE('2019-07-01','%Y-%m-%d'), interval T1.seq day) < '2019-08-01'
AND WEEKDAY(DATE_ADD(STR_TO_DATE('2019-07-01','%Y-%m-%d'), interval T1.seq day)) NOT IN (5,6)
위 SQL과 T_UseStation을 조인해서 7월 평일 19시의 데이터만 조회하도록 합니다. 최종 분석 SQL은 아래와 같습니다.(아래 SQL은 호선이 다른 환승은 고려 안했습니다. 각자 환승을 고려해서 SQL을 개발해 봅시다.)
-- 최종 결과.
SELECT T2.StationNo
,MAX(T3.LineName)
,MAX(T3.StationName)
,SUM(CASE WHEN T2.GetOnOffTYpe = 'ON' THEN T2.UseCount END) ON_COUNT
,SUM(CASE WHEN T2.GetOnOffTYpe = 'OFF' THEN T2.UseCount END) OFF_COUNT
,SUM(CASE WHEN T2.GetOnOffTYpe = 'OFF' THEN T2.UseCount END)
- SUM(CASE WHEN T2.GetOnOffTYpe = 'ON' THEN T2.UseCount END) TTL_OFF_CNT
FROM (
SELECT DATE_ADD(DATE_ADD(STR_TO_DATE('2019-07-01','%Y-%m-%d'), interval T1.seq day)
, interval 19 hour) UseDate
,WEEKDAY(DATE_ADD(STR_TO_DATE('2019-07-01','%Y-%m-%d'), interval T1.seq day)
) WeekDay
FROM C_Seq T1
WHERE DATE_ADD(STR_TO_DATE('2019-07-01','%Y-%m-%d'), interval T1.seq day) < '2019-08-01'
AND WEEKDAY(DATE_ADD(STR_TO_DATE('2019-07-01','%Y-%m-%d'), interval T1.seq day)) NOT IN (5,6)
) T1
INNER JOIN T_StationUse T2
ON (T1.UseDate = T2.UseDateTime)
INNER JOIN M_Station T3
ON (T3.StationNo = T2.StationNo)
GROUP BY T2.StationNo
ORDER BY SUM(CASE WHEN T2.GetOnOffTYpe = 'OFF' THEN T2.UseCount END)
- SUM(CASE WHEN T2.GetOnOffTYpe = 'ON' THEN T2.UseCount END) DESC
;
아래와 같은 결과를 얻었습니다. 결과로는 저녁에 신림역에서 김밥을 말면 될거 같습니다. 그런데 신림역은.. 술집도 많아서 회식하러 사람들이 많이 모이는거 아닐까 생각이 듭니다. 그렇다면, 아래 데이터에 생활권을 좀 고려해서 김밥 말 곳을 정하면 되지 않을까요?. 여기에.. 역 근처 식당, 술집 정보까지 얹는다면 쓸만하지 않을까 싶습니다.
다운한 Zip 파일을 압축 풀어보면, CSV 파일이 있습니다. CSV파일을 엑셀에서 열어보면 아래와 같습니다.
3. 테이블 설계
위 그림을 보고 테이블을 설계합니다. 먼저 아래와 같이 업로드용 테이블을 설계합니다.
위 테이블에서 U_ 는 업로드용 테이블을 뜻하는 약어입니다. 업로드용 테이블은 보통 PK, FK와 같은 제약을 설정하지 않습니다.
업로드용 테이블에 올린 후에는 아래 테이블 구조로 데이터를 관리합니다. 지하철역과 승하차 정보를 별도 테이블로 관리합니다.
M_는 마스터 테이블을 뜻하고, T_는 실적(Transaction)을 뜻하는 약어입니다.
4. 테이블 생성
아래 스크립트로 업로드용 테이블을 생성합니다.
-- 업로드용 테이블 생성
CREATE TABLE U_StationUse(
UseDate VARCHAR(100) CHARACTER SET UTF8MB4
,LineName VARCHAR(100) CHARACTER SET UTF8MB4
,StationNo VARCHAR(100) CHARACTER SET UTF8MB4
,StationName VARCHAR(100) CHARACTER SET UTF8MB4
,GetOnOffType VARCHAR(100) CHARACTER SET UTF8MB4
,H04 VARCHAR(100)
,H05 VARCHAR(100)
,H06 VARCHAR(100)
,H07 VARCHAR(100)
,H08 VARCHAR(100)
,H09 VARCHAR(100)
,H10 VARCHAR(100)
,H11 VARCHAR(100)
,H12 VARCHAR(100)
,H13 VARCHAR(100)
,H14 VARCHAR(100)
,H15 VARCHAR(100)
,H16 VARCHAR(100)
,H17 VARCHAR(100)
,H18 VARCHAR(100)
,H19 VARCHAR(100)
,H20 VARCHAR(100)
,H21 VARCHAR(100)
,H22 VARCHAR(100)
,H23 VARCHAR(100)
,H00 VARCHAR(100)
,H01 VARCHAR(100)
,H02 VARCHAR(100)
,H03 VARCHAR(100)
,Total VARCHAR(100)
) ENGINE = InnoDB;
아래 스크립트로 실제 사용할 역(M_Station)과 역승하차정보(T_StationUse) 테이블을 만듭니다.
-- M_Station, T_StationUse 생성
CREATE TABLE M_Station
(StationNo INT NOT NULL
,StationName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,LineName VARCHAR(100) CHARACTER SET UTF8MB4 NOT NULL
,PRIMARY KEY(StationNo)
,UNIQUE KEY(LineName, StationName));
CREATE TABLE T_StationUse
(UseDateTime DATETIME NOT NULL
,GetOnOffType VARCHAR(40) NOT NULL
,StationNo INT NOT NULL
,UseCount INT NOT NULL DEFAULT 0
,PRIMARY KEY(UseDateTime, GetOnOffType, StationNo)
,KEY(StationNo, UseDateTime)
);
ALTER TABLE T_StationUse
ADD CONSTRAINT FK_T_StationUse_1 FOREIGN KEY(StationNo) REFERENCES M_Station(StationNo);
