时间戳

首页 > 解决方案 > 如何使用 2 sql 查询计算可用性百分比并将其组合到结果中

sql - 如何使用 2 sql 查询计算可用性百分比并将其组合到结果中

问题描述

我必须使用来自这 2 个查询的信息来创建服务可用性。

第一个查询。

SELECT
  time_bucket('600s',"time") AS "time",
  count(availability)*30 AS "Reporting Period"
FROM query_response_time
WHERE
  "time" BETWEEN '2020-12-19T15:00:00Z' AND '2020-12-19T16:00:00Z' AND
  cnode = 'PDSAPUJDC' AND   
  node = 'dnsvgitndnsnode06.local' AND
  query = 'www.mampu.gov.my'
GROUP BY 1
ORDER BY 1;

示例结果

          time          | Reporting Period 
------------------------+------------------
 2020-12-19 23:00:00+08 |              600
 2020-12-19 23:10:00+08 |              570
 2020-12-19 23:20:00+08 |              600
 2020-12-19 23:30:00+08 |              600
 2020-12-19 23:40:00+08 |              570
 2020-12-19 23:50:00+08 |              600
 2020-12-20 00:00:00+08 |               30
(7 rows)

第二次查询

SELECT
  time_bucket('600s',"time") AS "time",
  count(availability)*30 AS "Unplanned Outage"
FROM query_response_time
WHERE
  "time" BETWEEN '2020-12-19T15:00:00Z' AND '2020-12-19T16:00:00Z' AND
  availability = 'false' AND
  cnode = 'PDSAPUJDC' AND
  node = 'dnsvgitndnsnode06.local' AND
  query = 'www.mampu.gov.my'
GROUP BY 1
ORDER BY 1;

示例结果

          time          | Reporting Period 
------------------------+------------------
 2020-12-19 23:16:00+08 |               30
 2020-12-19 23:41:00+08 |               30
(2 rows)

公式如下（（报告期 - 计划外停机）/报告期）*100

标签： sqlpostgresqldatetimecountpivot

解决方案

您可以使用条件聚合：

SELECT time_bucket('600s', "time") AS "time",
       count(*) * 30 as total,
       sum(case when availability <> 'false' then 1 else 0 end)*30 AS num_falses,
       avg(case when availability <> 'false' then 1.0 else 0 end) AS num_falses,
FROM query_response_time
WHERE "time" BETWEEN '2020-12-19T15:00:00Z' AND '2020-12-19T16:00:00Z' AND
      cnode = 'PDSAPUJDC' AND   
      node = 'dnsvgitndnsnode06.local' AND
      query = 'www.mampu.gov.my'
GROUP BY 1
ORDER BY 1;

注意：如果availability真的是一个布尔值（在 Postgres 中可能是合适的），您可以将其简化select为：

SELECT time_bucket('600s', "time") AS "time",
       count(*) * 30 as total,
       sum( (availability)::int )*30 AS num_falses,
       avg( (availability)::int ) AS num_falses,

推荐阅读