Math operations with lag()

Community
,
1

i have a questdb 8.3.0 and i want to graph a typical snmp counter containing network incoming traffic (ifHCInOctets).

if i run this query:

SELECT 
    timestamp,
    ifDescr,
    ifHCInOctets,
    (LAG(ifHCInOctets,1)) OVER (PARTITION BY ifDescr ORDER BY timestamp) as previous
FROM snmpInterface
WHERE ifDescr = 'ether8' AND timestamp > 1745792550000000;

i get the current value and the previous one:

image
image1232×852 81.7 KB

however, i want to get the substracted value (ifHCInOctets - LAG(…)) and I can’t get it :frowning:

this query works with InfluxDB 3.0:

SELECT ("ifHCInOctets" - LAG("ifHCInOctets", 1) OVER (PARTITION BY "ifDescr" ORDER BY time
  )) AS in, ("ifHCOutOctets" - LAG("ifHCOutOctets", 1) OVER (PARTITION BY "ifDescr" ORDER BY time
  )) AS out, time FROM "snmpInterface" WHERE "ifDescr" == 'ether8' AND "time" >= $__timeFrom AND "time" <= $__timeTo ORDER BY "time" ASC

is it possible to have the same query with QuestDB ? how ? thanks.

2

Does it work using a subquery? i.e.

SELECT timestamp, ifDescr, ifHCInOctets - previous AS difference FROM (
    SELECT 
        timestamp,
        ifDescr,
        ifHCInOctets,
        (LAG(ifHCInOctets,1)) OVER (PARTITION BY ifDescr ORDER BY timestamp) as previous
    FROM snmpInterface
    WHERE ifDescr = 'ether8' AND timestamp > 1745792550000000
);
3

yes, it does work. it’s slow, though :frowning:

both prometheus and influxdb have a function (rate() and non-negative-derivative(), respectively) that returns the change rate.

is there any way of doing this with questdb? i find a bit dirty/hacky to run subqueries or grafana transformations when other time series databases natively support this.

i love questdb, but with all the respects i think if questdb wants to compete with other time series databases it should provide features the others don’t have instead of lacking functionality.

4

What do you mean by slow? Subqueries are very light in QuestDB. Please can you run it with EXPLAIN and post the plan here?

Feel free to open a feature request for a rate function on GitHub :slight_smile:

5

yeah, it was slow because a network issue i had.

anyway, now it’s working fine, but when the counters restart, there’s a spike.

image
image1398×503 27.4 KB

this is the query i’m currently using:

SELECT timestamp,
  host,
  AccessRequests - previous AS "Requests" FROM (
    SELECT timestamp,
      AccessRequests,
      host,
      (LAG(AccessRequests,1)) OVER (PARTITION BY host ORDER BY timestamp) as previous
    FROM "myService"
    WHERE $__timeFilter(timestamp) SAMPLE BY $__sampleByInterval ALIGN TO CALENDAR ORDER BY timestamp ASC
);

any clue how to remove the spikes?

6

I suppose that is a true reflection of the delta when a counter goes from 100k to zero.

Best bet is probably just to clamp it:

SELECT host, greatest(AccessRequests - previous, 0)  AS Requests -- etc.