时间戳

首页 > 解决方案 > SQL：与平均值的百分比偏差

sql - SQL：与平均值的百分比偏差

问题描述

我有一张桌子

student class score
--------------------
A        1       6
B        1       5
C        2       6

我想在 SQL 中计算每个班级的分数百分比偏离所有班级的平均分数；换句话说

(avg_score_in_one_class- avg_score_all_classes)/avg_score_all_classes *100

我已经使用这个查询用 SQL 计算了它

SELECT 
class, 
(AVG(score) - (SELECT AVG(score) FROM table))
/
(SELECT AVG(score) FROM table)
*100
FROM table
GROUP BY class

结果应该是

class deviation
---------------
1      -2.9411764705882404
2      5.882352941176465

有没有更好的写法？例如，我在考虑您有很多分数列的情况。

按照 Gordon Linoff 的建议，我找到了部分解决方案：

WITH temp_table as 
(
    SELECT
        class,
    AVG(score) OVER (PARTITION by class) as avg_class,
    AVG(score) OVER () as avg_score
    FROM table
)

SELECT
    class,
AVG((avg_class-avg_score)/avg_score*100.0) as dev
FROM temp_table
GROUP BY class;

这仍然不能简单地推广到多个分数列。

标签： sqlpostgresqlgroup-by

解决方案

只需使用窗口函数：

select class, avg(score) as class_avg,
       (avg(score) * 100.0 / ( sum(sum(score)) over () / sum(count(*)) over () )) - 100.0 as deviation_from_overall_average
from t
group by class;

窗口表达式为：

  • sum(sum(score)) over ()计算分数的总和。
  • sum(count(*)) over ()计算原始数据中的行数。

该比率是总体平均值。

这是一个 db<>fiddle。

推荐阅读