0

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?

Dennis Kozevnikoff
  • 2,078
  • 3
  • 19
  • 29
nkrivenko
  • 1,231
  • 3
  • 14
  • 23

1 Answers1

1

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.

kes_
  • 336
  • 3
  • 6