[SQL] 비개발자도 스티밋 통계를 뽑을 때까지 - 5편 - 좀 더 복잡한 통계에 도전해봅시다. Join편!

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

개인적으로 미숙한 강좌를 작성함에도 따라와주시는 분들이 생겨 굉장히 기쁩니다. 아마 뭔가 팁과 정보들을 공유해주시는 분들의 마음도 매한가지겠지요. 내가 올린 글의 보상을 떠나 이런 피드백을 받으면 춤추게 된다고나 할까요.

지금까지 도전해주신 분은 2분이 계시며 추가적으로 도전해보신다는 분이 또 2분, 총 네분이 계시니 제 강좌가 헛되지 않으니 얼마나 보람찬지 모릅니다. 아직 도전하실분이 남아 숙제에 대한 피드백을 작성할 단계는 아닌것 같아 다음 포스팅으로 미룹니다.^^

댓글로 숙제 결과를 올려주신 @myhappycircle님, 따라하기 포스팅을 연재해주시는 @forhappywomen님, 주말에 숙제를 해보신다는 @dachshund님과 @siapa0516님께 무한한 감사를 드리며 5편을 작성해봅니다. (추가적으로 따라오시는 분이 계시다면 더더욱 좋겠습니다. )

[Intro] 통계를 보는 관점에 따라서 다양한 해석과 결과가 나옵니다.

일개 개발자인 제가 이런 이야기를 하니 좀 웃기긴 합니다. 통계학을 전공한것도 아니고 회사에서 데이터 몇번 추출해본 경험가지고 이런 이야기를 하니까요. 맞고 틀리다를 이야기하는 것이 아닌 그냥 이렇게 생각한다 정도로 받아들여주시면 감사하겠습니다.

통계의 왜곡
일례를 든다면 1990년도에 비행기 사고로 100명이 죽거나 다쳤습니다. 2010년에는 1,000명이 죽거나 다쳤습니다. 기사로 이렇게 쓸 수 있습니다. '20년간 비행기 사고자 1,000% 급증' 맞는걸까요? 물론 절대적인 사고자가 늘어난것은 맞습니다만 비행기 이용자 수는 몇십배가 늘었는지 그에 따른 사고자 비율로 통계를 뽑아야 맞는 시각이겠죠?

하지만 실제로 이런식의 통계 왜곡은 우리 주변에서도 심심찮케 볼수 있죠. 때로는 정부가, 기업이, 언론이 그럴듯한 자료로 우릴 속일 때가 있습니다. 좀 더 다양한 시각으로 바라볼 필요가 있습니다.

통계를 바라보는 관점과 표준편차
@forhappywomen님께서 응용편으로 시간 대별 포스팅의 보상을 올려주시면서 표준 편차라는 좋은 말씀을 해주셨네요. 항상 통계를 작성할 때 주의해야하는 것이지요. 항상 추출하고 나면 이리도 틀어도 보고 저리도 틀어보고 통계를 검증하기 위해 raw데이터를 아이체킹하기도 하며 통계 검증을 위한 자료를 뽑아보기도 합니다.

제가 한달전 쯤 Steemit kr 17년 9월 저자 별 보상통계 - 그리고 통계를 본 소감이라는 통계를 올린적이 있었죠. 이때에 명성도나 보유 스팀파워량으로 집계를 해볼까 하다가 관두고 개인별 데이터를 제공했습니다. 이유는? @forhappywomen님의 말씀대로 표준편차가 너무나 컸기 때문이죠.

@forhappywomen님의 시간대별 포스팅의 보상에 저는 명성도 60 미만의 분들로만 기준을 추가한다면 어떤 결과가 나올지 궁금해서 추려보았습니다. SQL문장에 명성도 조건만 추가했습니다. ^^

SELECT TOP 10
    DATENAME(DW,DATEADD(HOUR, 9, C.CREATED)) 요일
    , CONVERT(CHAR(2),DATEADD(HOUR, 9, C.CREATED), 108) 시간
    , AVG(C.NET_VOTES) AS 총VOTING
    , AVG(C.TOTAL_PAYOUT_VALUE) AS 평균저자보상
FROM [DBO].[COMMENTS] C (NOLOCK)
    INNER JOIN ACCOUNTS A (NOLOCK) ON C.AUTHOR = A.NAME -- 오늘 배울 조인입니다.
WHERE CHARINDEX('KO', C.BODY_LANGUAGE ) > 0 -- 한국어로 작성된 포스팅
AND C.CREATED BETWEEN '2017-01-01' AND '2017-10-19'
AND C.PARENT_AUTHOR = ''
AND C.TITLE <> ''
AND A.REPUTATION > 0
AND FLOOR( ( log10( A.REPUTATION ) - 9 ) * 9 + 25) < 60 -- 명성도 60미만
GROUP BY DATENAME(DW,DATEADD(HOUR, 9, C.CREATED)) , CONVERT(CHAR(2),DATEADD(HOUR, 9, C.CREATED), 108)
ORDER BY AVG(C.TOTAL_PAYOUT_VALUE) DESC

