Skip to content

Instantly share code, notes, and snippets.

@glennklockwood
Last active April 18, 2021 00:46
Show Gist options
  • Save glennklockwood/2a2ea9a5d5c1cb41f0db83daf6cbfc42 to your computer and use it in GitHub Desktop.
Save glennklockwood/2a2ea9a5d5c1cb41f0db83daf6cbfc42 to your computer and use it in GitHub Desktop.
Subtracting each row from the one before it in MySQL
SELECT
b.ts,
b.ost_name,
b.bytes_written - a.bytes_written,
b.bytes_read - a.bytes_read
FROM
(
SELECT
OST_DATA.TS_ID AS tsid,
TIMESTAMP_INFO.`TIMESTAMP` AS ts,
OST_INFO.OST_NAME AS ost_name,
OST_DATA.WRITE_BYTES AS bytes_written,
OST_DATA.READ_BYTES AS bytes_read
FROM
OST_DATA
INNER JOIN TIMESTAMP_INFO ON OST_DATA.TS_ID = TIMESTAMP_INFO.TS_ID
INNER JOIN OST_INFO ON OST_DATA.OST_ID = OST_INFO.OST_ID
WHERE
TIMESTAMP_INFO.`TIMESTAMP` >= '2016-06-04 00:00:00'
AND TIMESTAMP_INFO.`TIMESTAMP` < '2016-06-04 00:00:15'
AND OST_INFO.OST_NAME = "snx11025-OST005c"
) AS a
INNER JOIN (
SELECT
OST_DATA.TS_ID AS tsid,
TIMESTAMP_INFO.`TIMESTAMP` AS ts,
OST_INFO.OST_NAME AS ost_name,
OST_DATA.WRITE_BYTES AS bytes_written,
OST_DATA.READ_BYTES AS bytes_read
FROM
OST_DATA
INNER JOIN TIMESTAMP_INFO ON OST_DATA.TS_ID = TIMESTAMP_INFO.TS_ID
INNER JOIN OST_INFO ON OST_DATA.OST_ID = OST_INFO.OST_ID
WHERE
TIMESTAMP_INFO.`TIMESTAMP` >= '2016-06-04 00:00:00'
AND TIMESTAMP_INFO.`TIMESTAMP` < '2016-06-04 00:00:15'
AND OST_INFO.OST_NAME = "snx11025-OST005c"
) AS b ON b.tsid = a.tsid + 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment