select student_id, student_name, avg_grade, rank() over (order by avg_grade desc)
from (select s.student_id,
s.student_name,
avg(ce.grade) as avg_grade,
rank() over (order by avg(ce.grade) desc nulls last) as seqnum,
count(*) over () as cnt
from students s
left join
course_enrollment ce
on s.student_id = ce.student_id
group by s.student_id
) as ce_avg
where seqnum <= cnt * 0.1;
您还可以使用其他窗口功能,例如NTILE()
和percentile_DISC()
。我更喜欢直接计算,因为它可以更好地控制联系的处理方式。