MySQL의 DB에 저장된 데이터가 대략 1분 간격으로 저장된 데이터가 있다고 할때에 이 데이터에서 10분 간격의 데이터를 추출하고 싶을때 쿼리를 어떻게 작성해야 할까?
이를 위해 필요한 기법과 개념은
-. UNIX_TIMESTAMP() 함수
-. GROUP BY
-. DIV
정도이다.
테이블명 : tb_test
r_Date : 2022-08-01 15:59:00과 같은 형태로 대략 1분 간격의 데이터가 저장되어 있다고 할때
SELECT * FROM tb_test GROUP BY UNIX_TIMESTAMP(r_Date) DIV 600 ORDER BY r_Date DESC
-. UNIX_TIMESTAMP(r_Date)에서 출력되는 결과는 1659337140이다. 이는 1970.1.1이후 경과한 초의 값을 반환한다.
MySQL UNIX_TIMESTAMP() returns a Unix timestamp in seconds since '1970-01-01 00:00:00' UTC as an unsigned integer if no arguments are passed with UNIX_TIMESTAMP().
When this function used with a date argument, it returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC.
The argument may be a DATE, DATETIME,TIMESTAMP or a number in YYYYMMDD or YYMMDD.
-. UNIX_TIMESTAMP(r_Date) DIV 600
이것이 의미하는 것은 r_Date 칼럼에 있는 년-월-일 시:분:초의 값을 UTC 초 값으로 반환된 것을 600(10분에 대한 초)으로 나눈 값을 반환한다. 이때 소수점 이하는 절삭된 정수형 값을 반환한다.
-. GROUP BY UNIX_TIMESTAMP(r_Date) DIV 600
이것의 의미는 1분간격의 년-월-일 시:분:초의 값을 DIV 600으로 나눈 후 소수점을 절삭하면 10분 간격의 시간 값은 동일한 값을 갖게 된다. DIV 자체가 소수점을 절삭한 정수형을 반환한다.
사실은 2765561.x, 2765560.x, 2765559.x와 같이 소수점 이하에서 각각 다른 값들을 갖는데 소수점이 절삭된 정수형 값을 취하게 되면 아래와 같이 10분 간격의 값들은 동일한 값을 가지므로 GROUP BY로 묶을수 있는 조건이 된다.
2022-08-01 15:59:00; 2765561
2022-08-01 15:58:00; 2765561
2022-08-01 15:57:00; 2765561
2022-08-01 15:56:00; 2765561
2022-08-01 15:55:00; 2765561
2022-08-01 15:54:00; 2765561
2022-08-01 15:53:00; 2765561
2022-08-01 15:52:00; 2765561
2022-08-01 15:51:00; 2765561
2022-08-01 15:48:00; 2765560
2022-08-01 15:47:00; 2765560
2022-08-01 15:46:00; 2765560
2022-08-01 15:45:00; 2765560
2022-08-01 15:44:00; 2765560
2022-08-01 15:43:00; 2765560
2022-08-01 15:42:00; 2765560
2022-08-01 15:41:00; 2765560
2022-08-01 15:40:00; 2765560
2022-08-01 15:39:00; 2765559
2022-08-01 15:38:00; 2765559
2022-08-01 15:37:00; 2765559
2022-08-01 15:36:00; 2765559
2022-08-01 15:35:00; 2765559
2022-08-01 15:32:00; 2765559
2022-08-01 15:31:00; 2765559
2022-08-01 15:30:00; 2765559
따라서 GROUP BY로 묶으면 아래와 같이 10분 간격의 값을 추출할수가 있다. 만일 5분 간격으로 데이터를 추출하고자 한다면 DIV 300으로 하면 된다.
2022-08-01 15:51:00; 2765561
2022-08-01 15:40:00; 2765560
2022-08-01 15:30:00; 2765559
'MySQL' 카테고리의 다른 글
| 외부의 텍스트 파일로 작성된 SQL문을 이용하여 MySQL의 table 만들기 (0) | 2018.07.13 |
|---|---|
| 원격 IP로 MySQL(MariaDB)에 접속이 되지 않을 때의 해법 (3) | 2018.05.28 |
| MySQL에 root 계정 비밀번호 설정하기(변경하기) (0) | 2018.05.09 |
| MySQL의 외부 IP 접속 허용하기 (0) | 2018.05.01 |
| Data too long for column 에러 해법 : MySQL의 sql_mode 변경하기 (0) | 2017.06.19 |