[SQL] 비개발자도 스티밋 통계를 뽑을 때까지 - 5편 - 좀 더 복잡한 통계에 도전해봅시다. Join편!
![](https://steemitimages.com/DQmesLXchLsndu99UqdUzd7xGBrRHbpPYjDjc92k1qzfaky/image.png)
실력이 일천한 제가 아는 내에서 SQL 강좌를 한편씩 올리려고 합니다. 비 IT인들도 따라할수 있는 수준으로 진행하려고 노력할 예정이며 최종 목표는 따라하시는 분이 steemsql을 통해 여러가지 통계를 뽑는 수준이 되는 것입니다.
개인적으로 미숙한 강좌를 작성함에도 따라와주시는 분들이 생겨 굉장히 기쁩니다. 아마 뭔가 팁과 정보들을 공유해주시는 분들의 마음도 매한가지겠지요. 내가 올린 글의 보상을 떠나 이런 피드백을 받으면 춤추게 된다고나 할까요.
지금까지 도전해주신 분은 2분이 계시며 추가적으로 도전해보신다는 분이 또 2분, 총 네분이 계시니 제 강좌가 헛되지 않으니 얼마나 보람찬지 모릅니다. 아직 도전하실분이 남아 숙제에 대한 피드백을 작성할 단계는 아닌것 같아 다음 포스팅으로 미룹니다.^^
댓글로 숙제 결과를 올려주신 @myhappycircle님, 따라하기 포스팅을 연재해주시는 @forhappywomen님, 주말에 숙제를 해보신다는 @dachshund님과 @siapa0516님께 무한한 감사를 드리며 5편을 작성해봅니다. (추가적으로 따라오시는 분이 계시다면 더더욱 좋겠습니다. )
[Intro] 통계를 보는 관점에 따라서 다양한 해석과 결과가 나옵니다.
일개 개발자인 제가 이런 이야기를 하니 좀 웃기긴 합니다. 통계학을 전공한것도 아니고 회사에서 데이터 몇번 추출해본 경험가지고 이런 이야기를 하니까요. 맞고 틀리다를 이야기하는 것이 아닌 그냥 이렇게 생각한다 정도로 받아들여주시면 감사하겠습니다.
통계의 왜곡
일례를 든다면 1990년도에 비행기 사고로 100명이 죽거나 다쳤습니다. 2010년에는 1,000명이 죽거나 다쳤습니다. 기사로 이렇게 쓸 수 있습니다. '20년간 비행기 사고자 1,000% 급증' 맞는걸까요? 물론 절대적인 사고자가 늘어난것은 맞습니다만 비행기 이용자 수는 몇십배가 늘었는지 그에 따른 사고자 비율로 통계를 뽑아야 맞는 시각이겠죠?
![](https://steemitimages.com/640x0/https://steemitimages.com/DQme9DeP2TAsx7FrTrwn6u9SAxwddgpkmSn6Da2X894Lyo1/image.png)
하지만 실제로 이런식의 통계 왜곡은 우리 주변에서도 심심찮케 볼수 있죠. 때로는 정부가, 기업이, 언론이 그럴듯한 자료로 우릴 속일 때가 있습니다. 좀 더 다양한 시각으로 바라볼 필요가 있습니다.
통계를 바라보는 관점과 표준편차
@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)가 같은 집합의 데이터를 아래처럼 뽑아서 볼수 있죠.
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmRZ6eezYNC5WUqthFhgZoZPwFyUVN6QgpDTP8L7ZXXcyT/image.png)
엑셀을 다룰줄 아신다면 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
- 필요하다고 생각하는 항목이 있다면 자유롭게 추가해보세요~!
지난 회차 살펴보기
뭔가 눈에 너무 안들어온다구요? 백문이불여일타가 부족한 것입니다. 백타가 될때까지 치세요!
Cheer Up! 음~? 흥미로운 포스팅이군요.
나중에 데이터베이스 공부할때 참고 해야겠습니다 ㅎㅎ너무 좋은글,깔끔하고 명료한 편집 감사드려요!!
비 개발자분이 추출하기에 참고가 되도록 쉽게 작성해보려 합니다. ㅋㅋ
백견이 불여일타~~
시간내서 숙제 해보겠습니다. 눈으로만 보니 역시...
역시 통계는 내기 나름이고, 증명하기 나름인데... 어떡하죠 ㅎㅎㅎ 몇몇 분들이 아침일찍 글 올리시겠다고 말씀해버리셔서... ㅎㅎ 그래도 잘못된 정보가 수정되어서 다행이라고 생각합니다 ㅎㅎ
음? 무슨일이 있었나요? 잘못된 정보라는게 뭔가영? 저도 그분들의 숙제를 기다리고 잇네요 ㅎㅎㅈ
아 제가 아침에 올리는게 좋을것 같다고 한건 ㅎㅎㅎ 잘못된 정보인것 같습니다 ㅎㅎ
아니에용 뭐가 맞다 틀리다가 없어요~ 관점의 차이를 이야기하고 싶었어요
윽... 처음엔 알아듣는가 싶었는데 몇번 수업을 뺴먹으니 다시 외계어로 보이네요 ㅠ.ㅠ 첨부터 다시 복습해야겠어요 저는 ㅎㅎㅎㅎㅎ 그치만 정말 포스팅에 많은 정성 쏟으시는게 한눈에 보여요 대단하십니다 @nhj12311님 ^^
따라와주시는 분들이 생기니 더 애정을 쏟게 되네요. ^^ ㆍ
히….히…힌트… 정말 없는 건가요?
어…어..어렵습니다. ㅜ ㅜ
벌써 숙제 시작하시나요!!
아뇨. 저 밖이에요 ㅋㅋㅋ 아직 본문 이해도 못했어요 ㅠㅠ 해피우먼님 글도 다시 집중해서 봐야 할 것 같고요 ㅠㅠ 힌트 ㅠㅠ 기다립니다 ㅎㅎ
입출금 내역은 TxTransfers 테이블을 참조하시면 됩니다. 다 드렸습니다 ㅎㅎ
이런 포스팅 올리시는 분들 정말 존경스럽습니다.
스팀잇에서는 그림 잘 그리는 분들을 금손이라고 하는데
이 정도 올리시는 분은 금뇌라고 해야 하지 않을까요
부끄럽지만 따라하기는 커녕 일단 읽기도 벅차요.
흐엌 ㅋㅋ 이건 사실 기초중에 기초적인 내용이라 여기계신 전문가 분들이 보시면 웃습니다 ㅎㅎ 대학교 전공 1학년때 배우는 수준입니다.
전 단지 전공이 아니신분들이 최대한 쉽게 익히길 바라는 마음입니다.^^
스크롤만 내려봐도 참으로 유용할 것 같은 포스팅입니다! ㅎㅎ
감사합니다! 학교다닐때는 숙제가 참 싫었었는데.. 공부하는데 숙제가 참 유용하네요.. 스스로 익히고 점검해보는데 숙제만한게 없는거 같습니다! ㅎㅎ
sum case 를 사용 해야할 것 같은데... 아무리해도 계속
Incorrect syntax near '>'.
가 계속 나오네요.이것만 조금 알려주실수 있으신지요
SUM (CASE '열' WHEN 조건 ) 으로 계속 해보는데... 왜 부등호에서 걸리는지...
넹 업무중이라 잠시만요!
이거나
과 같은 식으로 사용하려고 하신것 아니었을까요? 첫번째는 IF 조건문처럼 사용하는 것이고 두번째는 SWITCH 문처럼 사용하는 예제입니다. ^^
감사합니다... 머리가 깨질것 같아요 흑흑흑
이렇게 했는데 결과가 엉망이네요. 나중에 시간될때 더해봐야겠습니다
좌절...
해피우먼님 대단하십니다. 저는 백만년 만에 그림 그리느라 자료도 못 찾아봤어요.
봐도 모르겠어요. ㅠㅠ 흑 ㅠㅠ
갑자기 수준히 좀 많이 올라간것 같아서 도서관에서 책빌려왔습니다
파이팅해야겠습니다!!
p.s. 힌트를 달라고 댓글 쓰는 것은 아닙니다 @nhj12311 님...
그림을 그리시나봐요??^^
꺅!! 책까지 빌려 오시고 ^^ 멋지십니다. 👍
그림 그리는 사람은 아닌데 그려야 하는 일이 생겨서 ㅎㅎ 움직이지 않는 손가락 굴려 지우개 가루만 만들어 내고 있습니다. ㅎㅎ 저는 왠지 이번 숙제 나머지 공부에 ㅠㅠ 받아쓰기까지 해야 할 거 같아요. 😢
이 ID가 테이블마다 다 공통으로 들어 있어서 유저 아이디인줄 알았네요;; 주말에 시간을 담보할 수 없어서 웬만하면 주중에 숙제를 마칠려고 했는데.. 이번 숙제 제출도 지각할 것 같습니다^^;;
천천히 하셔도 됩니다. 제가 스티밋에 있는한 적절한 피드백드리도록 하겠습니다.