如何用SQL实现基于分组的多层平均值计算？

需求说明 #

需要实现以下两层平均值计算逻辑：

• 第一步：按Category（对应需求中的Division）分组，计算Value的平均值；
• 第二步：按Name分组，对第一步得到的各Category平均值再次求平均。

数据集 #

Name    Category    Value
A       1           3
A       2           4
A       1           5
A       2           2
A       1           3
A       2           1
B       1           2
B       2           3
B       1           4
B       2           5
B       1           6
B       2           7
B       1           5

预期结果 #

Name    AvgResult
A       3
B       4.625

对应Excel公式参考 #

AVERAGE((SUMIFS(D:D,C:C,1,B:B,"A")/COUNTIFS(B:B,"A",C:C,1)),(SUMIFS(D:D,C:C,2,B:B,"A")/COUNTIFS(B:B,"A",C:C,2)))

SQL实现方案 #

方法1：使用子查询 #

SELECT
    Name,
    AVG(category_avg) AS AvgResult
FROM (
    -- 第一步：计算每个Name+Category的Value平均值
    SELECT
        Name,
        Category,
        AVG(Value) AS category_avg
    FROM your_table_name
    GROUP BY Name, Category
) AS category_level_avg
GROUP BY Name;

方法2：使用CTE（公共表表达式） #

WITH category_level_avg AS (
    -- 第一步：计算每个Name+Category的Value平均值
    SELECT
        Name,
        Category,
        AVG(Value) AS category_avg
    FROM your_table_name
    GROUP BY Name, Category
)
-- 第二步：对每个Name的category_avg求平均
SELECT
    Name,
    AVG(category_avg) AS AvgResult
FROM category_level_avg
GROUP BY Name;

逻辑解释 #

1. 内层查询/CTE先按Name和Category分组，计算每个分组下Value的平均值，得到每个用户每个分类的平均；
2. 外层查询再按Name分组，对第一步得到的分类平均值求平均，最终得到需求的结果。

注意：请将your_table_name替换为实际的表名。

内容的提问来源于stack exchange，提问作者Aldo Putra Setia