monetdb

Does percent_rank() analytic function return correct output for rows in partition

Consider the below query which is based on sample VOC dataset provided by MonetDB. I am trying to find the percent_rank() over partition by trip.

SELECT number, trip, tonnage, departure_Date, arrival_date,
RANK() OVER ( PARTITION BY trip ORDER BY tonnage ) AS RankAggregation,
PERCENT_RANK() OVER ( PARTITION BY trip ORDER BY tonnage ) as PercentRankGroup
FROM voyages WHERE particulars LIKE '%_recked%';

Ideally, the percent_rank() over partition should ideally be calculated as (rank() - 1) / (rows in partition - 1). But as per observations from output of above query, this is being calculated as (rank() - 1) / (total rows - 1) which is resulting in incorrect output for partitions [PercentRankGroup column in above query].

Can anyone point to me if the query is incorrect in someway. Is there any alternate way to calculate the same ( we can use directly rank() and count() over).

Thanks.

Solution

  • This is a bug that I just fixed it. Thanks for the report.