(SQL포트폴리오) 서울시 상권데이터를 통한 특수상권지역 파악과 피그마블 게임 구현

[7기_은해찬]

SQL_응용_NCS_평가_은해찬.pdf

* Portfolio Intro

 목표 1. 

  서울시에서 특수 상권이 있는 지역은 어디인지, 어떤 방법을 통해 접근할 것인지, 

 분석 결과는 어떻게 될 것인지 SQL을 통해 구현한다.

 목표 2.

  서울시 상권 데이터를 통해 '피그마블'이라는 게임을 구현/시뮬레이션 한다. 피그마블은 브루마블의 컨셉을 빌린 게임으로,

 주사위를 던져 매장을 방문 후 칼로리를 섭취하는 게임이다. 총 10번의 턴이 주어지며, 가장 많은 칼로리를 섭취한 플레이어가

 우승을 하는 방식이다.

1. Raw data source

 서울시의 상권데이터를 공공데이터 포털을 통해 가져옴.

 총 39개 컬럼, 52만건

 Data load는 sql developer를 통해 수행.

 데이터 출처

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

2. 테이블 생성

 raw data를 담기 위한 테이블인 market_loc를 생성함.

 <코드>

  create table market_loc

 (m_code number(10), m_name varchar2(100), m_name_loc varchar2(100),

  class1_code varchar2(10), class1_name varchar2(100),  class2_code varchar2(10),

  class2_name varchar2(10), class3_code varchar2(20), class3_name varchar2(100),

  class4_code varchar2(20), class4_name varchar2(100), sido_code number(10),

  sido_name varchar2(20), sigungu_code number(10), sigungu_name varchar2(20),

  hdong_code number(20), hdong_name varchar2(20), bdong_code number(20),

  bdong_name varchar2(20), jibun_code number(30), DJ_code number(10),

  DJ_name varchar2(10), jibun_main number(10), jibun_sub number(10),

  jibun_address varchar2(100), road_code‎ number(30), road_address varchar2(100),

  bld_main number(10), bld_sub number(10), bld_mt_num number(30),

  bld_name varchar2(20), roadm_address varchar2(20), old_postnum number(10),

  new_postnum number(10), dong_info number(10), story_info number(10),

  ho_info number(10), longitude number, latitude number );

3. 분석과정

  (1) 서울시 전체 상권 분석 : 한식, 카페, 유흥주점 순으로 매장이 많음.

   

  /*서울시에서 가장 많은 매장종류는? -> 한식, 카페, 유흥주점으로 많다.*/

  select sido_name, class2_name, cnt

  from (select sido_name, class2_name, count(*) cnt

      from market_loc

                   where sido_name = '서울특별시'

              and (class1_name ='음식' or class1_name = '관광/여가/오락')

                   group by sido_name, class2_name

                   order by cnt desc)  ;

 (2) 대부분의 지역에서 한식이 1위를 차지하고 있음을 확인함. 이를 통해 한식이 1위가 아닌 지역은 특수상권지역이라고

    할 수 있지 않을까라는 인사이트를 얻게 되었음. ex) 정자동 카페거리, 인천 차이나 타운

   

   /*대부분의 동네가 한식집이 가장 많다는 것을 발견 -1. 중계동*/

   select sido_name, class2_name, cnt

from (select sido_name, class2_name, count(*) cnt

     from market_loc

            where sido_name = '서울특별시'

             and bdong_name = '중계동'

             and (class1_name ='음식' or class1_name = '관광/여가/오락')

            group by sido_name, class2_name

            order by cnt desc)  ;

   

   /*대부분의 동네가 한식집이 가장 많다는 것을 발견 -2. 신림동*/

   select sido_name, class2_name, cnt

from (select sido_name, class2_name, count(*) cnt

     from market_loc

            where sido_name = '서울특별시'

             and bdong_name = '신림동'

             and (class1_name ='음식' or class1_name = '관광/여가/오락')

            group by sido_name, class2_name

            order by cnt desc)  ;

   

   /*대부분의 동네가 한식집이 가장 많다는 것을 발견 -3. 역삼동*/

   select sido_name, class2_name, cnt

from (select sido_name, class2_name, count(*) cnt

     from market_loc

            where sido_name = '서울특별시'

             and bdong_name = '역삼동'

             and (class1_name ='음식' or class1_name = '관광/여가/오락')

            group by sido_name, class2_name

            order by cnt desc)  ;

 (3) 한식집 매장수가 1위가 아니라 다른 매장분류가 1위인 지역들을 도출함

   

   /*메인쿼리. 한식집보다 다른 매장이 많은 동네 알아보기 -> 각 분류별 랭킹 1위만 뽑음*/

    select * 

     from 

       /*서브쿼리 2. 시도 + 동별 class2_name cnt -> 순위랭킹 알아보기*/

       ( select sido_name, bdong_name, class2_name, cnt,

                    rank() over (partition by sido_name, bdong_name order by cnt desc) rnk

                 from 

                 /*서브쿼리 1. 시도 + 동별 class2_name의 cnt 알아보기*/

                 (select sido_name, bdong_name, class2_name, count(*) cnt

                         from market_loc

                         where sido_name = '서울특별시'

                          and (class1_name ='음식' or class1_name = '관광/여가/오락')

                         group by sido_name, bdong_name, class2_name

                         order by cnt desc) )

       where rnk =1

           and class2_name <> '한식'

       order by cnt desc;

4. 분석결과

   실제 구글 검색 결과, 프랜차이즈가 아닌 다양하고 독특한 카페들이 많이 있는 것을 확인했음.

  아래 이미지는 다이닝코드라는 빅데이터 맛집검색 사이트인데, 대현동 커피라고 검색하니 아래와 같은 결과가 나왔음.

    

  1위인 대현동을 확인한 결과 이 지역은 이화여자대학교의 주변으로, 여대생들이 좋아하는 카페상권이 집중되어 있음을 확인.

 연세대학교도 카페 상권 발달에 한 몫 기여한 것으로 추정. 따라서 상당 수준 유의한 결과가 도출되었다고 결론지을 수 있음.

 즉, SQL로 도출한 리스트를 특수상권이라고 할 만한 근거로 삼을 수 있음.

   

5. 피그마블 게임

 게임 코드인 SQL 파일을 아래와 같이 첨부함.

@ pf_1.sql

@ pf_final.sql

@ pf_one_st.sql

@ pf_turnrst.sql

basic query.sql

 시뮬레이션 동영상을 찍어 유튜브에 올림. (클릭시 새창 이동)