SQL 語法 進階 SELECT – Date/Time Periods

測試環境為 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)

上面可以很清楚看到區間如下:

  1. 5290100 : 2020-04-16 17:40:00 ~ 2020-04-16 17:44:00
  2. 5290101 : 2020-04-16 17:45:00 ~ 2020-04-16 17:49:00
  3. 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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料