Databricks SQL IsNull Statement

Question

我是新来的Databricks和运行的语法问题与我的TSQL习惯。我使用砖/Azure。我正在寻找合适的方法来编写以下Databricks SQL。不是每个公司都有三年的收入，我希望用0代替空值。

co_family表有一个具有global_key (primary)的列，其中包含各种属性。Main_revenue表有一个global_key (primary)列，用xtab格式显示最近五年的收入。

%sql
Select
f.global_key as glb_num
,f.global_name as glb_name
,IsNull(r.tm2016, 0) as tm2016
,IsNull(r.tm2017, 0) as tm2017
,IsNull(r.tm2018, 0) as tm2018
FROM co_family as f -- Company Structure
Left Join main_revenue as r -- Revenue
On f.global_key = r.global_key

Answer 1

Databricks tsql支持Coalesce

%sql
Select
f.global_key as glb_num
,f.global_name as glb_name
,coalesce(r.tm2016, 0) as tm2016
,coalesce,(r.tm2017, 0) as tm2017
,coalesce,(r.tm2018, 0) as tm2018
FROM co_family as f -- Company Structure
Left Join main_revenue as r -- Revenue
On f.global_key = r.global_key