Using Grafana 7.0.6 with elasticsearch data source.
I have a number of queries, for each of which I’m interested in the count. I also have a number of derived metrics from these counts.
For example, lets say I groupby my created_at time field on a 1-day interval, and count the Total Count number of items on that day. Then, I have another field, e.g. Success Count with the same groupby, but with query success:true. Then I use an outer join transformation on created_at to join these two counts, and compute a new field called Success Rate using a transformation: Success Count / Total Count.
However, in addition to my 1-day interval, I also want to know the all-time aggregate. For Total Count and Success Count this is easy - I can just use the Reduce: Total transformation. However, this does not work for the Success Rate. Not only can I not pick and choose different reduces for different columns, even if I could, I’d need a Weighted Average which does not exist. I could first perform the Reduce, and then compute the metrics I need afterwards, but Reduce turns the columns into rows, meaning I can no longer compute an additional column based on my existing columns…
A second approach I’ve tried is grouping by a term, which would get me the aggregates for that term. However, for some reason that is beyond my understanding, Aliases do not work in this situation. This means that I cannot have a Total Count and Success Count column, because they are both still just called Count. If I then do an outer join on this term, it will just give me two identical columns (same name, and the values from whichever query was last). So I cannot compute the Success Rate grouped by term either.
Both of these situations seem like they should be trivial but neither seems possible. Am I missing something? What is the reason that these things are impossible?