常用函数列表

观小智发表于:2020年02月05日 11:36:44更新于:2020年09月17日 19:15:28

本文档中所列的函数适用于Guan-index数据集、ETL输出数据集、Workbench导入的数据集、Excel/CSV 数据集。 对于直连数据库的数据集,由于各类型数据库对SQL标准支持有差异,因此不保证本文档所列函数在该情形下可以正常使用。

类型

函数含义用法说明示例
窗口函数AVG平均值(窗口函数)AVG([字段]) OVER (PARTITION BY   ([开窗字段]))返回[字段]在同一[开窗字段]范围内的平均值
    NULL值不会计算
返回大区平均来客数:
    AVG([来客数]) over (partition by [大区] )
窗口函数COUNT      计数(窗口函数)COUNT([字段]) OVER (PARTITION BY   ([开窗字段]))返回[字段]在同一[开窗字段]范围内的数据条目数
    NULL值不会计算
每个大区的门店数:
    COUNT([店铺名称]) over (partition by [大区] )
窗口函数DENSE_RANK有并列,无占位的排序DENSE_RANK() OVER(ORDER BY   [排序字段])与 rank 函数类似,dense_rank   函数在生成序号时是连续的。
    dense_rank 函数出现相同排名时,将不跳过相同排名号
    排序结构一般是:1,2,2,3,4,4,5……
门店内各单品按照营业额从大到小排序,有并列排名:
    dense_rank() over (partition by [店名] order by [实际营业额] desc)
窗口函数FIRST取第一行的值FIRST([字段]) OVER(ORDER BY   [排序字段])返回同一分组中第一行的结果获得每张订单第一次发货日期
    First([发货日期]) over (partition by [订单] order by [发货日期])
窗口函数LAG取同列前n行的值LAG([字段],数值,默认值) OVER (ORDER BY   [排序字段])返回同列前n行的值,若取不到值(例如本行是第一行),则返回默认值每个门店上一个交易日金额,没有上一个数据的用“0”代替,可用于求日环比
    LAG([交易金额],1,0) over (partition by [门店] order by [交易日期])
窗口函数LAST取最后一行的值LAST([字段]) OVER(ORDER BY [排序字段])返回[字段]同一列中最后一行的结果获得每张订单最后一次发货日期
    Last([发货日期]) over (partition by [订单] order by [发货日期])
窗口函数LEAD取同列后n行的值LEAD([字段],数值,默认值) OVER (ORDER BY   [排序字段])返回同列后n行的值,若取不到值(例如本行是最后一行),则返回默认值企业分期还款,前一期的还款单还款日,需要由本期的还款单还款日确定,并返写至上一期还款单
    LEAD([还款日期],1,0) over (partition by [借款单] order by [还款日期])
窗口函数RANK有并列,有占位的排序RANK() OVER(ORDER BY [排序字段])返回结果集的分区内每行的排名,根据当前的记录数生成序号,即出现相同排名时跳过相同排名
    排序结构一般是:1,2,2,4,5,5,7……
rank() over (partition by [店名]   order by [实际营业额] desc)
窗口函数ROW_NUMBER行序号ROW_NUMBER() OVER(ORDER BY   [排序字段])为查询出来的每一行记录生成一个序号,依次排序且不会重复,即[排序字段]相同情况下,排序依然存在先后
    排序结构是:1,2,3,4,5,6,7……
row_number() over (partition by   [店名] order by [实际营业额] desc)
窗口函数SUM求和SUM([字段]) OVER (PARTITION BY   ([分组字段]))返回[字段]在同一[分组字段]范围内的累计每个大区的总营业额
    SUM([营业额]) over (partition by [大区] )
非聚合统计GREATEST对比最大值GREATEST([字段1], [字段2]……)对比同行中各列的值,返回最大的值GREATEST([部门1销售额],   [部门2销售额]),返回该行中部门1销售额与部门2销售额两个字段中的最大值
非聚合统计LEAST对比最小值LEAST([字段1], [字段2]……)对比同行中各列的值,返回值最小的LEAST([部门1销售额],   [部门2销售额]),返回该行中部门1销售额、部门2销售额两个字段中的最小值
聚合统计AVG平均值AVG([字段])返回[字段]所有值的平均值,只适用于数值[字段]
    NULL值不会计算
AVG(销售额),返回销售额[字段]对应的所有非空值的平均值
聚合统计COUNT计数COUNT([字段])返回表达式所有有效[字段]的数据条目数
    NULL值不会计算
