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?

Solution

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.