지하철 1~4호선 승하차 고객 유형별 인원

[3기_김지민]

1.  이 데이터를 사용하게 된 동기

매일 아침 7시 41분 지하철을 시작으로 하루에 3시간 30여분정도 지하철에서 시간을 보낸다.

그러다 문득 사람들은 얼마나 지하철을 이용하며 붐비는 시간은 언제이고 어느 지하철 역이 사람이 한산한지 등 지하철에 대한

궁금증이 생기기 시작하여 본격 지하철 이용객을 알아보기 위해 이 데이터를 선택했다.

2.  의미있는 좋은 질문

1) 지하철 이용 고객 유형 별 지하철역 별 승하차시 가장 붐비는 시간대를 상위 5개 출력하시오.(listagg 사용)

2) 중국, 일본, 영어 관광객들이 가장 지하철을 많이 이용한 날짜, 역, 순위를 상위 5개 출력하시오.

3.   참고 data  ( 첨부 )

subway.zip

- 데이터는 3월, 7월, 12월 이용객의 데이터를 합산한 파일입니다.  157,954건의 데이터 입니다.

- 이는 데이터의 계절성 파악을 위해 임의로 구분했으며 9월의 경우 3월과 비슷할 것이라는 가정하에 구분했습니다.

- 분석에 사용할 데이터는 subway2 테이블로 3,948,850으로 분석 시 다소 시간이 걸립니다.

# 출처: 공공데이터 포털 지하철 승하차 정보

15년 상반기 역별, 일별, 시간대별, 권종별 승하차인원

http://www.data.go.kr/comm/file/download.do?atchFileId=FILE_000000001274701&fileDetailSn=1  

15년 하반기 역별, 일별, 시간대별, 권종별 승하차인원

http://www.data.go.kr/comm/file/download.do?atchFileId=FILE_000000001293514&fileDetailSn=1

4. 테이블 생성 코드

SUBWAY스크립트.zip

CREATE TABLE subway(
날짜 DATE,
호선 VARCHAR2(20),
역명 VARCHAR2(50),
구분 VARCHAR2(10),
할인 VARCHAR2(50),
T0405 NUMBER(10),
T0506 NUMBER(10),
T0607 NUMBER(10),
T0708 NUMBER(10),
T0809 NUMBER(10),
T0910 NUMBER(10),
T1011 NUMBER(10),
T1112 NUMBER(10),
T1213 NUMBER(10),
T1314 NUMBER(10),
T1415 NUMBER(10),
T1516 NUMBER(10),
T1617 NUMBER(10),
T1718 NUMBER(10),
T1819 NUMBER(10),
T1920 NUMBER(10),
T2021 NUMBER(10),
T2122 NUMBER(10),
T2223 NUMBER(10),
T2324 NUMBER(10),
T2401 NUMBER(10),
T0102 NUMBER(10),
T0203 NUMBER(10),
T0304 NUMBER(10),
합계 NUMBER(10) );

--데이터 입력 시 첫줄부터 넣으면 됨

-- 테이블 NULL값 처리
DELETE FROM SUBWAY
where 날짜 IS NULL;

-- unpivot만들기 전 table space 늘리기 이유는 tablespace에 제한이 있기 때문에 아래의 큰 unpivot을 만들 수 없다.

-- cmd에서 sqlplus로 작업한다. 계정은 sysdba로 한다.

sqlplus / as sysdba

-- 접속 후

alter tablespace system
 add datafile 'c:\system02.dbf' size 200m

-- unpivot 테이블을 만든다.
CREATE table subway2
as
SELECT *
 FROM SUBWAY
  unpivot(cnt FOR ltime IN (T0405, T0506, T0607, T0708, T0809, T0910, T1011, T1112,
               T1213, T1314, T1415, T1516, T1617, T1718, T1819, T1920,
                            T2021, T2122, T2223, T2324, T2401, T0102, T0203, T0304, 합계) );

5.  칼럼 설명

- 날짜: 지하철 이용 날짜

- 호선: 1~4호선

- 역명: 역 이름과 숫자는 각 역을 구분하는 고유번호로 1호선서울역과 4호선 서울역은 각각 150, 426

- 구분: 승차, 하차

- 할인: 이용고객 구분으로, 대학생, 일반, 중고생, 중국어 일반, 초등생, 영어 어린이, 우대, 일어 일반, 일어 어린이, 영어 일반, 중국어 어린이, 청소년

- ltime: 04~05 시간대부터 다음날 02~03시 까지

