Is it possible to build this type of query? GroupBy dimension and custom interval.

Hello, is it possible to build a query that would return values in the following manner:

{
dimension_value_1: {
interval_1: {
aggregation_1: metric
},
interval_2: {
aggregation_1: metric
}
},
dimension_value_2: {
interval_1: {
aggregation_1: metric
},
interval_2: {
aggregation_1: metric
}
},
dimension_value_n: {
interval_1: {
aggregation_1: metric
},
interval_2: {
aggregation_1: metric
}
}
}

``

Basically, a topN or GroupBy over a single dimension, with aggregations over multiple custom intervals.

Currently, I am performing a topN query over each different custom interval, and pairing the returned values to compute a different aggregate. However, if Druid was able to compute the multiple interval aggregates in a single query and do the pairing, it would help the performance of my operation a lot; the cardinality of the dimension is very high.

Thank you in advance for any help.

Hey Carlos,

This will be possible using filtered aggregators on time in a future release. Currently, filtered aggregators do not support filtering on time. For now, you could work around this by ingesting time as a dimension as well, and filtering on it using the “bound” filter. If you use ISO8601 time then you should be able to express an interval as a lexicographic bound.

Thank you Gian.

Would the future solution be more performant than the bound filter?

Probably it would be. If you do go with the bound filter workaround, you can speed it up by reducing the cardinality of the time strings (perhaps by truncating on hour if you only need hourly granularity). This is similar to what Druid’s granularitySpec does natively for the time column.

Thank you Gian, I will go with your recommendation once I re-do the segments.