[SQL Angeles] 2018-01-09 LA 한인 SQL Server 스터디 모임

[SQL Angeles] 2018-01-09 LA 한인 SQL Server 스터디 모임

 

SQL Angeles 커뮤니티는 SQL PASS의 공식 회원이며, LA Chapter 그룹으로 PASS 커뮤니티 중 한국어로 진행되는 모임입니다. 자세한 소개는 아래 공식 홈페이지를 참고해주시기 바랍니다.

 

SQL Angeles PASS 공식 홈페이지 : http://SQLAngeles.com

 

SQL Angeles 커뮤니티는 정기적으로 화요일 8PM ~ 10PM (2시간, LA 시간 기준) 오프라인 스터디를 진행하며(장소 및 시간은 공식 홈페이지를 통해 공지 합니다.) SQL Server를 함께 공부하고 다양한 IT 트렌드를 공유하는 기술 및 네트워크를 공유하는 모임 입니다. 스터디는 참여 제한이 없습니다. 단, 오프라인으로 스터디에 참여하고 싶은 분들은 카카오톡(ID : SQLMVP), 페이스북(https://www.facebook.com/sqlmvp) 메신저, email(jevida@naver.com) 등으로 연락 주시기 바랍니다.  오프라인 모임의 경우 스터디 장소 출입이 자유롭지 못한 관계로 반드시 사전에 협의가 되어야 합니다.

 

SQL Angels Season 3에서는 오픈되어 있는 공공 데이터 활용하여 이론과 실습을 겸한 프로젝트형식으로 진행 합니다. 공공데이터를 SQL Server를 활용하여 데이터를 정제하는 과정(ETL), 데이터를 다양한 형대로 추출(SQL 쿼리문 작성 및 튜닝), 분석(기본 통계 이론 및 통계 쿼리 작성), 시각화(엑셀 및 SSRS를 활용한 리포트), 마지막으로 SQL Server에 포함된 데이터 마이닝 엔진을 이용하여 예측 모델을 만들어보는 것으로 진행합니다. 약 6개월 ~ 8개월 정도 (12회~ 15회 모임)에 걸쳐 진행할 예정이며, 스터디 일정 및 진행방향은 상황에 따라 변경될 수 있습니다. 스터디에 참석하시는 모든 분들은 랩탑을 지참해야 하며(실습으로 진행 됩니다.) SQL Server가 설치된 환경에서 진행 됩니다.

 

오늘 모임은 2018년 첫 모임으로 지금까지 실습했던 데이터에 대한 로드, 데이터 조회를 위한 기초 쿼리문, 집계 함수, 데이터 조인등을 모두 활용하여 한국의 범죄 통계 데이터를 이용해서 어떤 범죄가 많이 발생하는지, 어떤 지역에서 많은 범죄가 발생했는지, 증가한 범죄는 무엇인지 등 각자의 궁금사항와 요구사항에 따라 다양하게 쿼리를 작성하여 데이터를 추출하고, 각자 작성했던 쿼리를 소개하고 자신의 생각을 발표함으로써, 어떤 관점으로 코드를 만들고 데이터에 접근하였는지에 대해 토론할 수 있는 유익한 시간이었습니다.

 

아래 내용은 공공 데이터((https://www.data.go.kr/dataset/3074462/fileData.do)로 공개되어 있는 2015년, 2016년의 범죄데이터를 이용해서 데이터를 분석한 결과 입니다.

 

1.     2016년에 발생한 총 범죄 건수는 몇 건 인가요?

-      2016년에 발생한 총 범죄 건수는 1,849,450건 입니다.

 

2.     2016년 범죄 건수가 높은 상위 10개 도시는 어디인가요?

-      인구비율이 높은 지역의 범죄 발생율이 높은 것을 알 수 있습니다.

 

3.     2016년 많이 발생한 범죄 순서 및 건수는 어떻게 되나요?

-      교통범죄가 가장 많이 발생한 것으로 나타나고 있는데, 상세 데이터가 없어서 정확히는 알수 없으나 아마도, 가벼운 접촉사고등 중대과실이 아닌 경우도 모두 포함되어 있지않을까 생각이 듭니다. 의외로 사기 범죄도 많은 비중을 나타내고 있어 예상밖의 순위를 나타내는 지표도 있었습니다.

 

4.     2015년 대비 2016년에 증가한 범죄 및 건수는 어떻게 되나요?

-      특정 항목으로 분류되지 않은 범죄가 기타범죄로 분류되어 높은 숫자로 증가된 범죄를 나타내고 있습니다. 기타범죄를 제외하면 폭행이 가장 증가율이 높은 범죄로 나타났으며, 추행 및 강간 등 성범죄 관련 수를 합쳐보면 2015년 대비 성범죄가 많이 증가한 것을 알 수 있습니다.

(데이터를 분석할때 특정 항목의 이상치로 인해서 다른 그래프의 변화가 잘 보이지 않는 경우가 있습니다. 주의하시기 바랍니다.)

 

5.     2015년 대비 2016년에 감소한 범죄 및 건수는 어떻게 되나요?

-      폭력행위가 높은 수로 감소한것으로 나타나는데, 2016년에 가장 높게 증가한 항목이 폭행입니다. 데이터가 모순을 나타내는이 아마도 범죄 분류에 대한 카테고리가 변경되지 않았을까 하는 생각이 듭니다. 실제 2015년과 2016년 데이터 비교하였을때, 동일 또는 유사한 범죄에 대해서 일부 다른 카테고리로 분류되는 것을 확인할 수 있었습니다. 정확한 메탙 정보가 없어 추측한 내용이지만 실제 데이터를 집계할때, 이러한 부분때문에 데이터 정제과정이 매우 중요하며 표준화가 중요하다는 것을 알 수 있습니다.

 

6.     한국에서 발생한 범죄중 한국인을 제외한 국가별 범죄 발생 순위는 어떻게 되나요?

-      한국내에서 한국인을 제외하고 중국인이 가장 많은 범죄를 일으킨것으로 나타나는데 아마도 한국내 중국인이 많아서 상대적으로 많은 범죄가 발생하는 것으로 예상됩니다. 실제 국내 거주하는 외국인 수 대비 범죄 건수의 대한 비율을 확인할 수 있으면 실제 인구당 발생하는 범죄비율을 확인할 수 있지만 데이터를 구하지 못해서 확인하지 못하는 아쉬움이 있었습니다.

 

 

지금까지 공개된 데이터를 이용해서 몇 가지 분류 및 집계 작업을 진행해보았습니다.사실 공개되어 있는 데이터가 RAW데이터가 아닌 이미 집계된 데이터만 공개되어 있어 실습과정에서는 PIVOT 데이터는 UNPIVOT하여 다양한 관점에서 재집계하여 데이터를 추론하여 보았습니다. (RAW 데이터가 있었다면 더 많은 분석을 할 수 있었으리라 생각됩니다.)

위에서도 언급하였지만, 데이터를 분석하기 이전에 데이터를 정제하고 표준화 하는 과정에 매우 중요합니다. (대부분 분석이라는 마케팅에 가려져 중요하지 않게 취급되는 경우가 많습니다.) 이번 실습 사례에서도 확인할 수 있듯이, 동일 범죄가 각 년도에 따라 다른 카테고리로 분류되면서 엉뚱한 집계 결과를 나타내는 경우가 있기 때문에 데이터 표준화 및 클렌징 작업 뿐만 아니라 비즈니스에 대한 이해도를 높여 잘못된 분석으르 하지 않도록 유의해야 합니다.

 

다음시간에는 위의 데이터를 이용해서 SQL Server Reporting 서비스를 이용하여 데이터에 대한 차트를 생성하고, 생성된 보고서를 이메일을 통해 배포하여 공유하는 방법에 대해서 살펴보려고 합니다. 리포트 툴에 대한 설명 및 제작 방법 등을 학습합니다.

 

추가로 데이터에 대한 아쉬움 때문에 많은 것을 분석하지 못한 스터이 회원분들의 여론을 반영하여, 새로운 분석 데이터를 만들어보고자 최근 이슈가 되고 있는 비트코인에 대한 SNS 데이터를 크롤링 및 분석하여 사용자 데이터 기반으로 비트코인의 시세에 영향을 미치는 키워드가 무엇인지 살펴볼 예정 입니다.

 

오늘도 수고 많으셨습니다.  

 

 

2018-01-09 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

LA 한인 SQL 스터디 모임, LA IT 모임, DB 스터디, SQL 스터디, SQL Server, DB 스터디, LA SQL Server, SQL Angeles