Skip to content

Instantly share code, notes, and snippets.

@NickCraver
Created June 1, 2017 21:50
Show Gist options
  • Save NickCraver/94505cc29d0ffe4119083841949a862a to your computer and use it in GitHub Desktop.
Save NickCraver/94505cc29d0ffe4119083841949a862a to your computer and use it in GitHub Desktop.
SQL Divide By Zero Glitch with aggregates
SELECT [tag.iface], [tag.host], Sum([value]) [value], [timestamp]
FROM (SELECT [tag.iface], [tag.host], [tag.direction], [dateDiff], Max([value]) [value],
DateAdd(Second, DateDiff(Second, GETUTCDATE(), [timestamp]) / 15 * 15, GETUTCDATE()) [timestamp]
FROM (SELECT [tag.iface],
[tag.host],
[tag.direction],
[timestamp],
DateDiff(Second, [LastTime], [timestamp]) [dateDiff],
--(([value] - [LastValue]) / NullIf(DateDiff(Second, [LastTime], [timestamp]), 0)) [value]
(([value] - [LastValue]) / DateDiff(Second, [LastTime], [timestamp])) [value]
FROM (SELECT [tag.iface],
[tag.host],
[tag.direction],
[timestamp],
[value],
Lag([timestamp], 1) OVER (PARTITION BY [tag.iface],[tag.host],[tag.iname],[tag.direction] ORDER BY [timestamp]) LastTime,
Lag([value], 1) OVER (PARTITION BY [tag.iface],[tag.host],[tag.iname],[tag.direction] ORDER BY [timestamp]) LastValue
FROM metrics.[os.net.bond.bytes]
WHERE [tag.host] LIKE 'ny-%'
AND [timestamp] > DateAdd(Hour, -24, GETUTCDATE())) delta
WHERE LastValue IS NOT NULL
AND LastTime IS NOT NULL
AND DateDiff(Second, [LastTime], [timestamp]) > 0) derived
WHERE [value] > -1
GROUP BY [tag.iface], [tag.host], [tag.direction], [dateDiff],
DateAdd(Second, DateDiff(Second, GETUTCDATE(), [timestamp]) / 15 * 15, GETUTCDATE())) downsampled
GROUP BY [tag.iface], [tag.host], [timestamp]
ORDER BY [tag.iface], [tag.host], [timestamp]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment