Question 276206 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL LEETCODE 1661. Average Time of Process per Machine

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

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

My solution was accepted without ROUND, I don’t have any idea why I am getting this error

"function 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."

Also, I want to mention this answer was accepted in MySQL, but it is not working on PostgreSQL

Tags:

2

Answers


  1. 0

    The error message is because PostgreSQL’s ROUND function accepts two arguments: a numeric or double-precision value and the number of decimal places to round to. You’re using the ROUND function with only one parameter in your query.

    ROUND(AVG(a2.timestamp - a1.timestamp), 3)

    To make it work with PostgreSQL, use the ROUND function with two parameters, as shown below.

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

    The ::numeric specifies that the result should be transformed to a numeric data type with three decimal places.

    SELECT 
   a1.machine_id, 
   ROUND(AVG(a2.timestamp - a1.timestamp), 3)::numeric 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
    Login or Signup to reply.
  2. 0

    The first parameter given to ROUND should not be a float.

    select round((5.0/3)::float,3); Will fail, and

    select round((5.0/3)::decimal,3); Will produce the expected 1.667.

    For applying this to your query see: DBFIDDLE

    P.S. Instead of decimal you could have used numeric, see: Postgres: No difference, which explains that there is no diffence for these two types in postgresql.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search