hive 常用函数总结

1.选择的函数

• case when ：if… else if …else if…else

case 列名
when 列值1 then 返回值1
when 列值2 then 返回值2
when 列值2 then 返回值3
[esle 其他情况 ]
end

-- 1
select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  sum(case sex when '女' then 1 else 0 end) female_count
from 
  emp_sex
group by
  dept_id;
  
--2 
select case author
            when 'tom' then 'good',
            when 'bob'then 'nice',
            else ‘do not know’
             end
from test
--3.

select
  a.avid,original_sum - lite_sum diff,original_sum,lite_sum,                                                      
 case when original_sum!=lite_sum then concat(round(abs(original_sum-lite_sum)/original_sum,2)*100