실시간 수집 데이터를 관리하는, 통계 테이블 관리하기

개요

이전 포스트에서 많은 수치 데이터를 담은 화면을 설계했었습니다. 5분 단위로 하루에만 288개의 데이터를 표시하기 위해, 그래프와 일일 평균, 최고, 최저 수치를 보여 가독성을 높이고 보다 구체적인 페이지를 확인할 수 있는 팝업창을 설계했습니다.

설계된 화면처럼 평균, 최고, 최저 수치를 화면에 표시하기 위해, 데이터베이스에서부터 통계테이블을 설계해 보기로 했습니다. 우선 프로젝트에서 고려하는 기온과 습도 데이터의 경우 실시간으로 1분 단위로 데이터가 전송됩니다. 전송되는 데이터는 각각의 테이블에 저장되고, 저장되는 데이터를 자동으로 처리해서 통계테이블을 만들기 위해 프로시저와 트리거를 계획했습니다.

 

통계 테이블?

특정 데이터를 기반으로 통계된 데이터를 추출하기 위해 서버에서 sql문을 통해 언제든 처리할수는 있습니다만, 미리 해당 데이터를 테이블에서 관리하고 있다면 처리 속도는 빨라질 것입니다. 테이블로 정리되어 있으면 앞으로 관리하기에도 용이할 것이라고 생각해서, 통계 테이블을 별도로 설계하게 되었고, 자동으로 통계 테이블이 만들어지기 위해서는 트리거가 반드시 필요하게 됩니다.

 

프로시저

관계형 데이터베이스 관리 시스템에서 일련의 쿼리를 하나의 함수처럼 실행시켜줍니다.

프로시저를 사용함으로써 한 번의 요청으로 여러 SQL문을 실행시켜 네트워크 부하를 줄일 수 있습니다. 통계 테이블을 만들기 위해 필요한 특정 기간을 INPUT으로 받아서, 평균, 최고, 최저 값을 계산하는 쿼리를 작성했습니다.

아래는 습도테이블에서 기간을 입력받아 평균, 최고, 최저 값을 계산하는 프로시저 예시입니다.

CREATE PROCEDURE `GET_HUMIDITY_STATICS`(
	IN `INPUT_DATE` CHAR(50),
	IN `INPUT_EQUIPNO` INT,
	OUT `AVGResult` FLOAT,
	OUT `MAXResult` FLOAT,
	OUT `MINResult` FLOAT
)
BEGIN
	SELECT AVG(VALUE), MAX(VALUE), MIN(VALUE) INTO AVGResult, MAXResult, MINResult
		FROM humidity
		WHERE DATE LIKE CONCAT('%', INPUT_DATE,'%')
			AND equipment_no = INPUT_EQUIPNO
		GROUP BY DATE(date)
		ORDER BY DATE ASC;
END

 

트리거

프로시저를 작성했다면, 이제 트리거를 작성할 차례입니다. 만들고자 했던 통계테이블은 기온과 습도 데이터가 실시간으로 DB에 들어올 때, 자동으로 통계 테이블에 계산된 데이터가 관리되게끔 설계했습니다. 

트리거는 특정 테이블에 INSERT, DELETE, UPDATE 같은 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램입니다. 트리거가 자동으로 작성된 프로시저를 작동시켜 통계 테이블의 데이터가 관리 되게 작성해 보았습니다.

 

CREATE TRIGGER `HUMIDITY_STATICS_INSERT_TRIGGER` AFTER INSERT ON `humidity` FOR EACH ROW BEGIN
	CALL GET_HUMIDITY_STATICS(date_format(NEW.date,"%Y-%m-%d"), NEW.equipment_no, @AVGResult, @MAXResult, @MINResult);
	
	INSERT INTO humidity_statics
		SET DATE = date_format(NEW.date,"%Y-%m-%d")
		  , humidity_equipment_no = NEW.equipment_no
		  , AVG = @AVGResult
		  , MAX = @MAXResult
		  , MIN = @MINResult
		
			ON DUPLICATE KEY UPDATE
		   	  AVG = @AVGResult
		  		, MAX = @MAXResult
		  		, MIN = @MINResult;
END

 

예시로 하나의 트리거를 작성했지만, UPDATE, DELETE시마다 각각 통계 테이블에 적용되야 하는 쿼리문을 고민해서 작성해야 합니다.

 

정리

실시간으로 테이블에 값이 들어올 때, 통계 테이블에 DB에서 자동으로 계산된 값을 넣고, 수정하게 설계해 보았습니다. 그 과정에서 프로시저와 트리거를 처음 사용해 보게 되었고 큰 어려움 없이 자동으로 관리되는 통계 테이블을 만들 수 있었습니다. 

대용량 데이터를 관리해야 되는 경우에 하나의 참고자료가 되었으면 합니다.