Project 기획 및 관리 (4) MySQL, 파이썬 연동

SQL 데이터 분석

피벗테이블은 가로 세로 집계하는 것

성별로는 구매에 큰 차이가 없어서 성별은 영향이 없다고 확인됨.

50대 60대 구매율이 많다는 것을 확인할 수 있다. 지역에 대한 차이점이 있는지 확인해봐야한다.

트리맵은 한눈에 알아볼 수 있게 하는 시각화이다.

Window + E 버튼 → 파일탐색기 나옴

CSV 파일을 테이블에 넣으려고 하는데 계속 파일이 doesn’t exist 한다고 계속 떴음. 문제는 SQL Server 폴더에 파일을 넣어야 했어야 했음.

SHOW VARIABLES LIKE 'secure_file_priv'; 라고 치면 어디에 저장해야하는지 나온다.

MySQL 폴더는 C드라이브에서 보기-숨김파일 표시하면 ProgramData 폴더가 나온다.

그 폴더 안에 MySQL 폴더가 있다. 업로드 파일에 csv 파일을 넣는다.

그러면 csv파일을 테이블로 넣을 수 있다.

혹은 SQL 워크벤치 옵션에 들어가서 서버 설정에서 local 폴더 모두 접근 가능하게 바꿀 수도 있다.

Gender 글자 제한을 10에서 5로 바꾸는 쿼리는 아래와 같다.

member 테이블 속성 확인하는 방법은 DESCRIBE

나이대별로 성별에 따른 구매 패턴을 분석하기 위해, 나이를 10살 단위로 그룹화하고 성별별로 총 구매 금액을 계산하는 쿼리를 작성했다.

결과물 데이터 긁어와서 엑셀에서 시각화했다.

지피티를 통해서 분석할 수 있는 주요 인사이트를 생각해낼 수 있고, 쿼리를 복붙해서 실행할 수 있다. 이 인사이트들은 매출 분석, 고객 행동 분석, 제품 분석, 매장 성과 분석 등을 포함한다.

 

Python에서 MySQL 연동하기

pymysql 라이브러리 설치하여 사용할 수 있다. 파이썬 자체에는 MySQL을 인식하는 기능이 없다. 파이썬 코드에서 MySQL을 활용하기 위해 외부 라이브러리인 pymysql을 설치해야한다.

파이썬 소프트웨어 내에서도 앱을 간단히 만들 수 있어서 SQL에 있는 자료를 끌고 와서 파이썬에서 대시보드 형태로 만들 수 있다. 내가 쓰는 서버가 중요하고, 그 서버를 쓸 수 있게 아는 라이브러리가 뭔지 찾아야 한다. 파이썬이라는 소프트웨어 안에다가 연결하면 된다.

“네트워크 프로그래밍” 서버 클라이언트 끼리의 데이터를 주고받기 위해서는 연결을 해야한다. TCP/IP 소켓을 연결해서 작업해야 한다. 웹 페이지 프로토콜에 맞춰서 작업을 해야한다. 네트워크 관련일을 하지 않는다면 기본만 알면 된다.

우리가 하는 SQL은 혼자하니까 다이렉트로 들어가서 받고 있지만, 우리가 하는 회원가입, 조회서비스, 구매한 다음에 오더 조회 같은 것들은 우리 컴퓨터로 보이는게 아니라 서버 거쳐가서 나오는 것이기 때문에 네트워크 관련된 프로그램이 연결되어있다. JAVA가 네트워크 전문이다. 데이터받고 네트워크 받다가 뺐다가 하는 서버라는 것을 구현하는데 특화된 것이 자바이다. 내 모니터에 보이지는 않지만 저 멀리서 구현되고 있다는 것이 “백엔드” 이다. “프론트”는 모니터에 나오는 화면을 만들어 내는 사람들이다. 백엔드로 가면 보안문제, 네트워크 문제 등등 많다. 요즘은 용어가 혼재가 되어있다. 우리나라는 전산이 잘되어있어서 백엔드를 거하게 만드는 시스템들이 없다. 기술이 좋아지면서 화면 구성하는게 쉬워졌다. 그래서 둘다 하는 것이 “풀스텍”이다. 현실적으로 안 맞는게 프론트 하는 사람한테는 어렵고, 백엔드 하는 사람은 쉬운게 풀스텍이다. 그런데 풀스텍이 백엔드보다 연봉이 낮으니까 안하려고하고, 프론트에서는 어려워서 못한다. 풀스텍이 말이 안되는 것이다. 프로그래밍이 요즘 엄청 쉬워지고 있다.

 

Python 시각화

파이썬에서 데이터베이스와 관련되어 있는 것은 시각화도 그 중하나이다.

 

파이썬 db화면설계

데이터베이스 화면 설계만 전문으로 하는 모듈을 찾아야 한다. 데이터베이스를 얘기할 때는 화면에만 뿌릴건지, 네모박스에 화면을 잘 만들어서 거기에 주거나 받거니 할 것인지도 들어가기 때문에, 테이블끼리의 구조도가 복잡하다면 어떤 화면을 통해서 화면에 입력을 할지, 나올지 나 쓰고 있는 것이다. 화면 하나를 만들기 위해 필요한 것은 DB가 필요하고, 화면을 구성하는 모듈이 있어야 하고, 웹페이지가 있어야하고, 잘 처리에서 대시보드 형태로 내 모니터에 할지, 화면에 보일지 정해야한다.

