SQL Server：如何获得Top（n）和Bottom（n）记录的平均值

I have a table of scores:

我有一张分数表:

Student ¦ Score
  A     ¦ 23.5
  B     ¦ 34.9

How to get Average of ALL, top 15% and Bottom 15% like this:

如何获得ALL的平均值,前15%和后15%像这样:

Average ¦ top15Avg ¦ bottom15Avg

Thanks.

1 个解决方案

#1

You first calculate how many rows that 15% would be. Then you can do the selects and averages like this:

首先计算15%的行数。然后你可以这样做选择和平均:

declare @pct int, @all float, @top float, @bottom float
select  @pct = 0.15 * COUNT(*) from Students

select @all = AVG(score) from Students
select @top = AVG(score) from (select top (@pct) score from Students order by score desc)
select @bottom = AVG(score) as 'bottom15Avg' from (select top (@pct) score from Students order by score asc)
select @all as 'Average', @top as 'top15Avg', @bottom  as 'bottom15Avg'