I am wrestling with a query around getting a max value of a count per hour up to each. I will explain with an example...
Hour 1 - 400 visitors
Hour 2 - 200 visitors
Hour 3 - 250 visitors
Hour 4 - 100 visitors
Hour 5 - 700 visitors
Hour 6 - 900 visitors
I would like to calculate the max for each hour UP TO that hour. So for Hour 1, max would be 400. For Hour 2, the max will also be 400 since that is the max up to hour 2 (between 400 and 200). The max would be 400 for Hours 3 and 4 as well. Hour 5 would have a max of 700 visitors. Hour 6 would have a max of 900 visitors.
The structure of data I'm working with is...
Hour Visitor_Count
1 400
2 200
...
I would like the max per hour up to each hour to be column 3.
The one thing I've managed to do is calculate the max manually for each hour
| eventstats sum(eval(if(hr<=7,visitor_count,null()))) as max_for_hr_7
This works, but creates a column for each hour, and replicates the values for each column down all of the rows, so not ideal...
Hour Visitor_Count max_for_hr_1 max_for_hr_2 max_for_hr_3 ....
Any thoughts on how I could achieve adding one additional column with the max of visitor count up to each hour?
Thank you in advance!
