时间戳

首页 > 解决方案 > 如何在满足另一个条件时创建列的 AVG

mysql - 如何在满足另一个条件时创建列的 AVG

问题描述

当另一列值等于特定值时，我需要计算得分列的平均值。但是，我的计算是计算所有分数列的平均值，而忽略了我的情况。

最终结果是根据进度列是否是每个进度值来计算所有的分数值。例如。“高于预期”、“预期”等。我只显示所有测试行。

SELECT 
tbl_score.score,
tbl_progress.progress
(SELECT AVG(tbl_score.score) FROM tbl_score WHERE tbl_progress.progress = 'Expected') as expectedAv
FROM tbl_score
JOIN tbl_pupil ON tbl_score.pupilID = tbl_pupil.pupilID 
JOIN tbl_test ON tbl_score.testID = tbl_test.testID 
LEFT JOIN tbl_progress ON tbl_pupil.pupilID = tbl_progress.pupilID AND tbl_test.testID = tbl_progress.testID 
WHERE
tbl_test.schoolproviderID=16 AND tbl_test.schoolproductID=32 AND tbl_test.imported=1
ORDER by tbl_progress.progress

但是，结果看起来像这样，显示 av 计算实际上是在计算所有分数的平均值。

在此处输入图像描述

标签： mysqlsqldatabaseaverage

解决方案

您可以使用如下分析函数：

select score, progress, 
       sum(case when progress = 'Expected' then score end) over (partition by progress) 
       / count(case when progress = 'Expected' then 1 end) over (partition by progress) 
       as avg_
from your_Table

如果您想在自己的查询中实现解决方案，那么您可以替换

(SELECT AVG(tbl_score.score) FROM tbl_score WHERE tbl_progress.progress = 'Expected') 
 as expectedAv

和

sum(case when tbl_progress.progress = 'Expected' then tbl_score.score end) 
    over (partition by tbl_progress.progress) 
 / count(case when tbl_progress.progress = 'Expected' then 1 end) 
     over (partition by tbl_progress.progress) as expectedAv

推荐阅读