김밥 말러 갑시다 1/2

 

원본은 pdf및 첨부 파일은 아래 주소에서 받을 수 있습니다.

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

 

1. 목적 및 환경

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

사용 DBMS : MySQL 5.7 Windows

사용 Tool : MySQL Workbench, MySQL Command Line Client

활용 주제 : 지하철 승하차 정보를 통한 상권 분석.

 

2. 데이터 다운로드

공공데이터 포털에서 ‘서울교통공사_일별 역별 시간대별 승하차인원’를 다운 받습니다.

https://www.data.go.kr/dataset/15024829/fileData.do

아래 그림을 참고해서 다운합니다.

 

다운한 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);

 

오늘은 여기까지입니다. 

 

이어지는 글은 아래에 있습니다.

sweetquant.tistory.com/19

김밥 말러 갑시다 2/2