Need a query for accumulated values

Question

I need query for accumulated values from particular column with specific time interval.

time         |      value  
--------------------------  
2020-02-26 09:30:00 |  5  
2020-02-26 09:31:00 |  3  
2020-02-26 09:32:00 |  4  
2020-02-26 09:33:00 |  5  
2020-02-26 10:31:00 |  5  
2020-02-26 10:32:00 |  2  
2020-02-26 10:34:00 |  4  
2020-02-26 10:36:00 |  6  

Fetch the data from table interval wise(15m,1hour,1 day) & calculate the max value and finally calculated the accumulated(1,2,3 values should be 1,3,6).

For Ex : 1hour interval then the result was:

time         |      value  
--------------------------  
2020-02-26 09:00:00 |  5   
2020-02-26 10:00:00 |  6  

and accumulated result was:

time         |      value  
--------------------------  
2020-02-26 09:00:00 |  5   
2020-02-26 10:00:00 |  11 

Answer 1

You can combine a GROUP BY to find the max() value and a window function:

select date_trunc('hour', "time") as "time",
       sum(max(value)) over (order by date_trunc('hour', "time"))
from the_table
group by date_trunc('hour', "time")
order by date_trunc('hour', "time")

This works, because the window function is applied after the GROUP BY. Using an order by in the window definition turns a "regular" sum into a running total.

---

To round the timestamp to other intervals, see these two questions on Stackoverflow

• What is the fastest way to truncate timestamps to 5 minutes in Postgres?
• postgresql date_trunc to arbitrary precision?