김밥 말러 갑시다 2/2

지난 글에 이어지는 내용입니다.

sweetquant.tistory.com/18

김밥 말러 갑시다 1/2

 

5. 테이블 업로드

CSV 파일을 업로드용 테이블에 업로드할 차례입니다.

먼저 다운 받은 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
;

아래와 같은 결과를 얻었습니다. 결과로는 저녁에 신림역에서 김밥을 말면 될거 같습니다. 그런데 신림역은.. 술집도 많아서 회식하러 사람들이 많이 모이는거 아닐까 생각이 듭니다. 그렇다면, 아래 데이터에 생활권을 좀 고려해서 김밥 말 곳을 정하면 되지 않을까요?. 여기에.. 역 근처 식당, 술집 정보까지 얹는다면 쓸만하지 않을까 싶습니다.

 

 

끝입니다!!! 감사합니다~!