ODPS SQL 大全

• 目录索引
• 一、字符串函数
• 二、窗口函数
• 三、聚合函数
• 四、日期函数
• 五、数学函数

一、字符串函数

• 1.char_matchcount

bigint char_matchcount(string str1, string str2)

用途：用于计算字符串str1中的字符有多少个出现在字符串str2中；

• 2.char

string char(bigint ascii)

用途：将一个给定的ASCII码转换为字符

• 3.concat

  string concat(string a , string b …)

用途：将多个字符串凭凑成为一个字符串

• 4.get_json_object

string get_json_object( string json, string path)

用途：在一个标准的json字符串中，按照path抽取指定的字符串

• 5.instr

bigint instr(string str1, string str2, bigint strat_position, bigint nth_appearance)

用途：计算一个字符串str1在字符串str2中的位置

for example:

instr(‘tech on the net’, ‘e’,1 ,2) = 11

• 6.is_encoding

boolean is_encoding(string str, string from_encoding, string to_encoding)

用途：用于判断一个字符串是否可以从一个字符编码变成另外一种字符编码

for example:

is_encoding(‘测试’， ‘utf-8’, ‘GBK’) = true;

• 7.keyvalue

keyvalue(string srcstr, string split1, string split2, string key);

keyvalue(string srcstr, string key) //split1=’;’ , split2 = ‘:’

用途：用两个分隔符分割后的键值对，给一个键返回一个数值；默认的两个分隔符为封号和冒号

for example:

keyvalue(‘0:1\;1:2’, 1) = 2

• 8.length

bigint length(string str)

用途：返回一个字符串的长度

• 9.lengthb

bigint lengthb(string str)

用途：返回一个字符串有多少个字节，比如说，一个中文文字就是两个字节

• 10.md5

string md5(string value)

用途：计算输入字符串value的md5值

• 11.regexp_extract（不懂）

string regexp_extract(string source, string pattern, bigint occurrence)

用途:

• 12.regexp_instr(不懂)

• 13.regexp_replace

• 14.regexp_substr

• 15.regexp_count

• 16.split_part

string split_part(string str, string separator, bigint start[, bigint end])

用途：按照分隔符separator分割字符串，返回从start开始到end结束的字符串

• 17.substr

string substr(string str, bigint start_position[, bigint length])

用途：返回字符串str从start_position开始，长度为length的字符串。

• 18.tolower

string tolower(string source)

• 19.toupper

string toupper(string source)

• 20.to_char

string to_char(bigint/boolean/double/datetime value)

• 21.trim

string trim(string str)

用途：将指定字符串去除左右空格

• 22.ltrim

string ltrim(string str)

用途：将指定字符串去除左空格

• 23.rtrim

string rtrim(string str)

用途：将指定字符串去除左空格

• 24.reverse

string reverse(string str)

• 25.space

string space(bigint n)

用途：空格字符串函数，返回长度为n的空字符串。

• 26.repeat

string repeat(string str, bigint n)

用途：返回重复n次后的str字符串。

二、窗口函数

• 1.window_func() over (partition by col1, [col2…] order by col1 [desc|asc], col2[asc|desc] windowing_clause)

开窗函数，支持这种开窗函数的有avg/count/max/min/stddev/sum，一般都是用于分区表的操作。

• 2.count

bigint count([distinct] expr) over (partition by col1), [col2…] order by col1[asc|desc] window_clause

计算计数值

select user_id,

count(user_id) over (partition by user_id) as count

from test_src;

select user_id

count(user_id) over (partirion by user_id order by user_id) as count

from test_src;

• 3.avg

