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);
1 Like
3

@nwoolmer Hi, return this:

image
image445×163 5.06 KB

expected result:

time     Count    count_delta
00:00:01 1000     0
00:00:02 1000     0
00:00:03 1200     200
00:00:04 1500     300

any idea?

4

@nwoolmer after several workaround find issue but query so slow:

Here is the query that return expected result:

SELECT t1.ts, t1.c AS Count, 
COALESCE(t1.c - t2.c, 0) AS count_delta
FROM
  (SELECT ts, c, row_number() OVER (ORDER BY ts) AS rn FROM indeed) t1
LEFT JOIN
  (SELECT ts, c, row_number() OVER (ORDER BY ts) AS rn FROM indeed) t2
ON t1.rn = t2.rn + 1
ORDER BY t1.ts;

Any Idea?

5

Hi @Indeed_1 ,

Best bet would be to put a WHERE timestamp filter inside each sub-query to reduce the size of the JOIN.

6

@nwoolmer try that too but still slow!

7

Please could you quantify how slow?

  • data size, number of rows
  • hardware
  • hot query latency

Etc.