0

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 

1 Answer 1

0

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

1
  • thanks for answer, taken idea from your query.
    – Avz Vicky
    Commented Feb 27, 2020 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.