LeetCode 1661：PostgreSQL中ROUND函数报错问题求助

问题描述 #

我针对LeetCode 1661题写的SQL解法在MySQL中可正常运行，但在PostgreSQL中执行ROUND(AVG(a2.timestamp - a1.timestamp),3)时触发报错：function round(double precision, integer) does not exist，提示需要显式类型转换。去掉ROUND函数后代码能正常执行，想了解报错原因及解决办法。

题目示例 #

输入 #

Activity表:
+------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+
| 0          | 0          | start         | 0.712     |
| 0          | 0          | end           | 1.520     |
| 0          | 1          | start         | 3.140     |
| 0          | 1          | end           | 4.120     |
| 1          | 0          | start         | 0.550     |
| 1          | 0          | end           | 1.550     |
| 1          | 1          | start         | 0.430     |
| 1          | 1          | end           | 1.420     |
| 2          | 0          | start         | 4.100     |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.500     |
| 2          | 1          | end           | 5.000     |
+------------+------------+---------------+-----------+

输出 #

+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |
+------------+-----------------+

解释 #

共有3台机器，每台运行2个进程：

• 机器0的平均处理时间：((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
• 机器1的平均处理时间：((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
• 机器2的平均处理时间：((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456

我的SQL代码 #

select 
   a1.machine_id, 
   ROUND(AVG(a2.timestamp - a1.timestamp),3) as processing_time 
from Activity a1
INNER JOIN Activity a2
ON a1.process_id = a2.process_id
WHERE a2.activity_type = 'end' 
  AND a1.activity_type = 'start' 
  and a1.process_id = a2.process_id 
  and  a1.machine_id = a2.machine_id
GROUP BY a1.machine_id

问题原因与解决方法 #

报错原因 #

PostgreSQL与MySQL的ROUND函数实现存在差异：

• MySQL支持ROUND(double, int)的重载形式，可直接对双精度浮点数指定保留小数位数。
• PostgreSQL中，double precision类型的数值没有对应接受第二个整数参数的ROUND重载，仅提供ROUND(double precision)（默认保留0位小数）和ROUND(numeric, integer)两种形式。而AVG()函数返回的结果是double precision类型，直接传入ROUND就会触发函数不存在的报错。

解决办法 #

将AVG的结果显式转换为numeric类型后再调用ROUND即可，有两种写法：

1. 使用PostgreSQL专属的类型转换语法：

select 
   a1.machine_id, 
   ROUND(AVG(a2.timestamp - a1.timestamp)::numeric, 3) as processing_time 
from Activity a1
INNER JOIN Activity a2
ON a1.process_id = a2.process_id
WHERE a2.activity_type = 'end' 
  AND a1.activity_type = 'start' 
  and a1.process_id = a2.process_id 
  and  a1.machine_id = a2.machine_id
GROUP BY a1.machine_id

2. 使用标准SQL的CAST函数：

ROUND(CAST(AVG(a2.timestamp - a1.timestamp) AS numeric), 3)

另外，代码中JOIN条件和WHERE条件存在重复，可简化为：

select 
   a1.machine_id, 
   ROUND(AVG(a2.timestamp - a1.timestamp)::numeric, 3) as processing_time 
from Activity a1
INNER JOIN Activity a2
ON a1.machine_id = a2.machine_id 
   AND a1.process_id = a2.process_id
   AND a1.activity_type = 'start' 
   AND a2.activity_type = 'end'
GROUP BY a1.machine_id

内容的提问来源于stack exchange，提问作者Burak Özalp