SQL question: multiple aggregations in one query


#1

Our product often needs to perform multiple aggregations on different columns, but on the same core result set (i.e. same where clause). We often run 5-10 queries per page that are counts/group by’s on various columns, that look something like this:

select approx_count_distinct(id) as language_count, UNNEST(language_codes) as lang
from stories where language_codes is not null
AND ('music' = ANY interests)                /* SAME */
AND (birth_year < 1996)                      /* SAME */
AND (category_id = '123')                    /* SAME */
AND (dma = '501')                            /* SAME */
group by lang;
select approx_count_distinct(id) as culture_count, UNNEST(cultures) as culture
from stories where cultures is not null
AND ('music' = ANY interests)                /* SAME */
AND (birth_year < 1996)                      /* SAME */
AND (category_id = '123')                    /* SAME */
AND (dma = '501')                            /* SAME */
group by culture;

As we can see, the stuff in the where clause noted with SAME is common to both queries, but one query is getting language stats, and the other is getting culture stats. I was wondering if there was a way we could do all of this in one query, so we only pay the price of finding/loading those common rows once?

Thanks in advance.
BP


#2

Hi,

Hmm at first blush I don’t think so. What would you group by? Not even going to think about the double UNNEST potential cardinalities.

Regards


#3

Yeah, that was my assumption too, but I wasn’t sure if there was some SQL magic that I just wasn’t aware of to make this work. You can do this sort of thing in Elasticsearch.