Math operations with lag()

[vins]

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:

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

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.

[nwoolmer]

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
);

[vins]

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

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.

[nwoolmer]

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

[vins]

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.

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?

[nwoolmer]

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. 

[vins]

I have upgraded to 8.3.3 and the query stopped working. now i’m getting “pq: at least one aggregation function must be present in ‘select’ clause”. i roll back to 8.2.3 and it works fine.

this is the query i’m 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
);

what has changed? how can i fix it? i’ve tried with avg() but now i get “pq: Window function is not allowed in context of aggregation. Use sub-query”. any ideas?

[nwoolmer]

SAMPLE BY is used for time-based aggregations. Essentially, a GROUP BY but with specified time buckets.

What is the purpose of SAMPLE BY in this query? I guess it worked before because we translated it to a GROUP BY for you. In that case, a GROUP BY with only keys, no aggregates, is a DISTINCT.

But taking the delta between rows doesn’t require sampling.

[vins]

i have many RADIUS servers (tag “host”). AccessRequests is a counter, so i just want to draw the increase (non-negative, to avoid spikes due to counter reset).

with prometheus is very easy with rate(), with influxdb is very easy with non-negative-derivative() and non-negative-difference(), but with questdb is a nightmare. I’m a bit frustrated it requires subqueries and the fact it can’t be done in a single query makes it less flexible/powerful/attractive than other monitoring solutions.

[nwoolmer]

Please can you post your schema and your prometheus/influx query, I’ll help translate it for you.

Subqueries are very lightweight with QuestDB and are common ways to force optimisations for you query. That being said, we could add a rate() function to simplify things in future.

Let’s figure out the ideal solution and I’ll make an issue

[vins]

sure, this is the schema:

column	type	indexed	indexBlockCapacity	symbolCached	symbolCapacity	designated	upsertKey
host	SYMBOL	false	0	true	256	false	false
AccessRequests	DOUBLE	false	0	false	0	false	false
AccessAccepts	DOUBLE	false	0	false	0	false	false
AccessRejects	DOUBLE	false	0	false	0	false	false
AccessChallenges	DOUBLE	false	0	false	0	false	false
AuthResponses	DOUBLE	false	0	false	0	false	false
AuthDuplicateRequests	DOUBLE	false	0	false	0	false	false
AuthMalformedRequests	DOUBLE	false	0	false	0	false	false
AuthInvalidRequests	DOUBLE	false	0	false	0	false	false
AuthDroppedRequests	DOUBLE	false	0	false	0	false	false
AuthUnknownTypes	DOUBLE	false	0	false	0	false	false
timestamp	TIMESTAMP	false	0	false	0	true	false

this is the query i’m using with QuestDB:

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 "freeradius"
    WHERE $__timeFilter(timestamp) SAMPLE BY $__sampleByInterval ALIGN TO CALENDAR ORDER BY timestamp ASC
);

but as i said, it returns the “pq: at least one aggregation function must be present in ‘select’ clause” error

with influxdb, the query is this simple:

SELECT non_negative_derivative(mean("AccessRequests")) FROM "freeradius" WHERE $timeFilter GROUP BY time($__interval) fill(null)

non_negative_derivative() calculates the derivative (delta time is considered) while non_negative_difference() just calculates the difference with the previous value no matter the elapsed time.

[nwoolmer]

Hey, thank you. I agree with you, it is not convenient at the moment.

The window functions we have are strictly more powerful, but also more verbose.

I have this query for difference/derivative:

