部分常用Spark函数

【写在前面】

观远数据计算引擎基于Spark搭建，因此Spark支持的SQL函数均可以在观远平台上使用，Spark官方文档。

以下罗列了部分常用函数。

【适用场景】

适用于Guan-index数据集、ETL输出数据集、Excel/CSV 数据集。

对于直连数据库的数据集，由于各类型数据库对SQL标准支持有差异。

---

数值

函数	含义	用法	说明	示例
ABS	绝对值	ABS([字段])	返回绝对值	ABS(-3),返回3
CEIL	向上取整	CEIL(数值/[字段])	返回不小于数值/[字段]的最小整数	CEIL(4.12),返回5
FLOOR	向下取整	FLOOR(数值/[字段])	返回不大于数值/[字段]的最大整数	FLOOR(4.12),返回4
INT	转换为整型数值	INT([字段])	返回不大于数值/[字段]的最大整数 。 将String   格式转化为数值/[字段]类型	INT(1.5)返回1
LOG	取对数	LOG(数值/[字段]A,[数值/[字段]B])	以A为底，求B的对数，默认为自然对数底	LOG(2,4),返回2.0
POW	求次方	POW(数值/[字段]A,数值/[字段]B)	求数值/[字段]A的数值/[字段]B次方	POW(4，2),返回16.0
RAND	随机数	RAND()	返回大于0小于1的随机小数	RAND(),返回随机数
ROUND	四舍五入	ROUND(数值/[字段]A[,整数D])	返回数值/[字段]A四舍五入到小数点后D位。不填时为0	ROUND(4.12，1),返回4.1
SQRT	开平方	SQRT(数值/[字段])	求数值/[字段]的根号，需要大于等于零	SQRT(4),返回2.0

字符串

函数	含义	用法	说明	示例
CONCAT	拼接字符串	CONCAT([字段1], [字段2]...)	返回按照顺序连接的字符串	CONCAT([货品编号],   [类型编号])，返回货品编号和类型编号联结后的字符串
FORMAT_STRING	格式化输出	format_string(指定格式,[字段])	以指定形式对字符串进行格式化输出	format_string('%06d',82343) ，得到   082343
INSTR	查找字符串位置	INSTR([文本字段], [查找字段])	返回查找字符串在文本[字段]的值中第一次出现时的位置，结果是大于0的整数，如果找不到则返回0	INSTR([名字],   ',')，返回','在名字字段中第一次出现时的位置
LENGTH	字符串长度	LENGTH([字段])	返回字符串的长度	LENGTH([货品名])，返回货品名的长度
LOWER	小写形式	LOWER([字段])	返回表达式或[字段]值全部小写形式的字符串	LOWER(ABC)，返回abc
REGEXP_EXTRACT	字符串正则表达式解析	REGEXP_EXTRACT(字符串, 正则表达式, 索引)	返回字符串正则表达式解析结果，'索引'是返回结果(0表示返回全部结果，1表示返回正则表达式中第一个匹配结果)	REGEXP_EXTRACT([商品ID], '\\d+',   0)，返回商品ID中的数字部分
REGEXP_REPLACE	字符串正则表达式替换	REGEXP_REPLACE(字符串A, 正则表达式,   字符串B)	返回将字符串A中符合正则表达式的部分替换成字符串B后的结果	REGEXP_REPLACE([货品名], '\\d＋',   '')，将货品ID中数字部分替换成空字符串
REPEAT	重复字符串	REPEAT([字段], 数值)	返回字符串重复对应数值次数后的新字符串结果	REPEAT([货品名],   2)，返回货品名重复2次得到字符串，如[货品名][货品名]
REVERSE	倒转字符串	REVERSE([字段])	返回字符串倒转后的新字符串结果	REVERSE([类型编号])，输入ABC返回CBA
SUBSTR	截取字符串	SUBSTR([字段], 起始位置[, 长度])	返回从起始位置起对应长度的字符串的子字符串，长度为可选项；负数索引代表从结尾向前计算字符串个数	SUBSTR([商品类型],   4)，返回商品类型的索引为4起至末尾的子字符串；SUBSTR([商品类型], -4)，返回商品类型最后4个字符串
TRIM	去除空格	TRIM([字段])	去除表达式或[字段]中数据两边的空格	TRIM(" ABC   " ), 返回ABC
UPPER	大写形式	UPPER([字段])	返回表达式或[字段]值全部大写形式的字符串	UPPER("abc"),返回ABC

