[mysql] sub query를 이용해서 통계 만들기

올해 mrlee.co.kr을 운영하면서 썼던 게시글의 호응을 알아보기로 했다.

 

 

※참고사항

g5_board_new는 게시글 데이터를 모은 곳

wr_hit은 조회수

bo_subj는 게시글의 제목

bn_datetime은 게시글을 올린 당시 날짜

bo_table은 카테고리

 

1) 먼저 필터링을 설정

- 올해 작성된 게시글

- 카테고리(bo_table항목)는 ecopol(정치경제), mystery(미스테리), story(괴담)

 

 

SELECT wr_hit, bo_table, bo_subj  FROM g5_board_new 
WHERE 
bo_table IN ('ecopol', 'mystery', 'story')
AND
bn_datetime > '2021-01-01'

 

 

결과창

 

 

2) 여기서 카테고리를 기준으로 분류

- GROUP BY는 이후 제시되는 항목을 기준으로 분류

- SUM은 분류가 이뤄질 때 특정항목이 숫자타입일 경우 해당분류 속한 데이터의 항목 값을 모두 합산

- COUNT는 분류가 이뤄질 때 해당분류에 속한 데이터의 개수를 나타냄

 

 

SELECT bo_table, SUM(wr_hit) sum_hit, COUNT(0) AS cnt  FROM g5_board_new 
WHERE 
bo_table IN ('ecopol', 'mystery', 'story')
AND
bn_datetime > '2021-01-01'
GROUP BY 
bo_table

 

결과창

 

 

3) 분류된 값을 다 더하고, 한개의 row로 나타낸다.

- sub query는 출력된 데이터를 하나의 테이블로 인식하게 해주는 장치

- CASE ~ WHEN ~ THEN ~ ELSE ~

=> CASE에서 "조건을 제시하겠다" 선언

=> WHEN에선 "조건의 세부설정" (이 WHEN은 1개 이상의 구절)

=> THEN은 "WHEN"의 세부조건값이 맞을 경우 처리하는 부분

=> ELSE는 WHEN세부조건값에 해당되지 않는 나머지 데이터에 대한 처리부분

=> 이 구문의 끝은 END를 표기해서 다른 항목과 구분되도록 한다.

 

SELECT 
SUM(sum_hit) total_hit, 
SUM(cnt) total_cnt, 

SUM(case when bo_table = 'ecopol' then sum_hit ELSE 0 END) ecopol_hit,
SUM(case when bo_table = 'ecopol' then cnt ELSE 0 END) ecopol_cnt,

SUM(case when bo_table = 'mystery' then sum_hit ELSE 0 END) mystery_hit,
SUM(case when bo_table = 'mystery' then cnt ELSE 0 END) mystery_cnt,

SUM(case when bo_table = 'story' then sum_hit ELSE 0 END) story_hit,
SUM(case when bo_table = 'story' then cnt ELSE 0 END) story_cnt

FROM
(
SELECT bo_table, SUM(wr_hit) sum_hit, COUNT(0) AS cnt  FROM g5_board_new 
WHERE 
bo_table IN ('ecopol', 'mystery', 'story')
AND
bn_datetime > '2021-01-01'
GROUP BY 
bo_table
) A

 

 

결과창

 

 

 

4) 백분율로 나타내기

- 위의 데이터를 또 하나의 sub-query로 나타낸다.

 

SELECT 
total_hit, total_cnt,
ecopol_hit/total_hit*100 ecopol_hit_per, ecopol_cnt/total_cnt*100 ecopol_cnt_per,
mystery_hit/total_hit*100 mystery_hit_per, mystery_cnt/total_cnt*100 mystery_cnt_per,
story_hit/total_hit*100 story_hit_per, story_cnt/total_cnt*100 story_cnt_per
FROM 
(
SELECT 
SUM(sum_hit) total_hit, 
SUM(cnt) total_cnt, 

SUM(case when bo_table = 'ecopol' then sum_hit ELSE 0 END) ecopol_hit,
SUM(case when bo_table = 'ecopol' then cnt ELSE 0 END) ecopol_cnt,

SUM(case when bo_table = 'mystery' then sum_hit ELSE 0 END) mystery_hit,
SUM(case when bo_table = 'mystery' then cnt ELSE 0 END) mystery_cnt,

SUM(case when bo_table = 'story' then sum_hit ELSE 0 END) story_hit,
SUM(case when bo_table = 'story' then cnt ELSE 0 END) story_cnt

FROM
(
SELECT bo_table, SUM(wr_hit) sum_hit, COUNT(0) AS cnt  FROM g5_board_new 
WHERE 
bo_table IN ('ecopol', 'mystery', 'story')
AND
bn_datetime > '2021-01-01'
GROUP BY 
bo_table
) A
) AA

 

결과창

 

 

5) 결과값 이용 결론 도출

- 경제정치 글의 개수가 제일 많다는 걸 알 수 있다. (ecopol_hit_per = 20%)

- 미스테리 글의 관심이 제일 많다는 걸 알 수 있다. (mystery_hit_per = 51%)