sqlnewrelicnrql

NRQL percentage of one subset over another

I have a great big eventing table with lots of different types of events, and I'm working on a New Relic graph to show the percentage of one kind of event to another kind of event. (NOT percentage over the total row count in the table).

So in this example Segment table:

id  name
1   foo      
2   bar
3   baz
4   bar

I'd get the foo events with a query like this:

select count(*) from Segment where name='foo'

My question: how would I get the percentage of foo events to bar events? I've poked around trying to join two queries that save each "as" a particular name, but no luck yet. Does anyone know if/how we can make Common Table Expressions for use in a query/graph?

Solution

  • You can use conditional aggregation:

    select (sum(case when name = 'foo' then 1.0 else 0 end) /
        sum(case when name = 'bar' then 1.0 else 0 end)
       ) as foo_bar_ratio
from segment;

    EDIT:

    Perhaps this will work in NRLQ:

    select filter(count(*), where name = 'foo') / filter (count(*), where name = 'bar') as foo_bar_ratio
from segment;