时间戳

首页 > 解决方案 > SQL: Select statement to return ratio of 2 column values

sql - SQL: Select statement to return ratio of 2 column values

问题描述

I have a table of electronic testing data containing pass/fail results for pairs of components at given voltages. It looks like this:

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

For each Component_A, Component_B, Voltage, I'd like to show the failure ratio for the given Component_A/Component_B/Voltage combination as well as the failure ratio for that combination of Component_A/Component_B. The expected output would be as follows:

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)

It would be great to have some options (using unions or subqueries... the more options the better). I've tried a writing a subquery to at least get the voltage_fail_ratio, but it doesn't report component/voltage combos with no failures and I am not sure how to get the component_fail ratio:

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

标签： sql

解决方案

我认为这就是您想要的 - 它在两个单独的子查询中计算Voltage_Fail_Ratio和，然后在匹配和值上加入：Component_Fail_Ratiocomponent_acomponent_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                   0

SQLFiddle 上的演示

推荐阅读