CODEWITHSUNDEEP
influxdbinfluxql
nkrivenko
nkrivenko

Reputation: 1281

How can I normalize the time series in InfluxDB?

The goal is to create the time series normalized on the first value of the time frame. For example, we have the series

time                 | value
------------------------------
2020-07-13T00:00:00Z | 2
2020-07-14T00:00:00Z | 4
2020-07-15T00:00:00Z | 3

and want to get the result

time                 | value
------------------------------
2020-07-13T00:00:00Z | 1
2020-07-14T00:00:00Z | 2
2020-07-15T00:00:00Z | 1.5

I have tried the following query:

select "value" / first_value from (select "value" from "database"."autogen"."measurement"), (select first("value") as first_value from "database"."autogen"."measurement")

but it produces empty time series.

Could somebody explain me where is the mistake?

Upvotes: 0

Views: 938

Answers (1)

kes_
kes_

Reputation: 365

The desired outcome can be achieved by adding fill(previous) at the end of your query i.e.

select "value" / first_value from (select "value" from "database"."autogen"."measurement"), (select first("value") as first_value from "database"."autogen"."measurement") fill(previous)

...which gives the following output:

name: measurement
time                 value_first_value
----                 -----------------
2020-07-13T00:00:00Z 
2020-07-13T00:00:00Z 1
2020-07-14T00:00:00Z 2
2020-07-15T00:00:00Z 1.5

It seems that for the "value" / first_value calculation to take place values in both fields have to exist at the same timestamp. Since the result of the subquery select first("value") as first_value from "database"."autogen"."measurement" provides a single value at a specific timestamp, the requirement is not met. However, fill(previous) makes first_value available at the required timestamps.

Upvotes: 1

Related Questions