I was also caught out by the fact that this sort of historical statistics query is difficult to achieve out of the box. It’s a pity you need to design the schema with this sort of application in mind.
Like you, I was recording events in that timeline sort of style. Rather than changing that to suit my stats needs, I created a series of Continuous Queries that rip through the event data and create another measurement with the numbers necessary to generate the stats, grouped by periods.
So in the case of your original measurement, I’d create a CQ with a query like
SELECT count(Count) AS successful_count INTO stats FROM original WHERE Successful = true GROUP BY time(4h) fill(0), and an equivalent one for
You can then easily query the
stats measurement to calculate
successful_count/unsuccessful_count or whatever.
See Shift timestamp returned by SELECT query for a bit more information on my particular solution.