avg([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

计算平均值

• 4.max(min)

max([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

计算最大值|最小值

• 5.median

double median([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

decimal median([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

计算中位数

• 6.stddev

总体标准差

double stddev([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

decimal stddev([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

• 7.stddev_samp

样本标准差

double stddev([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

decimal stddev([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

• 8.sum

计算汇总值

sum([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

• 9.dense_rank

bigint dense_rank() over(partition by col1[, col2…]) order by col1[asc|desc] [col2[asc|desc]]

计算连续排名。

• 10.rank

bigint rank() over (partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…])

计算排名。col2相同的行数获得排名顺序下降

• 11.lag

lag(expr, bigint offset, default) over (partition by col1[, col2…])

order by col1[asc|desc][, col2[asc|desc]…])

按照偏移量取当前行之前第几行的值，如果当前行号为rn，则取行号为rn-offset的值。

• 12。lead

lead(expr, bigint offset, default) over (partition by col1[, col2…])

order by col1[asc|desc][, col2[asc|desc]…])

按照偏移量取当前行之后第几行的值，如果当前行号为rn，则取行号为rn+offset的值。

• 13.percent_rank

percent_rank() over partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…]

计算一组数据中某行的相对排名

• 14.row_number

row_number() over partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…]

计算行号，从1开始

• 15.cluster_sample

boolean cluster_sample(bigint x[, bigint y]) over(partition by col1[, col2…])

分组抽样

例如：有两个实验分组，group_a和group_b，每个实验分组的数值value都是几千个，现在要从a组和b组中随机抽样10%,用sql语句如何实现？

select group_id, value

from(

select group_id, value, cluster_sample(10,1) over (partition by group_id) as flag

from tbl ) sub

where flag = true;

三、聚合函数

• 1.count

bigint count([distinct|all value)

计算记录数,可以与聚合函数搭配使用，类似map_reduce里面的combiner

• 2.avg

double avg(double value)

decimal avg(double value)

计算平均数；如果value是bigint,会隐式转换为double类型后参与运算

• 3.max

max(value)

计算最大值，value是什么类型，返回值就是什么类型

• 4.min

min(value)

计算最小值，value是什么类型，返回值就是什么类型

• 5.median

double median(double number)

decimal median(decimal mumber)

计算中位数

• 6.stddev

double stddev(double number)

decimal stddev(decimal number)

计算总体标准差

• 7.stddev_samp

double stddev_samp(double number)

decimal stddev_samp(decimal number)

计算样本标准差

• 8.sum

sum(value)

计算汇总值，value是什么类型，返回什么类型

• 9.wm_concat

string wm_concat(string separator, string str)

用指定的分隔符，把str衔接起来

• 10.cast

cast(expr as )

将表达式的结果转换为指定的类型，例如：

cast(‘120’ as string)

cast(double as bigint)

cast(string as bigint)

cast(string as datetime) 或者 cast(datetime as string) 会采用默认的日期格式yyyy-mm-dd hh:mm:ss

• 11.coalesce

coalesce(expr1, expr2, …)

返回参数列表中第一个非NULL的值，如果列表中全部都是NULL则返回NULL

• 12.decode

decode(expression, search, result[, search, result]…[,default])

实现if-then-else分支选择的功能

以下两个案例是等值的

select

decode(customer_id

1, ‘taobao’,

2, ‘alipay’,

3, ‘aliyun’,

NULL, ‘N/A’

‘others’) as result

from sales_detail;

select

(if customer_id := 1 then

result := ‘taobao’;

elseif customer_id := 2 then

result := ‘alipay’;

elseif customer_id := 3 then

result := ‘aliyun’;

elseif customer_id := null then

result := ‘N/A’ )

else

result := ‘others’

end if;

) from sales_detail;

• 13.greatest

greatest(var1, var2, …)

返回输入参数中最大的一个

• 14.ordinal

ordinal(bigint nth, var1, var2, …)

将输入变量从小到大排序后，返回nth指定的位置的值。

• 15.least

least(var1, var2, …)

返回输入参数中最小的一个

• 16.uuid

uuid()

返回一个随机ID

• 17.sample

boolean sample(x, y, column_name)

对所有读入的column_name的值，哈希x份，取第y份。其中y和colum_name同时存在或者同时省略

• 18.case when

case value

when (condition1) then result1

when (condition2) then result2

…

else resultn

end

case

when (condition1) then result1

when (condition2) then result2

…

else resultn

end

• 19.if

if(testcondition, valuetrue, valuefalseornull)

判断testcondition是否为真，真取第一个数值，否则取第二个数值。

四、日期函数

• 1.dateadd

datetime dateadd(datetime date, bigint delta, string datepart)；

用途：按照指定的单位datepart和幅度delta修改date的值

• 2.datediff

bigint datediff(datetime date1, datetime date2, string datepart);

用途:计算两个时间date1, date2 在指定时间单位datepart的差值

• 3.datepart

bigint datepart(datetime date, string datepart)

用途：提取日期date中指定的时间单位datepart的值

• 4.datetrunc

datetime datetrunc(datetime date, string datepart)

用途：返回日期date被截取指定时间单位datepart后的日期值

• 5.from_unixtime

datetime from_unixtime(bigint unixtime)

用途：将数字型的unix时间日期值unixtime 转为日期值

• 6.UNIX_TIMESTAMP

bigint unix_timestamp(datetime date)

用途：将日期值转换成unixtime数值

• 7.GETTIME

datetime gettime()

用途：获取计算的当前日期值

• 8.ISDATE

boolean isdate(string date, string format)

用途：判断一个日期字符能否基于对应的格式转换为一个日期值，如果可以返回true，否则返回false

• 9.lastday

datetime lastday(datetime date)

用途：取date当月的最后一天，截取到天，时分秒部分为00：00：00

• 10.to_date

datetime to_date(string date, string format)

用途：将一个字符串date按照format的指定的格式转换为日期值

• 11.to_char

string to_char(datetime date, string fromat)

用途：将一个日期值按照指定的格式转换为字符串

• 12.weekday

bigint weekday(datetime date)

用途：返回date 日期当前周的第几天

• 13.weekofyear

bigint weekofyear(datetime date)

用途：返回date在那一年中的第几周

五、数学函数

• 1.abs

double abs(double number)

bigint abs(bigint number)

decimal abs(decimal number)

返回绝对值

select abs(id) from tb1;

• 2.acos

double acos(double number)

decimal acos(decimal number)

计算number的反余玄函数

• 3.asin

double asin(double number)

decimal asin(decimal number)

计算number的反正玄函数

• 4.atan

double atan(double number)

反正切函数

• 5.ceil

bigint ceil(double number)

bigint ceil(decimal number)

返回不小于number的最小整数

• 6.conv

string conv(string input, bigint from_base, bigint to_base)

进制转换函数

例如：

conv(‘1111111001010101’, 2, 10)

• 7.cos

double cos(double number)

decimal cos(decimal number)

计算number的余玄函数

• 8.cosh

double cosh(double number)

decimal cosh(decimal number)

计算number的双余玄函数

• 9.cot

double cot(double number)

decimal cot(decimal number)

计算number的余切函数

• 10.exp

double exp(double number)

decimal exp(decimal number)

计算number的双余玄函数

• 11.floor

bigint floor(double number)

bigint floor(decimal number)

向下取整，返回比number小的整数值

floor(1.2) = 1

floor(-1.2) = -2

floor(0.1) = 0

• 12.ln

double ln(double number)

decimal ln(decimal number)

计算number的自然对数

• 13.log

double log(double base, double x)

decimal log(decimal base, decimal x)

返回以base为底的x的对数

• 14.pow

double pow(double x, double, y)

decimal pow(decimal x, decimal, y)

返回x的y次方

• 15.rand

double rand(bigint seed)

返回以seed为种子的double随机数，返回值区间是的0-1；

• 16.round

double round(double number, [bigint decimal_places])

decimal round(decimal number, [bigint decimal_places])

四舍五入到指定小数点

decimal_places可以是负数，负数会从小数点向左开始计数，并且不保留小数部分；

round(123.345, -2) = 100.0

round(123.345, -4) = 0.0

• 17.sign

sign(x)

x为正值，返回1.0

x为负值，返回-1.0

当x为0， 返回0.0

当x为空，抛出异常

• 18.sin

double sin(double number)

正玄函数

• 19.sinh

double sinh(double number)

decimal sinh(decimal number)

双曲正玄函数

• 20.sqrt

double sqrt(double number)

decimal sqrt(decimal number)

计算平方根

• 21.tan

double tan(double number)

decimal tan(decimal number)

计算正切函数

• 21.tanh

double tanh(double number)

decimal tanh(decimal number)

计算双曲正切函数

• 22.trunc

double trunc(double number[, bigint decimal_places])

decimal trunc(decimal number[, bigint decimal_places])

将输入值number截取到指定小数点的位置，截取掉的部分补0，decimal_places也可以是负数，如果是负数，从小数点向左开始截取，并且不保留小数部分；如果decimal_places超过了整数部分长度，返回0

trunc(125.815) = 125.0

trunc(125.815,0)=125.0

trunc(125.815,1)=125.8

trunc(125.815,-1)=120.0