I needed to calculate a table of data reception frequencies.
This is the schema:
CREATE TABLE ‘measurements’ (
index_time TIMESTAMP,
val DOUBLE
) timestamp(index_time) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(index_time);
I used this query:
(With small modifications it can also be useful to check for any reception failures)
SELECT
(t1-t2)::long / 1000000 AS tim, count(*) AS cnt
FROM (
SELECT
timestamp_ceil(‘s’, index_time) AS t1,
lag(timestamp_ceil(‘s’, index_time)) OVER (ORDER BY index_time) AS t2
FROM measurements
) w1
WHERE t1-t2 IS NOT NULL
GROUP BY tim
ORDER BY cnt DESC
;
the result is:
(tim=seconds, cnt=number of rows)
While writing the query I noticed a couple of things:
A) The lag() function cannot be used in a mathematical operation
ex: timestamp_ceil(‘s’, index_time) - lag(timestamp_ceil(‘s’, index_time)) OVER (ORDER BY index_time)
B)The lag() function always returns a double type field, no matter what field is read.
I hope this can be useful to someone.
Thanks