PERCENT_RANK can return NaN instead of 0e0 (0) in some cases:
(example obtained from a Hibernate test):
create table EntityOfBasics (
id integer not null,
the_int integer,
primary key (id)
);With setup code
insert into EntityOfBasics (the_int, id) values (5, 1);
insert into EntityOfBasics (the_int, id) values (6, 2);
insert into EntityOfBasics (the_int, id) values (7, 3);
insert into EntityOfBasics (the_int, id) values (13, 4);
insert into EntityOfBasics (the_int, id) values (5, 5);
The query
select
id,
the_int,
row_number() over(partition by eob1_0.the_int order by eob1_0.id),
percent_rank() over(partition by eob1_0.the_int order by eob1_0.id),
cume_dist() over(partition by eob1_0.the_int order by eob1_0.id)
from EntityOfBasics eob1_0
order by 1;produces
| ID |
THE_INT |
ROW_NUMBER |
PERCENT_RANK |
CUME_DIST |
| 1 |
5 |
1 |
0.0 |
0.5 |
| 2 |
6 |
1 |
NaN |
1.0 |
| 3 |
7 |
1 |
NaN |
1.0 |
| 4 |
13 |
1 |
NaN |
1.0 |
| 5 |
5 |
2 |
1.0 |
1.0 |
Those NaN values should all be zero.
See also ISO/IEC 9075-2:2023 9.23 Evaluation and transformation of <window function>, specifically syntax rule 5g
PERCENT_RANKcan returnNaNinstead of0e0(0) in some cases:(example obtained from a Hibernate test):
With setup code
The query
produces
Those NaN values should all be zero.
See also ISO/IEC 9075-2:2023 9.23 Evaluation and transformation of <window function>, specifically syntax rule 5g