<div> <div style="font-family:gulim;line-height:21.6000003814697px;">의식의 흐름대로......</div> <div style="font-family:gulim;line-height:21.6000003814697px;"><br></div> <div style="font-family:gulim;line-height:21.6000003814697px;">1. 우선적으로 접속로그 테이블에서 5월 10일 이전 여시에서 링크를 타고 들어온</div> <div style="font-family:gulim;line-height:21.6000003814697px;">ip와 접속시각을 select 합니다.</div> <div style="font-family:gulim;line-height:21.6000003814697px;">=></div> <div style="font-family:gulim;line-height:21.6000003814697px;">select ip, regdate from visit_log where regdate < '2015-05-10'</div> <div style="font-family:gulim;line-height:21.6000003814697px;"><br></div> <div style="font-family:gulim;line-height:21.6000003814697px;">2. 1에서 select 된 결과를 로그인 로그테이블과 조인을 하는데 접속시각이후 1시간 이내에 로그인한</div> <div style="font-family:gulim;line-height:21.6000003814697px;">회원의 회원번호(mn)을 비교해서 추출합니다. 그러면 여시에서 링크를 타고온 적이 있는 오유 회원 리스트가 추출이 됩니다.</div> <div style="font-family:gulim;line-height:21.6000003814697px;">=></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><span style="line-height:21.6000003814697px;">select distinct mn from </span></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><span style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>(</span><span style="line-height:21.6000003814697px;font-size:9pt;">select ip, regdate from visit_log </span><span style="line-height:21.6000003814697px;font-size:9pt;">where regdate < '2015-05-10'</span><span style="font-size:9pt;line-height:21.6000003814697px;">) as A, </span></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><span style="line-height:21.6000003814697px;font-size:9pt;"><span class="Apple-tab-span" style="white-space:pre;"> </span>login_log as B </span></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><span style="font-size:9pt;line-height:21.6000003814697px;">where A.ip = B.ip and timestampdiff(hour, A.regdate, B.login_time) < 1</span></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><span style="line-height:21.6000003814697px;font-size:9pt;">요걸 임시 테이블 tempmember에다 넣어두고</span></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><br></div> <div style="font-family:gulim;line-height:21.6000003814697px;">3. 2에서 추출된 리스트를 중복제거 하고 추천 / 반대 로그테이블에서 이 리스트에 있는 회원이 공통적으로 </div> <div style="font-family:gulim;line-height:21.6000003814697px;">추천 / 반대를 한 글번호와 추천/반대 시각을 다시 추출합니다. </div> <div style="font-family:gulim;line-height:21.6000003814697px;">=> </div> <div style="font-family:gulim;line-height:21.6000003814697px;">추천 게시글번호, 추천인수(2인이상의 경우)</div> <div style="font-family:gulim;line-height:21.6000003814697px;"> <div style="line-height:21.6000003814697px;">select idx, cnt from (</div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>select idx, count(mn) as cnt from (</div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>select C.board_idx as idx, D.nm as mn from </div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>recommendation as C, </div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>tempmember as D </div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>where C.mn = D.mn</div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>) as E group by idx</div> <div style="line-height:21.6000003814697px;">)</div> <div style="line-height:21.6000003814697px;">as F where cnt > 2</div></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><span style="line-height:21.6000003814697px;font-size:9pt;">요걸 임시테이블 tempRecommBoardIdx 에다 넣어두고</span></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><span style="line-height:21.6000003814697px;font-size:9pt;"><br></span></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><span style="line-height:21.6000003814697px;font-size:9pt;">반대 게시글번호, 반대인수(2인이상의 경우)</span></div> <div style="font-family:gulim;line-height:21.6000003814697px;"> <div style="line-height:21.6000003814697px;">select idx, cnt from (</div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>select idx, count(mn) as cnt from (</div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>select C.board_idx as idx, D.nm as mn from </div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>opposition as C, </div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>tempmember as D </div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>where C.mn = D.mn</div> <div style="line-height:21.6000003814697px;"><span class="Apple-tab-span" style="white-space:pre;"> </span>) as E group by idx</div> <div style="line-height:21.6000003814697px;">)</div> <div style="line-height:21.6000003814697px;"><span style="line-height:21.6000003814697px;">as F where cnt > 2</span></div> <div style="line-height:21.6000003814697px;">요걸 임시테이블 tempOppoBoardIdx 에다 넣어두고</div></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><br></div> <div style="font-family:gulim;line-height:21.6000003814697px;">4. 3의 결과에서 추천과 반대가 공존하는 글번호를 제거 합니다. 여기서 여시출신 회원이 공통적으로 </div> <div style="font-family:gulim;line-height:21.6000003814697px;">추천/반대한 글이 추출됩니다.</div> <div style="font-family:gulim;line-height:21.6000003814697px;">=></div> <div style="font-family:gulim;line-height:21.6000003814697px;"> <div style="line-height:21.6000003814697px;">select distinct idx from </div> <div style="line-height:21.6000003814697px;">(SELECT A.idx FROM </div> <div style="line-height:21.6000003814697px;">tempRecommBoardIdx as A left join tempOppoBoardIdx as B on A.idx <> B.idx</div> <div style="line-height:21.6000003814697px;">union all</div> <div style="line-height:21.6000003814697px;">select B.idx from </div> <div style="line-height:21.6000003814697px;">tempRecommBoardIdx as A right join tempOppoBoardIdx as B on A.idx <> B.idx)</div> <div style="line-height:21.6000003814697px;">as C</div> <div style="line-height:21.6000003814697px;">이걸 임시테이블 doubtBoard 에다가 넣고</div></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><br></div> <div> <div style="font-family:gulim;line-height:21.6000003814697px;">5. 4의 결과에서 게시글별로 추천/반대 시각의 집중도를 조사하면 여시에서 추천/반대의 몰이표가 있는지를 </div> <div style="font-family:gulim;line-height:21.6000003814697px;">체크할수 있습니다.</div> <div style="font-family:gulim;line-height:21.6000003814697px;">=></div> <div> <div><font face="gulim"><span style="line-height:21.6000003814697px;">추천 몰이표 의심 글의 회원번호들과 추천시각</span></font></div> <div><font face="gulim"><span style="line-height:21.6000003814697px;">select A.board_idx, A.mn, A.regdate from recommendation as A,</span></font></div> <div><font face="gulim"><span style="line-height:21.6000003814697px;">tempmember as B</span></font></div> <div><font face="gulim"><span style="line-height:21.6000003814697px;">where A.board_idx = B.idx </span></font></div> <div><font face="gulim"><span style="line-height:21.6000003814697px;"><br></span></font></div> <div><font face="gulim"><span style="line-height:21.6000003814697px;">반대 몰이표 의심 글의 회원번호들과 추천시각</span></font></div> <div><font face="gulim"><span style="line-height:21.6000003814697px;">select A.board_idx, A.mn, A.regdate from opposition as A,</span></font></div> <div><font face="gulim"><span style="line-height:21.6000003814697px;">tempmember as B</span></font></div> <div><font face="gulim"><span style="line-height:21.6000003814697px;">where A.board_idx = B.idx </span></font></div></div> <div style="font-family:gulim;line-height:21.6000003814697px;"><br></div></div></div> <div style="font-family:gulim;line-height:21.6000003814697px;">아 갑자기 필받아서 머리속에 떠오른것들 정리....</div>