TopN with specified dimensions

Hi, is it possible to execute a TopN query where I explicitly state the dimensions I want in the query?

I know I could simply do this-
SELECT COUNT(*) FROM “table” GROUP BY dimension WHERE dimension IN (‘B’,‘C’,‘A’)

What I really want is something like this-
SELECT COUNT(*) FROM “table” GROUP BY dimension IN (‘B’,‘C’,‘A’)

Ideally it would have these properties:

  • resulting rows would correspond to order of the specified dimensions (B,C,A).
  • hopefully this would be very efficient to compute, because we’re not considering all possible values of dimension, just the 3 we specified here.

I thought about breaking out the specific dimensions I want and placing them into multiple aggregations without a group by like so-
SELECT CASE WHEN dimension = ‘A’ THEN COUNT() END AS “A”, CASE WHEN dimension = ‘B’ THEN COUNT() END AS “B”

Unfortunately it seems case/when/then functionality is only supported in group by queries, which puts me back at square one =)

Thanks.

You can use filtered aggregation.

SELECTSUM(CASE WHEN dimension = ‘B’ THEN 1 ELSE 0 END),
SUM(CASE WHEN dimension = ‘C’ THEN 1 ELSE 0 END),
SUM(CASE WHEN dimension = ‘A’ THEN 1 ELSE 0 END)
FROM table

Morning - just following on from Matt a bit (!) to see if that worked… but also could you expand a little on what it is about the WHERE version that makes it unusable?

Hi Matt and Peter-

The filtered aggregation does work for me, thank you.

Peter-

My understanding of topn is that it will consult many bitmap indexes in the dimension to find the topn values (which can be approximate depending on cardinality, zipfian-ness, etc…). However for this particular query access pattern use case I have, I know the specific set of dimension values ahead of time.

So I would like to express the query in a way that only accesses the specific bitmaps for dimension values ‘B’,‘C’,‘A’ and not others. The filtered aggregation method does this and–as I understand it–has the added benefit that it won’t under count due to issues of high cardinality.