前n个百分比前n个百分比

问题描述

我确实有以下代码.

    SQL> select * from student_gpa;

    SSN                    GPA
    --------------- ----------
   22222                    3
   11111                    4
   55555                    3
   33333                    4
   44444                    3

我确实具有此功能来获取前两个gpa得分行.

I do have this function to get the top two gpa score rows.

SQL> select * from(从gpa desc按Student_gpa顺序选择ssn，gpa)，其中rownum< = 2;

SQL> select * from (select ssn, gpa from student_gpa order by gpa desc) where rownum <= 2;

     SSN                    GPA
    --------------- ----------
    11111                    4
    33333                    4

我的问题是我使用什么功能来获得GPA得分的前n％.例如，由于我有两个个人的GPA为4，所以密集排名将返回11111(占据第1行)和22222(占据第2行).我实际上是在寻找一个函数，该函数计算出GPA分数的5％，返回值将是11111和22222.5％函数不应仅返回11111.即使我有gpa为4的更多条目，5％函数仍将返回gpa为4的所有行.谢谢

My question is what function do I use to get the top n% of the GPA score. For example, Since I have two individuals with a GPA of 4, dense rank would return 11111 occupying row 1 and 22222 occupying row 2. I was actually looking for a function say that calculates 5% of the GPA score and the returned value would be both 11111 and 22222. The 5% function SHOULD NOT return 11111 only. Even if I had more entries that had gpa of 4, the 5% function would still return all rows that had gpa of 4.Thanks

推荐答案

您可以尝试以下方法:

WITH     got_analytics     AS
(
     SELECT     ssn, gpa
     ,     ROW_NUMBER () OVER (ORDER BY  gpa  DESC)     AS r_num
     ,     COUNT (*)     OVER ()                                AS n_rows
     FROM  student_gpa
)
SELECT       ssn, gpa
FROM       got_analytics
WHERE       r_num     <= ROUND (n_rows * 12/*insert here your n%*/ / 100)
ORDER BY  gpa     DESC

这篇关于前n个百分比前n个百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！