이렇게 조금만 보는 각도를 틀어도 다른 데이터가 나옵니다. 어떤 데이터가 맞다라고 보기엔 많은 검증이 필요합니다. 제가 추출한 데이터도 명성도 60미만의 데이터만 뽑은 것이기 때문에 어떤건 맞고 어떤건 틀리다라고 단정 할 수 없습니다. 단지 스티밋의 보상은 보팅 한번에 들쑥 날쑥하니 너무 신경쓰지 않으셨으면 좋겠습니다. ㅋㅋ

그럼 좀 더 복잡한 통계에 도전하기 위한 조인편을 시작해봅시다. 조인이란?
말 그대로 우리가 '오늘 ㅇㅇ팀과 조인해서 회식합시다^^'라고 표현 할때의 그 조인입니다. 회사에서 각기 다른 부서간 조인해서 일을 하거나 회식을 하는 것처럼 DB에서도 서로 다른 테이블에서 자료를 같이 추출하기 위해서 사용합니다. 아마 중학교 다닐 때 배웠죠?

지금은 교집합의 의미로 이해하는게 쉽습니다.

• 물론 합집합, 차집합도 있으나 자료 추출과 실무에서 사용빈도가 극히 낮아 교집합부터 이해하고 넘어갑시다.
• 두 원을 각기 다른 ACCOUNTS, COMMETNS 테이블이라고 이해한다면 같은 ACCOUNTS의 계정명 ( NAME)과 COMMETNS의 저자 ( AUTHOR)가 같은 집합의 데이터를 아래처럼 뽑아서 볼수 있죠.

엑셀을 다룰줄 아신다면 VLOOKUP과도 비슷한 의미라고 생각하시면 됩니다.

• VLOOKUP을 모르신다구요? 그래도 표를 보시면 이해가 쉽습니다~
• 아래와 같이 우리가 공부했던 COMMENTS SELECT(조회) 정보가 있는데 ACCOUNTS(계정정보)에서 명성을 가져오고 싶을 때 우린 엑셀에서 VLOOKUP을 사용합니다. 이것과 우리가 배울 JOIN의 의미는 거의 같습니다.

VLOOKUP을 추가하는것과 JOIN 문장을 추가하는것은 아주 흡사하다고 이야기 하고 싶습니다.

COMMENTS의 이름과 ACCOUNTS의 이름이 같은곳의 명성 데이터를 가져오는거죠. 제가 이름을 먼저 선택하고 accounts 테이블을 선택해서 저자명과 매칭하는것이 vlookup이고 이것과 지금 배울 조인의 개념이 아주 흡사하다는 것입니다.

조인을 배우기 전에 아주 간단한 ALIAS(별칭?) 라는걸 배워보죠!

SELECT 
TOP 10 
    C.AUTHOR AS NAME
    , C.TITLE 
FROM COMMENTS AS C (NOLOCK) 
WHERE C.AUTHOR = 'nhj12311'

이런 식으로 테이블이나 컬럼 뒤에 'AS [머머]'라고 해주면 SQL 문장 내에서 그녀석의 이름을 새로 짓는 셈이 됩니다. 전 COMMENTS를 C라고 이름을 새로 지었고 WHERE 내에서 C.AUTHOR라고 사용했지요. 물론 같은 이름이 하나밖에 없다면 그냥 AUTHOR라고 사용해도 무방합니다.

조인을 통해 테이블을 두개 이상 사용하기 위해서는 반드시 필요하게 되지요.

 문법 : INNER JOIN [테이블명] ON [조건절(WHERE)와 동일하게 사용]

위의 엑셀이나 교집합 과정을 그대로 SQL 문장으로 나타낸다면 아래와 같습니다.

-- 조인 사용의 예
SELECT 
    C.AUTHOR
    , COUNT(*) AS 총갯수
    , SUM(C.PENDING_PAYOUT_VALUE) AS 페이아웃전보상합계
    , SUM(C.TOTAL_PAYOUT_VALUE) AS 저자보상합계
    , SUM(C.CURATOR_PAYOUT_VALUE) AS 큐레이터보상합계 
    , AVG( C.TOTAL_PAYOUT_VALUE ) AS 평균저자보상
    , AVG( C.CURATOR_PAYOUT_VALUE ) AS 평균큐레이터보상
    , FLOOR( ( log10( MAX(A.REPUTATION) ) - 9 ) * 9 + 25) AS 명성
FROM COMMENTS C (NOLOCK)
    INNER JOIN ACCOUNTS A (NOLOCK) ON C.AUTHOR = A.NAME -- JOIN절 추가!!
WHERE C.AUTHOR IN ('nhj12311', 'asbear', 'segyepark') -- 계정명
AND C.PARENT_AUTHOR = ''      -- PARENT_AUTHOR가 있을땐 댓글입니다.
AND C.TITLE <> ''             -- 제목이 없는 것도 댓글입니다.'<>' : 같지 않은.
GROUP BY C.AUTHOR             -- AUTHOR로 결과를 묶어 집계한다.

