spark-sqlCOALESCE()函数解析⽤法——多字段优先级选择⾮空字段作为值简介:
很多⼈知道ISNULL函数,但是很少⼈知道Coalesce函数,⼈们会⽆意中使⽤到Coalesce函数,并且发现它⽐ISNULL更加强⼤,其实到⽬前为⽌,这个函数的确⾮常有⽤,其实使⽤CASE WHEN THEN ELSE END 或者IF(column1 IS NULL,column2,column1)本⽂主要讲解其中的⼀些基本使⽤:
COALESCE()函数:
主流数据库系统都⽀持COALESCE()函数,这个函数主要⽤来进⾏空值处理,其参数格式如下:
COALESCE( expression,value1,value2……,valuen)
COALESCE()函数的第⼀个参数expression为待检测的表达式,⽽其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第⼀个⾮空表达式。
如果expression不为空值则返回expression;否则判断value1是否是空值,
如果value1不为空值则返回value1;否则判断value2是否是空值,
如果value2不为空值则返回value2;……以此类推, 如果所有的表达式都为空值,则返回NULL。
⽰例:
我们将使⽤COALESCE()函数完成下⾯的功能,返回⼈员的“重要⽇期”:
如果出⽣⽇期不为空则将出⽣⽇期做为“重要⽇期”,如果出⽣⽇期为空则判断注册⽇期是否为空,如果注册⽇期不为空则将注册⽇期做为“重要⽇期”,如果注册⽇期也为空则将“2008年8⽉8⽇”做为“重要⽇期”。实现此功能的SQL语句如下:
MYSQL、Spark、MSSQLServer、DB2:
SELECT
FName,
FBirthDay,
FRegDay,
COALESCE(FBirthDay,FRegDay,'2008-08-08')AS ImportDay
FROM T_Person
Oracle:
SELECT
FBirthDay,
FRegDay,
COALESCE(FBirthDay,FRegDay,TO_DATE('2008-08-08','YYYY-MM-DD HH24:MI:SS'))AS ImportDay
FROM T_Person
执⾏完毕我们就能在输出结果中看到下⾯的执⾏结果:
FName FBirthDay FRegDay ImportDay
Tom 1981-03-2200:00:001998-05-0100:00:001981-03-2200:00:00
Jim 1987-01-1800:00:001999-08-2100:00:001987-01-1800:00:00
Lily 1987-11-0800:00:002001-09-1800:00:001987-11-0800:00:00
Kelly 1982-07-1200:00:002000-03-0100:00:001982-07-1200:00:00
Sam 1983-02-1600:00:001998-05-0100:00:001983-02-1600:00:00
Kerry <NULL>1999-03-0100:00:001999-03-0100:00:00
Smith <NULL><NULL>2008-08-08
BillGates 1972-07-1800:00:001995-06-1900:00:001972-07-1800:00:00
这⾥边最关键的就是Kerry和Smith这两⾏,可以看到这⾥的计算逻辑是完全符合我们的
需求的。
COALESCE()函数可以⽤来完成⼏乎所有的空值处理,不过在很多数据库系统中都提供了它的简化版,这些简化版中只接受两个变量,其参数格式如下:
MYSQL:
IFNULL(expression,value)
MSSQLServer:
isnull的用法ISNULL(expression,value)
Oracle:
NVL(expression,value)
这⼏个函数的功能和COALESCE(expression,value)是等价的。
⽐如SQL语句⽤于返回⼈员的“重要⽇期”,如果出⽣⽇期不为空则将出⽣⽇期做为“重要⽇期”,如果出⽣⽇期为空则返回注册⽇期的值:
MYSQL:
SELECT
FBirthDay,
FRegDay,
IFNULL(FBirthDay,FRegDay)AS ImportDay
FROM T_Person
MSSQLServer:
SELECT
FBirthDay,
FRegDay,
ISNULL(FBirthDay,FRegDay)AS ImportDay
FROM T_Person
Oracle:
SELECT
FBirthDay,
FRegDay,
NVL(FBirthDay,FRegDay)AS ImportDay
FROM T_Person
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论