Optimized availability percentage calculation from 2 or more complex sql query

Muhammad Al-iman Mohd Zain 0
Viewed 21

I may have ask almost the same question before, but I found out there is calculation error in the result. As based on my last question from here. From the calculation it does not take count the missing point when the data is failed to be collected or loss in transit. So this data should be taken in as outage. So I did try to create a new query as below.

Outage Query (UO)

SELECT 
(SELECT 
  (count(outage_list.*) * 30)::float/60 AS "outage"
FROM
(SELECT
  time_bucket_gapfill('30.0s',time) AS "time",
  rtime
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND   
  node = '$node' AND
  query = '$query' AND
  rtime < 0) AS "outage_list"
) + (SELECT 
(SELECT 
(count(*) * 30)::float/60 AS "reporting_period"
FROM (SELECT
  time_bucket_gapfill('30s',time) AS "time",
  avg(rtime)
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND
  query = '$query' AND
  node = '$node'
GROUP BY 1) AS sub_query_rp) - (SELECT
  (count(*) * 30)::float/60 AS "probe_minutes"
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND   
  node = '$node' AND
  query = '$query') AS "packet_lost") AS "unplan_outage"

Reporting Period Query (T)

SELECT 
(count(*) * 30)::float/60 AS "reporting_period"
FROM (SELECT
  time_bucket_gapfill('30s',time) AS "time",
  avg(rtime)
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND
  query = '$query' AND
  node = '$node'
GROUP BY 1) AS sub_query_rp

Planned Outage (PO) This value is a int variable from grafana.

Here are the information on the formula availability formula

I try to combine both query into single query, but I get error and I don't know how to solve the deep and nested query. Is there a way to simplified and combined this calculation into one single query that is optimized.

timescaledb sql postgresql
1 Answers

okay, I try giving myself a try.. and here I get the best as I can.

SELECT 
*,
(reporting_period - probe_minutes) AS "packet_lost",
((reporting_period - probe_minutes) + reported_outage) AS "unplanned_outage",
ROUND((((( reporting_period - $PO ) - ((reporting_period - probe_minutes) + reported_outage) )/( reporting_period - $PO ) ) * 100)::numeric,3) AS "availability"
FROM
(SELECT 
(count(*) * 30)::float/60 AS "reporting_period"
FROM (SELECT
  time_bucket_gapfill('30s',time) AS "time",
  avg(rtime)
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND   
  node = '$node' AND
  query = '$query' 
GROUP BY 1) AS sub_query_rp) AS "rp_data",
(SELECT 
  (count(outage_list.*) * 30)::float/60 AS "reported_outage"
FROM
(SELECT
  time_bucket_gapfill('30.0s',time) AS "time",
  rtime
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND   
  node = '$node' AND
  query = '$query' AND
  rtime < 0) AS "outage_list"
) AS ro_data,
(SELECT
  (count(*) * 30)::float/60 AS "probe_minutes"
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND   
  node = '$node' AND
  query = '$query') AS "probe_data"

just simplified.. and take out only query I need and combine. But I still think that there is a better way to do this like the where part that is actually repeatable. or there is part that may further improve query performance and server resources.

Muhammad Al-iman Mohd Zain0
Related