SQL:计算在输出列上出现的次数，并根据出现的次数计算一些百分比

问题描述

我的sql查询获取固件的错误修复验证列表，例如def-456是一张票，要求我对产品进行固件测试. def-456有几个子任务，记录结果.结果记录为:id:abc-123，abc-124，abc-125等(如下表所示).这些对应的ID的结果为通过"或失败".我需要计算两个值---->1.尝试次数:在以下示例中，尝试次数将为3/5，有3次通过和2次失败(即通过/通过+失败)，这里我可以使用count()方法，但是不知道如何追加"/5"和2.第一次尝试成功率:在以下示例中，abc-123失败，但是abc-124通过，abc-125失败，但是abc-126通过，abc-127通过，没有任何失败，这表示我的成功率为:1分3(即1/3)，如何在sql中显示这些值?这有点棘手，我无法为此找到逻辑.

My sql query fetches list of bug fix verification for a firmware for e.g. def-456 is a ticket which asks me to test firmware test on a product. def-456 has several sub-tasks where results are being recorded. Results are recorded as :id: abc-123, abc-124, abc-125 etc (as shown in table below). These corresponding id's have "pass" or "fail" result. I need to calculate two values ---->1. Number of attempts : in following example number of attempts would be 3/5 , there are 3 pass and 2 fail (i.e. pass/pass+fail) , here i can use count() method however don't know how to append "/5" and 2. 1st attempt success rate : in following example, abc-123 failed however abc-124 passed, abc-125 failed however abc-126 passed and abc-127 passed without any failure, means my success rate is : 1 out 3 (i.e. 1/3), how can I display these values in sql? this is bit tricky and I am not able to find logic for this.

这是我的def-456数据:

Here is my data for def-456:

    value | id| 
    --------------
    fail | abc-123  
    pass | abc-124   
    fail | abc-125   
    pass | abc-126   
    pass | abc-127  

这是我要显示的o/p:

and here is o/p I am trying to display :

id   |   value | attempts| %for attempts  | 1st attempt
----------------------------------------------------
abc-123    fail |   3/5  | 60%              | 1/3
abc-124    pass |   3/5  | 60%              | 1/3
abc-125    fail |   3/5  | 60%              | 1/3
abc-126    pass |   3/5  | 60%              | 1/3
abc-127    pass |   3/5  | 60%              | 1/3

推荐答案

我相信这是您需要的:

SELECT
    a.id,
    a.resolution,
    b.*
FROM 
    Table1 a
CROSS JOIN
    (
        SELECT
            CONCAT(SUM(aa.resolution = 'pass'), '/', COUNT(*)) AS attempts,
            CONCAT((SUM(aa.resolution = 'pass') / COUNT(*)) * 100, '%') AS percent_attempts,
            CONCAT(SUM(bb.mindate IS NOT NULL AND resolution = 'pass'), '/', SUM(resolution = 'pass')) AS first_attempt 
        FROM
            Table1 aa
        LEFT JOIN 
            (
                SELECT 
                    MIN(`date`) AS mindate 
                FROM 
                    Table1
            ) bb ON aa.`date` = bb.mindate
    ) b

SQLFiddle链接

这篇关于SQL:计算在输出列上出现的次数，并根据出现的次数计算一些百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！