从每所学校获得前10名学生的平均值 [英] Getting the avg of the top 10 students from each school

问题描述

我们的学区设有38所小学.孩子们参加了考试.学校的平均水平分散很大，但我想比较每所学校的前10名学生的平均水平.

We have a school district with 38 elementary schools. The kids took a test. The averages for the schools are widely dispersed, but I want to compare the averages of JUST THE TOP 10 students from each school.

要求:仅使用临时表.

我以非常费时费力的方式完成了此操作，如下所示.
(sch_code =例如9043; -schabbrev =例如"Carter"； -totpct_stu =例如61.3)

I have done this in a very work-intensive, error-prone sort of way as follows.
(sch_code = e.g., 9043; -- schabbrev = e.g., "Carter"; -- totpct_stu = e.g., 61.3)

DROP TEMPORARY TABLE IF EXISTS avg_top10 ;
CREATE TEMPORARY TABLE avg_top10
   ( sch_code   VARCHAR(4),
     schabbrev  VARCHAR(75),
     totpct_stu DECIMAL(5,1)
   );

INSERT 
  INTO avg_top10
SELECT sch_code
     , schabbrev
     , totpct_stu
  FROM test_table
 WHERE sch_code IN ('5489')
 ORDER
    BY totpct_stu DESC
 LIMIT 10;

-- I do that last query for EVERY school, so the total 
-- length of the code is well in excess of 300 lines.  
-- Then, finally...

SELECT schabbrev, ROUND( AVG( totpct_stu ), 1 ) AS top10
  FROM avg_top10
 GROUP
    BY schabbrev
 ORDER
    BY top10 ;

-- OUTPUT:
-----------------------------------
schabbrev   avg_top10
----------  ---------
Goulding         75.4
Garth            77.7
Sperhead         81.4
Oak_P            83.7
Spring           84.9
-- etc...

问题:这样做可行，但是没有更好的方法吗?

Question: So this works, but isn't there a lot better way to do it?

谢谢！

PS-看起来像家庭作业，但这是...真实的.

PS -- Looks like homework, but this is, well...real.

推荐答案

使用 查看全文