(Импала) Выбор наиболее распространенного значения в результатах поиска в поле "Подзапросы не поддерживаются в списке выбора"

Question

Я пытаюсь сделать агрегацию, которая принимает наиболее распространенное значение группы, например:

with t1 as (
    select
        id
        , colA
        , colB
    from some_Table
)
select 
    id
    , count(*) as total
    , max(colA) as maxColA
    , most_common(colB) -- this is what I'm trying to achieve
from t1
group by id

Вот что я пытался сделать:

with t1 as (
    select
        id
        , colA
        , colB
    from some_Table
)
select 
    id
    , count(*) as total
    , max(colA) as maxColA
    , (select colB, count(colB) as counts from t1 group by colB order by counts desc limit 1) as most_freq_colB_per_id
from t1
group by id

Тем не менее, это говорит мне AnalysisException: Subqueries are not supported in the select list, Как еще я могу это сделать?

Answer 1

Impala, насколько я знаю, не имеет встроенной функции агрегирования для расчета режима (статистическое название того, что вы пытаетесь вычислить).

Вы можете использовать два уровня агрегации. Ваш CTE ничего не делает, поэтому вы можете сделать:

select id, sum(total) as total, max(maxColA) as maxColA,
       max(case when seqnum = 1 then colB end) as mode
from (select id, colB, count(*) as total, max(colA) as maxColA,
             row_number() over (partition by id order by count(*) desc) as seqnum
      from sometable
      group by id, colb
     ) t
group by id;