SQL: Select语句返回2个列值的比率
SQL: Select语句返回2个列值的比率
提问于 2020-01-17 12:04:48
我有一个电子测试数据表，其中包含给定电压下成对元件的通过/失败结果。它看起来是这样的：
Component_A|Component_B|Voltage|Result
1 2 1.0 Pass
1 2 1.0 Pass
1 2 1.0 Fail
1 2 1.0 Fail
1 2 2.0 Pass
1 2 2.0 Pass
1 2 2.0 Pass
1 2 2.0 Pass
3 4 1.0 Pass
3 4 1.0 Pass
3 4 1.0 Pass
3 4 1.0 Pass
3 4 2.0 Pass
3 4 2.0 Pass
3 4 2.0 Pass
3 4 2.0 Pass对于每个Component_A、Component_B、Voltage，我想显示给定Component_A/Component_B/Voltage组合的故障率以及该Component_A/Component_B组合的故障率。预期输出将如下所示：
Component_A|Component_B|Voltage|Voltage_Fail_Ratio|Component_Fail_Ratio
1 2 1.0 2/4(0.5) 2/8(0.25)
1 2 2.0 0/4(0) 0/8(0)
3 4 1.0 4/4(0) 0/8(0)
3 4 2.0 0/4(0) 0/8(0)如果有一些选项(使用联合或子查询...选项越多越好)。我已经尝试写了一个子查询，以至少获得voltage_fail_ratio，但它不报告没有故障的组件/电压组合，并且我不确定如何获得component_fail比率：
SELECT f.component_a,f.component_b,f.voltage,f.failcount,p.passcount, f.failcount/(f.failcount+p.passcount) FROM
((SELECT component_a,component_b,voltage, count(*) as failcount from `tests` where
result='FAIL'
GROUP BY component_a,component_b,voltage) f INNER JOIN
(SELECT component_a,component_b,voltage, count(*) as passcount from `tests` where
result='PASS'
GROUP BY component_a,component_b,voltage) p on p.component_a=f.component_a and p.component_b=f.component_b and
p.voltage=f.voltage);http://sqlfiddle.com/#!9/623ae/1
回答 3
Stack Overflow用户
发布于 2020-01-17 12:22:16
我认为这就是您想要的-它在两个单独的子查询中计算Voltage_Fail_Ratio和Component_Fail_Ratio，然后在匹配的component_a和component_b值上进行联接：
SELECT t1.component_a, t1.component_b, t1.Voltage, t1.Voltage_Fail_Ratio, Component_Fail_Ratio
FROM (
SELECT component_a, component_b, Voltage,
SUM(result = 'FAIL') / COUNT(*) AS Voltage_Fail_Ratio
FROM tests
GROUP BY component_a, component_b, Voltage
) t1
JOIN (
SELECT component_a, component_b,
SUM(result = 'FAIL') / COUNT(*) AS Component_Fail_Ratio
FROM tests
GROUP BY component_a, component_b
) t2 ON t2.component_a = t1.component_a AND t2.component_b = t1.component_b
;输出：
component_a component_b Voltage Voltage_Fail_Ratio Component_Fail_Ratio
1 2 1 0.5 0.25
1 2 2 0 0.25
3 4 1 0 0
3 4 2 0 0Demo on SQLFiddle
Stack Overflow用户
发布于 2020-01-17 15:45:16
这在某种程度上类似于@Nick的，但将视图与WITH一起使用。只是为了给你更多的选择。
WITH for_volt_fail_ratio AS(
SELECT com_a, com_b, volt, SUM(result = 'FAIL') / COUNT(*) AS Voltage_Fail_Ratio
FROM tests
GROUP BY com_a, com_b, volt),
for_com_fail_ratio AS(
SELECT com_a, com_b,SUM(result = 'FAIL') / COUNT(*) AS Component_Fail_Ratio
FROM tests
GROUP BY com_a, com_b),
sh AS(
SELECT tb1.com_a,tb1.com_b,tb1.volt,tb1.Voltage_Fail_Ratio,tb2.Component_Fail_Ratio
FROM for_volt_fail_ratio tb1 INNER JOIN for_com_fail_ratio tb2 ON (tb1.com_a=tb2.com_a AND tb1.com_b=tb2.com_b)
)
SELECT * FROM shStack Overflow用户
发布于 2020-01-17 21:22:03
最简单的方法是条件聚合：
select component_a, component_b, voltage,
sum(case when result = 'PASS' then 1 else 0 end) as num_pass,
sum(case when result = 'FAIL' then 1 else 0 end) as num_fail,
avg(case when result = 'FAIL' then 1.0 else 0 end) as fail_rate
from tests
group by component_a, component_b, voltage;如果您确实在使用MySQL (正如SQL Fiddle所建议的那样)，则可以将其简化为：
select component_a, component_b, voltage,
sum(result = 'PASS') as num_pass,
sum(result = 'FAIL') as num_fail,
avg(result = 'FAIL') as fail_rate
from tests
group by component_a, component_b, voltage页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59781034
复制相关文章
相似问题
腾讯云开发者