聚合统计

函数	含义	用法	说明	示例
AVG	平均值	AVG([字段])	返回[字段]所有值的平均值，只适用于数值[字段] NULL值不会计算	AVG(销售额)，返回销售额[字段]对应的所有非空值的平均值
COUNT	计数	COUNT([字段])	返回表达式所有有效[字段]的数据条目数 NULL值不会计算	COUNT(销售额)，返回销售额[字段]对应的所有非空值的数据条目数
COUNT_DISTINCT	去重计数	COUNT(DISTINCT([字段]))	去重计数，返回表达式所有有效[字段]的不同数据条目数，空值不会计算	COUNT(DISTINCT(销售额))，返回销售额[字段]对应的所有非空值的不同数据条目数
MAX	最大值	MAX([字段])	返回表达式或数值[字段]中的最大值，只适用于数值字段	MAX(销售额)，返回销售额字段对应值的最大值
MIN	最小值	MIN([字段])	返回表达式或数值[字段]中的最小值，只适用于数值字段	MIN(销售额)，返回销售额字段对应值的最小值
PERCENTILE	中位数/百分位数	PERCENTILE([字段], 百分位)	求数值类型的中位数及百分位数, 传入参数为数值   第二个参数范围是0到1, 例如0.1，0.25，0.75，0.95	PERCENTILE([数学考试分数],   0.5)，返回考试分数的中位数
STDDEV	样本标准差	STDDEV([字段])	返回样本标准差	STDDEV([销售额])
STDDEV_POP	总体标准差	STDDEV_POP([字段])	返回总体标准差	STDDEV_POP([销售额])
SUM	求和	SUM([字段])	返回表达式或数值[字段]所有值的合计，只适用于数值[字段] Null值不会计算	SUM(销售额)，返回销售额[字段]对应的所有非空值的总和
VAR_POP	总体方差	VAR_POP([字段])	返回总体方差	VAR_POP([销售额])
VARIANCE	样本方差	VARIANCE([字段])	返回样本方差	VARIANCE([销售额])

非聚合统计

函数	含义	用法	说明	示例
GREATEST	对比最大值	GREATEST([字段1], [字段2]……)	对比同行中各列的值，返回最大的值	GREATEST([部门1销售额],   [部门2销售额])，返回该行中部门1销售额与部门2销售额两个字段中的最大值
LEAST	对比最小值	LEAST([字段1], [字段2]……)	对比同行中各列的值，返回值最小的	LEAST([部门1销售额],   [部门2销售额])，返回该行中部门1销售额、部门2销售额两个字段中的最小值

逻辑

函数	含义	用法	说明	示例
CASE WHEN	判断函数	CASE WHEN(表达式1) THEN 结果1 WHEN (表达式2) THEN 结果2 …… ELSE 结果n END	满足表达式则返回对应结果，都不满足返回ELSE后的默认结果 最后必须有END关键词	CASE WHEN([成绩]>=60 and   [成绩]<70) THEN '及格' WHEN ([成绩]>=70 and [成绩]<80) THEN '中' WHEN ([成绩]>=80 and [成绩]<90) THEN '良' WHEN ([成绩]>=90) THEN '优' ELSE '不及格' END
COALESCE	返回非空值	COALESCE([字段1], [字段2], '默认')	返回一组[字段]中的第一个非空值； 如果所有值都为NULL，那么返回NULL	COALESCE([名字1], [名字2],   '匿名'); 返回参数中的第一个非空名字，若都为空，返回‘匿名’
IF	判断函数	IF(表达式,结果1,结果2)	满足表达式返回结果1，否则返回结果2	IF([成绩]>80,'合格','不合格')