배틀그라운드 공식 카페의 버그/건의 게시판에서 가장 핫한 키워드는 무엇인가?

[6기_이한웅]

1. 질문

 배틀그라운드 공식 카페의 버그/건의 게시판에서 가장 핫한 키워드는 무엇인가?

2. 참고data

battleground.csv

출처 : http://cafe.naver.com/playbattlegrounds (건의사항, 버그제보 게시판)

3. 테이블생성스크립트

  create table battleground 

    ( category varchar2(4000),

   titles   varchar2(4000),

   contents varchar2(4000) );

컬럼 설명 

 category : 게시판 이름

 titles : 게시글 제목

 contents : 게시글 내용

4. 분석쿼리

① 게시글 제목을 단어별로 잘라서 보기 위해 battleword 라는 뷰를 생성  

 create or replace view battleword

   as

   select regexp_substr(titles, ('[^ ]+'), 1, 1) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 2) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 3) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 4) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 5) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 6) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 7) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 8) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 9) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 10) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 11) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 12) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 13) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 14) word

     from battleground

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 15) word

     from battleground;

추가 질문 : 핵으로 의심받고 있는 게임 아이디를 출력하라.

② 핵과 관련이 있는 타이틀 내용 전체를 단어로 잘라서 보는 battleword2 뷰 생성

  create or replace view battleword2   

   as

   select regexp_substr(titles, ('[^ ]+'), 1, 1) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 2) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 3) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 4) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 5) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 6) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 7) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 8) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 9) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

   union all

   select regexp_substr(titles, ('[^ ]+'), 1, 10) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

       union all

   select regexp_substr(titles, ('[^ ]+'), 1, 11) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

       union all

   select regexp_substr(titles, ('[^ ]+'), 1, 12) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

       union all

   select regexp_substr(titles, ('[^ ]+'), 1, 13) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

       union all

   select regexp_substr(titles, ('[^ ]+'), 1, 14) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') )

       union all

   select regexp_substr(titles, ('[^ ]+'), 1, 15) word

     from  ( select titles

                   from battleground

                   where titles like ('%핵%') );

5. 결과화면

① 가장 핫한 키워드는 무엇인지 순위 출력 

  select word, count(*)

       from battleword

       where word is not null

    group by word

    order by 2 desc;

  ⓐ 핵이라는 키워드와 비슷한 단어 출력

    select distinct word       

       from battleword

       where word like ('%핵%');

  ⓑ 핵과 관련된 게시글 제목의 개수 출력   

     select count(*)

        from

          (select word       

            from battleword

   ② 핵으로 의심받고 있는 사람들의 아이디 출력

      select distinct word as "핵사용 의심 아이디"

      from battleword2

      where regexp_like(word, '[a-zA-Z]');

6. 결론

 배틀그라운드 공식 카페의 버그/ 건의 게시판에서 가장 핫한 키워드는 핵이었다. 

 불법 핵 프로그램을 근절 시키기 위한 1차적 대응으로는 핵을 사용하는 사람들을 강력하게 제재하는 것이기 때문에 핵이라는 단어와 연관 분석을 통해 일반유저들이 느끼는 핵 의심유저들의 블랙리스트를 만들었다. 

 그리고 배틀그라운드 공개카페에 핵 이용자 신고 게시판을 따로 둘 필요성이 있어 보인다. 배틀그라운드 게임상에 신고하는 기능이 있음에도 불구하고 이들은 공개카페까지 와서 이렇게 글들을 많이 남기기 때문에 따로 신고게시판 채널을 두고 따로 관리할 필요가 있다. 

7. 연습문제

battleground 테이블의 contents 컬럼에서 title 컬럼에 포함되지 않은 핵 의심 아이디를 찾아 이들을 모두 포함시킨 블랙리스트를 새로 작성하시오. (아래의 뷰를 활용하여 출력하시오.)

    create or replace view battleword3    
   as
   select regexp_substr(contents, ('[^ ]+'), 1, 1) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
   union all
   select regexp_substr(contents, ('[^ ]+'), 1, 2) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
   union all
   select regexp_substr(contents, ('[^ ]+'), 1, 3) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
   union all
   select regexp_substr(contents, ('[^ ]+'), 1, 4) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
   union all
   select regexp_substr(contents, ('[^ ]+'), 1, 5) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
   union all
   select regexp_substr(contents, ('[^ ]+'), 1, 6) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
   union all
   select regexp_substr(contents, ('[^ ]+'), 1, 7) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
   union all
   select regexp_substr(contents, ('[^ ]+'), 1, 8) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
   union all
   select regexp_substr(contents, ('[^ ]+'), 1, 9) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
   union all
   select regexp_substr(contents, ('[^ ]+'), 1, 10) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
       union all
   select regexp_substr(contents, ('[^ ]+'), 1, 11) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
       union all
   select regexp_substr(contents, ('[^ ]+'), 1, 12) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
       union all
   select regexp_substr(contents, ('[^ ]+'), 1, 13) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
       union all
   select regexp_substr(contents, ('[^ ]+'), 1, 14) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
       union all
   select regexp_substr(contents, ('[^ ]+'), 1, 15) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 16) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 17) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 18) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 19) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 20) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 21) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 22) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 23) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 24) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 25) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 26) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 26) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 27) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 28) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 29) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') )
                          union all
   select regexp_substr(contents, ('[^ ]+'), 1, 30) word
     from  ( select contents
                   from battleground
                   where contents like ('%핵%') );

[6기_심재현]

select distinct word as "핵사용 의심 아이디"
from (select * from battleword2 union select * from battleword3)
where regexp_like(word, '[a-zA-Z]')
and word not like '%"%';

[6기_조은성]

select fuckinghacker
from
(
select distinct word as fuckinghacker
from battleword3
where regexp_like(word, '[a-zA-Z]')
)
where fuckinghacker not in (select distinct word
from battleword2
where regexp_like(word, '[a-zA-Z]')
)
union all
select distinct word
from battleword2
where regexp_like(word, '[a-zA-Z]');