[숙제 풀이 과정] [SQL] 비개발자도 스티밋 통계를 뽑을 때까지 - 4편 - 이젠 진짜 통계를 뽑아봅시다.

[숙제 풀이 과정] [SQL] 비개발자도 스티밋 통계를 뽑을 때까지 - 4편 - 이젠 진짜 통계를 뽑아봅시다.

Hive account@nhj12311 님께서 SQL 강의를 연재중이십니다.

본 포스팅은 Hive account@nhj12311님 께서 내주신 숙제 풀이 과정에 대한 포스팅입니다.

숙제가 들어있는 포스팅은 다음 포스팅입니다.

[SQL] 비개발자도 스티밋 통계를 뽑을 때까지 - 4편 - 이젠 진짜 통계를 뽑아봅시다.

그 중 숙제의 내용은 다음 포스팅의 결과물을 SQL문으로 직접 작성해보라는 것이었습니다.

[통계] 스티밋의 가입자 수 40만 돌파 기념 - 월별 가입자 수 추이와 생각

결과물은 다음과 같이 월별 스팀잇 가입자수의 로우데이터 테이블을 뽑아 내는 것입니다.

풀이과정

먼저 숙제에서 요구하는 스팀잇 가입 유저의 데이터가 어디에 있는지부터 살펴보려 하지만 일단 막막합니다. nhj12311님께서 친절하시게도 힌트를 주셨습니다.

힌트 : ACCOUNTS.CREATED(생성일=가입일), LEFT 정말 다 드렸습니다.

힌트를 기반으로 created가 어디에 있는지 찾아봅니다.

구조를 보아하니 어디선가 봤었던 기억이 납니다.

보팅파워를 확인하느라 종종 봤었던 https://steemd.com/@siapa0516 에서 봤었던 구조와 비슷합니다. 이제 대충 account 라는 DB의 하부 카테고리에는 어떤 데이터들이 들어있을지 예상이 됩니다.

먼저 account 의 created에 예상한 대로 데이터가 들어있는지 확인부터 해보았습니다.

SQL4편 숙제 1.png

SQL4편 숙제 2.png

as를 붙여서 테이블에 년월이라고 제목도 넣어주었습니다.

SQL4편 숙제 3.png

SQL4편 숙제 4.png

저 데이터 상에서 연도와 월만 골라내면 간단히 숙제를 마칠 수 있을 것만 같습니다.
그리고 Hive account@nhj12311 님께서 힌트로 left 를 이용하라고 했으니, 잘은 이해가 되지 않지만 left를 이용하면 데이터상에서 왼쪽의 필요한 값만 추출해 내는 것만 알아내면 숙제는 끝일 것 같습니다.

그리고 SQL left 검색을 통해서 사용법을 숙지하고 왼쪽에서 7자리 데이터만 선택하게끔 명령어를 입력해봅니다.
SQL4편 숙제 5.png

오잉 근데 결과가 조금 이상합니다. 분명 위의 데이터상으로는 왼쪽에서 7자리 선택하면 YYYY-MM 라는 형식의 결과값이 출력이 되어야 할 것 같은데.. 결과값은 월 일이 출력되고 있습니다.

SQL4편 숙제 6.png

혹시나 해서 자릿수 12자리로 늘려봅니다.

출력값의 형식이 MM DD YYYY 형식으로 출력됩니다. 숙제가 쉽게 끝나나 했습니다.

SQL4편 숙제 8.png

이제는 코딩을 공부하면서 접했던 표현인 구글신님을 뵈러 갈 준비를 해야 할 것 같습니다.

구글에서 sql left date 라고 검색해보았습니다.

역시나 구글신님은 모르는게 없습니다. 비슷한 경험을 했던 사람들이 많았었 던 것인지 검색결과가 마구 쏟아집니다.

저는 그중에서 아래의 글에서 문제를 해결할 답을 찾았습니다.
https://stackoverflow.com/questions/889629/how-to-get-a-date-in-yyyy-mm-dd-format-from-a-tsql-datetime-field

SQL4편 숙제 8-1.png

SQL4편 숙제 8-2.png

디테일한 원리는 아직 모르겠으나, 위의 검색 결과를 토대로 보건데 날짜 형식은 mon dd yyyy 가 기본값으로 설정되어 있는 듯하고 데이터 형식을 날짜에서 변수(?) 형식으로 바꿔주어야 하는 것 같습니다.

일단 위의 형식에 맞춰 명령문을 작성해봅니다.
SQL4편 숙제 9.png

오 결과값이 일단 원하는 형식대로 출력됨을 확인 할 수 있습니다.

SQL4편 숙제 10.png

데이터가 원하는 형식으로 출력됨을 확인하였으니, 다음에는 정렬을 한번 해봅니다.

SQL4편 숙제 11.png

SQL4편 숙제 12.png

이제는 결과값을 그룹으로 묶어만 주면 될 것만 같습니다.
문득 맨 윗줄에서 select 구문에서 created에서 7자리를 추출했기 때문에 그룹을 묶을때 created만을 사용해도 되려나 하는 궁금증이 생깁니다.

SQL4편 숙제 14.png

이전의 결과값과 비교하면 1970-01 데이터만 하나로 묶였음을 확인 가능합니다.
그룹을 묶을때도 convert 문을 사용해야겠다는 생각이 듭니다.

SQL4편 숙제 15.png

SQL4편 숙제 16.png

이제 거의 다 한 것 같습니다. 중간에 사라졌던 테이블 제목을 달아주고요

SQL4편 숙제 17.png

SQL4편 숙제 18.png

날짜별로 그룹핑 된 셀 값이 몇개인지도 count 명령문을 사용해서 알아봅니다.

SQL4편 숙제 19.png

SQL4편 숙제 20.png

이제 마지막으로 1970년도 데이터 셀 하나만 지우면 될 것 같습니다.
Where 문을 사용해서 1970 연도 값은 읽지 말라고 추가해줍니다.

SQL4편 숙제 21.png

SQL4편 숙제 22.png

숙제 끝 입니다!