TICKscript to replace missing values with average of last 5 point

1

Hi,
I’m trying to write a TICKscript that replaces missing values with average of last 5 points and write the new data to a bucket. Here is my code:

var currentDB = 'myDB'

var currentRP = 'autogen'

var measurement = 'machine26'

var field = 'sensor155'

var defval = batch
    |query('select mean("sensor155") as "sensor155" from "myDB".autogen.machine26 limit 5')
        .every(10s)
        .period(10s)
        .align()

var realdata = batch
    |query('select "sensor155" as "sensor155" from "myDB".autogen.machine26')
        .every(10s)
        .period(10s)
        .align()

realdata
    |join(defval)
        .as('realdata', 'defval')
        .tolerance(15s)
    |eval(lambda: if(isPresent("realdata.sensor155"), float("realdata.sensor155"), float("defval.sensor155")))
        .as(sensor)
    |influxDBOut()
        .database('testDB')
        .retentionPolicy(currentRP)
        .measurement(measurement)

The thing I’m missing seems like the batch part that calculates the mean creates only one point. So join only creates one data point, whereas I want to check all points and if missing replace with the average. Also the first batch takes 5 points rather than last 5 valid points. I want to evaluate the last 5 valid points. I’m sure there must be a better way. How can I achieve this?

Thanks in advance.

2

I changed it with something like this:

var defval = stream
    |from()
        .measurement('machine26')
    |where(lambda: isPresent("sensor155"))
    |window()
        .period(10s)
        .everyCount(5)
    |bottom(5, sensor)
        .as('data_set')
    |mean('data_set')
        .as('defvalue')

var realdata = stream
    |from()
        .measurement('machine26')
    |eval(lambda: "sensor155")
        .quiet()
        .as('realvalue')

realdata
    |join(defval)
        .as('realdata', 'defval')
        .fill('null')
        .tolerance(5s)
    |log()
    |eval(lambda: if(isPresent("realdata.realvalue"), float("realdata.realvalue"), float("defval.defvalue")))
        .as('sensor155_v2')
    |log()
    |influxDBOut()
        .database('testDB')
        .retentionPolicy('autogen')
        .measurement('machine26')

But still, this won’t replace null values, in fact I didn’t see any null values logged here even though I logged the data in different tickscript and it has null values. I guess I didn’t understand the null value condition here. At what interval Stream node checks the data? For example the script above logs data like in every one second. On the other hand:

var checkdata = stream
    |from()
        .measurement('machine26')
    |default()
        .field(sensor, 10)
    |eval(lambda: float("sensor155") * float(2))
        .as('sensor155-null-check')
    |log()
    |influxDBOut()
        .database('testDB')
        .retentionPolicy('autogen')
        .measurement('machine26')

this one logs data nearly at every one nanosecond(not sure if it’s called nanosecond). So which one is true? Should InfluxDB has data at every nanosecond or at every second or can we change this behavior? How does it decides if the value is null?