How can I make a continuous query to calcuate a continuous mean value

influxdb

#1

Here is the sample:
time t_tag t_value
14:01 people 1
14:02 people 2
14:03 people 3
14:04 people 4
14:05 people 5
14:06 people 6
14:07 people 7
14:08 people 8
14:09 people 9
14:10 people 10
14:11 people 11
14:12 people 12
14:13 people 13
14:14 people 14
14:15 people 15

I want to calculate the mean value for the last 10 minites.
for example,
at 14:11: (1+2+3…+9+10)/10 = 5.5
at 14:12: (2+3+4…+10+11)/10 = 6.5
at 14:13: (3+4…+11+12)/10 = 7.5

and store in a new measuret such as:
time t_tag new_t_value
14:11 people 5.5
14:12 people 6.5
14:13 people 7.5

my influx QL:
CREATE CONTINUOUS cq_g10m ON mydb
RESAMPLE EVERY 1m FOR 10m
BEGIN
SELECT mean(t_value) AS new_t_value INTO cq_store.g10m FROM d1 GROUP BY time(10m), t_tag
END

And only get the data at 14:10, 14:20, 14:30…, which is not continuous by 1 minute.
How to solve at this situation?


#2

You could use Kapacitor to process your data.

Stream the data from your measurement using a TICK script and use Kapacitors output node to write it back to the database.

You’re grouping by 10 minutes, which means the data is chunked into 10 minute buckets i think. If you use something like this

stream
    |from()
        .database('telegraf')
        .measurement('measurement')
        .retentionPolicy('retentionpolicy')
        .groupBy(time(1m), *)
    |window()
        .period(1m)
        .every(1m)
        .align()
    |mean('t_value')
        .as('mean_t_value')
    |influxDBOut()
        .database('telegraf')
        .retentionPolicy('retentionpolicy')
        .measurement('measurement')
        .precision('s')

That should stream 1 minute of data every minute and output the mean value, grouped together per minute.