观远 BI

部分常用Spark函数

创建于 2022-11-03 / 最近更新于 2024-01-29 / 10449
字体: [默认] [大] [更大]

【写在前面】

观远数据计算引擎基于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,'合格','不合格')

22 人点赞过