如何在SQL中获得每个幼儿园的最近三个等级的平均值？

Question

我在SQL中有两个表。第一个表格包含幼儿园的名称和ID，第二个表格包含年级ID、幼儿园ID、成绩和评分日期。我只需要得到每个幼儿园最后三个年级的平均值。

下面是我手头的表格：

幼儿园|kindergarden_id|kindergarden_name|kindergarden_address|kindergarden_user_id|

kindergarden_grade |grade_id|grade|grade_date|kindergarden_kindergarden_id|

我目前正在尝试这样做，通过这个查询，我得到了所有成绩的平均值，而不是最近的三个分数。

SELECT r.kindergarden_name, AVG(r.grade)FROM (SELECT kindergarden.kindergarden_name, kindergarden_grade.grade from kindergarden left join kindergarden_grade on kindergarden.kindergarden_id=kindergarden_grade.kindergarden_kindergarden_id order by kindergarden_grade.grade_date)as r group by r.kindergarden_name

这是我得到的，这是幼儿园和他们所有成绩的平均值：

柯基3.0000蝴蝶4.0000想象力2.0000

Answer 1

您可以使用窗口函数来获取最后三个等级：

SELECT k.kindergarden_name, AVG(r.grade)
FROM (SELECT k.kindergarden_name, kg.grade,
             ROW_NUMBER() OVER (PARTITION BY k.kindergarden_name ORDER BY kg.grade_date) as seqnum
      FROM kindergarden k LEFT JOIN
           kindergarden_grade kg
           ON k.kindergarden_id = kg.kindergarden_kindergarden_id 
    ) k
WHERE seqnum <= 3
GROUP BY k.kindergarden_name;

编辑：

旧版本的MySQL不支持ROW_NUMBER()。有多种解决方案，但只有一种：

SELECT k.kindergarden_name, AVG(r.grade)
FROM kindergarden k LEFT JOIN
     kindergarden_grade kg
     ON k.kindergarden_id = kg.kindergarden_kindergarden_id 
WHERE kg.grade_date >= ANY (SELECT kg2.grade_date
                            FROM kindergarden_grade kg2
                            WHERE kg2.kindergarden_kindergarden_id = kg.kindergarden_kindergarden_id
                            ORDER BY kg2.grade_date DESC
                            LIMIT 1 OFFSET 2
                           )
 GROUP BY k.kindergarden_name;

这使用子查询返回第三个最新日期。ANY处理特定幼儿园没有三个日期的情况。