Math operations with lag()

Community
,
21

i have two querys: one with the requests per host, and another with both together. that one is the one without host:

WITH averaged AS (
  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

if you see, the query doesn’t mention the host column at all and it’s not using keys.

22

Right, but it doesn’t filter for a specific host either. I’m wondering if there is one host which is an outlier?

I’m willing to believe its a bug in the query too, but I don’t have the data to reproduce it :slight_smile: