在PieCloudDB中使用PERCENT_RANK()时遇ROUND函数报错的问题

我有一张Students表，结构及数据如下：

需求是统计每个学生在所属部门的排名百分比，计算公式为：(student_rank_in_the_department - 1) * 100 / (number_of_students_in_the_department - 1)，结果保留两位小数，成绩相同的学生排名一致。

我通过以下SQL语句在PieCloudDB中得到了正确结果：

SELECT student_id ,department_id ,
COALESCE(ROUND((rk-1)*100.0/(cnt-1),2),0) percentage 
FROM(
SELECT student_id ,department_id ,RANK() OVER (PARTITION BY department_id ORDER BY mark DESC) rk ,
COUNT(student_id) OVER (PARTITION BY department_id) cnt 
FROM Students 
) a
ORDER BY department_id;

查询结果：

之后我尝试用PERCENT_RANK()函数直接实现需求，执行了如下SQL：

SELECT student_id, department_id,
       ROUND((PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY mark DESC)) * 100, 2) AS percentage
FROM Students
ORDER BY department_id;

但触发报错：ERROR: function round(double precision, integer) does not exist，想了解报错原因及解决办法。

报错原因 #

PieCloudDB基于PostgreSQL内核，在PostgreSQL的函数重载体系中：

• PERCENT_RANK()窗口函数返回的是double precision（双精度浮点数）类型；
• 系统默认提供的ROUND()函数中，没有直接支持ROUND(double precision, integer)的重载，仅支持ROUND(numeric, integer)和ROUND(double precision)（无小数位数参数）这两种形式。

因此当你传入double precision类型的数值和整数位数参数时，数据库找不到匹配的函数，就会抛出该错误。

解决办法 #

将PERCENT_RANK()计算后的结果转换为numeric类型，再调用ROUND()即可：

方法1：使用CAST显式转换

SELECT student_id, department_id,
       ROUND(CAST(PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY mark DESC) * 100 AS numeric), 2) AS percentage
FROM Students
ORDER BY department_id;

方法2：使用PostgreSQL风格的类型转换语法

SELECT student_id, department_id,
       ROUND((PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY mark DESC) * 100)::numeric, 2) AS percentage
FROM Students
ORDER BY department_id;

这两种写法都能得到和你原SQL一致的结果，同时避免函数不匹配的报错。

内容的提问来源于stack exchange，提问作者Meliodas Dragon