測試環境為 CentOS 8 x86_64 (虛擬機) .
我有一個溫度統計(數字) 的資料庫,要如何針對時間區間 (Date Time) 來統計資料,可以使用函數 UNIX_TIMESTAMP 來分類,搜尋.
先建立一個資料庫 (testdb) 與 tables (tempsta) 格式為 數字欄位 Temp INT(11) ,與時間欄位 TS TIMESTAMP .
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE testdb; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> USE testdb; Database changed MariaDB [testdb]> CREATE TABLE tempsta (Temp INT(11) , TS TIMESTAMP); Query OK, 0 rows affected (0.009 sec) MariaDB [testdb]> DESCRIBE tempsta; +-------+-----------+------+-----+---------------------+-------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------------------+-------------------------------+ | Temp | int(11) | YES | | NULL | | | TS | timestamp | NO | | current_timestamp() | on update current_timestamp() | +-------+-----------+------+-----+---------------------+-------------------------------+ 2 rows in set (0.002 sec)
並建立測試所需的資料.
MariaDB [testdb]> INSERT INTO tempsta (Temp , TS) VALUES ('30' ,'2020-04-16 17:40:00') , ('31' ,'2020-04-16 17:41:00') , ('32' ,'2020-04-16 17:42:00') , ('33' ,'2020-04-16 17:43:00') , ('34' ,'2020-04-16 17:44:00') , ('32' ,'2020-04-16 17:45:00') , ('34' ,'2020-04-16 17:46:00') , ('37' ,'2020-04-16 17:47:00') , ('37' ,'2020-04-16 17:47:00') , ('38' ,'2020-04-16 17:49:00') , ('38' ,'2020-04-16 17:50:00');
Query OK, 11 rows affected (0.004 sec)
Records: 11 Duplicates: 0 Warnings: 0
MariaDB [testdb]> SELECT * FROM tempsta;
+------+---------------------+
| Temp | TS |
+------+---------------------+
| 30 | 2020-04-16 17:40:00 |
| 31 | 2020-04-16 17:41:00 |
| 32 | 2020-04-16 17:42:00 |
| 33 | 2020-04-16 17:43:00 |
| 34 | 2020-04-16 17:44:00 |
| 32 | 2020-04-16 17:45:00 |
| 34 | 2020-04-16 17:46:00 |
| 37 | 2020-04-16 17:47:00 |
| 37 | 2020-04-16 17:47:00 |
| 38 | 2020-04-16 17:49:00 |
| 38 | 2020-04-16 17:50:00 |
+------+---------------------+
11 rows in set (0.001 sec)
UNIX_TIMESTAMP(TS) DIV
我們怎麼把 5分鐘為一個區間,算出溫度的平均值呢?
先透過函數 UNIX_TIMESTAMP 把時間轉換成為數字,轉換方式為: 指定的時間 -(減去) 1970-01-01 00:00:00 得到的秒數差(回傳為無符號整數),之後在使用整數 DIV (除以) 300秒(5分鐘) 來分區間.
MariaDB [testdb]> SELECT Temp , TS , UNIX_TIMESTAMP(TS) , UNIX_TIMESTAMP(TS) DIV 300 FROM tempsta; +------+---------------------+--------------------+----------------------------+ | Temp | TS | UNIX_TIMESTAMP(TS) | UNIX_TIMESTAMP(TS) DIV 300 | +------+---------------------+--------------------+----------------------------+ | 30 | 2020-04-16 17:40:00 | 1587030000 | 5290100 | | 31 | 2020-04-16 17:41:00 | 1587030060 | 5290100 | | 32 | 2020-04-16 17:42:00 | 1587030120 | 5290100 | | 33 | 2020-04-16 17:43:00 | 1587030180 | 5290100 | | 34 | 2020-04-16 17:44:00 | 1587030240 | 5290100 | | 32 | 2020-04-16 17:45:00 | 1587030300 | 5290101 | | 34 | 2020-04-16 17:46:00 | 1587030360 | 5290101 | | 37 | 2020-04-16 17:47:00 | 1587030420 | 5290101 | | 37 | 2020-04-16 17:47:00 | 1587030420 | 5290101 | | 38 | 2020-04-16 17:49:00 | 1587030540 | 5290101 | | 38 | 2020-04-16 17:50:00 | 1587030600 | 5290102 | +------+---------------------+--------------------+----------------------------+ 11 rows in set (0.001 sec)
上面可以很清楚看到區間如下:
- 5290100 : 2020-04-16 17:40:00 ~ 2020-04-16 17:44:00
- 5290101 : 2020-04-16 17:45:00 ~ 2020-04-16 17:49:00
- 5290102 : 2020-04-16 17:50:00
就可以利用這個數字來 GROUP BY 了.
MariaDB [testdb]> SELECT ROUND(AVG(Temp),2) AS "Average", GROUP_CONCAT(TS SEPARATOR ',') AS "Time Range" FROM tempsta GROUP BY UNIX_TIMESTAMP(TS) DIV 300; +---------+-----------------------------------------------------------------------------------------------------+ | Average | Time Range | +---------+-----------------------------------------------------------------------------------------------------+ | 32.00 | 2020-04-16 17:40:00,2020-04-16 17:41:00,2020-04-16 17:42:00,2020-04-16 17:43:00,2020-04-16 17:44:00 | | 35.60 | 2020-04-16 17:49:00,2020-04-16 17:47:00,2020-04-16 17:47:00,2020-04-16 17:46:00,2020-04-16 17:45:00 | | 38.00 | 2020-04-16 17:50:00 | +---------+-----------------------------------------------------------------------------------------------------+ 3 rows in set (0.001 sec)
函數說明:
- ROUND(AVG(Temp),2)
ROUND : 取小數點,四捨五入.
AVG : 計算數字平均. - GROUP_CONCAT(TS SEPARATOR ‘,’)
GROUP_CONCAT : 通常搭配 GROUP BY 使用,可以把同 Group 的欄位文字整合在一起. - UNIX_TIMESTAMP(TS) DIV 300
UNIX_TIMESTAMP : 預設回傳從 1970-01-0100:00:00 的秒數(無符號整數), DIV 就是除以,區間為 300 (300秒 = 5分鐘).
UNIX_TIMESTAMP(TS) MOD
我們怎麼把 5分鐘為一個區間,取樣其中一筆資料呢!
一樣透過函數 UNIX_TIMESTAMP 把時間轉換成為數字,轉換方式為: 指定的時間 -(減去) 1970-01-01 00:00:00 得到的秒數差(回傳為無符號整數),之後在使用 MOD (餘數) 300秒(5分鐘) .
MariaDB [testdb]> SELECT Temp , TS , UNIX_TIMESTAMP(TS) , UNIX_TIMESTAMP(TS) MOD 300 FROM tempsta; +------+---------------------+--------------------+----------------------------+ | Temp | TS | UNIX_TIMESTAMP(TS) | UNIX_TIMESTAMP(TS) MOD 300 | +------+---------------------+--------------------+----------------------------+ | 30 | 2020-04-16 17:40:00 | 1587030000 | 0 | | 31 | 2020-04-16 17:41:00 | 1587030060 | 60 | | 32 | 2020-04-16 17:42:00 | 1587030120 | 120 | | 33 | 2020-04-16 17:43:00 | 1587030180 | 180 | | 34 | 2020-04-16 17:44:00 | 1587030240 | 240 | | 32 | 2020-04-16 17:45:00 | 1587030300 | 0 | | 34 | 2020-04-16 17:46:00 | 1587030360 | 60 | | 37 | 2020-04-16 17:47:00 | 1587030420 | 120 | | 37 | 2020-04-16 17:47:00 | 1587030420 | 120 | | 38 | 2020-04-16 17:49:00 | 1587030540 | 240 | | 38 | 2020-04-16 17:50:00 | 1587030600 | 0 | +------+---------------------+--------------------+----------------------------+ 11 rows in set (0.001 sec)
可以看到每 300秒(5分鐘) 其餘數的範圍為 0~299 ,我們就可以利用這條件作搜尋(取樣).
MariaDB [testdb]> SELECT Temp , TS FROM tempsta WHERE MOD(UNIX_TIMESTAMP(TS),300)=0; +------+---------------------+ | Temp | TS | +------+---------------------+ | 30 | 2020-04-16 17:40:00 | | 32 | 2020-04-16 17:45:00 | | 38 | 2020-04-16 17:50:00 | +------+---------------------+ 3 rows in set (0.003 sec)
沒有解決問題,試試搜尋本站其他內容