时间戳

首页 > 解决方案 > 使用 group by 和有子句计算来自同一个表的子查询的百分比

sql - 使用 group by 和有子句计算来自同一个表的子查询的百分比

问题描述

我正在使用 PostGres 10.12 DB，其中包含有关测试的各种字段：

|test_name|result |report_time|main_version|environment|
|    A    |error  |29/11/2020 |     1      |   john    |
|    A    |failure|28/12/2020 |     1      |   john    |
|    A    |error  |29/12/2020 |     1      |   alice   |
|    B    |passed |30/12/2020 |     2      |   ben     |
|    C    |failure|31/12/2020 |     2      |   alice   |
|    A    |error  |31/12/2020 |     2      |   john    |

我正在尝试计算在同一天运行的所有测试中同时具有“失败/错误”和“通过”结果的测试百分比。

我创建了以下查询：

SELECT s.environment, COUNT(*) AS total, COUNT(*)::float / t.total_tests * 100 as percentage
FROM (
     SELECT test_name, environment
     FROM tests where report_time >= now() - interval '5 day' 
     and main_version='1' and environment='John'
     GROUP BY test_name, environment
     having COUNT(case when result in ('failure', 'error') then 1 else null end) > 0 
     and count(case when result = 'passed' then 1 else null end) > 0
     order by environment asc
) s
CROSS JOIN (
      SELECT COUNT(*) AS total_tests FROM tests where report_time >= now() - interval '5 day' 
      and main_version='1' and environment='John'
) t
GROUP BY s.environment, t.total_tests

这适用于单个环境和版本。当我尝试组合环境时，计数是错误的。
如何正确计算每天的正确百分比？

标签： sqlpostgresqlcountpercentage

解决方案

我正在尝试计算在同一天运行的所有测试中同时具有“失败/错误”和“通过”结果的测试百分比。

我不知道“同一天”是指什么。样本数据取自五天范围内的数据，所以我可能猜到这就是您的意思。

无论如何，基本思想是使用条件聚合：

SELECT test_name, environment,
       AVG( (result = 'passed')::int ) as passed_ratio,
       AVG( (result in ('failure', 'error') )::int ) as fail_error_ratio
FROM tests 
WHERE report_time >= now() - interval '5 day'  AND
      main_version = '1' AND
      environment = 'John'
GROUP BY test_name, environment;

这将返回 0 到 1 之间的比率。如果您想要 0 到 100 之间的百分比，只需乘以 100。

推荐阅读