DECLARE @15m := (15 * 60 * 1E6)
WITH averaged AS (
  SELECT timestamp, avg(price) as price
  FROM trades
  WHERE symbol = 'ETH-USD'
  SAMPLE BY 15m
), lagged AS (
  SELECT
   timestamp as curr_time,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   price as curr_price,
   lag(price, 1) OVER (ORDER BY timestamp) as prev_price
   FROM averaged
), diffed AS (
  SELECT 
  curr_time,
  curr_price,
  curr_price - prev_price as price_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, curr_price, price_delta as difference, price_delta / (time_delta / @15m) as derivative
FROM diffed;

This query should give you something close to what you want. You can remove the avg and SAMPLE BY, and instead calculate the point-by-point values, no grouping required. You can add an abs and/or greatest to handle the ‘non-negative’ part.

I will explain why it requires the multiple subqueries, based on the current functionality we have.

1. The InfluxQL query allows you to nest aggregates like non_negative_derivative(mean("foo")) whereas QuestDB doesn’t. We could resolve this by adding CURSOR variants of the functions, or by automatically rewriting them into the subquery version (we do this in other cases).
2. Window functions like lag do not support inline arithmetic, this a limitation of the implementation. Therefore, the delta calculation has to be lifted into an outer query.
3. Since the function is not a ‘one-shot’, you have to do the final derivative arithemetic yourself.

Therefore you end up with four selects - the sample by, then the window, then the difference, and then the final derivative. You could condense the last two if you wanted.

I will feed this back and see what we can do to make things more convenient. It may be that the shortest route is just adding a few functions like derivative, rate. But in the long run, making these things a bit more flexible would be great.

P.S There may be better ways than the example I have given.

[vins]

splendid! that query works like a charm!

WITH averaged AS (
  SELECT timestamp, avg(AccessRequests) as AccessRequests
  FROM freeradius
  WHERE $__timeFilter(timestamp)
  SAMPLE BY 15m
), lagged AS (
  SELECT
   timestamp as curr_time,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   AccessRequests as curr_AccessRequests,
   lag(AccessRequests, 1) OVER (ORDER BY timestamp) as prev_AccessRequests
   FROM averaged
), diffed AS (
  SELECT 
  curr_time,
  curr_AccessRequests,
  curr_AccessRequests - prev_AccessRequests as AccessRequests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, AccessRequests_delta / (time_delta / @15m) as Requests
FROM diffed;

however, data is sampled to fixed intervals of 15m regardless of the “zoom” level. if i select a 30-day time window, a fixed 15m interval is overkilling, and on the other hand, if i select a 1-hour time window, 15m interval would only display 4 points; not enough resolution.
i seem to remember grafana has an inteval macro ($__interval) that automatically sets a suitable interval depending on the selected time window. i tried to replace the “15m” macro directly with $__interval, but it didn’t work. any idea how to make it work? (sorry if it’s slightly offtopic!)

[vins]

also, that query is showing a single value (average). how can i show the AccessRequests per host? i tried to add the host column:

WITH averaged AS (
  SELECT timestamp, host, avg(AccessRequests) as AccessRequests
  FROM freeradius
  WHERE $__timeFilter(timestamp)
  SAMPLE BY 5m
), lagged AS (
  SELECT
   timestamp as curr_time, host,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   AccessRequests as curr_AccessRequests,
   lag(AccessRequests, 1) OVER (ORDER BY timestamp) as prev_AccessRequests
   FROM averaged
), diffed AS (
  SELECT 
  host, curr_time,
  curr_AccessRequests,
  curr_AccessRequests - prev_AccessRequests as AccessRequests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT host, curr_time, AccessRequests_delta / (time_delta / @5m) as Requests
FROM diffed

but it produces a broken graph:

i tried to use group by host, but it’s complaining i need an aggregate function, which i don’t understand because we are already using avg()

[vins]

alright, i got how to get the automatic interval. for anyone interested:

WITH averaged AS (
  SELECT timestamp, avg(AccessRequests) as AccessRequests
  FROM freeradius
  WHERE $__timeFilter(timestamp)
  SAMPLE BY $__interval
), lagged AS (
  SELECT
   timestamp as curr_time,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   AccessRequests as curr_AccessRequests,
   lag(AccessRequests, 1) OVER (ORDER BY timestamp) as prev_AccessRequests
   FROM averaged
), diffed AS (
  SELECT 
  curr_time,
  curr_AccessRequests,
  curr_AccessRequests - prev_AccessRequests as AccessRequests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, AccessRequests_delta / (time_delta / ($__interval_ms * 1000)) as Requests
FROM diffed;

how i’m trying to figure out how to get the rate growth per host

[nwoolmer]

You will need to ‘key’ the query. If you provide the host column, the query can be keyed and run for each symbol. Likewise for the window functions.

WITH averaged AS (
  SELECT timestamp, host, avg(AccessRequests) as AccessRequests
  FROM freeradius
  WHERE timestamp IN $__timeFilter(timestamp)
  SAMPLE BY $__interval
), lagged AS (
  SELECT
   timestamp as curr_time,
   host,
   lag(timestamp, 1) OVER (PARTITION BY host ORDER BY timestamp) AS prev_time,
   AccessRequests as curr_AccessRequests,
   lag(AccessRequests, 1) OVER (PARTITION BY host ORDER BY timestamp) as prev_AccessRequests
   FROM averaged
), diffed AS (
  SELECT 
  host,
  curr_time,
  curr_AccessRequests,
  curr_AccessRequests - prev_AccessRequests as AccessRequests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, host, AccessRequests_delta, AccessRequests_delta / (time_delta / ($__interval_ms * 1000)) as Requests
FROM diffed
ORDER BY curr_time;

We will shortly be releasing PIVOT, this will allow you to have a column per symbol i.e

timestamp, host1, host2, host3 ....

That will make it easy to plot on a Grafana time-series chart.

[vins]

there’s something odd.

when i run this query (either in grafana explorer or as a dashboard panel):

  SELECT timestamp, avg(AccessRequests) as requests
  FROM freeradius
  WHERE $__timeFilter(timestamp)
  SAMPLE BY $__interval
), lagged AS (
  SELECT
   timestamp as curr_time,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   requests as curr_requests,
   lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests
   FROM averaged
), diffed AS (
  SELECT 
  curr_time,
  curr_requests,
  curr_requests - prev_requests as requests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, requests_delta / (time_delta / ($__interval_ms * 1000)) as Requests
FROM diffed

sometimes i get a graph like this (which is the expected graph) :

but sometimes i get something like this:

it’s quite random. i just refresh the graph and sometimes i get the correct one, sometimes the wrong one. it looks like the last value gives a wrong delta value either for timestamp or for accessrequests value.

[nwoolmer]

I suppose you are grouping on an average over a time range. If that final bucket does not have complete data, the average may be lower and skew the dataset?

Can you share the raw table data for the wonky graph?

[vins]

Sure. This JSON contains the request and also the returning dataset for a “correct” query:

{
  "request": {
    "url": "api/ds/query?ds_type=questdb-questdb-datasource&requestId=explore_xx0",
    "method": "POST",
    "data": {
      "queries": [
        {
          "refId": "A",
          "datasource": {
            "type": "questdb-questdb-datasource",
            "uid": "denc7cxgco2rkc"
          },
          "queryType": "sql",
          "rawSql": "WITH averaged AS (\r\n  SELECT timestamp, avg(AccessRequests) as requests\r\n  FROM freeradius\r\n  WHERE $__timeFilter(timestamp)\r\n  SAMPLE BY 200ms\r\n), lagged AS (\r\n  SELECT\r\n   timestamp as curr_time,\r\n   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n   requests as curr_requests,\r\n   lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests\r\n   FROM averaged\r\n), diffed AS (\r\n  SELECT \r\n  curr_time,\r\n  curr_requests,\r\n  curr_requests - prev_requests as requests_delta,\r\n  curr_time - prev_time as time_delta\r\n  FROM lagged\r\n)\r\nSELECT curr_time, requests_delta / (time_delta / (200 * 1000)) as Requests\r\nFROM diffed",
          "meta": {
            "builderOptions": {
              "mode": "list",
              "fields": [],
              "limit": "",
              "timeField": ""
            },
            "timezone": "Europe/Paris"
          },
          "format": 1,
          "selectedFormat": 1,
          "datasourceId": 3,
          "intervalMs": 200,
          "maxDataPoints": 1070
        }
      ],
      "from": "1750086946250",
      "to": "1750087246250"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "status": 200,
        "frames": [
          {
            "schema": {
              "name": "A",
              "refId": "A",
              "meta": {
                "typeVersion": [
                  0,
                  0
                ],
                "preferredVisualisationType": "table",
                "executedQueryString": "WITH averaged AS (\r\n  SELECT timestamp, avg(AccessRequests) as requests\r\n  FROM freeradius\r\n  WHERE timestamp >= cast(1750086946250000 as timestamp) AND timestamp <= cast(1750087246250000 as timestamp)\r\n  SAMPLE BY 200ms\r\n), lagged AS (\r\n  SELECT\r\n   timestamp as curr_time,\r\n   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n   requests as curr_requests,\r\n   lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests\r\n   FROM averaged\r\n), diffed AS (\r\n  SELECT \r\n  curr_time,\r\n  curr_requests,\r\n  curr_requests - prev_requests as requests_delta,\r\n  curr_time - prev_time as time_delta\r\n  FROM lagged\r\n)\r\nSELECT curr_time, requests_delta / (time_delta / (200 * 1000)) as Requests\r\nFROM diffed"
              },
              "fields": [
                {
                  "name": "curr_time",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time",
                    "nullable": true
                  }
                },
                {
                  "name": "Requests",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float64",
                    "nullable": true
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  1750086950000,
                  1750086960000,
                  1750086970000,
                  1750086980000,
                  1750086990000,
                  1750087000000,
                  1750087010000,
                  1750087020000,
                  1750087030000,
                  1750087040000,
                  1750087050000,
                  1750087060000,
                  1750087070000,
                  1750087080000,
                  1750087090000,
                  1750087100000,
                  1750087110000,
                  1750087120000,
                  1750087130000,
                  1750087140000,
                  1750087150000,
                  1750087160000,
                  1750087170000,
                  1750087180000,
                  1750087190000,
                  1750087200000,
                  1750087210000,
                  1750087220000,
                  1750087230000,
                  1750087240000
                ],
                [
                  null,
                  0.02,
                  0,
                  0,
                  0.05,
                  0,
                  0,
                  0.01,
                  0.05,
                  0.03,
                  0.02,
                  0.05,
                  0.01,
                  0.04,
                  0.01,
                  0.07,
                  0.04,
                  0.05,
                  0.02,
                  0.04,
                  0.01,
                  0,
                  0.01,
                  0.06,
                  0.02,
                  0.03,
                  0.01,
                  0.04,
                  0.03,
                  0.1
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

and this one for the wrong query (note the -2.74) at the end:

{
  "request": {
    "url": "api/ds/query?ds_type=questdb-questdb-datasource&requestId=explore_xx0",
    "method": "POST",
    "data": {
      "queries": [
        {
          "refId": "A",
          "datasource": {
            "type": "questdb-questdb-datasource",
            "uid": "xxx"
          },
          "queryType": "sql",
          "rawSql": "WITH averaged AS (\r\n  SELECT timestamp, avg(AccessRequests) as requests\r\n  FROM freeradius\r\n  WHERE $__timeFilter(timestamp)\r\n  SAMPLE BY 200ms\r\n), lagged AS (\r\n  SELECT\r\n   timestamp as curr_time,\r\n   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n   requests as curr_requests,\r\n   lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests\r\n   FROM averaged\r\n), diffed AS (\r\n  SELECT \r\n  curr_time,\r\n  curr_requests,\r\n  curr_requests - prev_requests as requests_delta,\r\n  curr_time - prev_time as time_delta\r\n  FROM lagged\r\n)\r\nSELECT curr_time, requests_delta / (time_delta / (200 * 1000)) as Requests\r\nFROM diffed",
          "meta": {
            "builderOptions": {
              "mode": "list",
              "fields": [],
              "limit": "",
              "timeField": ""
            },
            "timezone": "Europe/Paris"
          },
          "format": 1,
          "selectedFormat": 1,
          "datasourceId": 3,
          "intervalMs": 200,
          "maxDataPoints": 1070
        }
      ],
      "from": "1750086784632",
      "to": "1750087084632"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "status": 200,
        "frames": [
          {
            "schema": {
              "name": "A",
              "refId": "A",
              "meta": {
                "typeVersion": [
                  0,
                  0
                ],
                "preferredVisualisationType": "table",
                "executedQueryString": "WITH averaged AS (\r\n  SELECT timestamp, avg(AccessRequests) as requests\r\n  FROM freeradius\r\n  WHERE timestamp >= cast(1750086784632000 as timestamp) AND timestamp <= cast(1750087084632000 as timestamp)\r\n  SAMPLE BY 200ms\r\n), lagged AS (\r\n  SELECT\r\n   timestamp as curr_time,\r\n   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n   requests as curr_requests,\r\n   lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests\r\n   FROM averaged\r\n), diffed AS (\r\n  SELECT \r\n  curr_time,\r\n  curr_requests,\r\n  curr_requests - prev_requests as requests_delta,\r\n  curr_time - prev_time as time_delta\r\n  FROM lagged\r\n)\r\nSELECT curr_time, requests_delta / (time_delta / (200 * 1000)) as Requests\r\nFROM diffed"
              },
              "fields": [
                {
                  "name": "curr_time",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time",
                    "nullable": true
                  }
                },
                {
                  "name": "Requests",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float64",
                    "nullable": true
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  1750086790000,
                  1750086800000,
                  1750086810000,
                  1750086820000,
                  1750086830000,
                  1750086840000,
                  1750086850000,
                  1750086860000,
                  1750086870000,
                  1750086880000,
                  1750086890000,
                  1750086900000,
                  1750086910000,
                  1750086920000,
                  1750086930000,
                  1750086940000,
                  1750086950000,
                  1750086960000,
                  1750086970000,
                  1750086980000,
                  1750086990000,
                  1750087000000,
                  1750087010000,
                  1750087020000,
                  1750087030000,
                  1750087040000,
                  1750087050000,
                  1750087060000,
                  1750087070000,
                  1750087080000
                ],
                [
                  null,
                  0.01,
                  0.04,
                  0.04,
                  0.04,
                  0.04,
                  0.04,
                  0.01,
                  0.08,
                  0,
                  0.02,
                  0.05,
                  0.02,
                  0.07,
                  0.06,
                  0.03,
                  0.05,
                  0.02,
                  0,
                  0,
                  0.05,
                  0,
                  0,
                  0.01,
                  0.05,
                  0.03,
                  0.02,
                  0.05,
                  0.01,
                  -2.74
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

[nwoolmer]

@vins

WITH averaged AS (\r\n SELECT timestamp, avg(AccessRequests) as requests\r\n FROM freeradius\r\n WHERE timestamp >= cast(1750086784632000 as timestamp) AND timestamp <= cast(1750087084632000 as timestamp)\r\n SAMPLE BY 200ms\r\n), lagged AS (\r\n SELECT\r\n timestamp as curr_time,\r\n lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n requests

The query isn’t keyed, so it is global across all hosts. Maybe one host crashes during the final bucket and messes up the numbers. Perhaps try running a keyed version and see if it is isolated to one host.