全部产品
分析型数据库

附录二 SQL函数表

更新时间:2017-06-07 13:26:11   分享:   

特色函数

UDF_SYS_COUNT_COLUMN

  1. 作用:用于做多group by的聚合, 可以将多个group by的语句合并成多个UDF,写到一条sql中

  2. 格式:UDF_SYS_COUNT_COLUMN(columnName, columnName2…), 参数必须是列名

  3. 返回:一个json的字符串列,类似: {“0”:3331656,”2”:3338142,”1”:3330202}

  4. 实例:

a. select UDF_SYS_COUNT_COLUMN(c1) from table 等价于select count(*) from table group by c1

b. select UDF_SYS_COUNT_COLUMN(c1,c2) from table 等价于select count(*) from table group by c1,c2

c. select UDF_SYS_COUNT_COLUMN(c1), UDF_SYS_COUNT_COLUMN(c2),UDF_SYS_COUNT_COLUMN(c3), UDF_SYS_COUNT_COLUMN(c4)

等价于如下四条sql语句:select count() from table group by c1;select count() from table group by c2;select count() from table group by c3;select count() from table group by c4;

d. 一个真实的实例:select UDF_SYS_COUNT_COLUMN(user_gender), UDF_SYS_COUNT_COLUMN(user_level) from db_name.userbase返回1行,2列:{“0”:3331656,”2”:3338142,”1”:3330202},{“0”:4668150,”2”:1891176,”1”:1984606,”6”:5818}

UDF_SYS_RANGECOUNT_COLUMN

  1. 作用:用于做静态样本分段(老的函数UDF_SYS_SEGCOUNT_COLUMN已经弃用,请使用该函数)

  2. 格式:UDF_SYS_RANGECOUNT_COLUMN(columnName, count, min, max)

    • 第一个参数是列名
    • 第二个参数分段数目
    • 第三个参数是参与分段的该列在全表的最小值
    • 第四个参数是参与分段的该列在全表的最大值
  3. 返回:一个json的字符串列,类似:{“ranges”:[{“start”:0,”end”:599}, {“start”:600,”end”:1899}, {“start”:1900,”end”:65326003}]}

  1. 使用说明:使用该函数进行动态分段统计,需要三个步骤:

    • 第一步,通过min, max求出符合条件的最小值和最大值
    • 第二步,通过UDF_SYS_RANGECOUNT_COLUMN获取各个分段。
    • 第三步,通过case when+group by获取每个分段中真实聚合数据。
  2. 特别说明UDF_SYS_RAGNECOUNT_COLUMN和通过UDF_SYS_RANGECOUNT_SAMPLING_COLUMN的区别在于,前者是静态分段,也就是根据(max-min+1)/segcount进行分段,而后在是动态分段,可以保证每个分段区间内的数目是大致均衡的。

UDF_SYS_GEO_IN_CYCLE

  1. 作用:用于做基于地理位置的经纬度画圈

  2. 格式:UDF_SYS_GEO_IN_CYCLE(longitude, latitude, point, radius)

    • 第一个参数为经度列名称, 类型float
    • 第二个参数为纬度列名称, 类型float
    • 第三个参数为圆圈中心点的位置,格式=>”经度,维度”, =>”120.85979,30.011984”
    • 第四个参数为圆圈的半径,单位米
  3. 返回:返回一个boolean值

  4. 使用说明:

    select count(*) db_name.usertag where udf_sys_geo_in_cycle(longitude,latitude, “120.85979,30.011984”, 5000)=true求以”120.85979,30.011984”为中心点,半径为5km的圆圈内的人数

    select longitude,latitude from db_name.usertag where udf_sys_geo_in_cycle(longitude,latitude, “120.85979,30.011984”, 5000)=true order by longitude

UDF_SYS_GEO_IN_RECTANGLE

  1. 作用:用于做基于地理位置的经纬度画矩形

  2. 格式:UDF_SYS_GEO_IN_RECTANGLE(longitude, latitude, pointA, pointB)

    • 第一个参数为经度列名称, 类型float
    • 第二个参数为纬度列名称, 类型float
    • 第三个参数为矩形的左下角坐标,格式=>”经度,维度”, =>”120.85979,30.011984”
    • 第四个参数为矩形的右上角坐标,格式=>”经度,维度”, =>”120.88450,31.21011”
  3. 返回:返回一个boolean值

  4. 使用说明:

    select count(*) db_name.usertag where udf_sys_geo_in_rectangle(longitude,latitude, “120.85979,30.011984”, “120.88450,31.21011”)=true求以”120.85979,30.011984”和””120.88450,31.21011””为2个斜角构成的矩形圈内的人数