COUNT(销售额),返回销售额[字段]对应的所有非空值的数据条目数
聚合统计COUNT_DISTINCT去重计数COUNT(DISTINCT([字段]))去重计数,返回表达式所有有效[字段]的不同数据条目数,空值不会计算COUNT(DISTINCT(销售额)),返回销售额[字段]对应的所有非空值的不同数据条目数
聚合统计MAX最大值MAX([字段])返回表达式或数值[字段]中的最大值,只适用于数值字段MAX(销售额),返回销售额字段对应值的最大值
聚合统计MIN最小值MIN([字段])返回表达式或数值[字段]中的最小值,只适用于数值字段MIN(销售额),返回销售额字段对应值的最小值
聚合统计PERCENTILE中位数/百分位数PERCENTILE([字段], 百分位)求数值类型[字段]的中位数及百分位数, 传入参数为数值[字段];   第二个参数范围是0<p<1, 例如0.1,0.25,0.75,0.95PERCENTILE([数学考试分数],   0.5),返回考试分数的中位数
聚合统计STDDEV样本标准差STDDEV([字段])返回样本标准差STDDEV([销售额])
聚合统计STDDEV_POP标准差STDDEV_POP([字段])返回标准差STDDEV_POP([销售额])
聚合统计SUM求和SUM([字段])返回表达式或数值[字段]所有值的合计,只适用于数值[字段]
    Null值不会计算
SUM(销售额),返回销售额[字段]对应的所有非空值的总和
聚合统计VAR_POP方差VAR_POP([字段])返回方差VAR_POP([销售额])
聚合统计VARIANCE样本方差VARIANCE([字段])返回样本方差VARIANCE([销售额])
逻辑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,否则返回结果2IF([成绩]>80,'合格','不合格')
时间和日期DATE_ADD日期推算(向后)DATE_ADD(起始日期, 数值)返回从起始日期算起,数值[字段]对应天数之后的日期DATE_ADD([入库日期], 1),返回货品入库第二天的日期
时间和日期DATE_FORMAT格式化日期DATE_FORMAT(日期[字段], 格式)返回该日期[字段]格式化后的文本
    格式示例:'yyyy-MM-dd HH:mm:ss'
DATE_FORMAT([下单时间], 'HH'),返回小时
时间和日期DATE_SUB日期推算(向前)DATE_SUB(起始日期, 数值)返回从起始日期算起,数值[字段]对应天数之前的日期DATE_SUB([出库日期], 1),返回货品出库前一天的日期
时间和日期DATEDIFF间隔天数DATEDIFF([终止日期字段], [起始日期字段])返回起始日期距离终止日期的间隔天数,只允许传入日期型[字段]DATEDIFF([离职日期],   [入职日期]),返回同一行中离职日期至入职日期的间隔天数
时间和日期DAYOFMONTH日期中,日的数值DAYOFMONTH(日期[字段])返回该日期对应的日的值。只允许传入日期型字段DAYOFMONTH([下单时间]),返回该行下单时间[字段]对应的日的值
时间和日期FROM_UNIXTIME时间戳转日期FROM_UNIXTIME(时间戳[字段])UNIX时间戳转日期,支持秒级,传入参数为unix时间戳FROM_UNIXTIME([时间戳[字段]]),   FROM_UNIXTIME(1441509383)
时间和日期HOUR时间小时值HOUR(日期[字段])返回该日期对应的小时的值,只允许传入日期型字段HOUR([下单时间]),返回该行下单时间[字段]对应的小时的值
时间和日期LAST_DAY日期所在当月最后一天LAST_DAY([字段])返回日期在当月的最后一天,只允许传入日期型字段LAST_DAY([下单时间]),如果该行的下单时间是2016-03-15,则返回2016-03-31
时间和日期MONTH日期对应月份MONTH(日期[字段])返回该日期对应的月份。只允许传入日期型[字段]MONTH([下单时间]),返回该行下单时间[字段]对应的月份
时间和日期MONTHS_BETWEEN间隔月份MONTHS_BETWEEN(日期[字段]1, 日期[字段]2)返回两个日期相差的月数,不足一月则按天计算小数值,只允许传入日期型[字段]MONTHS_BETWEEN([离职日期], [入职日期]   ),返回同一行上离职日期至入职日期间隔月数
时间和日期NOW当前系统时间NOW()返回当前系统时间,无需参数NOW(),返回当前系统时间,精确到秒
时间和日期QUARTER日期当年季度值QUARTER(日期[字段])返回该日期在当年的第几个季度,只允许传入日期型[字段]QUARTER([入职日期]),返回入职日期为该年的第几个季度
时间和日期TO_DATE转化为日期[字段]TO_DATE(日期[字段]/文本[字段])返回日期[字段]的年月日部分,或者将文本[字段]转换为年月日格式的日期类型TO_DATE([入库日期]),返回入库日期的年月日时间
时间和日期WEEKOFYEAR日期在当年的第几周WEEKOFYEAR(日期[字段])返回该日期在当年的第几周,只允许传入日期型[字段]WEEKOFYEAR([入职日期]),返回入职日期为该年的第几周
时间和日期YEAR日期对应的年份YEAR(日期[字段])返回该日期对应的年份。只允许传入日期型[字段]YEAR([下单时间]),返回该行下单时间[字段]对应的年份
数值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位。不填时为0ROUND(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的整数,如果找不到则返回0INSTR([名字],   ','),返回','在名字字段中第一次出现时的位置
字符串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


观远数据计算引擎基于Spark搭建,因此Spark支持的SQL函数均可以在观远平台上使用,具体列表请访问Spark官方文档:

http://spark.apache.org/docs/latest/api/sql/#_14


    您需要登录后才可以回复