Nested groupBy to split on count metric

Hi there!

I need to create a graph that shows quantity of users per count of pageviews done for a given url. For example, for a url “blabla.com/hi” I want to know how many users did only one pageview, how many did 2, how many did 3… and so on.

I figured that I could achieve that with a nested group by query like this one:

{

“queryType”: “groupBy”,

“dataSource”: {

“type”: “query”,

“query”: {

“queryType”: “groupBy”,

“dataSource”: “someSource”,

“granularity”: “all”,

“dimensions”: [“userdimension”],

“aggregations”: [

{ “type”: “longSum”, “name”: “partial_count”, “fieldName”: “count” }

],

“filter”: { “type”: “selector”, “dimension”: “url”, “value”: “blabla.com/hi” },

“intervals”: [ “2016-12-01/2017-01-05” ]

}

},

“granularity”: “all”,

“dimensions”: [“partial_count”],

“aggregations”: [

{ “type”: “count”, “name”: “count”}

],

“intervals”: [ “2016-12-01/2017-01-05” ]

}

However the second group by on field “partial_count” (which is the metric of the previous group by) doesn’t work, that field isn’t splitted per partial_count value. Only one field is returned with a total count.

Am I missing something? Is is possible to split on a metric on a nested group by?

Thanks!

Hey Federico,

Does this work with groupBy v2? I think it should work in master (with v2) but I’m not totally sure if it works in 0.9.2 or not. (By “this” I mean “an outer query that groups on a numeric field from a subquery”). It definitely doesn’t work with groupBy v1.

Worked as a charm :slight_smile: thanks a lot !

Hm, I actually tried a very similar query, and then the exact query from above on a test datasource, and wasn’t able to get it to group on the numeric field from the subquery. What I get as an answer is this:

[ {

“version” : “v1”,

“timestamp” : “2017-04-01T00:00:00.000Z”,

“event” : {

“count” : 139513,

“partial_count” : null

}

  • Connection #0 to host druid1003.eqiad.wmnet left intact

} ]

So it seems to be ok with using partial_count in the “dimensions” of the outer query, it doesn’t throw an error. But it just sees it as null. Any thoughts? Anyone else having that on 0.9.2 or just me? We just upgraded from 0.9.0 to 0.9.2 thinking it would solve this issue. We would love to go to latest but it’s more complicated with Java 8.