Calculating rates of change (Druid 25.0.0)

[richarde]

Hi All,

Using the following query

SELECT FLOOR(__time to HOUR) AS HourTime,  latest("value", 20), max(__time) 
FROM "Telegraf"
WHERE source = 'switch1' AND resource = 'Ethernet17' AND 
    metric_group = 'ifcounters' AND metric = 'in_octets' AND
    TIME_IN_INTERVAL("__time", '2023-05-01/2023-05-05')
GROUP BY 1

Which gives me output like the below. Note the datapoints are not exactly on the time boundary and there could be gaps too in the series.

What I am after is how to obtain the previous period latest values in the same row so that I can work out a rate of change. I have tried adding latest_by("value", time_shift(__time, 'PT1H', -1), 20) but this does not give me the desired result.

Any pointers in the right direction would be appreciated.

2023-05-02T06:00:00.000Z

60927555494

2023-05-02T06:59:46.000Z

2023-05-02T07:00:00.000Z

60934812275

2023-05-02T07:59:48.000Z

2023-05-02T08:00:00.000Z

60941988007

2023-05-02T08:59:48.000Z

2023-05-02T09:00:00.000Z

60949227597

2023-05-02T09:59:47.000Z

2023-05-02T10:00:00.000Z

60956428348

2023-05-02T10:59:47.000Z

2023-05-02T11:00:00.000Z

60963568463

2023-05-02T11:59:47.000Z

[John Kowtko]

Hi Richarde,

Until Window Functions show up (which should be soon) you are likely going to have to do a self-join semi-cartesian product here, for example:

with tmp as (

 SELECT FLOOR(__time to HOUR) AS HourTime,

        max(__time) MaxTime

   FROM "Telegraf"
  WHERE source = 'switch1' 

    AND resource = 'Ethernet17' 

    AND metric_group = 'ifcounters' 

    AND metric = 'in_octets' 

    AND TIME_IN_INTERVAL("__time", '2023-05-01/2023-05-05')
  GROUP BY 1

)
 select a.HourTime,
        max(b.MaxTime)
   from tmp a cross join tmp b

  where b.MaxTime < a.HourTime
  group by a.Grp, a.MaxTime
 

Of course this will knock out the lowest record for A because there will be no B matches for it ... if you need that row then you will need to change this to an outer join, I am not sure if  "tmp a left join tmp b on 1=1" will work ...

-- John