使用 lateral view 与 explode 对数据进行聚合。

select id
,sum(cast(col='a' as int)) as cnt_a
,sum(cast(col='b' as int)) as cnt_b
,sum(cast(col in ('a','b') as int)) as cnt_total
from tbl
lateral view explode(array(ci_1,ci_2,ci_3,ci_4,ci_5)) tbl as col
group by id

hive中的一种方法是：

select ( (case when cl_1 = 'a' then 1 else 0 end) +
         (case when cl_2 = 'a' then 1 else 0 end) +
         (case when cl_3 = 'a' then 1 else 0 end) +
         (case when cl_4 = 'a' then 1 else 0 end) +
         (case when cl_5 = 'a' then 1 else 0 end)
       ) as count_a,
       ( (case when cl_1 = 'b' then 1 else 0 end) +
         (case when cl_2 = 'b' then 1 else 0 end) +
         (case when cl_3 = 'b' then 1 else 0 end) +
         (case when cl_4 = 'b' then 1 else 0 end) +
         (case when cl_5 = 'b' then 1 else 0 end)
       ) as count_b
from t;

为了获得总计数，我建议使用子查询并添加 count_a 以及 count_b .