mysql - 计算聚合函数中错误记录占总记录的百分比

Question

我正在寻找计算 bool 值百分比的解决方案:

Model:

Products 1 <-> n Components

Components.essential is a boolean attribute

我想计算的是这样的:

SELECT products.*, count(comp_a.essential)/count(comp_b.essential) AS essential_percentage
From products
INNER JOIN components AS comp_a ON products.id = comp_a.product_id
INNER JOIN components AS comp_b ON products.id = comp_b.product_id
WHERE comp_a.essential = 1
GROUP BY products.id

不幸的是，由于组件模型上有两个 INNER JOIN...

有什么帮助吗？

马库斯

Answer 1

只需要一个连接。请注意标准的 GROUP BY 子句。

这给出了“真实”的百分比，因为我认为这就是你的意思(不是错误的)

SELECT
  p.*, 
  count(CASE WHEN c.essential = 1 THEN 1 END) / count(*) AS essential_percentage
From 
  products p
  LEFT OUTER JOIN 
  components c ON p.id = c.product_id
GROUP BY 
  p.*;

或

  count(nullif(c.essential, 0)) / count(*) AS essential_percentage