Example 1:
Input:
Activity table:
+------------+------------+---------------+-----------+
| 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 |
+------------+------------+---------------+-----------+
Output:
+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0 | 0.894 |
| 1 | 0.995 |
| 2 | 1.456 |
+------------+-----------------+
Explanation:
There are 3 machines running 2 processes each.
Machine 0's average time is ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
Machine 1's average time is ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
Machine 2's average time is ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456个字符
我的解决方案没有ROUND就被接受了，我不知道为什么会出现这个错误
“函数round(double precision, integer) does your text not exist LINE 2: select a1.machine_id,(SELECT ROUND(AVG(a2.timestamp - a1.tim... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts."
另外，我想提一下这个答案在MySQL中被接受，但在PostgreSQL中不起作用
1条答案
按热度按时间moiiocjp1#
给ROUND的第一个参数不应该是float。
select round((5.0/3)::float,3);将失败，并且select round((5.0/3)::decimal,3);将生成预期的1.667。要将此应用于查询，请参阅：DBFIDDLE
P.S.你可以使用
numeric而不是decimal，参见：Postgres: No difference，这解释了在postgresql中这两种类型没有区别。