I have a python script that outputs the following below. How can I get the delta between two timestamps of the same mac. Having the first timestamp as a reference point and subtract the delta between two consecutive resetcount values for the same mac. Required output below.
Timestamp,Mac,ResetCount
1,2022-03-22 10:00:00,10:93:97:F6:5B:71,25
2,2022-03-22 10:00:00,10:93:97:F6:63:89,31
3,2022-03-22 10:17:23,10:93:97:F6:5B:71,25
4,2022-03-22 10:17:23,10:93:97:F6:63:89,35
5,2022-03-22 10:39:39,10:93:97:F6:5B:71,28
6,2022-03-22 10:39:39,10:93:97:F6:63:89,38
7,2022-03-22 10:59:52,10:93:97:F6:5B:71,28
8,2022-03-22 10:59:52,10:93:97:F6:63:89,40
Required output:
2022-03-22 10:17:23,10:93:97:F6:5B:71,0 (delta between lines 1 and 3)
2022-03-22 10:17:23,10:93:97:F6:63:89,4 (delta between lines 2 and 4)
2022-03-22 10:39:39,10:93:97:F6:5B:71,3 (delta between lines 3 and 5)
2022-03-22 10:39:39,10:93:97:F6:63:89,3 (delta between lines 4 and 6)
2022-03-22 10:59:52,10:93:97:F6:5B:71,0 (delta between lines 5 and 7)
2022-03-22 10:59:52,10:93:97:F6:63:89,2 (delta between lines 6 and 8)
PS:
This is my query in my python script.
select timestamp AS Timestamp,JSON_KEYS(qoeFlaps,"$.rgFM") AS Mac,JSON_EXTRACT(qoeFlaps,"$.rgFM.*") AS ResetCount from ptdata.qoe_cmts_details where timestamp between '2022-03-22 10:00:00' and '2022-03-22 11:00:00';
Output above
CodePudding user response：
with cte as (
select mac, timestamp, lag(timestamp) over (partition by mac order by timestamp) as last_timestamp
from mytable
)
select mac, timestamp, last_timestamp, timestampdiff(minute, last_timestamp, timestamp) as diff
from cte;
Output:
------------------- --------------------- --------------------- ------
| mac | timestamp | last_timestamp | diff |
------------------- --------------------- --------------------- ------
| 10:93:97:F6:5B:71 | 2022-03-22 10:00:00 | NULL | NULL |
| 10:93:97:F6:5B:71 | 2022-03-22 10:17:23 | 2022-03-22 10:00:00 | 17 |
| 10:93:97:F6:5B:71 | 2022-03-22 10:39:39 | 2022-03-22 10:17:23 | 22 |
| 10:93:97:F6:5B:71 | 2022-03-22 10:59:52 | 2022-03-22 10:39:39 | 20 |
| 10:93:97:F6:63:89 | 2022-03-22 10:00:00 | NULL | NULL |
| 10:93:97:F6:63:89 | 2022-03-22 10:17:23 | 2022-03-22 10:00:00 | 17 |
| 10:93:97:F6:63:89 | 2022-03-22 10:39:39 | 2022-03-22 10:17:23 | 22 |
| 10:93:97:F6:63:89 | 2022-03-22 10:59:52 | 2022-03-22 10:39:39 | 20 |
------------------- --------------------- --------------------- ------
The window functions like LAG() and the CTE syntax requires MySQL 8.0, but this has been the current version of MySQL since 2018, so you should be using it by now.
CodePudding user response：
I am not really sure. why you omit some rows, as you wanted the delta of every row.
but on MySQL 8 you can use window function in this case LAG() gets you the last value of the row.
CREATE TABLE table1 ( `Timestamp` VARCHAR(19), `Mac` VARCHAR(17), `ResetCount` INTEGER ); INSERT INTO table1 (`Timestamp`, `Mac`, `ResetCount`) VALUES ('2022-03-22 10:00:00', '10:93:97:F6:5B:71', '25'), ('2022-03-22 10:00:00', '10:93:97:F6:63:89', '31'), ('2022-03-22 10:17:23', '10:93:97:F6:5B:71', '25'), ('2022-03-22 10:17:23', '10:93:97:F6:63:89', '35'), ('2022-03-22 10:39:39', '10:93:97:F6:5B:71', '28'), ('2022-03-22 10:39:39', '10:93:97:F6:63:89', '38'), ('2022-03-22 10:59:52', '10:93:97:F6:5B:71', '28'), ('2022-03-22 10:59:52', '10:93:97:F6:63:89', '40');
WITH CTE AS( SELECT `Timestamp`, `Mac`, `ResetCount`, LAG(`ResetCount`) OVER(PARTITION BY `Mac` ORDER BY `Timestamp`) lastcount FROM table1) SELECT `Timestamp`, `Mac`, IF ( lastcount IS NULL,0,ResetCount - lastcount ) mycount FROM CTE ORDER BY `Timestamp` ASCTimestamp | Mac | mycount :------------------ | :---------------- | ------: 2022-03-22 10:00:00 | 10:93:97:F6:5B:71 | 0 2022-03-22 10:00:00 | 10:93:97:F6:63:89 | 0 2022-03-22 10:17:23 | 10:93:97:F6:5B:71 | 0 2022-03-22 10:17:23 | 10:93:97:F6:63:89 | 4 2022-03-22 10:39:39 | 10:93:97:F6:5B:71 | 3 2022-03-22 10:39:39 | 10:93:97:F6:63:89 | 3 2022-03-22 10:59:52 | 10:93:97:F6:5B:71 | 0 2022-03-22 10:59:52 | 10:93:97:F6:63:89 | 2
db<>fiddle here
For mysql 5.7 this gets much more uglier
Thie fiorst selct shows only that it works, for your code you must use the second query
The order of the result is sorted by timestamp, but as you have the same timestamp for more than one mac, if you want another order you need to add what ever determines taht.
CREATE TABLE table1 ( `Timestamp` VARCHAR(19), `Mac` VARCHAR(17), `ResetCount` INTEGER ); INSERT INTO table1 (`Timestamp`, `Mac`, `ResetCount`) VALUES ('2022-03-22 10:00:00', '10:93:97:F6:5B:71', '25'), ('2022-03-22 10:00:00', '10:93:97:F6:63:89', '31'), ('2022-03-22 10:17:23', '10:93:97:F6:5B:71', '25'), ('2022-03-22 10:17:23', '10:93:97:F6:63:89', '35'), ('2022-03-22 10:39:39', '10:93:97:F6:5B:71', '28'), ('2022-03-22 10:39:39', '10:93:97:F6:63:89', '38'), ('2022-03-22 10:59:52', '10:93:97:F6:5B:71', '28'), ('2022-03-22 10:59:52', '10:93:97:F6:63:89', '40');
SELECT `Timestamp`, mac, dif FROM (SELECT `Timestamp`, IF(@mac = `Mac`, `ResetCount` - @count, -1) as dif, @count := `ResetCount` as Resetcount, @mac := `Mac` as mac FROM (SELECT `Timestamp`, `Mac`, `ResetCount` FROm table1 ORDER BY `Mac`,`Timestamp`) t1 ,(SELECT @mac := '', @count := 0) t2) t3 WHERE dif >= 0 ORDER BY `Timestamp` ASCTimestamp | mac | dif :------------------ | :---------------- | --: 2022-03-22 10:17:23 | 10:93:97:F6:63:89 | 4 2022-03-22 10:17:23 | 10:93:97:F6:5B:71 | 0 2022-03-22 10:39:39 | 10:93:97:F6:63:89 | 3 2022-03-22 10:39:39 | 10:93:97:F6:5B:71 | 3 2022-03-22 10:59:52 | 10:93:97:F6:63:89 | 2 2022-03-22 10:59:52 | 10:93:97:F6:5B:71 | 0
SELECT `Timestamp`, mac, dif FROM (SELECT `Timestamp`, IF(@mac = `Mac`, `ResetCount` - @count, -1) as dif, @count := `ResetCount` as Resetcount, @mac := `Mac` as mac FROM (SELECT timestamp AS Timestamp, JSON_KEYS(qoeFlaps, '$.rgFM') AS Mac, JSON_EXTRACT(qoeFlaps, '$.rgFM.*') AS ResetCount FROM ptdata.qoe_cmts_details WHERE timestamp BETWEEN '2022-03-22 10:00:00' AND '2022-03-22 11:00:00' ORDER BY `Mac`,`Timestamp`) t1 ,(SELECT @mac := '', @count := 0) t2) t3 WHERE dif >= 0 ORDER BY `Timestamp` ASCSELECT command denied to user 'u_1973485473'@'localhost' for table 'qoe_cmts_details'
db<>fiddle here