UDF_SYS_GEO_DISTANCE

  1. 作用:用作一个经纬度列和一个固定的坐标点的距离计算

  2. 格式:UDF_SYS_GEO_DISTANCE(longitude, latitude, pointA)

    • 第一个参数为经度列名称, 类型float
    • 第二个参数为纬度列名称, 类型float
    • 第三个参数为固定坐标点的经纬度,格式=>”经度,维度”, =>”120.85979,30.011984”
  3. 返回:返回一个int值,单位为米(M)

  4. 使用说明:

    select count(*) db_name.usertag where udf_sys_geo_in_rectangle(longitude,latitude, “120.85979,30.011984”, “120.88450,31.21011”)=true求以”120.85979,30.011984”和””120.88450,31.21011””为2个斜角构成的矩形圈内的人数

字符串函数

Concat

连接2个字符串,格式类似cancat(str1, str2,…)

Lcase

返回字符串的全小写

Ucase

返回字符串的全大写

Length

返回字符串的长度

Substring

返回字符串的字串SUBSTRING (str, pos, [len])这里的pos为下标从1开始,比如substring(‘abc’, 2,2)返回的是’bc’

Trim

类似java string的trim, 用于除去字符串前后的space

MID

MID 函数用于从文本字段中提取字符。 MID(column_name,start[,length])

  • column_name 必需。要提取字符的字段。
  • start 必需。规定开始位置(起始值是 1)
  • length 可选, 要返回的字符数。如果省略,则MID函数返回剩余文本。

Left/Rigtht

Left/Right(str,len) 返回从字符串str 开始的len 最左/右字符

Reverse

reverse(str) 返回字符串str的倒叙内容,如 reverse(‘a,b,c,d’)返回’d,c,b,a’

instr

instr(str_col, str) 返回字符串str在某一个字段str_col的内容中的位置, 没有找到字符串返回0,否则返回位置(从1开始)

日期函数

Year

查询指定列的年份,例:year(date_test);

Month

查询指定列的月份,例:month(date_test);

Day

查询指定列的日,例:day(date_test);

Week

查询指定列的是所属年的第几周,支持mode参数(与MySQL定义相同),例:week(date_test, 1);

WeekDay

返回日期d是星期几的索引(位置),0表示星期一,1表示星期二,…,6表示星期日。

WeekofYear

相当于 WEEK(d,3)

Hour/Minute/Second

返回一个Timestamp的小时/分钟/秒,例:hour(time_stamp_test)

Datediff

用于判断在两个日期之间存在的指定时间间隔的数目,用法 Datediff(date1,date2)

to_days

给定一个日期date,返回一个天数 (从年份0开始的天数),用法 to_days(date)

Yearmonth

查询指定列的日和月,例如YEARMONTH(‘20140602’)=201406;

Curdate

查询当前日期,例:curdate();

DateDiff

datediff(date1, date2) 返回date1 date2两个日期之间相差的天数,也可以传入timestamp类型

from_unixtime

from_unixtime(time_int[, format_str]) time_int为Unix时间戳,该函数将Unix时间戳转换为字符串格式的日期时间。若不传入format_str参数,则返回”yyyy-MM-dd hh:MM:ss”格式的字符串。如果传入format_str,则format_str中的参数格式中:yyyy代表年,MM代表月,dd代表日,hh代表小时,MM代表分钟,ss代表秒。

unix_timestamp

unix_timestamp(date) 返回一个date或timestamp类型数据的Unix时间戳(整形)。

聚合函数

sum

求分组中一列所有数据的和。必须输入数值类型。

count([distinct] )

求分组中的记录数。

若使用count(distinct col_name)去重,group by中的列或col_name建议为分区列;如果不是分区列,则不能和其他聚合函数在同一个sql语句中使用。

avg

求分组中一列所有数据的平均数。必须输入数值类型

group_concat

字符串聚合函数。

目前仅支持在聚合时Group By中包括所有参与计算的表分分区列(维度表)时可以使用。

语法:GROUP_CONCAT([DISTINCT] expr [,expr …] [ORDER BY col_name [ASC | DESC] [,col_name …]] [SEPARATOR str_val])

distinct用于将组内多个相同的字符串仅输出一个。若含有ORDER BY结构,则组内字符串会根据col_name列表排序输出。使用SEPARATOR可以指定聚合后的字符串分隔符,默认是逗号。

min/ max

求一个分组中的列的最小、最大值。支持传入字符串或数值类型。

其它函数

CAST

转换当前列的数据类型,例: cast(string_test as bigint)

Coalesce

返回第一个非null的表达式, 使用方式:coalesce(expression [,…n])例:colesce(string_test, “”);

Bit_Count

Bit_Count(value):返回 value 的二进制转换中”1“字节的个数

UUID

uuid(): 返回一个字符串,在当前集群内保证唯一,算法参考mongodb的objectid实现

本文导读目录
本文导读目录
以上内容是否对您有帮助?