主要分为两种:简单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