[SQL] 비개발자도 스티밋 통계를 뽑을 때까지 - 4편 - 이젠 진짜 통계를 뽑아봅시다.
![](https://steemitimages.com/DQmesLXchLsndu99UqdUzd7xGBrRHbpPYjDjc92k1qzfaky/image.png)
실력이 일천한 제가 아는 내에서 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을 활용해서 뽑아보시기 바랍니다~(귀찮은게 절대 아닙니다!)
![](https://steemitimages.com/DQmZ49HGP6kZS1wZ6vMuXvypn63jQdNhfDFZnYGmXrvMJaC/image.png)
흠.. 제 보상 금액이 생각보다 크게 나왔는데... 제가 그동안 보팅 봇을 사용한것도 있고 지원을 받은것도 있어 실제로는 이보다는 훨씬 적은 수치입니다. ^^
2명 이상의 데이터를 뽑고 싶다?
지난번에 제가 유저별 집계 데이터를 올린적이 있습니다. 2명 이상의 집계는 어떻게 한걸까요? 그건 집계를 그룹별로 해주는 'GROUP BY 항목명'
을 사용해서 묶고 싶은 값으로 묶으면 됩니다.
만약에 이 엑셀의 결과를 이름별로 집계를 내고 싶다면 'GROUP BY 이름'
으로 쿼리 문장 밑에 달아주면 색깔별로묶어서 집계를 할 수 있습니다.
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmS4t2EsyJ53rgPKQyzqwEpb1gamvKzczBxJ1NHA8B5edk/image.png)
이 경우 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'
을 넣고 ()
안에 ''로 감싸서 나열하면 됩니다. 그럼 결과는? 아래처럼 나오게 되지요. 😊
![](https://steemitimages.com/DQmTU7knn3XnrxW558RqXZYgDozZmzssVemDF4WYBwyfPuE/image.png)
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmfAUFoPvCJiQ5rBkpkTtHyMBngBF1jWbFqRxPajPepjo2/image.png)
난 한국인 전체의 데이터를 뽑고 싶은데?
조금 더 응용해봅시다. 한국어 포스팅을 사용한 모든 저자의 집계로 확장해봅시다. 건수가 많고 데이터가 클수록 쿼리는 시간이 오래 걸리기 마련이니 참을성을 가지고 기다려야 합니다.
-- 모든 한국어로 작성된 저자의 보상합계 내역
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 만든이)이 명세를 올려줬으면 훨씬 편하게 했겠지만...
결과 : 어때요. 결과를 보니 재미있지 않나요? 아실만한 분들이 위에 계십니다 ^^
![](https://steemitimages.com/DQmSFN1NfXc1JMqzqnVxYYFDCxLk46NeVf9R4QSsvEDfNiF/image.png)
혹시나 전체 리스트와 자기 데이터가 궁금하실 분을 위해 문서를 공유해드립니다.
어때요 눈에 보이는 아웃풋을 낼수 있다니 관심이 좀 가지 않으신가요?
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmeiMvzrcbLpL6vi1b52bN11T3xjA96iPkdQB6p6bzazta/image.png)
후아~ 너무 따분하셨죠? 😁😁😁 정말 정말 수고 많으셨습니다.
그래서 이 글의 페이아웃 * 0.375를 걸고 작은 숙제를 내겠습니다. 페이아웃 전까지 쿼리문장을 작성하셔서 인증샷을 올려주시면 이 글의 37.5%에 해당하는 SBD를 전송해드립니다. 여러분께서 하시면 1/N이 되구요. 제가 보기엔 한분이 하시면 그분이 다 가져가시는 구조가 아닐까 생각합니다. ^^ ( 하...한분이나 해주실까;;😢 )
- 추가 : 최저보상제도로 이 글의 총 보상이 $1이더라도 7SBD를 보장합니다.
물론 누가 봐도 숙련자인분들은 제외입니다. ^^ ( 보고있나. 5분이면 할 그대들? +_+ )
[숙제]
제가 얼마전에 가입자수 추이라는 통계를 올린적이 있습니다. 그것을 똑같이 만들어서 올려주시면 됩니다. ^^ 지금까지 해온 과정과 약간의 검색만 한다면 비전공자분도 30분에서 한시간 내에 하실수 있을거라 생각합니다.
오히려 본문의 문장보다 훨씬 간단합니다.
힌트 : ACCOUNTS.CREATED(생성일=가입일), LEFT 정말 다 드렸습니다.
지난 회차 살펴보기
다음편부터는 숙련되는 과정과 적절한 피드백이 필요합니다. 따라서 숙제를 마치시는 분이 생기실때까지 5편은 유보됩니다. ^^
페이아웃시점까지 한분도 인증 하시는 분이 없다면 그때는 니즈가 없다고 판단하고 제가 유용하다고 생각하는 장표들을 만들어 그냥 공개, 공유하거나 장표를 보여드리는 사이트를 개발하는걸로 컨텐츠를 변경합니다. 감사합니다. 😎😎
[5편 예고]
- 5편은 여러가지 테이블의 데이터를 같이 뽑을 수 있는 조인 개념을 설명해보려 합니다. (예를 들어 사용자 정보와 포스팅 정보를 합쳐서 뽑아야 되는 경우 )
Cheer Up!
ㅎㅎㅎ 너무 재밌게 작성해주셔서 잘 읽었습니다 ㅎㅎ
아... left를 써야되는것이라고 말씀하시는데... 그런 설명을 어디서 어떻게 검색해야 나오는지 모르겠습니다... ㅎㅎㅎ
엑셀 같은경우는 LEFT에 대해서 사용방법을 모르면 '엑셀 함수 LEFT'로 검색하면 나오는데, ssms는 뭐라고 검색을 해야하나요...
mssql left라고 하시면 되요 ㅋㅋ 이럴때가 젤 보람차죠 ㅋㅋ tip!
완성한것 같습니다, 오예!
@nhj12311 님 LEFT 를 써야 하는 군요 ㅠㅠ
예 일부러 구글 검색을 하시게 하고 싶어서 설명을 안했ㅇ습니당 mssql left로 검색을 해보세용
귀찬아서가 아니라 저도 검색으로 일을 하기에 그 습관을 알려드리려는 의도입니다 ^^
에고 죄송해요. ^^ 벌써 찾아서 지웠는데 ㅠㅠ 답변을 벌써 주셨어요 ^^
감사합니다. :)
Hi @forhappywomen! @nhj12311 is sending you 0.1 SBD tip and @tipU upvote
:)
@tipU - send tips by writing tip! in the comment, get share of the profit :)숙제 보상입니다 ^^ tip! 4.0
Hi @forhappywomen! You have just received 4.0 SBD tip from @nhj12311!
@tipU - send tips by writing tip! in the comment, get share of the profit :)으악... 복잡한 수식기호들이 저는 범접할수가 없을것만 같은데 .. ㅠ.ㅠ 그래도 한번 도전은 해보겠습니다!!! ㅎㅎㅎ 그나저나 @nhj12311님은 진정한 능력자셔.....
ㅎㅎ 여건이 되시면 한번 해보세요 ^^
이렇게 친절히 가르쳐주셔서 감사합니다!!!! 주말에 꼭 해보고 숙제 풀어오겠습니다!! ㅎ_ㅎ 짱이에요!!!!!!!!!!!!
넹 그래요 ㅎㅎㅎ 기대하고 있을게요
그리고 제가 보기엔 인증 글로 올려주시면 포스팅 꺼리도 되고 좋지 않을까요?
Good morning ~~!
잘 보았습니다
풀보팅
저도 늘 잘보고 있습니다 ^^
Good morning~!
반가 반가
이걸 따라해 보시면 다른 업무에도 엄청나게 유용하게 사용할 수 있을 거예요-!
많은 분들이 해보시고 인증 해주시길~ ㅎ
옷... 트리님 감사합니다.^^
감사합니다. 이번주말에는 무슨일이 있어도 숙제부터 해야겠군요!
숙제했다고 보상도 주신다니.. 이런 좋은 선생님이 어디 또 있을까요! ㅎㅎ
보상이 얼마 안될거 같아 걱정되긴 합니다 ^^;; 보상이 $ 1이어도 최저보상 걸었으니 7SBD니까 두분이 하시면 최소 3.5씩은 갈거에요!
초보자가 시작할 계기 및 접할 수 있는 너무나 귀하고 유용한 포스팅입니다! 보상을 받을게 아니라 드려야되는 포스팅이죠! ㅎㅎ 감사합니다!!
제가 보기엔 인증 글로 올려주시면 포스팅 꺼리도 되고 좋지 않을까요?^^
공부도 되고 포스팅 아이템도 얻고 일석이조 군요!! ㅎㅎ
벌써 4편까지 ㅠㅠ 있으시네요 ㅠㅠ 숙제까지 ㅠㅠ
프로그램 열려 있을때 해보려고 했으나 ㅠㅠ 자꾸 에러만 나와서 급좌절 ㅠㅠ 공부 좀 더 하고 다시 오겠습니다 ㅎㅎ
생각보다 쉬우니 도전해보세요 ^^
그... 그럴까요? ㅎㅎ저는 저의 회사 데이타좀 뽑아 보고 싶어서 시작했는데 ㅋㅋ 하다 보니 재미는 있습니다. 주말에 다시 도전해 볼께요 ㅋㅋ 무슨 암호 해석 하는거 같아욧! ㅎㅎㅎ
편안한 밤 되시고~ 내일은 오늘보다 더 행복하세요~
일단 지금까지 한거 보여 드려요 ㅎㅎ
![Screen Shot 2017-10-26 at 10.05.18 PM.png](https://steemitimages.com/DQmdxFdbtL5La8UxPZouZ4bZDRhXsvURbssntPFEyk24M3y/Screen%20Shot%202017-10-26%20at%2010.05.18%20PM.png)
ㅎㅎ 내용보러왔다가
통계를 한참 구경하다가 갑니다.
ㅋㅋㅋㅋㅋ 또 뭔가 조용하게 하고 계신거 같으요 기대되요!
오 진짜 신기하네요. 저도 차근차근 보면서 따라 해보고 싶어지는걸요?
생각보다 여러 님들이 따라해준다고 하십니다. 관심만으로도 감사합니다 ^^