[MySQL] WEEKDAY(), CASE문 사용해 차트에서 '요일' 구현

https://cobook.tistory.com/36

 안녕하세요 코북입니다. 지난번 차트를 만들다가 차트에 날짜만 표시되는 것이 뭔가 부족하다고 느껴져서 요일도 추가해봤습니다. mysql에 있는 WEEKDAY() 메소드와 CASE문을 사용해서 구현했습니다. 컨트롤러와 Mapper Interface는 수정할 필요 없었고 JavaScript와 Mapper.xml, VO를 수정하여 구현했습니다. 작업 순서는 다음과 같습니다. 

 

1. sql문으로 쿼리 생성
2. 작성한 쿼리에 맞춰 Mapper.xml 수정
3. VO 수정
4. JavaScript 수정

1. SQL문으로 쿼리 생성

select * 
from POSTURE 
where mb_id='adkim' and pos_type='거북목' 
and pos_time between DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW() 
order by pos_time;

 기존 쿼리입니다. select *로 값을 가져오기 때문에 가져오는 시간 값들의 유형이 '2021-11-01' 과 같은 형식이었습니다. 먼저 요일로 바꿔주기 위해 WEEKDAY() 메소드를 사용했습니다. 

select mb_id, pos_type, pos_count, pos_time, weekday(pos_time)
from POSTURE 
where mb_id='adkim' and pos_type='거북목' 
and pos_time between DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW() 
order by pos_time;

 요일을 알기 위해 weekday() 메소드안에 pos_time 컬럼을 넣어줬습니다. 결괏값은 0부터 6까지 출력됐습니다. 0은 월요일이고, 6까지 월화수목금토일 순서로 출력됐습니다. 일요일은 6입니다. 

 

 이제 case문을 사용해 각각의 값에 맞게 요일을 설정해줍니다. 결괏값으로 나오는 컬럼 이름이 바뀌기 때문에 나중에 값을 사용하기가 불편해집니다. 저는 as를 사용해 pos_time2로 재설정했습니다.

select mb_id, pos_type, pos_count, pos_time, case weekday(pos_time) 
when '0' then '월'
when '1' then '화'
when '2' then '수'
when '3' then '목'
when '4' then '금'
when '5' then '토'
when '6' then '일'
end as pos_time2
from POSTURE 
where mb_id='adkim' and pos_type='거북목' 
and pos_time between DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW() 
order by pos_time;

 

 

 사용자가 보는 날짜 값에 년도가 불필요하다고 판단해서 DATE_FORMAT() 메소드로 년도를 제거했습니다.

select mb_id, pos_type, pos_count, DATE_FORMAT(pos_time,'%m-%d') as pos_time, case weekday(pos_time) 
when '0' then '월'
when '1' then '화'
when '2' then '수'
when '3' then '목'
when '4' then '금'
when '5' then '토'
when '6' then '일'
end as pos_time2 from POSTURE 
where mb_id='adkim' and pos_type='거북목' 
and pos_time between DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW() order by pos_time;

2. 작성한 쿼리에 맞춰 Mapper.xml 수정

 이제 작성한 쿼리에 맞춰 Mapper.xml 파일을 수정해줍니다. 밑에 주석은 기존에 있던 쿼리입니다.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="city.turtle.service.GraphService">

    <select id="countTurtle" resultType="city.turtle.vo.GraphVO">
        select mb_id, pos_type, pos_count, date_format(pos_time,'%m-%d') as pos_time, case weekday(pos_time) 
        when '0' then '월요일'
	    when '1' then '화'
	    when '2' then '수'
	    when '3' then '목'
	    when '4' then '금'
	    when '5' then '토'
	    when '6' then '일'
    	end as pos_time2 from POSTURE 
    	where mb_id=#{mb_id} and pos_type=#{pos_type} 
    	and pos_time between DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW() order by pos_time;
    </select>
    
