百度流式计算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语句
    下一篇
    窗口函数