[SQL] 사용자별 댓글 별점 평균 구하기

1. 댓글(REPLY) 테이블의 게시글 번호와, 별점 조회.

SELECT PRODUCT_NO, REPLY_STAR 
FROM REPLY;

 

2. 게시글 번호의 평균 조회

(AVG, SUM등의 집계함수는 집계함수를 제외한 컬럼을 GROUP BY절로 묶어 주어야 한다.)
SELECT PRODUCT_NO, AVG(REPLY_STAR) 
FROM REPLY
GROUP BY PRODUCT_NO;

 

3. 소숫점 첫째자리로 반올림

SELECT PRODUCT_NO, ROUND(AVG(REPLY_STAR),1)
FROM REPLY 
GROUP BY PRODUCT_NO;

 

4-1. 게시글 테이블의 게시글 작성자 이름 조회

SELECT MASTER_NICKNAME, PRODUCT_NO 
FROM PRODUCT;

(JOIN은 컬럼명이 같다면 USING()을, 다르다면 ON()을 사용하면 된다.

PRODUCT_NO은 컬럼명이 같지만 이후의 쿼리문을 위해 각각 별칭을 붙여주어 ON을 사용.)

 

4-2. 게시글테이블과 댓글테이블을 JOIN하여, 게시글번호, 별점, 사용자 조회

SELECT R.PRODUCT_NO, ROUND(AVG(REPLY_STAR),1), P.MASTER_NICKNAME
FROM REPLY R 
JOIN PRODUCT P ON(P.PRODUCT_NO = R.PRODUCT_NO)
GROUP BY R.PRODUCT_NO, P.MASTER_NICKNAME;

상품 별 평균이 나오게 된다.

 

5.  SELECT 절과 GROUP BY절의 PROCUT_NO를 제외시켜,

게시글 작성자 별 별점 평균 구하기. 

SELECT ROUND(AVG(REPLY_STAR),1), P.MASTER_NICKNAME
FROM REPLY R 
JOIN PRODUCT P ON(P.PRODUCT_NO = R.PRODUCT_NO)
GROUP BY P.MASTER_NICKNAME;

팬티는 3.1로, 아무나는 (3.3+4.3)/2의 3.7로 평균이 나오게 된다.

6. 게시글 작성자의 모든 게시물의 별점 평균 구하기.

 

SELECT절의 사용자를 WHERE절로 옮기면, SELECT절이 집계함수 컬럼 한 개 이므로

GROUP BY절을 사용하지 않아도 된다.

5번의 결과값을 이용하여 WHERE절에 게시글 작성자를

서브쿼리로 댓글을 작성하는 해당 게시글의 번호를 입력해준다.

 

SELECT ROUND(AVG(R.REPLY_STAR),1) STAR_AVG
FROM REPLY R
JOIN PRODUCT P ON(P.PRODUCT_NO = R.PRODUCT_NO)
WHERE P.MASTER_NICKNAME = (SELECT MASTER_NICKNAME FROM PRODUCT WHERE PRODUCT_NO = 1);

작성자 "아무나"의 전체 게시글 평균은 3.7이다.

7.  단일행 결과값을 UPDATE 시켜주기.

SET 의 값을 서브쿼리로 6번의 결과값을 넣어주고,

WHERE절에 마찬가지로 서브쿼리의 WHERE절과 같은 조건을 넣어주면 된다.

UPDATE NEEZ_MASTER SET MASTER_STAR = 

(

SELECT ROUND(AVG(R.REPLY_STAR), 1) STAR_AVG
FROM REPLY R
JOIN PRODUCT P ON(P.PRODUCT_NO = R.PRODUCT_NO)
WHERE P.MASTER_NICKNAME = (SELECT MASTER_NICKNAME FROM PRODUCT WHERE PRODUCT_NO = 1)

)
WHERE MASTER_NICKNAME = (SELECT MASTER_NICKNAME FROM PRODUCT WHERE PRODUCT_NO = 1);

 

8. 회원 테이블의 별점 조회해보기.

SELECT MASTER_NICKNAME,MASTER_STAR 
FROM NEEZ_MASTER;

잘 들어갔다.

 

a. jsp Ajax

        // 댓글 등록 AJAX
        $(function(){
            
            // 댓글 조회 ajax
            getReplyList();
            
            // 사용자 댓글 등록 ajax
            $("#addReply").on("click",function(){
                
                var pNo = "${ product.no }";    // 상품 번호
                var rContent = $("#rContent").val(); // 댓글 내용
                var rStar = $(".modal_star").find(".fas").length; // 꽉찬 별 갯수당 숫자로 치환
                var rName = "${ sessionScope.loginUser.name }"; // 댓글 작성자
                var refRno = $("#firstno").val(); // 댓글 참조 번호
                var rLevel = $("#firstlevel").val(); // 댓글 레벨
                
                 $.ajax({
                    url:"addReply.do",
                    data:{
                        pNo:pNo,
                        rContent:rContent,
                        rStar:rStar,
                        rName:rName,
                        refRno:refRno,
                        rLevel:rLevel
                    },
                    type:"post",
                    success:function(data){
                        if(data == "success"){
                            getReplyList();
                            
                            $("#rContent").val(""); // 댓글 초기화
                            $(".modal_container").css("display", "none"); // 모달 팝업 닫기
                            $(".modal_star i").addClass('far');    // 별 바꾸기
                            $(".modal_star i").removeClass('fas'); // 별 비우기
                        }
                    },error:function(request,status,errorData){
                        console.log(request.status + ":" + errorData);
                    }
                });
            });
        });

 

 

