I have a “measurement” with this structure:
time «-» Count «-» Successful
xxxxx «-» integer «-» boolean
I want to query the success rate. That is, the Sum of Counts where Successful = False / the Sum of Counts where Successful = *.
How can I query that, if at all possible? I’m not figuring this one out… Having a nasty suspicion this is not possible.
Regardless of whether that is possible or not, I came to the conclusion that I created a “wrong” data structure and I’m changing it to:
time «-» SuccessfulCount «-» FailedCount
@paulo Glad you figured out how to construct the data!
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 unsuccessful_count.
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.
Hello,
I did it with Kapacitor like explain:
It was quite straightforward but this add the need of Kapacitor.
The rule then put back the sample value into a new measurement.
Fabrice
Well done Fabrice. Care to share any more information? As I describe in the linked post, my preference was Kapacitor, but its infuriating inability to replay historical data in a predicable way killed that idea for me, hence the CQ route.
