I want the average data from the last hour in short if the current time is 2:30, the query should return the data from 1:00 to 2:00 and the average of 1:00 to 2:00 (which will be only one value (not changing).

time            data       Avg_data (There should be one output)
1/2/2022 1:01   1.2          1.5416
1/2/2022 1:02   1.3
1/2/2022 1:03   1.4
1/2/2022 1:04   1.1
1/2/2022 1:05   1.2
1/2/2022 1:07   1.2
1/2/2022 1:08   1.5
1/2/2022 1:09   1.7
1/2/2022 1:10   1.6
1/2/2022 1:11   1.5
1/2/2022 1:12   1.4
1/2/2022 1:13   1.8
1/2/2022 1:14   1.8
1/2/2022 1:15   1.8
1/2/2022 1:16   2.1
1/2/2022 1:17   2.9
1/2/2022 1:21   1.8
1/2/2022 1:22   1.9
1/2/2022 1:23   1.8
1/2/2022 1:24   1.5
1/2/2022 1:25   1.6
1/2/2022 1:26   1.6
1/2/2022 1:27   1.5
1/2/2022 1:28   1.5
1/2/2022 1:29   1.5
1/2/2022 1:30   1.3
1/2/2022 1:31   1.3
1/2/2022 1:32   1.4
1/2/2022 1:33   1.7
1/2/2022 1:34   1.8
1/2/2022 1:35   1.7
1/2/2022 1:36   1.5
1/2/2022 1:37   1.6
1/2/2022 1:38   1.3
1/2/2022 1:39   1.4
1/2/2022 1:43   1.7
1/2/2022 1:44   1.3
1/2/2022 1:45   1.4
1/2/2022 1:46   1.4
1/2/2022 1:47   1.1
1/2/2022 1:48   1.3
1/2/2022 1:49   1.2
1/2/2022 1:50   1.4
1/2/2022 1:51   1.3
1/2/2022 1:52   1.5
1/2/2022 1:53   1.5
1/2/2022 1:57   1.4
1/2/2022 1:58   1.2
1/2/2022 1:59   1.4
1/2/2022 2:00   1.3

My query looks something like

SELECT time, avg(data)
FROM exercise 
WHERE  
time >= (NOW() - INTERVAL '1 hour')
group by time,data;

CodePudding user response：

The main issue is your GROUP BY statement. time is unique across all values, but so is time combined with data. The group needs to specify the common value that all items in the group share. We can do that by truncating the time to the hour, then group by that.

The following shows the averages for just the last full hour:

SELECT date_trunc('hour', time) as Hour, avg(data) as Avg
FROM exercise 
WHERE time >= date_trunc('hour', now()) - INTERVAL '1 hour'
  AND time < (date_trunc('hour', now()))
GROUP BY date_trunc('hour', time);

For all hours except the current partial hour just use this filter:

WHERE time < (date_trunc('hour', now()))

UPDATE:

The data example from OP has changed significantly. The following fiddle demonstrates a fictious example dataset that is used for the explanation that follows:
https://www.db-fiddle.com/f/rqZ5rsgKFJBKufPsxmFzFX/3

Query #1

Show the averages of the data values for each hour of the day. We are using DATE_TRUNC to group the readings and compute the average, this implementation will consider the following arbitrary groupings:

• Group 01:00:
  01:00, 01:01, 01:59
• Group 02:00:
  02:00, 02:01, 02:59

SELECT date_trunc('hour', time) as Hour, avg(data) as Avg
FROM exercise 
GROUP BY date_trunc('hour', time);

To filter this to only the most recent completed hour, so like the original post:

if the current time is 2:30, ...return the average of 1:00 to 2:00

Then the following WHERE clause can be used 9as was shown above:

WHERE time >= date_trunc('hour', now()) - INTERVAL '1 hour'
  AND time < (date_trunc('hour', now()))

However to work with our current dataset, now() won't work and as PostgreSQL doesn't support variable declarations, it's out of scope to demonstrate further, if you ran that query, with the WHERE clause at 2022-01-26 2:30:00 then it would only return this:

It is important to understand these basics before implementing window functions or joining back onto the original results

Query #2

If you need the average in-line with the raw data, then we can join this result-set back onto the original query, or we can use a Window Function:

SELECT time
     , data
     , date_trunc('hour', time) as Hour
     , AVG  (data) 
       OVER (PARTITION BY date_trunc('hour', time)) as Avg
FROM exercise;

NOTE:
The partition value has been included in the output to make it visually obvious what value was used to group the records used in the window function.

You can apply the same WHERE clause as previously stated to this query to limit the results to satisfy the original requirement:

if the current time is 2:30, the query should return the data from 1:00 to 2:00 and the average of 1:00 to 2:00

SELECT time
     , data
     , date_trunc('hour', time) as Hour
     , AVG  (data) 
       OVER (PARTITION BY date_trunc('hour', time)) as Avg
FROM exercise
WHERE time >= date_trunc('hour', now()) - INTERVAL '1 hour'
  AND time < (date_trunc('hour', now()));

Query 3:

OP has asked a specific variation to this question in the comments:

Is there a way to get average from 1:01 to 2:00?

To do that we simply shift the time value by one minute, but the core underlying query is the same, including the WHERE clause:

SELECT time
     , data
     , date_trunc('hour', time - interval '1 minute') as "Hr 1min"
     , AVG (data) 
       OVER (PARTITION BY (date_trunc('hour', time - interval '1 minute'))) as Avg
FROM exercise ;

View on DB Fiddle

CodePudding user response：

This is the solution:

select avg("data") from yourtable
where "time">=(date_trunc('hour',NOW()::timestamp) - INTERVAL '1 hour')
and
"time"<=date_trunc('hour',NOW()::timestamp);

sqlfiddle was showing null as data gets old with time.. please check at your end.