sql - 百分位分数，有多少人做的比你做的 postgresql 差

Question

我想计算有多少人(百分比)在我的测试中表现比我差。

这是我想要的结果:

 student | vak   | resultaat | percentielscore 
---------+----------+-----------+-----------------
 1000001 | IBUI  | 5.1 | 0
 1000001 | ILNUX1| 3.4 | 0
 1000001 | IMUML | 6.9 | 0
 1000001 | IRDB  | 2.5 | 0
 1000002 | IARCH | 7.8 | 0
 1000002 | ICOM  | 5.6 | 0
 1000002 | INST  | 6.2 | 0
 1000002 | IRDB  | 7.2 | 100
 1000003 | IARCH | 7.8 | 0
 1000003 | ILNUX1| 7.4 | 33
 1000003 | IMUML | 6.9 | 0
 1000003 | INST  | 6.2 | 0
 1000003 | IRDB  | 3.5 | 25
 1000004 | IBUI  | 9.5 | 100
 1000004 | ICOM  | 5.6 | 0
 1000004 | ILNUX1| 7.4 | 33
 1000004 | IRDB  | 3.5 | 25
 1000005 | ILNUX1| 7.4 | 33
 1000005 | IMHTB | 4.2 | 
 1000005 | IMUML | 6.9 | 0
 1000005 | INST  | 6.2 | 0
 1000005 | IRDB  | 4.8 | 75

我根本不知道如何处理这个问题，谷歌百分位结果总是以这样的方式结束:

student | vak   | resultaat | percentielscore 
---------+----------+-----------+-----------------
"1000001";"ILNUX1  ";2.9;  2.40
"1000001";"IMUML   ";6.4;  5.29
"1000001";"IBUI    ";4.6;  3.80
"1000001";"IRDB    ";2.0;  1.65
"1000002";"INST    ";5.7;  4.71
"1000002";"IARCH   ";7.3;  6.03
"1000002";"IRDB    ";6.7;  5.54
"1000002";"ICOM    ";5.1;  4.21
"1000003";"IMUML   ";6.4;  5.29
"1000003";"IRDB    ";3.0;  2.48
"1000003";"INST    ";5.7;  4.71
"1000003";"IARCH   ";7.3;  6.03
"1000003";"ILNUX1  ";6.9;  5.70
"1000004";"IRDB    ";3.0;  2.48
"1000004";"ILNUX1  ";6.9;  5.70
"1000004";"ICOM    ";5.1;  4.21
"1000004";"IBUI    ";9.0;  7.44
"1000005";"IRDB    ";4.3;  3.55
"1000005";"ILNUX1  ";6.9;  5.70
"1000005";"IMUML   ";6.4;  5.29
"1000005";"IMHTB   ";3.7;  3.06
"1000005";"INST    ";5.7;  4.71

有什么想法吗？

Answer 1

您可以只使用滚动计数。因此，如果您想计算有多少人(百分比)在我的测试中表现比我差，请使用此查询:

with cte as (
    select
        student, vak, resultaat, percentielscore,
        count(*) over(partition by vak, resultaat) as result_count,
        count(*) over(partition by vak order by resultaat) as rolling_count,
        count(*) over(partition by vak) as total_count
    from Table1
    order by vak
)
select
    student, vak, resultaat, percentielscore,
    (rolling_count - result_count) * 100.0 / total_count as percentielscore2
from cte
order by student, vak

sql fiddle demo

但结果与您的结果并不完全相同，因为从我的角度来看，您的结果没有显示有多少(百分比)的人在我的测试中比我做的差，检查，例如，ILNUX1 测试 - 有 4 个人，其中 3 个有 7.4，一个有 3.4，percentilscore 怎么可能是 33？