Rate of specific metric in questdb

Community
1

Hi I have metric that show number of messages pass from my app, this metric generated by Prometheus and send via telegraf to questdb, the issue is value of this metric always increase, but I need number message in each second pass through my app. Something like rate of my app, how can I do this with questdb query?

Data in source like this
time Count
00:00:01 1000
00:00:02 1000
00:00:03 1200
00:00:04 1500
in line 2 must show zero
in line 3 need to show 200 message pass through my app
in step 4 need to show 300 message pass

Any idea?
Thanks

2

Hi @Indeed_1 ,

This will be much easier once we merge the LAG and LEAD PR: feat(sql): introduce lag(D) & lead(D) window function by kafka1991 · Pull Request #5255 · questdb/questdb · GitHub

In the meantime, you can handle this with window functions, perhaps with a CROSS JOIN like this:

create table indeed (
  ts timestamp,
  c int
) timestamp(ts);

insert into indeed (ts, c)
values ('1970-01-01T00:00:01', 1000),
       ('1970-01-01T00:00:02', 1000),
       ('1970-01-01T00:00:03', 1200),
       ('1970-01-01T00:00:04', 1500);

SELECT ts, c2-c1 as count_delta FROM
(
  (SELECT ts, c as c1, row_number() OVER () FROM
  (SELECT * FROM indeed)) t1
JOIN    
  (SELECT c as c2, row_number() OVER () FROM
  (SELECT * FROM indeed)) t2
ON t1.row_number = t2.row_number - 1
)
timestamp(ts);