기술은 하나하나 구현하면 되는데 파이썬은 프로그래밍한 사람들이 해보면 너무 쉽다고 한다함. 굉장히 분야가 넓어서 파이썬은 다루기가 쉽지 않다. 자기 분야에 따라서 파이썬을 할 수 있는 범위가 다다르다. 본인 업무만 보면 되니까. 기술은 하나만 하면 되는데, 분석은 다해야하는 상황이다. 대신 기술자처럼만 안하면 된다. 어쩔 수 없이 앞으로 회사가면 새로운거 엄청 볼 것이다. 기술은 계속 발전하기 때문이다. 스스로 검색하여 많이 할 수 밖에 없다.

 

LEFT JOIN

이런 LEFT JOIN 테이블은 임시테이블보다 VIEW에 많이 만들어 놓는다.

사람들이 물건을 구매할 때 색상을 고민하는가? 연식을 고민하는가? 자동차를 사가는 사람들이 나중에 재구매할 때 혹은 처음 구매할 때 어떤 것에 집중하는가에 집중해서 거기에 맞는 데이터 셋을 만들어 놔야 한다. 자동차 볼 때 뭘보세요?라는 질문에 고객이 어떤 것을 대답할지 알고, 그거에 맞게 데이터셋을 구성해야한다. 고객 정보를 구성할 때도 판매상에서 고객의 정보를 어디까지 원하는가를 고려해야한다. 주로 근무지랑 이동거리가 보통 몇km내외인지, 가족끼리 탈건지, 몇명인지, 애기랑 탈건지, 주말에 쓰는지, 주중에 쓰는지, 장거리용인지 단거리용인지 많은 방법들이 있다. 차를 구매하는 고객들의 패턴을 알아내야 한다.

우리나라 데이터 분석이 잘 안되는 이유가 데이터가 없기 때문이다. 예전에 고객 정보를 받을 때 거주지, 이름 정도 밖에 없었기 때문이다. 애기랑 해서 몇명인지, 용도가 어떤건지 등등 세부적인 니즈에 대한 정확한 데이터가 없다. 분석에는 수많은 요소들이 들어가야 분석이 제대로 이루어진다. 차를 팔면서 연동해주면서 캐피탈에서는 갚을 능력이 있는가가 가장 중요한다. 여기서 제일 중요한 데이터는 신용도가 있다. 은행에서는 신용도를 연체기록, 금융기록을 보면서 알 수 있다. 신용카드 기록까지 본다. 신용도에 통신요금을 잘 갚는지도 신용점수에 들어간다. 은행권의 데이터는 우리가 기본적으로 제공한 정형데이터밖에 없다. 우리 집안의 가족 구성원에 어떤 문제가 있는지 회사는 알수없다. 분석이라고 하는 것은 위에서 데이터량이 많으면 좋겠지만, 왼쪽에서 오른쪽으로 컬럼이 많은지가 중요하다. 예전 데이터셋은 이런게 없다. 머신러닝이 하고 싶어도 분석할 데이터가 없다. 기본정보 데이터는 위에서 아래 데이터이고, 특징이 다양한 왼쪽에서 오른쪽으로 가는 데이터가 많아야 좋다. 요즘은 온라인으로 구매하면서 구매패턴이 생긴다. 회원정보도 있지만 이사람이 들어와서 자동차 카달로그를 얼마나 클릭해서 봤는지, 어느 제품을 유심하게 봤는지, 모니터 화면에서 어느 구역에 많이 머물렀는지 알 수 있다.(히트맵) 어떤 부분을 유심하게 봤는지, 어떤 검색을 이어서 했는지를 보면서 관심도가 나온다. 혼다 자동차의 연비는 얼마인지, 사람들은 뭐라고 하는지 구글검색에 나온다. 검색에 나오는 트렌드 데이터를 분석해서 사람들의 취향을 분석할 수 있었다. 그래서 네이버 구글이 클 수 밖에 없는 이유가 사람들의 관심, 트렌드가 기록이 많이 되기 때문이다. 카카오스토어에서 구매 행태를 볼 수 있다. 20대가 어디에 많이 구매했는지, 누구한테 줬는지도 확인할 수 있다. 인터넷에 사람들이 데이터를 남기는 곳만 살아남을 수 밖에 없다. 회사들이 가지고 있는 수많은 빅데이터를 구성한다. 기록이 되어있지 않은 곳들은 분석 못하고, 머신러닝이라고 해도 데이터가 없어서 못한다.

자동차 마케팅을 한다고 할 때, 사람들은 어떤 요소를 고려해서 물건을 사는지 고려해서, 데이터 셋자체에 해당하는 정보들을 싹 다 바꿔서 데이터를 받아내야한다.

대기업들이 이런 정보를 갖고 있지 않은 케이스가 많다. 어떻게든 데이터를 남길 수 있는지 방법론을 많이 생각하고 있다. 사람들의 행태가 다 남겨야 하기 때문에.

 

데이터 마트 구성하기

통계랑 시각화에 특화된 것이 R이다. R에서 파이썬 코드로 바꿔주는 것도 지피티도 잘한다. 회사에서는 어떨때는 파이썬을 하고, R은 분석에 특화되어있다. 분석 보고서 통계가 R 소프트웨어다. 코드에 대한 변환들은 지피티가 잘해준다. 지피티 의존도가 있어야 한다.