平均SQL

发布于05月03日

我想知道我们如何在SQL上实现这个公式

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

平均

A   3
B   4.625

例如，有2个名称A B，每个组有1、2、3、.部门，每个部门都有价值

First step: 平均 Value by Division (it might be have more than 1 value)
Second step: 平均 Value by Group (Division1,Division2,......)
Third step: 平均 Value by Name (Group1,Group2,....)

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)))

如有任何帮助，我们将不胜感激.

推荐答案

根据详细信息，try 执行以下步骤.

按部门划分的平均值:

SELECT Name, Division, AVG(Value) AS AverageValueByDivision
FROM YourTable
GROUP BY Name, Division;

各组平均值:

SELECT Name, AVG(AverageValueByDivision) AS AverageValueByGroup
FROM (
    SELECT Name, Division, AVG(Value) AS AverageValueByDivision
    FROM YourTable
    GROUP BY Name, Division
) AS DivisionAverages
GROUP BY Name;

名称平均值:

SELECT AVG(AverageValueByGroup) AS AverageValueByName
FROM (
    SELECT Name, AVG(AverageValueByDivision) AS AverageValueByGroup
    FROM (
        SELECT Name, Division, AVG(Value) AS AverageValueByDivision
        FROM YourTable
        GROUP BY Name, Division
    ) AS DivisionAverages
    GROUP BY Name
) AS GroupAverages;