[MySQL] 5분 단위로 그룹핑하여 평균값 계산

MYSQL을 사용하여 5분 단위로 그룹핑하여 평균값 계산하는 방법에 대해 알아보겠습니다.

테이블 생성

먼저 분 단위로 그룹핑과 평균값 계산을 위해 DATETIME, FLOAT 타입이 포함된 테이블을 생성합니다.

CREATE TABLE test (
  id INT NOT NULL AUTO_INCREMENT,
  sensor_value FLOAT,
  log_time DATETIME,
  PRIMARY KEY(id),
) ENGINE=MYISAM CHARSET=utf8;

테이블이 생성되면 값을 입력합니다.

첫 번째 방법

시간 단위로 먼저 그룹핑한 후 분단위를 5로 나누어 그룹핑합니다.

SELECT date_format(log_time, '%Y%m%d%H%i%S'), AVG(sensor_value)
FROM test
GROUP BY SUBSTR(date_format(log_time, '%Y%m%d%H%i%S'), 1, 10),
        FLOOR(SUBSTR(date_format(log_time, '%Y%m%d%H%i%S'), 11, 2) / 5)
ORDER BY log_time;

또는 아래와 같이 SUBSTR 함수를 빼고 format을 다르게 해서 할 수 있습니다.

SELECT date_format(log_time, '%Y%m%d%H%i%S'), AVG(sensor_value)
FROM test
GROUP BY date_format(log_time, '%Y%m%d%H'),
        FLOOR(date_format(log_time, '%i') / 5);
ORDER BY log_time;

FLOOR 함수는 가장 가까운 정수로 버림하여 반환합니다.

두 번째 방법

시간 및 분 단위로 분리하는 함수를 사용합니다.

SELECT date_format(log_time, '%Y%m%d%H%i%S'), AVG(sensor_value)
FROM test
GROUP BY DATE(log_time), HOUR(log_time), FLOOR(MINUTE(log_time) / 5)
ORDER BY log_time;

DATE, HOUR, MINUTE 함수는 날짜, 시간, 분의 값을 반환합니다.

5분 단위 데이터 출력

SELECT *
FROM test
WHERE MOD(date_format(log_time, '%i'), 5) = 0

또는

SELECT *
FROM test
WHERE date_format(log_time, '%i') % 5 = 0

MOD 함수는 첫 번째 값을 두 번째 값으로 나눈 나머지를 반환합니다.

참고

date_format 함수의 두 번째 항목인 format 내용은 아래 사이트에서 자세하게 확인해 볼 수 있습니다.

• https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format