MariaDB – 算出與上一筆的差值

測試環境為 CentOS 8 x86_64

建立測試用的資料庫 (testdb)

[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.00 sec)
  
MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed

測試用的資料表 (TimeDiff) 資料格式為 T1 TIMESTAMP .

MariaDB [testdb]> CREATE TABLE TimeDiff (T1 TIMESTAMP);
Query OK, 0 rows affected (0.003 sec)

新增等一下要測試的資料.

MariaDB [testdb]> INSERT INTO TimeDiff (T1) VALUES('2021-12-21 19:50:22' ), ('2021-12-30 12:00:59') , ('2022-01-02 13:30:24') , ('2022-01-22 14:11:45') , ('2022-02-20 11:44:33') , ('2
022-03-11 18:51:40');
Query OK, 6 rows affected (0.001 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM TimeDiff;
+---------------------+
| T1                  |
+---------------------+
| 2021-12-21 19:50:22 |
| 2021-12-30 12:00:59 |
| 2022-01-02 13:30:24 |
| 2022-01-22 14:11:45 |
| 2022-02-20 11:44:33 |
| 2022-03-11 18:51:40 |
+---------------------+
6 rows in set (0.000 sec)

有辦法算出與上一筆的差值嗎? 可利用 ROW_NUMBER() 建立相對應 Index ,關於建立 Index 還可以使用 @變數 , 請參考 – https://benjr.tw/104194

MariaDB [testdb]> SELECT ROW_NUMBER() OVER(ORDER BY T1) AS Index1 , T1 FROM TimeDiff;
+--------+---------------------+
| Index1 | T1                  |
+--------+---------------------+
|      1 | 2021-12-21 19:50:22 |
|      2 | 2021-12-30 12:00:59 |
|      3 | 2022-01-02 13:30:24 |
|      4 | 2022-01-22 14:11:45 |
|      5 | 2022-02-20 11:44:33 |
|      6 | 2022-03-11 18:51:40 |
+--------+---------------------+

有了 Index 之後就可以透過 sub-query 做出來了.

SELECT D1.Index1 , D2.Index1 , D2.T1 AS Start , D1.T1 AS Stop FROM (SELECT ROW_NUMBER() OVER(ORDER BY T1) AS Index1 , T1 FROM TimeDiff) D1 
LEFT OUTER JOIN 
(SELECT ROW_NUMBER() OVER(ORDER BY T1) AS Index1 , T1 FROM TimeDiff) D2 
ON D1.Index1=D2.Index1+1 ORDER BY D1.Index1;

執行結果

MariaDB [testdb]> SELECT D1.Index1 , D2.Index1 , D2.T1 AS Start , D1.T1 AS Stop FROM (SELECT ROW_NUMBER() OVER(ORDER BY T1) AS Index1 , T1 FROM TimeDiff) D1 LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY T1) AS Index1 , T1 FROM TimeDiff) D2 ON D1.Index1=D2.Index1+1 ORDER BY D1.Index1;
+--------+--------+---------------------+---------------------+
| Index1 | Index1 | Start               | Stop                |
+--------+--------+---------------------+---------------------+
|      1 |   NULL | NULL                | 2021-12-21 19:50:22 |
|      2 |      1 | 2021-12-21 19:50:22 | 2021-12-30 12:00:59 |
|      3 |      2 | 2021-12-30 12:00:59 | 2022-01-02 13:30:24 |
|      4 |      3 | 2022-01-02 13:30:24 | 2022-01-22 14:11:45 |
|      5 |      4 | 2022-01-22 14:11:45 | 2022-02-20 11:44:33 |
|      6 |      5 | 2022-02-20 11:44:33 | 2022-03-11 18:51:40 |
+--------+--------+---------------------+---------------------+
6 rows in set (0.001 sec)

SELECT D1.Index1 , TIMEDIFF(D1.T1 , D2.T1) AS 'Time Diff' FROM (SELECT ROW_NUMBER() OVER(ORDER BY T1) AS Index1 , T1 FROM TimeDiff) D1 
LEFT OUTER JOIN 
(SELECT ROW_NUMBER() OVER(ORDER BY T1) AS Index1 , T1 FROM TimeDiff) 
D2 ON D1.Index1=D2.Index1+1 ORDER BY D1.Index1;

執行結果

MariaDB [testdb]> SELECT D1.Index1 , TIMEDIFF(D1.T1 , D2.T1) AS 'Time Diff' FROM (SELECT ROW_NUMBER() OVER(ORDER BY T1) AS Index1 , T1 FROM TimeDiff) D1 LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY T1) AS Index1 , T1 FROM TimeDiff) D2 ON D1.Index1=D2.Index1+1 ORDER BY D1.Index1;
+--------+-----------+
| Index1 | Time Diff |
+--------+-----------+
|      1 | NULL      |
|      2 | 208:10:37 |
|      3 | 73:29:25  |
|      4 | 480:41:21 |
|      5 | 693:32:48 |
|      6 | 463:07:07 |
+--------+-----------+
6 rows in set (0.001 sec)