Average of entries from last date

InfluxDB 2
1

I want to calculate the average of entries from the last known day.

Below shows you the last two days data I have (14 entries per day)

> select * from "variable" order by time desc limit 28
name: variable
time                 area_code area_name                     unit_price
----                 --------- ---------                     ----------
2021-05-11T23:00:00Z P         Northern_Scotland             18.4695
2021-05-11T23:00:00Z N         Southern_Scotland             17.598
2021-05-11T23:00:00Z M         Yorkshire                     16.968
2021-05-11T23:00:00Z L         South_Western_England         18.6795
2021-05-11T23:00:00Z K         Southern_Wales                18.081
2021-05-11T23:00:00Z J         South_Eastern_England         18.501
2021-05-11T23:00:00Z H         Southern_England              17.5875
2021-05-11T23:00:00Z G         North_Western_England         17.4615
2021-05-11T23:00:00Z F         North_Eastern_England         17.262
2021-05-11T23:00:00Z E         West_Midlands                 17.6085
2021-05-11T23:00:00Z D         Merseyside_and_Northern_Wales 19.4355
2021-05-11T23:00:00Z C         London                        17.4405
2021-05-11T23:00:00Z B         East_Midlands                 17.3565
2021-05-11T23:00:00Z A         Eastern_England               17.871
2020-11-01T00:00:00Z P         Northern_Scotland             17.073
2020-11-01T00:00:00Z N         Southern_Scotland             16.2225
2020-11-01T00:00:00Z M         Yorkshire                     15.6135
2020-11-01T00:00:00Z L         South_Western_England         17.094
2020-11-01T00:00:00Z K         Southern_Wales                16.527
2020-11-01T00:00:00Z J         South_Eastern_England         16.8945
2020-11-01T00:00:00Z H         Southern_England              16.128
2020-11-01T00:00:00Z G         North_Western_England         16.0125
2020-11-01T00:00:00Z F         North_Eastern_England         15.7395
2020-11-01T00:00:00Z E         West_Midlands                 16.086
2020-11-01T00:00:00Z D         Merseyside_and_Northern_Wales 17.8605
2020-11-01T00:00:00Z C         London                        15.897
2020-11-01T00:00:00Z B         East_Midlands                 15.8445
2020-11-01T00:00:00Z A         Eastern_England               16.2855

As you can see here, limit 14 average isthe same value as no limit.

> select mean(unit_price) from "variable" order by time desc limit 14
name: variable
time                 mean
----                 ----
1970-01-01T00:00:00Z 16.2924375
> select mean(unit_price) from "variable"
name: variable
time                 mean
----                 ----
1970-01-01T00:00:00Z 16.2924375
>

I have tried nested selects, but can’t seem to find how to get an average of the final 14 entries.

Any help would be very much appreciated.

2

Hello @Zarch,
What is the value when you run:

Select mean(unit_price) from "variable" where time >= '2021-05-11T23:00:00Z' and time <= `2020-11-01T00:00:00Z`
3

Hi @Anaisdg

Thanks for taking the time to reply, appreciate it.

With more fiddling I managed to come up with something that works.

> select mean(unit_price) from "variable" group by time(1d) fill(none)
name: variable
time                 mean
----                 ----
2019-04-12T00:00:00Z 15.572249999999997
2020-01-15T00:00:00Z 15.340499999999997
2020-11-01T00:00:00Z 16.377
2021-05-11T00:00:00Z 17.880000000000003

> select last("mean") from (select mean(unit_price) from "variable" group by time(1d) fill(none))
name: variable
time                 last
----                 ----
2021-05-11T00:00:00Z 17.880000000000003
>