1、CASE WHEN
1.1、CASE WHEN
语法
case when 条件表达式
then 条件成立,结果1
else 条件不成立,结果2
end
1.2、单个条件
1.2.1、CASE WHEN … THEN … END
结构
如果不写ELSE,那么除了符合WHEN** **条件之外的数据,其他数据默认为NULL
spark-sql> select
> user_id,
> name,
> case when address='shanghai' then '上海' end as address
> from test.test8;
1 xiaoming 上海
2 xiaolan NULL
Time taken: 0.219 seconds, Fetched 2 row(s)
1.2.2、case when…then…else…end
结构
spark-sql> select
> user_id,
> name,
> case when address='shanghai' then '上海' else '其他' end as address
> from test.test8;
1 xiaoming 上海
2 xiaolan 其他
Time taken: 0.396 seconds, Fetched 2 row(s)
1.3、多个条件
1.3.1、CASE WHEN … THEN … WHEN … THEN … ELSE END
结构
spark-sql> select
> user_id,
> name,
> case when address='shanghai' then '上海'
> when address='beijing' then '北京'
> else '其他'
> end as address
> from test.test8;
1 xiaoming 上海
2 xiaolan 北京
Time taken: 4.57 seconds, Fetched 2 row(s)
1.3.2、CASE WHEN嵌套结构
case when…then…case when…then…else…end when…then…case when…then…else…end else…end
结构:
spark-sql> select
> user_id,
> name,
> case
> when name='xiaoming'
> then
> case when address='shanghai' then '上海' else '其他' end
> when name='xiaolan'
> then
> case when address='beijing' then '北京' else '其他' end
> else '其他' end as address
> from test.test8;
1 xiaoming 上海
2 xiaolan 北京
3 xiaohong 其他
Time taken: 1.072 seconds, Fetched 3 row(s)
2、IF
语法
2.1、IF
语法
if(条件表达式,条件成立-结果1,条件不成立-结果2)
2.2、单个条件
if(条件,结果1,结果2)结构:
spark-sql> select
> user_id,
> name,
> address,
> if(address='shanghai','上海','其他') as address_name
> from test.test8;
1 xiaoming shanghai 上海
2 xiaolan beijing 其他
3 xiaohong guangzhou 其他
Time taken: 11.957 seconds, Fetched 3 row(s)
2.3、多个条件
2.3.1、IF
嵌套结构
if(if(条件, 结果1, 结果2), 结果1, 结果2)
spark-sql> select
> user_id,
> name,
> address,
> if(name='xiaoming',if(address='shanghai','上海','其他'),'其他') as address_name
> from test.test8;
1 xiaoming shanghai 上海
2 xiaolan beijing 其他
3 xiaohong guangzhou 其他
Time taken: 5.452 seconds, Fetched 3 row(s)
2.3.2、IF
和CASE WHEN
混合使用
if(条件, case when…then…end, 结果2)结构
spark-sql> select
> user_id,
> name,
> address,
> if(name='xiaoming',
> case when address='shanghai' then '上海' else '其他' end,
> '其他') as address_name
> from test.test8;
1 xiaoming shanghai 上海
2 xiaolan beijing 其他
3 xiaohong guangzhou 其他
Time taken: 12.791 seconds, Fetched 3 row(s)