获取每所学校前 10 名学生的平均值

发布于 2024-10-11 11:25:16 字数 1099 浏览 7 评论 0原文

我们的学区有 38 所小学。孩子们参加了测试。各学校的平均分很分散，但我想比较每所学校前 10 名学生的平均分。

要求：仅使用临时表。

我以一种非常工作密集、容易出错的方式完成了这项工作，如下所示。
（sch_code = 例如，9043； -- scabbrev = 例如，“卡特”； -- totpct_stu = 例如, 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...

问题：所以这可行，但是没有更好的方法吗？

谢谢！

PS——看起来像家庭作业，但这是，嗯……真实的。

原文

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.

Requirement: use temporary tables only.

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?

Thanks!

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁吢 2024-10-18 11:25:16

使用此技术。

select sch_code,
       schabbrev,
       ROUND( AVG( totpct_stu ), 1 ) AS top10
from   (select sch_code,
               schabbrev,
               totpct_stu,
               @num := if(@group = sch_code, @num + 1, 1) as row_number,
               @group := sch_code as dummy
        from   test_table
        order by sch_code, totpct_stu desc) as x
where  row_number <= 10
GROUP BY sch_code,
       schabbrev

Using this technique.

select sch_code,
       schabbrev,
       ROUND( AVG( totpct_stu ), 1 ) AS top10
from   (select sch_code,
               schabbrev,
               totpct_stu,
               @num := if(@group = sch_code, @num + 1, 1) as row_number,
               @group := sch_code as dummy
        from   test_table
        order by sch_code, totpct_stu desc) as x
where  row_number <= 10
GROUP BY sch_code,
       schabbrev

回复收藏 0 原文

~没有更多了~