[mysql] 없는 날짜에 데이터 0 처리 하는 방법

[상황]

2주 정도 데이터 통계를 그래프로 표현해야하는 상황

 

2021-12-06 ~ 2021-12-20 날짜별 데이터를 mysql 쿼리를 통해 가져왔지만

중간 중간 데이터가 없는 문제

 

데이터 없을 시, 아래와 같이 데이터 가져옴

날짜	데이터
2021-12-06	2
2021-12-08	5
2021-12-19	20

아래와 같이 빈 날짜와 빈 데이터에 날짜 추가하고 없는 데이터에는 0을 표현하고 싶음

날짜	데이터
2021-12-06	2
2021-12-07	0
2021-12-08	5
2021-12-09	0
...	...
2021-12-18	0
2021-12-19	20

 

[문제]

2중 for문으로 처리 할려고 했으나

코드가 너무 길어지고 보기 싫어짐

날짜 배열과 데이터 배열의 길이가 맞지 않으면

처리하기 굉장히 복잡해짐

 

mysql 날짜 테이블을 생성하고 join 하는 방법으로

할려고 하니 굳이... 테이블 생성을 ? ... 귀찮음.

 

그냥 스크립트 단에서 빨리 처리하고 싶었음

 

[해결]

1. 스크립트 단에서 날짜 배열을 생성 (moment.js 이용 - new Date 이용하면 아이폰에서 에러 발생)

2. 비교할 날짜 생성, DB에서 데이터 가져오기

3. 날짜 배열을 기준으로 for 문 돌리기

4. 비교할 날짜와 날짜 배열의 데이터 비교

5. 날짜가 같으면 새 배열에 데이터 추가 및 인덱스 +1 날짜가 다르면 새 배열에 0 추가

 

 

-> moment.js 추가

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.0/moment.min.js"></script>

 

-> 비교할 날짜 배열 생성

var dateRange = [];
for (var i=14; i>=1; i--) {
	dateRange.push(moment().subtract(i, 'day').format("YYYY-MM-DD"));
}

/*
["2021-12-06", "2021-12-07", "2021-12-08", 
"2021-12-09", "2021-12-10", "2021-12-11", 
"2021-12-12", "2021-12-13", "2021-12-14", 
"2021-12-15", "2021-12-16", "2021-12-17", 
"2021-12-18", "2021-12-19", "2021-12-20"]
*/

오늘 기준으로 날짜 배열 생성

예) 오늘 2021-12-20 부터 ~ 2021-12-06 일 까지 생성

 

-> DB에서 가져온 데이터

var queryData1 = new Array();

var aJson = new Object();
aJson.log_day = "2021-12-06";
aJson.cnt = 31;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-07";
aJson.cnt = 6;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-08";
aJson.cnt = 22;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-11";
aJson.cnt = 5;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-12";
aJson.cnt = 6;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-19";
aJson.cnt = 3;
queryData1.push(aJson);

/*
[{
  cnt: 31,
  log_day: "2021-12-06"
}, {
  cnt: 6,
  log_day: "2021-12-07"
}, {
  cnt: 22,
  log_day: "2021-12-08"
}, {
  cnt: 5,
  log_day: "2021-12-11"
}, {
  cnt: 6,
  log_day: "2021-12-12"
}, {
  cnt: 3,
  log_day: "2021-12-19"
}]
*/

없는 데이터는 날짜 없음

 

-> 날짜 배열 기준으로 for 문 

// 커스텀 인덱스
var arrayIndex = 0;

// 없는 날짜에 0 데이터 추가할 새 배열
var modiData = [];

// 비교할 날짜를 기준으로 for문
for (var i=0; i<dateRange.length; i++) {
	
  // 비교할 날짜 생성
  // ex. 2021-12-06, 2021-12-07... 2021-12-20
  var tempD = moment(dateRange[0]).add(i, 'day').format('YYYY-MM-DD');
  
  // 비교할 날짜와 DB에서 가져온 날짜가 일치
  if (queryData1[arrayIndex] != null && queryData1[arrayIndex].log_day == tempD) {
	
    // 해당 날짜가 가진 데이터 새 배열에 추가
	modiData.push(queryData1[arrayIndex].cnt);
    // 커스텀 인덱스 +1
	arrayIndex++;
	
    // 날짜가 일치하지 않을 경우 0 추가
    // 커스텀 인덱스 유지
	} else modiData.push(0);
	    
}
console.log(modiData); // [31, 6, 22, 0, 0, 5, 6, 0, 0, 0, 0, 0, 0, 3, 0]

 

이를 이용하여

날짜 배열은 그래프의 라벨로, 데이터는 값으로 사용

 

-> 최종 코드

HTML

// moment
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.0/moment.min.js"></script>

// chart.js
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.bundle.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.js"></script>

// graph area
<div>
  <canvas id="lineChart" height="140"></canvas>
</div>

JAVASCRIPT

// DB에서 가져온 데이터
var queryData1 = new Array();

var aJson = new Object();
aJson.log_day = "2021-12-06";
aJson.cnt = 31;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-07";
aJson.cnt = 6;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-08";
aJson.cnt = 22;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-11";
aJson.cnt = 5;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-12";
aJson.cnt = 6;
queryData1.push(aJson);

var aJson = new Object();
aJson.log_day = "2021-12-19";
aJson.cnt = 3;
queryData1.push(aJson);

//console.log(queryData1);

// DB에서 가져온 데이터 End

// 날짜 목록
var dateRange = [];
for (var i=14; i>=0; i--) {
	dateRange.push(moment().subtract(i, 'day').format("YYYY-MM-DD"));
}

// 날짜 목록 End

// 커스텀 인덱스 & 새 배열
var arrayIndex = 0;
var modiData = [];

// 커스텀 인덱스 & 새 배열 End

// 비교 for 문
for (var i=0; i<dateRange.length; i++) {
	
  var tempD = moment(dateRange[0]).add(i, 'day').format('YYYY-MM-DD');
  
  if (queryData1[arrayIndex] != null && queryData1[arrayIndex].log_day == tempD) {
				
				modiData.push(queryData1[arrayIndex].cnt);
				arrayIndex++;
			
	} else modiData.push(0);  
}
//console.log(modiData);

// 비교 for 문 End

// chart.js 그리기
var lineData = {
        labels: dateRange,	// 날짜를 라벨로 사용
        datasets: [

            {
                label: "Data 1",
                backgroundColor: 'rgba(26,179,148,0.5)',
                borderColor: "rgba(26,179,148,0.7)",
                pointBackgroundColor: "rgba(26,179,148,1)",
                pointBorderColor: "#fff",
                data: modiData	// 0 추가된 데이터 배열
            }
        ]
    };

    var lineOptions = {
        responsive: true
    };


    var ctx = document.getElementById("lineChart").getContext("2d");
    new Chart(ctx, {type: 'line', data: lineData, options:lineOptions});

없는 부분은 0 으로 잘 표시 됨

https://jsfiddle.net/sapogLw4/6/

 

 

날짜 테이블을 생성해서 join  하는 것 보다는 이게 더 낫겠다 라는 생각

(데이터베이스 초급이라 날짜 테이블 생성하고 join 해보니 할 것도 많고 복잡함...)