Forum Discussion
Plotting Average Lag Time For Every Monitoring Device
Hi tiger98 ,
I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Inputs: I have a table ActiveMonitoringDeviceFact(on the screenshot in the original question), where MonitoringDeviceID is a foreign key. For each 15-minute incerement (Reporting Date also included in the screenshot), I may have only 1 occurence of a specific MonitroingDeviceID. For each of those increments there is lag time(NumMinutesSinceLastMeasurementReceived) (minutes it took measurements to get to the server (null or some numeric value)).
Output: Given all the attributes I mentioned above, I want to calculate average lag time for every MontoringDeviceID (over all the 15-minute increments in the Reproting Date) and assign every device to a specific group (0-15 average lag time, 15-30 average lag time, etc.) and build a bar chart based on the number of devices in each respective group.
Here's an SQL query that I think accomplishes that:
SELECT DISTINCT(MonitoringDeviceID), AVG(NumMinutesSinceLastMeasurementReceived),
CASE
WHEN AVG(NumMinutesSinceLastMeasurementReceived) BETWEEN 0 AND 15 THEN '0-15 Bin'
WHEN AVG(NumMinutesSinceLastMeasurementReceived) BETWEEN 15 AND 30 THEN '15-30 Bin'
WHEN AVG(NumMinutesSinceLastMeasurementReceived) BETWEEN 30 AND 45 THEN '30-45 Bin'
WHEN AVG(NumMinutesSinceLastMeasurementReceived) BETWEEN 45 AND 60 THEN '45-60 Bin'
WHEN AVG(NumMinutesSinceLastMeasurementReceived) BETWEEN 60 AND 1440 THEN '60-1440 Bin'
WHEN AVG(NumMinutesSinceLastMeasurementReceived) > 1440 THEN '>1440 Bin'
ELSE 'Undefined'
END AS FreqeuncyBin
FROM ActiveMonitoringDeviceFact
GROUP BY MonitoringDeviceID;
