主要分为两种:简单case函数和搜索case函数
简单case函数
case column
when 'A' then 'a'
when 'B' then 'b'
else 0 end;
搜索case函数
case when column='A' then 'a'
when column='B' then 'b'
else 0 end;
简单case函数语法比较简洁,但是较搜索case函数而言,功能上会有所限制.下面重点总结下搜索case函数的常见用法.
所有case…when…用法的本质都是:给已知数据重新赋值形成新的字段
需要注意的:不加else条件,会默认返回null (hive中必须要加else语句)
常见用法1 : 重新分组
table A的数据如下:
country |
flower_classes |
中国 |
2000 |
印度 |
1000 |
法国 |
3000 |
英国 |
2000 |
意大利 |
2500 |
新西兰 |
3000 |
美国 |
1500 |
按照洲的名称统计花的种类,计算sql如下:
select sum(flower_classes) as flower_classes
,case when country = '中国' then '亚洲'
when country = '印度' then '亚洲'
when country = '法国' then '欧洲'
when country = '英国' then '欧洲'
when country = '意大利' then '欧洲'
when country = '新西兰' then '大洋洲'
end as continent
from table A
group by
case when country = '中国' then '亚洲'
when country = '印度' then '亚洲'
when country = '法国' then '欧洲'
when country = '英国' then '欧洲'
when country = '意大利' then '欧洲'
when country = '新西兰' then '大洋洲'
end;
常见方法2 : 与聚合函数一起使用
table B的数据如下:
city |
sex |
population |
武汉 |
男 |
1000 |
武汉 |
女 |
2000 |
长沙 |
男 |
500 |
长沙 |
女 |
600 |
上海 |
男 |
1000 |
上海 |
女 |
2000 |
北京 |
男 |
1500 |
北京 |
女 |
2000 |
深圳 |
男 |
3000 |
深圳 |
女 |
4000 |
分别统计男女的人口数量,计算sql如下: |
|
|
select
from
sum(case when sex='男' then population else 0 end) as male_population
,sum(case when sex='女' then population else 0 end) as
female_population
from table B
常见用法3:与聚合函数一起使用
table C的数据如下:
name |
club_id |
club_name |
main_club_id |
林一 |
1 |
羽毛球 |
1 |
小白 |
2 |
篮球 |
0 |
小白 |
3 |
足球 |
1 |
大黄 |
2 |
篮球 |
1 |
大黄 |
3 |
足球 |
0 |
大黄 |
4 |
游泳 |
0 |
小麦 |
4 |
游泳 |
1 |
数据解释:如果此人只加入了1个社团,其main_club_id设为1;如果此人加入的社团大于1个,主社团的main_club_id设为1,其他为0. |
|
|
|
要求统计:对于只加入1个社团的人,其club_id;加入多个社团的人,其主社团的club_id.统计sql如下: |
|
|
|
select
case when count(club_id)=1 then max(club_id)
else max(case when main_club_id=1 then club_id end)
end as main_club_id
from table C
group by name
参考链接