按Master_ID计算去重Group_ID后的平均分数技术问询

示例数据表 #

需求说明 #

计算每个Master_ID对应的平均分数，规则为：同一Master_ID下的同一Group_ID仅视为一次（因该分组下分数始终相同），需先基于Group_ID去重，再对去重后的分数求和，最后除以去重后的Group_ID数量。
以示例数据为例：4（Group_ID 1）+3.5（Group_ID 3）+5（Group_ID 2）=12.5，除以3个去重分组，结果为4.61。

当前尝试的SQL语句 #

SELECT appraisal_lines_kbi.AppraisalID, cnt.oCount, sm.Scoring, ifnull(sm.Scoring/cnt.oCount, 0) AS AvrKbiScore 
FROM appraisal_lines_kbi 
left join (select AppraisalID, Scoring FROM appraisal_lines_kbi GROUP BY KbiGroup) sm 
ON appraisal_lines_kbi.AppraisalID = sm.AppraisalID
left join (Select AppraisalID, COUNT(DISTINCT(KbiGroup)) as oCount FROM appraisal_lines_kbi) cnt 
ON appraisal_lines_kbi.AppraisalID = cnt.AppraisalID
WHERE appraisal_lines_kbi.AppraisalID = 45

正确SQL方案 #

核心思路 #

先通过DISTINCT或分组操作，得到每个Master_ID下唯一的Group_ID及其对应分数，再基于这个去重后的数据集计算总分和分组数量，最终得到平均分数。

通用写法（支持所有主流数据库） #

SELECT
    Master_ID,
    SUM(Score) / COUNT(Group_ID) AS AvrKbiScore
FROM (
    -- 第一步：按Master_ID+Group_ID去重，保留唯一的分组分数
    SELECT DISTINCT
        Master_ID,
        Group_ID,
        Score
    FROM
        appraisal_lines_kbi
    -- 如需指定单个AppraisalID，添加WHERE条件
    -- WHERE AppraisalID = 45
) AS unique_group_scores
GROUP BY
    Master_ID;

针对指定AppraisalID的写法 #

SELECT
    Master_ID,
    ROUND(SUM(Score) / COUNT(Group_ID), 2) AS AvrKbiScore -- 保留两位小数
FROM (
    SELECT DISTINCT
        Master_ID,
        Group_ID,
        Score
    FROM
        appraisal_lines_kbi
    WHERE
        AppraisalID = 45
) AS unique_group_scores
GROUP BY
    Master_ID;

原SQL的问题说明 #

1. 子查询select AppraisalID, Scoring FROM appraisal_lines_kbi GROUP BY KbiGroup未按AppraisalID分组，会导致每个KbiGroup仅返回一条随机的AppraisalID和Scoring，逻辑错误。
2. 第二个子查询Select AppraisalID, COUNT(DISTINCT(KbiGroup)) as oCount FROM appraisal_lines_kbi未添加GROUP BY AppraisalID，只会返回全表所有AppraisalID的总去重分组数，而非每个AppraisalID单独的分组数。

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