CODEWITHSUNDEEP
sqlquestdb
djbobo
djbobo

Reputation: 547

How do I calculate spikes in data over the last x amount of minutes in QuestDB

I have a table CPU with some metrics coming in over Influx line protocol, how can I run a query that tells me if data has exceeded a certain threshold in the last x minutes,

I am trying to do something like:

select usage_system, timestamp
from cpu
Where usage_system > 20

But I have all records returned. For the time range, I don't want to hardcode timestamps or have to replace it with the current time to get a relative query

usage timestamp
27.399999999906 2021-04-14T12:02:30.000000Z
26.400000000139 2021-04-14T12:02:30.000000Z
25.666666666899 2021-04-14T12:02:30.000000Z
... ...

Ideally I would like to be able to check if usage is above an average, but above hardcoded value is fine for now.

Upvotes: 0

Views: 511

Answers (1)

Brian Smith
Brian Smith

Reputation: 1315

The following query will dynamically filter the last 5 minutes on the timestamp field and will return rows based on a hardcoded value (when usage_system is above 20):

SELECT *
FROM cpu
WHERE usage_system > 20
AND timestamp > dateadd('m', -5, now());

If you want to add some more details to the query, you can cross join an aggregate value:

WITH avg_usage AS (select avg(usage_system) average FROM cpu)
SELECT timestamp, cpu.usage_system usage, average, cpu.usage_system > avg_usage.average above_average
FROM cpu CROSS JOIN avg_usage
Where timestamp > dateadd('m', -5, now());

This will add a boolean column above_average and will be true or false depending on whether the row's usage_system is above the aggregate:

timestamp usage average above_average
2021-04-14T13:30:00.000000Z 20 10 true
2021-04-14T13:30:00.000000Z 5 10 false

If you want all columns, it might be useful to move this filter down into the WHERE clause:

WITH avg_usage AS (select avg(usage_system) average FROM cpu)
SELECT *
FROM cpu CROSS JOIN avg_usage
WHERE timestamp > dateadd('m', -5, now())
AND cpu.usage_system > avg_usage.average;

This will then allow to do more complex filtering, such as returning all rows which are in a certain percentile, like the following which will return rows where the usage_system is above 80% of the recorded maximum in the last 5 minutes (i.e. highest by CPU usage):

WITH max_usage AS (select max(usage_system) maximum FROM cpu)
SELECT *
FROM cpu CROSS JOIN max_usage
WHERE timestamp > dateadd('m', -5, now())
AND cpu.usage_system > (max_usage.maximum / 100 * 80);

edit: the last query was based on the example in the QuestDB WITH keyword documentation

Upvotes: 1

Related Questions