内置函数
所有文档

          百度流式计算 BSC

          内置函数

          概述

          主要提供以下类型的内置函数:

          字符串函数

          函数名 功能描述 示例
          base64(bin) 将 bin表示的 Byte[] 字节数组转换为 Base64 字符串。 -
          char_length(expr) 返回字符串的长度。 > SELECT CHAR_LENGTH('Spark SQL ');
          10
          char_length(expr) 与char_length(expr) 相同。 > SELECT CHAR_LENGTH('Spark SQL ');
          10
          char(expr) 返回与expr等价的ASCII字符,如果n大于256,返回chr(n%256) > SELECT char(65);
          A
          chr(expr) 将ASCII码转换为字符 > SELECT char(65);
          A
          concat(str1, str2, ..., strN) 返回多个字符串连接后的字符串 >  SELECT concat('Spark', 'SQL');
          SparkSQL
          decode(bin, charset) 将 bin表示的 Byte[] 字节数组以 charset 指定的字符集解码 >  SELECT decode(encode('abc', 'utf-8'), 'utf-8');
          abc
          encode(str, charset) 将 str 字符串以 charset 指定的字符集编码为 Byte[] 数组 > SELECT encode('abc', 'utf-8');
          abc
          initcap(str) 将 stri转为以大写字母开头,其他是小写字母的形式,单词之间由空格隔开 >  SELECT initcap('sPark sql');
          Spark Sql
          lower(str) 返回 str的全小写字母形式。 >  SELECT lower('SparkSql');
          sparksql
          lpad(str, len, pad) 使用 pad指定的字符串从左侧填充str字符串到指定长度 len. 如果 str比 len 更长,则会截断到 len的长度。 >  SELECT lpad('hi', 5, '??');
          ???hi
          >  SELECT lpad('hi', 1, '??');
          h
          md5(expr) 返回字符串的MD5值,如果参数为NULL返回NULL,为空串返回空串 -
          parse_url(url, partToExtract[, key]) 解析url,获取partToExtract的值 >  SELECT parse_url('http://spark.apache.org/path?query=1', 'HOST')
          spark.apache.org
          >  SELECT parse_url('http://spark.apache.org/path?query=1', 'QUERY')
          query=1
          >  SELECT parse_url('http://spark.apache.org/path?query=1', 'QUERY', 'query')
          1
          position(substr, str[, pos]) 获取字符串 str在 字符串substr 中第一次出现的位置。 >  SELECT position('bar', 'foobarbar');
          4
          regexp_extract(str, regexp[, idx]) 使用正则模式pattern匹配抽取字符串str中的第index个子串,index 从1开始 正在匹配提取, 参数为null或者正则不合法返回null。 >  SELECT regexp_extract('100-200', '(\d+)-(\d+)', 1);
          100
          regexp_replace(str, regexp, rep) 用字符串rep替换字符串str中符合regexp的子串,返回新的字符串 >  SELECT regexp_replace('100-200', '(\d+)', 'num');
          num-num
          repeat(str, n) 返回以str重复字符串值,重复次数为N的新的字符串。参数为null返回null,重复次数为0或负数返回空串 >  SELECT repeat('123', 2);
          123123
          reverse(str) 反转字符串,返回字符串值的相反顺序。任一参数为NULL,返回NULL。 >  SELECT reverse('Spark SQL');
          LQS krapS
          rpad(str, len, pad) 使用 pad指定的字符串从右侧填充 str字符串到指定长度 len。如果 str比 len 更长,则会截断到 len 的长度。 >  SELECT rpad('hi', 5, '??');
          hi???
          >  SELECT rpad('hi', 1, '??');
          h
          str_to_map(text[, pairDelim[, keyValueDelim]]) 使用listDelimiter将text分隔成K-V对,然后使用keyValueDelimiter分隔每个K-V对,组装成 MAP 返回。默认 listDelimiter 为 ,, keyValueDelimiter为 =。 >  SELECT str_to_map('a:1,b:2,c:3', ',', ':');
          map("a":"1","b":"2","c":"3")
          >  SELECT str_to_map('a');
          map("a":null)
          substring(string, pos[, len]) 获取从 pos 位开始的子串,默认行为是直到源字符串的最后,可以使用 FOR 来指定子串的长度。 >  SELECT substring('Spark SQL', 5);
          k SQL
          unbase64(str) 将 Base64 编码的字符串解码为 Byte[] 字节数组 -
          upper(str) 返回 str 的全大写字母形式。 >  SELECT upper('SparkSql');
          SPARKSQL
          ascii(str) 返回str第一个字符的ASCII码值 >  SELECT ascii('231');
          50
          concat_ws(sep, [str | array(str)]+) 使用sep作为分隔符连接多列字符串 >  SELECT concat_ws(' ', 'Spark', 'SQL');
          Spark SQL
          format_number(expr1, expr2) 将string类型的expr转换为经过舍入的有expr2个小数点的数值 >  SELECT format_number(12332.123456, 4);
          12,332.1235
          format_string(strfmt, obj, ...) - -
          instr(str, substr) 返回substr在str中第一次出现的位置 >  SELECT instr('SparkSQL', 'SQL');
          6
          length(expr) 返回expr的长度,字符串的长度包括结尾空格,二进制数值长度包括里面的0 >  SELECT length('Spark SQL ');
          10
          levenshtein(str1, str2) 计算两个字符串之间的编辑距离 >  SELECT levenshtein('kitten', 'sitting');
          3
          locate(substr, str[, pos]) 返回substr在str的位置pos之后第一次出现的位置, -
          ltrim(str) 去掉str左边的空格、空白字符 >  SELECT ltrim(' SparkSQL ');
          SparkSQL
          ltrim(trimStr, str) 去掉str左边的指定字符 -
          trim(str) 去掉str左右两边的空格、空白字符 >  SELECT trim(' SparkSQL ');
          SparkSQL
          trim(BOTH trimStr FROM str) 从字符串str两边去掉指定关键字 -
          trim(LEADING trimStr FROM str) 从字符串str左边去掉指定关键字 -
          trim(TRAILING trimStr FROM str) 从字符串str右边去掉指定关键字 -
          soundex(str) 计算桑迪克斯代码(用于按英语发音来索引姓名,发音相同但拼写不同的单词,会映射成同一个码) >  SELECT soundex('Miller');
          M460
          split(str, regex) 将str中符合regex的字符作为分隔符,返回分割后的str >  SELECT split('oneAtwoBthreeC', '[ABC]');
          ["one","two","three",""]
          translate(input, from, to) 将input中与字符串from相同的部分替换为字符串to >  SELECT translate('AaBbCc', 'abc', '123');
          A1B2C3

          条件函数

          函数名 功能描述 示例
          coalesce(expr1, expr2, ...) 如果前值是 NULL 则提供一个后续的值 > SELECT coalesce(NULL, 1, NULL);
          1
          if(expr1, expr2, expr3) 以第一个参数的布尔值为判断标准,若为true,则返回第二个参数,若为false,则返回第三个参数,第一个参数为null时看作false > SELECT if(1 < 2, 'a', 'b');
          a
          nullif(expr1, expr2) 如果 expr1 与 expr2 相同则返回 NULL;否则返回第一个值。 > SELECT nullif(2, 2);
          NULL

          类型转换函数

          函数名 功能描述 示例
          cast(value AS type) 将某个值转为 type 类型,例如 CAST(hello AS VARCHAR) 会将 hello 字段转为 VARCHAR 类型。 > SELECT cast('10' as int);
          10
          to_timestamp(timestamp[, fmt]) 将fmt转换为 timestamp 类型的时间戳。输入内容不合法时返回Null > SELECT to_timestamp('2016-12-31 00:12:00');
          2016-12-31 00:12:00
          > SELECT to_timestamp('2016-12-31', 'yyyy-MM-dd');
          2016-12-31 00:00:00
          date_format(timestamp, fmt) 将时间戳timestamp中与fmt指定的日期部分转换为字符串 > SELECT date_format('2016-04-08', 'y');
          2016
          bigint(expr) 将expr转换为bigint类型 -
          binary(expr) 将expr转换为binary类型 -

          时间相关函数

          函数名 功能描述 示例
          add_months(start_date, num_months) 返回增加num_months月之后的varchar类型日期 > SELECT add_months('2016-08-31', 1);
          2016-09-30
          current_date() 返回当前日期 -
          current_timestamp() 返回当前时间戳 -
          date(expr) 将 expr转换为日期类型并返回 -
          date_add(start_date, num_days) 返回增加num_days天数之后的varchar类型日期 > SELECT date_add('2016-07-30', 1);
          2016-07-31
          date_format(timestamp, fmt) 将时间戳转换为string类型的指定的日期格式 > SELECT date_format('2016-04-08', 'y');
          2016
          date_sub(start_date, num_days) 将日期减去num_days天数并返回 > SELECT date_sub('2016-07-30', 1);
          2016-07-29
          date_trunc(fmt, ts) 从指定的格式fmt将时间戳ts进行截断并返回 > SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');
          2015-01-01 00:00:00
          > SELECT date_trunc('MM', '2015-03-05T09:32:05.359');
          2015-03-01 00:00:00
          > SELECT date_trunc('DD', '2015-03-05T09:32:05.359');
          2015-03-05 00:00:00
          > SELECT date_trunc('HOUR', '2015-03-05T09:32:05.359');
          2015-03-05 09:00:00
          datediff(endDate, startDate) 返回从enddate到startdate两个时间的天数差值 > SELECT datediff('2009-07-31', '2009-07-30');
          1
          > SELECT datediff('2009-07-30', '2009-07-31');
          -1
          dayofmonth(date) 返回日期date中的日,范围1~31 > SELECT dayofmonth('2009-07-30');
          30
          dayofweek(date) 返回日期date属于第几周 > SELECT dayofweek('2009-07-30');
          5
          dayofyear(date) 返回日期date在一年中的天数 > SELECT dayofyear('2016-04-09');
          100
          from_unixtime(unix_time, format) 将时间戳转换成字符串格式 > SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
          1970-01-01 00:00:00
          from_utc_timestamp(timestamp, timezone) 将时间戳转换成指定时区的时间戳 > SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul');
          2016-08-31 09:00:00
          hour(timestamp) 返回时间戳中的小时值 > SELECT hour('2009-07-30 12:58:59');
          12
          last_day(date) 返回日期中的月末日期 > SELECT last_day('2009-01-12');
          2009-01-31
          minute(timestamp) 返回时间戳中的分钟值 > SELECT minute('2009-07-30 12:58:59');
          58
          month(date) 返回日期中的月份值 > SELECT month('2016-07-30');
          7
          months_between(timestamp1, timestamp2) 返回两个时间戳之间的月数差值 > SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
          3.94959677
          next_day(start_date, day_of_week) 返回指定日期之后的下一个周一、二…的日期 > SELECT next_day('2015-01-14', 'TU');
          2015-01-20
          quarter(date) 返回日期中的季度值 > SELECT quarter('2016-08-31');
          3
          runc(date, fmt) 将date从指定的fmt开始截断,fmt必须是"year", "yyyy", "yy", "mon", "month", "mm"] > SELECT trunc('2009-02-12', 'MM');
          2009-02-01
          > SELECT trunc('2015-10-27', 'YEAR');
          2015-01-01
          second(timestamp) 返回时间戳中的秒数值 > SELECT second('2009-07-30 12:58:59');
          59
          to_date(date_str[, fmt]) 将date_str转换为日期类型 > SELECT to_date('2009-07-30 04:17:52');
          2009-07-30
          > SELECT to_date('2016-12-31', 'yyyy-MM-dd');
          2016-12-31
          to_utc_timestamp(timestamp, timezone) 将指定时区时间戳转UTF时间戳 > SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul');
          2016-08-30 15:00:00
          unix_timestamp([expr[, pattern]]) 指定格式的时间字符串转换成时间戳 > SELECT unix_timestamp();
          1476884637
          > SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
          1460041200
          weekofyear(date) 返回日期date在一年中的周数 > SELECT weekofyear('2008-02-20');
          8
          year(date) 返回日期中的年数值 > SELECT year('2016-07-30');
          2016

          算术函数

          函数名 功能描述 示例
          acos(expr) 返回expr的反余弦值 > SELECT acos(1);
          0.0
          asin(expr) 返回expr的反正弦值 > SELECT asin(0);
          0.0
          atan(expr) 返回expr的反正切值 > SELECT atan(0);
          0.0
          atan2(exprY, exprX) 返回原点至点(exprY, exprX)的方位角,即与X轴的夹角,与java.lang.Math.atan类似 > SELECT atan2(0, 0);
          0.0
          bin(expr) 将long类型转为对应二进制数值的字符串 > SELECT bin(13);
          1101
          > SELECT bin(-13);
          1111111111111111111111111111111111111111111111111111111111110011
          > SELECT bin(13.3);
          1101
          bround(expr, d) 返回expr到d小数点的位置(使用HALF_EVEN模式) > SELECT bround(2.5, 0);
          2.0
          cbrt(expr) 返回expr的立方根 > SELECT cbrt(27.0);
          3.0
          ceil(expr) 返回不小于expr的最小整数 > SELECT ceil(-0.1);
          0
          > SELECT ceil(5);
          5
          conv(num, from_base, to_base) 将num从from_base进制转换成to_base进制 > SELECT conv('100', 2, 10);
          4
          > SELECT conv(-10, 16, -10);
          -16
          cos(expr) 返回expr的余弦值 > SELECT cos(0);
          1.0
          cosh(expr) 返回expr的双曲余弦值 > SELECT cosh(0);
          1.0
          expr1 - expr2 返回expr1-expr3的结果 > SELECT 2 - 1;
          1
          expr1 % expr2 返回expr1/expr2的余数 > SELECT 2 % 1.8;
          0.2
          expr1 * expr2 返回expr1与expr2相乘的结果 > SELECT 2 * 3;
          6
          expr1 / expr2 返回expr1与expr2相除的结果 > SELECT 3 / 2;
          1.5
          expr1 + expr2 返回expr1与expr2相加的结果 > SELECT 1 + 2;
          3
          floor(expr) 返回不大于expr的最大整数 > SELECT floor(-0.1);
          -1
          > SELECT floor(5);
          5
          hex(expr) 返回expr转换为十六进制后的数值 > SELECT hex(17);
          11
          log(base, expr) 返回以base为底expr的对数 > SELECT log(10, 100);
          2.0
          log10(expr) 返回以10为底expr的对数 > SELECT log10(10);
          1.0
          log1p(expr) 返回log(1+expr) > SELECT log1p(0);
          0.0
          pmod(expr1, expr2) 返回mod(expr1, expr2)的正值 1
          > SELECT pmod(-10, 3);
          2
          pow(expr1, expr2) 返回expr1的expr2次方的值 > SELECT pow(2, 3);
          8.0
          radians(expr) 返回角度expr转换为弧度的值 > SELECT radians(180);
          3.141592653589793
          rint(expr) 返回与expr最接近的double类型的整数 > SELECT rint(12.3456);
          12.0
          round(expr, d) 返回expr四舍五入之后的值(使用HALF_UP舍入模式) > SELECT
          round(2.5, 0);
          3.0
          shiftleft(base, expr) 返回base按位左移expr之后的值 > SELECT shiftleft(2, 1);
          4
          shiftright(base, expr) 返回base按位(带符号)右移expr之后的值 > SELECT shiftright(4, 1);
          2
          shiftrightunsigned(base, expr) 返回base按位(无符号)右移expr之后的值 > SELECT shiftrightunsigned(4, 1);
          2
          signum(expr) 返回expr数值的正负符号(-1.0表示负数符号,1表示正数符号) > SELECT signum(40);
          1.0
          sin(expr) 返回expr的正弦值 > SELECT sin(0);
          0.0
          sinh(expr) 返回expr的双曲正弦值 > SELECT sinh(0);
          0.0
          sqrt(expr) 返回expr的平方根 > SELECT sqrt(4);
          2.0
          tan(expr) 返回expr的正切值 > SELECT tan(0);
          0.0
          tanh(expr) 返回expr的双曲正弦切值 > SELECT tanh(0);
          0.0
          unhex(expr) 返回十六进制的expr转换为二进制后的值 -
          abs(expr) 返回expr的绝对值 > SELECT abs(-1);
          1
          cot(expr) 返回expr的余切值 > SELECT cot(1);
          0.6420926159343306
          e() 返回自然常数e的double类型值 > SELECT e();
          2.718281828459045
          exp(expr) 返回e的expr次方的值 > SELECT exp(8);
          2980.9579870417283
          ln(expr) 返回ln(expr) -
          pi() 返回pi值 > SELECT pi();
          3.141592653589793
          rand([seed]) 返回0~1之间的DOUBLE类型随机数,返回值区间为0~1 > SELECT rand();
          0.9629742951434543
          degrees(expr) 将弧度expr转换为度 > SELECT degrees(3.141592653589793);
          180.0

          聚合函数

          函数名 功能描述
          approx_count_distinct(expr[, relativeSD]) 返回expr中的去重总数的一个近似值
          avg(expr) 返回指定列的所有输入的算术平均值
          collect_list(expr) 聚合指定字段的值到list
          collect_set(expr) 聚合指定字段的值到set
          corr(expr1, expr2) 返回指定两列所有输入的Pearson相关系数
          count(*) 返回输入的行数
          count(expr [, expr]* ) 返回非 NULL expr 的输入行数
          covar_pop(expr1, expr2) 返回数据对的总体协方差
          covar_samp(expr1, expr2) 返回数据对的样本协方差
          first(expr[, isIgnoreNull]) 返回指定列所有输入的第一个元素,isIgnoreNull为真时,返回非空元素
          kurtosis(expr) 返回指定列所有输入的峰度值
          last(expr[, isIgnoreNull]) 返回指定列所有输入的最后一个元素,isIgnoreNull为真时,返回非空元素
          max(expr) 返回指定列所有输入的最大值
          mean(expr) 返回指定列的所有输入的算术平均值。
          min(expr) 返回指定列所有输入的最小值
          skewness(expr) 返回指定列所有输入的偏度值
          stddev_pop(expr) 计算指定列所有输入的总体标准差。
          stddev_samp(expr) 计算指定列所有输入的样本标准差。
          sum(expr) 返回指定列的所有输入和。
          var_pop(expr) 计算指定列所有输入的总体方差。
          var_samp(expr) 计算指定列所有输入的样本方差。
          variance(expr) 计算指定列所有输入的样本方差

          逻辑函数

          函数名 功能描述 示例
          expr1 = expr2 比较expr1 和 expr2 是否相等,如果相等则返回TRUE,如果不相等则返回 FALSE。
          1、expr1和expr2的类型必须相同,或者可以转换为同一类型
          2、不支持Map类型
          3、array和struct类型的数组必须支持排序
          > SELECT 2 = 2;
          true
          > SELECT 1 = '1';
          true
          > SELECT true = NULL;
          NULL
          ! expr 逻辑not相同 -
          expr1 < expr2 如果expr1小于expr2,返回TRUE,否则返回FALSE. > SELECT 1 < 2;
          true
          expr1 <= expr2 如果expr1小于等于expr2,返回TRUE,否则返回FALSE. > SELECT 2 <= 2;
          true
          expr1 <=> expr2 如果expr1和expr2均不为Null,该功能等同于equal,其中一个为Null,返回FALSE,均为Null返回TRUE > SELECT 2 <=> 2;
          true
          > SELECT 1 <=> '1';
          true
          > SELECT true <=> NULL;
          false
          > SELECT NULL <=> NULL;
          true
          expr1 == expr2 如果expr1相等于expr2,返回TRUE,否则返回FALSE > SELECT 2 == 2;
          true
          > SELECT 1 == '1';
          true
          > SELECT true == NULL;
          NULL
          > SELECT NULL == NULL;
          NULL
          expr1 > expr2 如果expr1大于expr2,返回TRUE,否则返回FALSE. > SELECT 2 > 1;
          true
          expr1 >= expr2 如果expr1大于等于expr2,返回TRUE,否则返回FALSE. > SELECT 2 >= 1;
          true
          expr1 ^ expr2 返回expr1和expr2逐位进行OR比较的结果 > SELECT 3 ^ 5;
          2
          expr1 & expr2 返回expr1和expr2逐位进行AND比较的结果 > SELECT 3 & 5;
          1
          expr1 and expr2 如果expr1 和 expr2均为TRUE,则为TRUE;否则为FALSE -
          isnotnull(expr) 如果expr为Null,返回FALSE,否则返回TRUE > SELECT isnotnull(1);
          true
          isnull 如果expr为Null,返回TRUE,否则返回FALSE > SELECT isnull(1);
          false
          str like pattern 如果匹配,返回TRUE,否则返回FALSE > SELECT '%SystemDrive%\Users\John'
          like
          '\%SystemDrive\%\Users%'
          true
          not expr 如果expr是TRUE,返回FALSE,否则返回TRUE -
          expr1 or expr2 跟OR逻辑相同 -
          expr1 in(expr2, expr3, ...) 如果expr1等同于(expr2, expr3, ...) 中的任何一个,返回TRUE,否则返回FALSE > SELECT 1 in(2, 3, 4);
          false
          isnan(expr) 如果expr是NaN,返回TRUE,否则返回FALSE > SELECT isnan(cast('NaN' as double));
          true

          集合函数

          函数名 功能描述 示例
          array_contains(array, value) 如果数组array包含数值value,返回TRUE,否则返回FALSE > SELECT array_contains(array(1, 2, 3), 2);
          true
          explode(expr) 将array或map展开为多行 > SELECT explode(array(10, 20));
          10
          20
          explode_outer(expr) 同explode,但当array或map为空或null时,会展开为null -
          posexplode(expr) 将array或map展开为带位置索引的多行 > SELECT posexplode(array(10,20));
          0 10
          1 20
          posexplode_outer(expr) 同explode_outer,带位置索引 -
          from_json(jsonStr, schema[, options]) 将JSON字符串转换为StructType or ArrayType > SELECT from_json('{"a":1, "b":0.8}', 'a INT, b DOUBLE');
          {"a":1, "b":0.8}
          > SELECT from_json('{"time":"26/08/2015"}', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
          {"time":"2015-08-26 00:00:00.0"}
          to_json(expr[, options]) 将expr转换为json字符串 > SELECT to_json(named_struct('a', 1, 'b', 2));
          {"a":1,"b":2}
          get_json_object(json_txt, path) 获取指定json路径的json对象字符串 > SELECT get_json_object('{"a":"b"}', '$.a');
          b
          json_tuple(jsonStr, p1, p2, ..., pn) 获取json中指定字段对应的值 > SELECT json_tuple('{"a":1, "b":2}', 'a', 'b');
          1 2
          map_keys(map) 返回map的键组成的array > SELECT map_keys(map(1, 'a', 2, 'b'));
          [1,2]
          map_values(map) 返回map的值组成的array > SELECT map_values(map(1, 'a', 2, 'b'));
          ["a","b"]
          size(expr) 返回array或者map的长度 > SELECT size(array('b', 'd', 'c', 'a'));
          4
          sort_array(array[, ascendingOrder]) 将array中元素排序(自然排序),默认asc > SELECT sort_array(array('b', 'd', 'c', 'a'), true);
          ["a","b","c","d"]

          其他函数

          函数名 功能描述 示例
          array(expr, ...) 将多列合并为数组 > SELECT array(1, 2, 3);
          [1,2,3]
          map(key0, value0, key1, value1, ...) 将多个键值对合并为map > SELECT map(1.0, '2', 3.0, '4');
          {1.0:"2",3.0:"4"}
          greatest(expr, ...) 返回多列中的最大值 > SELECT greatest(10, 9, 2, 4, 3);
          10
          least(expr, ...) 返回多列中的最小值 > SELECT least(10, 9, 2, 4, 3);
          2
          input_file_name() 返回当前被读取的文件名称 -
          monotonically_increasing_id() 返回单调递增唯一ID,但不同分区的ID不连续。ID为64位整型。 -
          nanvl(expr1, expr2) 如果expr1为NaN,返回expr2 > SELECT nanvl(cast('NaN' as double), 123);
          123.0
          struct(col1, col2, col3, ...) 将多列组合成一个新的struct -
          assert_true(expr) 如果expr不为TRUE,抛出异常 > SELECT assert_true(0 < 1);
          NULL
          bit_length 返回strig或者数字的字节长度 -
          上一篇
          DML语句
          下一篇
          窗口函数