附錄二 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