Calculating weighted average of system uptimes

1

I have a measurement in which I collect the daily percentage uptime of different systems in our environment.

name: system_uptime_daily
time host percentage_uptime

1598023678615478128 A 0.034722222222222224
1598023678615478128 B 0.034722222222222224
1598110078615484392 A 0.011574074074074073
1598110078615484392 B 0.011574074074074073
1598227200000000000 A 44.513888888888886
1598227200000000000 B 48.47222222222222
1598227200000000000 C 0.2777777777777778
1598313600000000000 A 53.47222222222222
1598313600000000000 B 53.47222222222222
1598400000000000000 A 52.77777777777778
1598400000000000000 B 52.77777777777778

System may get removed some times. So the daily average will be missing for those days.

Now, I want calculate the average system uptime for our environment over a time period.

For this purpose I want to calculate the weighted average of the system uptime across each host.

In the sample data pasted above, I need to calculate the sum of

SUM(SUM(percentage_uptime for a host) / Count(percentage_uptime of all host))

I’m not sure how to convert above statement into an Influx Query.

Using below query

    SELECT * FROM( 
SELECT sum("percentage_uptime") as cumulative_uptime    FROM "one_year"."system_uptime_daily" WHERE $timeFilter GROUP BY  host fill(0)
 ), (
SELECT count("percentage_uptime") as uptime_days FROM "one_year"."system_uptime_daily" WHERE $timeFilter
 )

I’ll get the sum of percentage_uptime for each host and total count of precentage uptime.

How can didvide the cumulative_uptime by uptime days to get average uptime of a host and sum it?

P.S. I’m using InfluxDB 1.7.x

2

Hello @Robert_George,
The following query worked for me:

SELECT cumulative_uptime/uptime_days as "weighted_avg" FROM (SELECT sum("usage_user") as cumulative_uptime, count("usage_user") as uptime_days FROM "telegraf"."autogen"."cpu" WHERE time > :dashboardTime: AND time < :upperDashboardTime: GROUP BY  host fill(0) )