case when 配合 lateral view explode 实现不同维度指标的共同展示

select 
 b.types
,sum(show) as show 
,sum(click) as click
,sum(finish) as finish
from (
  select
   case when action = 'b_click' then '一'
        when action = 'c_click' then '二'
        when action = 'd_finish' then '三'
        when action = 'a' then '一,二,三' 
        end as type 
  ,count(case when action in ('a') then 1 end) as show 
  ,count(case when action rlike '_click$' then 1 end) as click
  ,count(case when action rlike '_finish$' then 1 end) as finish
  from (
    select 'a' as action 
    union all 
    select 'b_click' as action 
    union all 
    select 'c_click' as action 
    union all 
    select 'd_finish' as action 
    union all 
    select 'b_click' as action 
    union all 
    select 'c_click' as action 
    union all 
    select 'd_finish' as action 
  ) as a 
  group by 
   case when action = 'b_click' then '一'
        when action = 'c_click' then '二'
        when action = 'd_finish' then '三'
        when action = 'a' then '一,二,三' 
        end 
) as a
lateral view explode(split(type,',')) b as types 
group by 
 types

结果：

符合期望的结果，类型一、二、三的show数量均为1 （其实是表示三个分类的总的show数量），后两列分别是三个分类的click和finish数量。即：show的值没有类型types的维度，click和finish指标有类型types的维度。上面sql通过case when 配合 lateral view explode实现了不同维度指标的共同展示。