Flaky测试查询

Question

给定一个(postgresql(测试结果表，我们希望找到不稳定的测试：失败然后在同一次运行中通过的测试：

+-----------+--------+-----------------+----------------------------+----------+
| result_id | run_id |    scenario     |            time            |  result  |
+-----------+--------+-----------------+----------------------------+----------+
|     12031 |    123 | @loginHappyFlow | 2020-12-22 12:23:20.077636 |  Pass    |
|     12032 |    123 | @signUpSocial   | 2020-12-22 12:22:03.355052 |  Fail    |
|     12033 |    123 | @signUpSocial   | 2020-12-22 12:19:19.812301 |  Pass    |
+-----------+--------+-----------------+----------------------------+----------+

不知道该如何处理，请建议，谢谢！

Answer 1

SELECT
a.result_id
,a.run_id
,a.scenario
,a.time
,a.result
,b.quantity
FROM
table a
JOIN (
SELECT
run_id
,scenario
,COUNT(*) AS quantity
FROM
table
GROUP BY 1,2
) b on (a.run_id = b.run_id and a.scenario = b.scenario)
WHERE
b.quantity > 1