本文档中所列的函数适用于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.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([销售额]) |
逻辑 | 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,'合格','不合格') |
时间和日期 | 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位。不填时为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 |
观远数据计算引擎基于Spark搭建,因此Spark支持的SQL函数均可以在观远平台上使用,具体列表请访问Spark官方文档:
http://spark.apache.org/docs/latest/api/sql/#_14