附录二 SQL函数表__附录_使用手册_分析型数据库-阿里云

特色函数

UDF_SYS_COUNT_COLUMN

作用：用于做多group by的聚合，可以将多个group by的语句合并成多个UDF，写到一条sql中
格式：UDF_SYS_COUNT_COLUMN(columnName, columnName2…), 参数必须是列名
返回：一个json的字符串列，类似： {“0”:3331656,”2”:3338142,”1”:3330202}
实例：

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

作用：用于做静态样本分段(老的函数UDF_SYS_SEGCOUNT_COLUMN已经弃用，请使用该函数)
格式：UDF_SYS_RANGECOUNT_COLUMN(columnName, count, min, max)
- 第一个参数是列名
- 第二个参数分段数目
- 第三个参数是参与分段的该列在全表的最小值
- 第四个参数是参与分段的该列在全表的最大值
返回：一个json的字符串列，类似：{“ranges”:[{“start”:0,”end”:599}, {“start”:600,”end”:1899}, {“start”:1900,”end”:65326003}]}

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

UDF_SYS_GEO_IN_CYCLE

作用：用于做基于地理位置的经纬度画圈
格式：UDF_SYS_GEO_IN_CYCLE(longitude, latitude, point, radius)
- 第一个参数为经度列名称，类型float
- 第二个参数为纬度列名称，类型float
- 第三个参数为圆圈中心点的位置，格式=>”经度，维度”, =>”120.85979,30.011984”
- 第四个参数为圆圈的半径，单位米
返回：返回一个boolean值
使用说明：

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

作用：用于做基于地理位置的经纬度画矩形
格式：UDF_SYS_GEO_IN_RECTANGLE(longitude, latitude, pointA, pointB)
- 第一个参数为经度列名称, 类型float
- 第二个参数为纬度列名称, 类型float
- 第三个参数为矩形的左下角坐标，格式=>”经度，维度”, =>”120.85979,30.011984”
- 第四个参数为矩形的右上角坐标，格式=>”经度，维度”, =>”120.88450,31.21011”
返回：返回一个boolean值
使用说明：

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

作用：用作一个经纬度列和一个固定的坐标点的距离计算
格式：UDF_SYS_GEO_DISTANCE(longitude, latitude, pointA)
- 第一个参数为经度列名称, 类型float
- 第二个参数为纬度列名称, 类型float
- 第三个参数为固定坐标点的经纬度，格式=>”经度，维度”, =>”120.85979,30.011984”
返回：返回一个int值，单位为米(M)
使用说明：

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实现

最后更新：2016-11-29 15:54:25