b. 댓글 등록 컨트롤러

    /**
     * 7. 댓글 등록 메소드
     * @param r
     * @return
     */
    @ResponseBody
    @RequestMapping(value="addReply.do")
    public String addReply(Reply r) {
        int result = pService.insertReply(r);
        System.out.println(r.getpNo());
        
        if(result == 1) {
            
            int starResult = pService.updateMasterStar(r.getpNo());
            
            if(starResult == 1) return "success";
            else return "fail";
            
        }else {
            return "fail";
        }
    }

 

c. 댓글 목록 컨트롤러

 

    /**
     * 8. 댓글 리스트 조회
     * @throws JsonIOException
     * @throws IOException
     */
    @RequestMapping(value="rList.do", produces="application/json; charset=UTF-8")
    public void getReplyList(HttpServletResponse response, int pNo) throws JsonIOException, IOException {
        ArrayList<Reply> rList = pService.selectReplyList(pNo);
        response.setContentType("application/json; charset=utf-8");
        
        Gson gson = new GsonBuilder().setDateFormat("yyyy-MM-dd").create();
        gson.toJson(rList,response.getWriter());
    }

 

d. 댓글 목록 jsp Ajax

 

 

// 댓글 목록 AJAX
        function getReplyList(){
            
            var pNo = "${ product.no }";
            
            $.ajax({
                url:"rList.do",
                data:{pNo:pNo},
                dataType:"json",
                success:function(data){
                    
                    console.log(data);
                     $cmtWrap = $(".cmt_wrap");
                    $cmtWrap.html("");
                     
                    
                    var $li; // 1단계
                    
                    var $divInfo; // 2단계
                    
                    var $divCmt; // 2-1단계
                    
                    var $span; // 3단계
                    var $divTutee; // 3단계
                    
                    var $pCon; // 3-1단계
                    
                    var $img;    // 4-1 단계
                    
                    var $spanName; // 4-2단계
                    var $divDate; // 4-2단계
                    
                    var $pDate; // 5단계
                    var $hr; // 구분선
                    
                    if(data.length > 0){ // 댓글이 있을 경우
                        for(var i in data){
                            
                            $li = $("<li class='list'>"); // 1단계
                            
                            $divInfo = $("<div class='tutee_info'>"); // 2단계
                            
                            $divCmt = $("<div class='cmt'>"); // 2-1단계
                            
                            $span = $("<span class='img'>"); // 3단계
                            $divTutee = $("<div class='tutee'>"); // 3단계
                            
                            $pCon = $("<p class='hide'>").text(data[i].rContent); // 3-1단계
                            
                            $img = $("<img src='resources/img/동민_PIC.jpg'>"); // 4-1 단계
                            
                            $spanName = $("<span class='name'>").text(data[i].rName); //4-2단계
                            $divDate  = $("<div class='date'>"); // 4-2단계
                            
                            $pDate = $("<p>").text(data[i].rCreateDate); // 5단계
                            
                            $hr = $("<hr>"); // 구분선
                            
                            $divDate.append($pDate); // 5단계 추가
 
                            $span.append($img);    // 4-1 단계 추가
                            
                            $divTutee.append($spanName); // 4-2단계 추가
                            $divTutee.append($divDate);    // 4-2단계 추가
                            
                            $divInfo.append($span); // 3단계 추가
                            $divInfo.append($divTutee); // 3단계 추가
                            
                            $divCmt.append($pCon); // 3-1단계 추가 추가
                            
                            $li.append($divInfo); // 2단계 추가
                            $li.append($divCmt); // 2-1단계 추가
                            
                            $cmtWrap.append($li); // ul태그에 1단계 추가
                            $cmtWrap.append($hr); // 구분선 추가!!
                        }
                    }else{ // 댓글이 없을 경우
                        alert("A");
                        /* $tr = $("<tr>");
                        $rContent = $("<td colspan='3'>").text("등록된 댓글이 없습니다.");
                        
                        $tr.append($rContent);
                        $tableBody.append($tr);
                         */
                    }
                     
                },error:function(request,status,errorData){
                    console.log(request.status + ":" + errorData);
                }
            });
        }
    
        // 별찍기
        $(".modal_star i").click(function(){
          // 빈 별일 때
          if($(this).hasClass('far')){
            // 자신과 뒤의 형제 빈 별 지우고
            $(this).removeClass('far').prevAll('i').removeClass('far');
            // 자신과 뒤의 형제 꽉찬 별 그리기.
            $(this).addClass('fas').prevAll('i').addClass('fas');
          }
          // 꽉찬 별일 때
          else{
            // 자신 앞의 형제 꽉찬 별 지우고 빈 별 그리기.
            $(this).nextAll('i').removeClass('fas').addClass('far');
          }
        })
 
        // 댓글 작성 클릭 시 별점 모달 팝업 
        $("#cmt_btn").click(function () {
          $(".modal_container").css("display", "block");
        });
        // 모달 팝업 배경 클릭 시 닫기
        $(".modal_container").click(function (e) {
          // 모달 닫고
          $(".modal_container").css("display", "none");
          // 찍은 별 초기화
          $(".modal_star i").addClass('far');
          $(".modal_star i").removeClass('fas');
        });
        //  모달 팝업 클릭 시 부모 요소 이벤트 버블링 차단
        $(".cmt_modal").click(function (e) {
          e.stopPropagation();
        });
        
    </script>