이런식으로 조인을 걸수 있습니다. GROUP BY 와 함께 걸었기에 MAX나 MIN을 사용하지 않으면 사용될 수 없기에 부득이하게 MAX를 걸었습니다. 하지만 ACCOUNTS는 하나밖에 없기에 MAX를 사용하든 MIN을 사용하든 상관없습니다~

다른 방법도 있습니다. 쿼리 안에 쿼리라는 서브쿼리를 사용하는 방법도 있습니다.

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

일반적으로 서브쿼리는 대상건수가 많을수록 부하가 심해서 주의해서 사용해야합니다~ 하지만 ACCOUNTS 정도의 건수에는 그저 웃지요.

세개 이상의 테이블에서도 정보를 가져올 수 있습니다.

• 실무에서는 데이터를 뽑기 위해서 수십개의 테이블을 사용하는 일도 제법 나옵니다.

이런식으로 여러개의 테이블을 Join 문으로 이어가면 됩니다.

FROM COMMENTS C (NOLOCK)
    INNER JOIN ACCOUNTS A (NOLOCK) ON C.AUTHOR = A.NAME
    INNER JOIN (
        SELECT 0 AS NO
        UNION
        SELECT 1 AS NO
    ) TMP ON 1=1
      ---이어가고자 하면 INNER JOIN문으로 계속 연결 -----

-- 테이블 N개 사용의 예 
SELECT 
    C.AUTHOR
    , COUNT(*) AS 총갯수
    , SUM(C.PENDING_PAYOUT_VALUE) AS 페이아웃전보상합계
    , SUM(C.TOTAL_PAYOUT_VALUE) AS 저자보상합계
    , SUM(C.CURATOR_PAYOUT_VALUE) AS 큐레이터보상합계 
    , AVG( C.TOTAL_PAYOUT_VALUE ) AS 평균저자보상
    , AVG( C.CURATOR_PAYOUT_VALUE ) AS 평균큐레이터보상
    , FLOOR( ( log10( MAX(A.REPUTATION) ) - 9 ) * 9 + 25) AS 명성
    , MAX(TMP.NO) AS MAX_NO
    , MIN(TMP.NO) AS MIN_NO
FROM COMMENTS C (NOLOCK)
    INNER JOIN ACCOUNTS A (NOLOCK) ON C.AUTHOR = A.NAME
    INNER JOIN (
        SELECT 0 AS NO
        UNION
        SELECT 1 AS NO
    ) TMP ON 1=1
WHERE C.AUTHOR IN ('nhj12311', 'asbear', 'segyepark') -- 계정명
AND C.PARENT_AUTHOR = ''      -- PARENT_AUTHOR가 있을땐 댓글입니다.
AND C.TITLE <> ''             -- 제목이 없는 것도 댓글입니다.'<>' : 같지 않은.
GROUP BY C.AUTHOR             -- AUTHOR로 결과를 묶어 집계한다.

제가 TMP라는 서브쿼리의 결과를 임시 테이블로 만들어 조인을 해서 토탈 3개를 조인한 셈입니다. 아무래도 조인은 한편으로는 끝내기 힘들겠네요. 😅😅

하지만 따라와주시는 분들은 따로 검색으로도 이미 공부도 하고 계시니 전 사전 열거 방식은 최대한 지양하며 개념탑재에 힘쏟도록 하겠습니다.

이번편에서 배운것을 활용해보기 위한 숙제가 나가겠습니다. 이번 편은 보상이 목적이 되어선 안된다는 취지로 상금은 없습니다. 😀😀

---

[숙제]

17년 10월 한달간 1회 이상 포스팅한 스티밋 한국 유저들의 입출금 합계 내역을 추출해보시기 바랍니다. 이번에 숙제를 해주신 두분을 보니 제가 여러분을 너무 무시한것 같기도 하고 힌트를 안드려도 되겠다는 생각이 듭니다. 그럼 파이팅~! 👍 이 데이터를 궁금해하실 한국 스티미언분들도 많을테니 좋은 포스팅 소재가 될것입니다.

• 결과 필수 항목 : 계정명, 입금SBD, 입금 STEEM, 출금 SBD, 출금 STEEM
• 필요하다고 생각하는 항목이 있다면 자유롭게 추가해보세요~!

---

지난 회차 살펴보기

• 1편 - 데이터베이스 개념편
• 2편 - 백문이불여일견 일단 깔고 봅시다.-접속 프로그램 설치와 간단한 실행 편!
• 3편 - 본인 혹은 다른이의 포스팅 리스트 뽑아보기
• 4편 - 이젠 진짜 통계를 뽑아봅시다.

---

뭔가 눈에 너무 안들어온다구요? 백문이불여일타가 부족한 것입니다. 백타가 될때까지 치세요!