非空查找函数: COALESCE 应用

原创
2017/06/20 16:35
阅读数 61

语法: COALESCE(T v1, T v2,…)

返回值: T

说明: 返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL

示例:

hive> select COALESCE(null,'100','50′) from lxw_dual;

100

实际应用

代码

insert overwrite table user_profile_db.t_population_before_apply_city partition(datepartition='${day_01}')
select 
a.cid,
COALESCE(b.city_name,a.population_apply_city) as city_name
from
(select *
from
user_profile_db.t_population_apply_city 
where datepartition='${day_01}'
)a
left join
(select cid,
city_name
from
(select *,
ROW_NUMBER() over(partition by cid 
                             order by create_date asc) as rank
from
(select a.rank_apply,
b.*
from
(
select *
from
user_profile_db.t_population_apply_city
where datepartition='${day_01}'
) a
inner join
(
select *
from
user_profile_db.t_population_cnt_before_after_apply_tmp 
where datepartition='${day_01}'
) b
on
a.cid=b.cid
) a
) b
where rank=rank_apply-1 
and datediff(cnt_apply_time,create_date)<30
and datediff(cnt_apply_time,create_date)>=0
)b
on 
a.cid=b.cid
;

如果b.city_name is null 则取a.population_apply_city的值 可以理解为相当于case when模式

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部