非空查找函数: COALESCE 应用

语法: 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模式

转载于:https://my.oschina.net/u/3455048/blog/993821