<!--    <select id="countTurtle" resultType="city.turtle.vo.GraphVO">
        select * from POSTURE 
        where mb_id=#{mb_id} and pos_type=#{pos_type} 
        and pos_time between DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW() 
        order by pos_time;        
    </select> -->
    
</mapper>

3. VO 수정

 pos_time2컬럼에 대한 VO가 생성되어 있지 않기 때문에 VO를 수정해줍니다.

package city.turtle.vo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class GraphVO {
	
	private int pos_seq;
	private String pos_type;
	private String pos_time;
	private int pos_count;
	private String mb_id;
	private String pos_time2;

}

4. JavaScript 수정

 마지막으로 새로운 쿼리에서 받아오는 값을 처리하기 위해 JavaScript를 수정해줍니다. 기존 코드와 달라진 것은 for문에서 반복되는 data[i].pos_time2 값을 기존의 timeList 리스트에 더해서 넣어줬다는 점입니다.

for (let i = 0; i<data.length;i++){       
	timeList.push(data[i].pos_time+" (" +data[i].pos_time2+")");       
	posList.push(data[i].pos_count);
}

    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.5.0/Chart.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script type="text/javascript">

      // 페이지 시작할 때 차트 함수 
      $(document).ready(function(){ 
    		getGraph();
    	});
      
      // 거북목 라인 차트 그리기
      function getGraph(){
       	  let timeList = [];
    	  let posList = [];
    	  
    	  $.ajax({
    		  url:"${cpath}/countTurtle.do",
    		  type:"get",
    		  data:{mb_id:"${signIn.mb_id}", pos_type:"거북목"},
    		  dataType:"json",
    		  success:function(data){
    			  // console.log(data[0].pos_count);
    			  // 그래프로 나타낼 자료 리스트에 담기
    			  for (let i = 0; i<data.length;i++){    				  
						timeList.push(data[i].pos_time+" (" +data[i].pos_time2+")");    				  
						posList.push(data[i].pos_count);
    			  }
    			 
    			  // console.log(timeList);
    			  // console.log(posList);  	
				  // 그래프
    			  new Chart(document.getElementById("line-chart"), {
    		    	  type: 'line',
    		    	  data: {
    		    	    labels: timeList,
    		    	    datasets: [{ 
    		    	        data: posList,
    		    	        label: "거북목",
    		    	        borderColor: "#3e95cd",
    		    	        fill: false
    		    	      }
    		    	    ]
    		    	  },
    		    	  options: {
    		    	    title: {
    		    	      display: true,
    		    	      text: '주간 거북목'
    		    	    }
    		    	  }
    		    	}); //그래프
    		  },
    		  error:function(){
    			  alert("실패");
    		  }  	     		  
    	  }) // ajax	  
      } // getGraph
      </script>

구현 화면

 구현 화면입니다. 예전 그래프와 다르게 X축에 요일이 추가된 모습입니다.

사용 기술

MySQL, JavaScript, JQuery, Ajax, EL

 

배운 점

 mysql의 메소드를 사용하여 기존 날짜에서 요일 값을 가져와 사용할 수 있었습니다. WEEKDAY() 메소드와 CASE문을 사용해 새로운 요일 컬럼을 만들 수 있었고 DATE_FORMAT()을 사용해 시간 값을 수정할 수 있었습니다. as를 사용해 컬럼 이름을 간편한 값으로 재설정할 수 있었습니다. 

 

 

 

 

 

본 글은 아래 링크의 내용을 참고하여 학습한 내용을 나름대로 정리한 글임을 밝힙니다.

WEEKDAY()

http://www.webmadang.net/database/database.do?action=read&boardid=4003&page=1&seq=35

https://cirius.tistory.com/1518

CASE문

https://m.blog.naver.com/k97b1114/140195148880

DATE_FORMAT

https://han288.tistory.com/46

https://devjhs.tistory.com/89

List

https://alwayspr.tistory.com/28

Chart.js

https://mieumje.tistory.com/56