# Calculating rates of change (Druid 25.0.0)

20 views

### richarde

May 2, 2023, 9:51:10 PM5/2/23
to Druid User
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

May 4, 2023, 9:14:54 AM5/4/23
to Druid User
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