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

nhj12311 (64)in #database • 7 years ago (edited)

실력이 일천한 제가 아는 내에서 SQL 강좌를 한편씩 올리려고 합니다. 비 IT인들도 따라할수 있는 수준으로 진행하려고 노력할 예정이며 최종 목표는 따라하시는 분이 steemsql을 통해 여러가지 통계를 뽑는 수준이 되는 것입니다.

제가 계속 강조를 하지만 데이터베이스에 저장되는 구조의 모습은 엑셀과 거의 동일하다라고 말씀드렸습니다. 이 부분은 항상 유념하셔서 생각하시면 그리 어렵지 않게 접근하시리라 생각합니다.

3편에서는 리스트의 데이터를 뽑아봤습니다~ 몇번 해보셨다면 이제 다른 리스트 자체를 추출하시는데 큰 어려움이 없을 것이라 생각합니다. 이제 진짜 통계라고 할 수 있는 집계 데이터를 뽑아볼 것입니다. 바로 평균과 합계 그리고 갯수를 세는 일입니다.

지난 기억을 리마인드하기 위함과 새로운 설명을 하기 위해서 지난 시간 쿼리를 꺼내보겠습니다~

SELECT 
    TITLE                   -- 제목
    , PENDING_PAYOUT_VALUE  -- 페이아웃전보상
    , TOTAL_PAYOUT_VALUE    -- 저자보상
    , CURATOR_PAYOUT_VALUE  -- 큐레이터보상
    , CREATED               -- 포스팅등록일시
FROM COMMENTS 
WHERE AUTHOR = 'nhj12311'   -- 계정명
AND PARENT_AUTHOR = ''      -- PARENT_AUTHOR가 있을땐 댓글입니다.
AND TITLE <> ''             -- 제목이 없는 것도 댓글입니다.'<>' : 같지 않은.
ORDER BY CREATED ASC        -- 생성일시로 ASC(순차), DESC(역순) 정렬합니다.

결과 :

이렇게 나온 상태입니다. 내 글의 총 갯수와 보상 합계를 구하고 싶다면? 집계 함수들을 사용하면 됩니다.

평균 : AVG
합계 : SUM
갯수 : COUNT
최고값 : MAX
최소값 : MIN

을 각각 항목명에 앞에 두고 항목명을 '( )'로 감싸주시면 됩니다. 단 집계를 사용할 땐 집계하지 않는 항목은 빼셔야 됩니다. 이렇게요.

SELECT 
    COUNT(*) AS 총갯수
    , SUM(PENDING_PAYOUT_VALUE) AS 페이아웃전보상합계
    , SUM(TOTAL_PAYOUT_VALUE) AS 저자보상합계
    , SUM(CURATOR_PAYOUT_VALUE) AS 큐레이터보상합계 
    , AVG( TOTAL_PAYOUT_VALUE ) AS 평균저자보상
    , AVG( CURATOR_PAYOUT_VALUE ) AS 평균큐레이터보상
FROM COMMENTS 
WHERE AUTHOR = 'nhj12311'   -- 계정명
AND PARENT_AUTHOR = ''      -- PARENT_AUTHOR가 있을땐 댓글입니다.
AND TITLE <> ''             -- 제목이 없는 것도 댓글입니다.'<>' : 같지 않은.

결과 : MIN과 MAX을 활용해서 뽑아보시기 바랍니다~(귀찮은게 절대 아닙니다!)

흠.. 제 보상 금액이 생각보다 크게 나왔는데... 제가 그동안 보팅 봇을 사용한것도 있고 지원을 받은것도 있어 실제로는 이보다는 훨씬 적은 수치입니다. ^^

2명 이상의 데이터를 뽑고 싶다?
지난번에 제가 유저별 집계 데이터를 올린적이 있습니다. 2명 이상의 집계는 어떻게 한걸까요? 그건 집계를 그룹별로 해주는 'GROUP BY 항목명'을 사용해서 묶고 싶은 값으로 묶으면 됩니다.

만약에 이 엑셀의 결과를 이름별로 집계를 내고 싶다면 'GROUP BY 이름' 으로 쿼리 문장 밑에 달아주면 색깔별로묶어서 집계를 할 수 있습니다.

이 경우 asbear님은 총 보상 40SBD, segyepark님은 85SBD가 될것입니다.

쿼리 문장으로 나타내면 아래와 같습니다.

SELECT 
    AUTHOR
    , COUNT(*) AS 총갯수
    , SUM(PENDING_PAYOUT_VALUE) AS 페이아웃전보상합계
    , SUM(TOTAL_PAYOUT_VALUE) AS 저자보상합계
    , SUM(CURATOR_PAYOUT_VALUE) AS 큐레이터보상합계 
    , AVG( TOTAL_PAYOUT_VALUE ) AS 평균저자보상
    , AVG( CURATOR_PAYOUT_VALUE ) AS 평균큐레이터보상