- cnt: 각 날짜 호선 역명 구분 할인 ltime별 이용객 수

6.   분석 쿼리

 1) 지하철 이용 고객 유형 별 지하철역 별 승하차시 가장 붐비는 시간대를 상위 5개 출력하시오.(listagg 사용)

SELECT 할인 유형, listagg(rr||'위 '||regexp_substr(역명, '[^(]+')||'('||ltime||')',', ') within group(ORDER BY 합 desc) AS "역 별 붐비는 순위"
 from
     (SELECT 할인, 역명, ltime, 합, ROW_NUMBER() OVER(PARTITION BY 할인 ORDER BY 합 desc) rr
       FROM (
          SELECT 할인, 역명, ltime, 합, r
           FROM
             (
              SELECT 할인, 역명, ltime, 합, row_number() OVER(PARTITION BY 할인, 역명 ORDER BY 합 desc) r
               from
                 (SELECT 할인,역명, ltime, SUM(cnt) 합
                  FROM SUBWAY2
                  WHERE ltime != '합계'
                  group BY 할인, 역명, ltime)
                          )
           WHERE r=1 AND 합>0)
                    )
  WHERE rr<=5
  GROUP BY 할인;

7.  결과 화면  ( 표 형태로 표시 )

1) 지하철 이용 고객 유형 별 지하철역 별 승하차시 가장 붐비는 시간대를 상위 5개 출력하시오.(listagg 사용)

2) 중국, 일본, 영어 관광객들이 가장 지하철을 많이 이용한 날짜를 상위 5개 출력하시오. (같이 풀어볼 문제)

8.  결론

1) 지하철 이용 고객 유형 별 지하철역 별 승하차시 가장 붐비는 시간대를 상위 5개 출력하시오.(listagg 사용)

1-1. 영어, 중국어, 일본인의 경우 명동에 가장 많이 머무는 것으로 파악됐다.

1-2. 일본, 중국의 경우 주로 쇼핑과 관련된 역을 이용했으며 영어의경우 쇼핑을 제외하면 고궁과 관련된 역을 이용하였다.

1-3. 국내 연령별로는 요금에 따라 초등, 중고생, 성인으로 나뉠 수 있다. 초등생의 경우 1위인 잠실 부근에 초등학교가 많기 때문에 1위로 나왔다.

1-4. 중고생의 경우 학교와 무관하게 홍대입구나 건대입구가 나왔는데 이는 만19세의 대학생들이 주로 가는 곳이라 많이 나온것 처럼 보인다.

1-5. 우대의 경우 만 65세 이상 국민이면 해당되는 사항인데 종로3가와 제기동 즉 탑골공원같이 노인분들이 많이 모이시는 곳이 1위로 나왔다.

2) 중국, 일본, 영어 관광객들이 가장 지하철을 많이 이용한 날짜를 상위 5개 출력하시오.

2-1. 모든 관광객이 많이 모이는 곳은 명동이다. 서울의 쇼핑과 주요 관광도시간에 연결이 잘 되어 있기 때문이라고 추측된다.

2-2. 중국, 영어 관광객의 경우 12월 연말에 많이 방문했다.

2-3. 일본 관광객은 특이하게 3월에 많이 방문했는데 15년 3월에 경상수지가 큰 흑자를 내 경제적상황이 좋았고 기타 다른 외부요인이 많이 작용한 것으로 파악된다.

9.  아쉬운점

 - 서울의 1~4호선까지 없고 이마저도 서울 일부 노선만 있기 때문에 분석이 제한적이었다.

 - 여의도, 인천(공항), 용인(에버랜드)와 같이 직장인들이나 관광객들이 주로 이용하는 지하철역에 대한 분석이 불가했다.

10.  향후 분석에 도움이 될 데이터

- 전 호선에 대한 이용객 정보

- 이용하는 고객에 대한 상세정보(예: 연령대, 성별, 출신) / 이는 카드회사 데이터와 매시업하여 파악할 수 있다고 생각함.

11.  문제에 대한 답

CREATE VIEW 중국

as

SELECT 날짜, 역명, SUM(cnt) 합

FROM subway2

  where ltime = '합계' AND 할인 LIKE '중국%'

  GROUP BY 날짜, 역명

  ORDER BY 3 desc;

CREATE VIEW 영어

as

SELECT 날짜, 역명, SUM(cnt) 합

FROM subway2

  where ltime = '합계' AND 할인 LIKE '영어%'

  GROUP BY 날짜, 역명

  ORDER BY 3 desc;

