MySQL中计算公司GDPR步骤整体完成率的查询错误排查

先说说你写的查询哪里错了 #

你尝试的那个查询，核心问题是没有先计算每个步骤的完成率，就直接在原始数据上做了全局聚合，导致两个关键错误：

1. sum(compliance.status)/count(compliance.status) 算的是这家公司所有initiative的整体完成率，而不是每个步骤完成率的总和
2. count(gdpr_steps.id_step) 统计的是关联后所有initiative的数量，而不是公司的步骤总数（比如Mango有2个步骤，但关联后有3条initiative记录，你这里分母用了3而不是2）

举个实际例子，Mango的两个步骤完成率是50和100，总和150除以2得75，但你的查询里，sum(status)是2（1+1+0），count(status)是3，算出来的单步平均是66.67，再除以3（count(gdpr_steps.id_step)的结果），得到的是22.22，完全不符合预期。

正确的思路：分步计算 #

我们需要先算出每个步骤的完成率（这部分你已经写对了！），然后再对这些步骤的完成率求平均值。可以用子查询或者CTE来实现：

方法1：用子查询（兼容所有MySQL版本） #

select 
    company_name as 'Company',
    avg(step_ratio) as 'Overall ratio %'
from (
    -- 这部分是你已经写好的正确步骤完成率查询
    select 
        c.name as company_name,
        gs.id_step,
        (sum(comp.status)/count(comp.status)) * 100 as step_ratio
    from compliance comp
    join gdpr_steps gs on gs.id_step = comp.id_step
    join company c on c.id_company = gs.id_company
    group by c.id_company, gs.id_step
) as step_level_ratios
group by company_name;

方法2：用CTE（MySQL 8.0及以上版本可用，代码更清晰） #

如果你的MySQL版本支持CTE（8.0+），可以这么写：

with step_level_ratios as (
    select 
        c.name as company_name,
        (sum(comp.status)/count(comp.status)) * 100 as step_ratio
    from compliance comp
    join gdpr_steps gs on gs.id_step = comp.id_step
    join company c on c.id_company = gs.id_company
    group by c.id_company, gs.id_step
)
select 
    company_name as 'Company',
    avg(step_ratio) as 'Overall ratio %'
from step_level_ratios
group by company_name;

结果验证 #

运行上面的代码后，会得到你想要的结果：

Company | Overall ratio %
--------|-----------------
Mango   | 75
Kiwi    | 50

另外提个小建议：以后写SQL尽量用显式的JOIN语法（比如JOIN ... ON ...），代替逗号分隔表的隐式连接，这样代码可读性更高，也不容易出现关联错误。

内容的提问来源于stack exchange，提问作者user8795980