FROM COMMENTS 
WHERE AUTHOR IN ('nhj12311', 'asbear', 'segyepark') -- 계정명
AND PARENT_AUTHOR = ''      -- PARENT_AUTHOR가 있을땐 댓글입니다.
AND TITLE <> ''             -- 제목이 없는 것도 댓글입니다.'<>' : 같지 않은.
GROUP BY AUTHOR -- AUTHOR로 결과를 묶어 집계한다.

네. 여러개의 값을 조건으로 넣고 싶을때는 '=' 대신 'IN'을 넣고 () 안에 ''로 감싸서 나열하면 됩니다. 그럼 결과는? 아래처럼 나오게 되지요. 😊

이렇게 하니 세명의 데이터가 같이 나왔습니다.

난 한국인 전체의 데이터를 뽑고 싶은데?
조금 더 응용해봅시다. 한국어 포스팅을 사용한 모든 저자의 집계로 확장해봅시다. 건수가 많고 데이터가 클수록 쿼리는 시간이 오래 걸리기 마련이니 참을성을 가지고 기다려야 합니다.

-- 모든 한국어로 작성된 저자의 보상합계 내역
SELECT 
    AUTHOR
    , COUNT(*) AS 총갯수
    , SUM(PENDING_PAYOUT_VALUE) AS 페이아웃전보상합계
    , SUM(TOTAL_PAYOUT_VALUE) AS 저자보상합계
    , SUM(CURATOR_PAYOUT_VALUE) AS 큐레이터보상합계 
    , AVG( TOTAL_PAYOUT_VALUE ) AS 평균저자보상
    , AVG( CURATOR_PAYOUT_VALUE ) AS 평균큐레이터보상
FROM COMMENTS (NOLOCK) -- 결과집계가 빨라지는 워딩이라고 생각해주세여. ㅎㅎ
WHERE 1=1
AND CHARINDEX('KO', BODY_LANGUAGE ) > 0 -- 한국어로 작성된 포스팅
AND PARENT_AUTHOR = ''      -- PARENT_AUTHOR가 있을땐 댓글입니다.
AND TITLE <> ''             -- 제목이 없는 것도 댓글입니다.'<>' : 같지 않은.
GROUP BY AUTHOR -- NAME으로 결과를 묶어 집계한다.
ORDER BY COUNT(*) DESC -- 가장 많이 포스팅을 작성한 순으로

1. '(NOLOCK)'은 말그대로 테이블 락을 잡지 않겠다는 말로 MSSQL에서만 사용가능한 실행 힌트 같은건데... 그냥 이거하면 조회할때 더 빨라진다 라고 생각하심 될것 같습니다. ㅎㅎ

2. 'CHARINDEX'는 해당 항목 내용에 문자열의 위치를 찾는 함수입니다~ BODY_LANGUAGE라는 항목 내에 'KO'라는 문자열이 있으면 한국어 포스팅입니다.

어떻게 알았냐구요? 하나 하나 조회를 해보며 짐작한 다음 몇개의 샘플링 데이터를 뽑은후 검증단계를 거쳐 사용합니다. 일명 노가다지요. ARCANGE 양반(steemsql 만든이)이 명세를 올려줬으면 훨씬 편하게 했겠지만...

결과 : 어때요. 결과를 보니 재미있지 않나요? 아실만한 분들이 위에 계십니다 ^^

혹시나 전체 리스트와 자기 데이터가 궁금하실 분을 위해 문서를 공유해드립니다.

Steemit Kr 유저별 전체기간 합계 내역

어때요 눈에 보이는 아웃풋을 낼수 있다니 관심이 좀 가지 않으신가요?

후아~ 너무 따분하셨죠? 😁😁😁 정말 정말 수고 많으셨습니다.

그래서 이 글의 페이아웃 * 0.375를 걸고 작은 숙제를 내겠습니다. 페이아웃 전까지 쿼리문장을 작성하셔서 인증샷을 올려주시면 이 글의 37.5%에 해당하는 SBD를 전송해드립니다. 여러분께서 하시면 1/N이 되구요. 제가 보기엔 한분이 하시면 그분이 다 가져가시는 구조가 아닐까 생각합니다. ^^ ( 하...한분이나 해주실까;;😢 )

추가 : 최저보상제도로 이 글의 총 보상이 $1이더라도 7SBD를 보장합니다.

물론 누가 봐도 숙련자인분들은 제외입니다. ^^ ( 보고있나. 5분이면 할 그대들? +_+ )

[숙제]

제가 얼마전에 가입자수 추이라는 통계를 올린적이 있습니다. 그것을 똑같이 만들어서 올려주시면 됩니다. ^^ 지금까지 해온 과정과 약간의 검색만 한다면 비전공자분도 30분에서 한시간 내에 하실수 있을거라 생각합니다.

오히려 본문의 문장보다 훨씬 간단합니다.
힌트 : ACCOUNTS.CREATED(생성일=가입일), LEFT 정말 다 드렸습니다.

[통계]월별 가입자 수 추이와 생각