CREATE VIEW 일본

as

SELECT 날짜, 역명, SUM(cnt) 합

FROM subway2

  where ltime = '합계' AND 할인 LIKE '일어%'

  GROUP BY 날짜, 역명

  ORDER BY 3 desc;

SELECT a.날짜, a.역명, a.합, a.중국순위, b.날짜, b.역명, b.합, b.영어순위, c.날짜, c.역명, c.합, c.일본순위 

FROM ( (SELECT 날짜, 역명, 합, ROWNUM 중국순위

FROM 중국) a JOIN (SELECT 날짜, 역명, 합, ROWNUM 영어순위

FROM 영어) b ON(a.중국순위=b.영어순위) ) JOIN (SELECT 날짜, 역명, 합, ROWNUM 일본순위

         FROM 일본) c ON (a.중국순위=c.일본순위)

WHERE a.중국순위<=5;

[기진성]

나 사줭 :(

[안지훈]

ㅋ

[김동혁(3기)]

오늘 형 한명을 잃었습니다.

[홍주원]

SELECT c.순위,c.날짜,c.역명,c.중국,c.날짜_1,역명_1,c.영어,d.날짜,d.역명,d.일어
FROM ddd c, 일어1 d
WHERE c.순위 = d.순위 AND c.순위<=5 ;

[홍주원]

CREATE VIEW 영어1
as
SELECT 역명,날짜,영어, DENSE_RANK() OVER (ORDER BY 영어 DESC) 순위 from
(SELECT 날짜,역명,sum(cnt) 영어 FROM SUBWAY2
WHERE 할인 LIKE '%영어%' AND ltime='합계'
GROUP BY 역명,날짜);

[홍주원]

CREATE VIEW 일어1
as
SELECT 역명,날짜,일어, DENSE_RANK() OVER (ORDER BY 일어 DESC) 순위 from
(SELECT 날짜,역명,sum(cnt) 일어 FROM SUBWAY2
WHERE 할인 LIKE '%일어%' AND ltime='합계'
GROUP BY 역명,날짜);

[홍주원]

CREATE VIEW 중국1
as
SELECT 역명,날짜,중국, DENSE_RANK() OVER (ORDER BY 중국 DESC) 순위 from
(SELECT 날짜,역명,sum(cnt) 중국 FROM SUBWAY2
WHERE 할인 LIKE '%중국%' AND ltime='합계'
GROUP BY 역명,날짜);

[기진성]

CREATE VIEW chinnn
SELECT * FROM (
SELECT DENSE_RANK()OVER(ORDER BY cnt desc)랭크, 날짜, 역명, cnt 중국 FROM(
SELECT 날짜,역명, SUM(cnt) cnt
FROM subway2
WHERE 할인 LIKE '%중국%'
AND ltime != '합계'
GROUP BY 날짜, 역명))
WHERE 랭크<=5;


CREATE VIEW jappp
SELECT * FROM (
SELECT DENSE_RANK()OVER(ORDER BY cnt desc)랭크, 날짜, 역명, cnt 일본 FROM(
SELECT 날짜,역명, SUM(cnt) cnt
FROM subway2
WHERE 할인 LIKE '%일본%'
AND ltime != '합계'
GROUP BY 날짜, 역명))
WHERE 랭크<=5;

[홍주원]

CREATE VIEW ddd
as
SELECT b.순위,b.날짜,b.역명,b.중국,a.날짜 날짜_1,a.역명 역명_1,a.영어
FROM 영어1 a, 중국1 b
WHERE a.순위 = b.순위;

[기진성]

CREATE VIEW enggg
SELECT * FROM (
SELECT DENSE_RANK()OVER(ORDER BY cnt desc)랭크, 날짜, 역명, cnt 영어 FROM(
SELECT 날짜,역명, SUM(cnt) cnt
FROM subway2
WHERE 할인 LIKE '%영어%'
AND ltime != '합계'
GROUP BY 날짜, 역명))
WHERE 랭크<=5;

[기진성]

SELECT a.랭크,a.날짜1,a.역명1,a.중국,a.날짜2,a.역명2,a.영어, j.날짜,j.역명,j.일본 FROM
(SELECT c.랭크, c.날짜 날짜1, c.역명 역명1, c.중국,e.날짜 날짜2,e.역명 역명2,e.영어
FROM chinnn c, enggg e
WHERE c.랭크 = e.랭크) a, jappp j
WHERE a.랭크 = j.랭크;