开放数据处理服务ODPS_SQL一些基础知识

显式类型转换

• 将double类型转为bigint类型时，小数部分会被截断，例如：cast(1.6 as bigint) = 1；
• 满足double格式的string类型转换为bigint时，会先将string转换为double，再将double转换

• 满足bigint格式的string类型可以被转换为double类型，小数点后保留一位，例如：cast(“1”

• 不支持的显式类型转换会导致异常；
• 如果在执行时转换失败，报错退出；
• 日期类型转换时采用默认格式yyyy-mm-dd hh:mi:ss，详细说明信息请参考String类型与

• 部分类型之间不可以通过显式的类型转换，但可以通过SQL内建函数进行转换，例如：从

• 关于cast的介绍请参阅 CAST ；
• DECIMAL超出值域，CAST STRING TO DECIMAL可能会出现最高位溢出报错，最低位溢截

• 不支持的隐式类型转换会导致异常；
• 如果在执行时转换失败，也会导致异常；
• 由于隐式类型转换是ODPS依据上下文使用环境自动进行的类型转换，因此，我们推荐在类型不
• 匹配时显式的用cast进行转换;
• 隐式类型转换规则是有发生作用域的。在某些作用域中，只有一部分规则可以生效。详细信息请
• 参考隐式类型转换的作用域；

A如果A或B为NULL，返回NULL；如果A小于B，返回TRUE，否则返回FALSE A<=B ：如果A或B为NULL，返回NULL；如果A小于等于B，返回TRUE，否则返回FALSE A>B： 如果A或B为NULL，返回NULL；如果A大于B，返回TRUE，否则返回FALSE A>=B ：如果A或B为NULL，返回NULL；如果A大于等于B，返回TRUE，否则返回FALSE A IS NULL： 如果A为NULL，返回TRUE，否则返回FALSE A IS NOT NULL： 如果A不为NULL，返回TRUE，否则返回FALSE A LIKE B： 如果A或B为NULL，返回NULL，A为字符串，B为 要匹配的模式， 如果匹配，返回TRUE，否则返回FALSE。'%'匹配任意多个字符，'_'匹配单个字符 。要匹配'%'或’_’需要用转义符表示’\%’，’\_’。‘aaa’ like ‘a__’ = TRUE ‘aaa’like ‘a%’ = TRUE‘aaa’ like‘aab’ = FALSE ‘a%b’ like‘a\%b’ = TRUE ‘axb’ like‘a\%b’ = FALSE A RLIKE B：A是字符串，B是字符串常量 正则表达式； 如果匹配成功，返回TRUE，否则返回FALSE； 如果B为空串会报错退出；如果A或B为NULL，返回NULL； A IN B：B是一个集合，如果A为NULL，返回NULL，如A在B中则返回TRUE，否则返回FALSE 若B仅有一个元素NULL，即A IN (NULL)，则返回NULL。 若B含有NULL元素，将NULL视为B集合中其他元素的类型。 B必须是常数并且至少有一项，所有类型要一致 位操作符-A和B 必须为Bigint类型。 A & B：返回A与B进行按位与的结果。例如：1&2返回0，1&3返回1，NULL与任何值按位与都为NULL。 A和B必须为Bigint类型。 A | B：返回A与B进行按位或的结果。例如：1 |2返回3，1 |3返回3，NULL与任何值按位或都为NULL。
group by：分组查询, 一般group by是和聚合函数配合使用。在select中包含聚合函数时： 用group by的key可以是输入表的列名;也可以是由输入表的列构成的表达式，不可以是select语句的输出列的别名;规则1的优先级高于规则2。当规则1和规则2发生冲突时，即group by的key即是输入表的列或表达式，又是select的输出列，以规则1为准。 order by：对所有数据按照某几列进行全局排序。如果您希望按照降序对记录进行排序，可以使用 DESC关键字。由于是全局排序， order by必须与limit共同使用。对在使用order by排序时 ，NULL会被认为比任何值都小，这个行为与Mysql一致，但是与Oracle不一致。 与group by不同 ，order by后面必须加select列的别名，当select某列时，如果没有指定列的别名，将列名作为列的 别名。 distribute by：对数据按照某几列的值做hash分片，必须使用select的输出列别名。 sort by：局部排序，语句前必须加distribute by。实际上sort by是对distribute by的结果进行局部 排序。必须使用select的输出列别名。order by不和distribute by/sort by共用，同时group by也不和distribute by/sort by共用，必须使用select的输出列别名。order by/sort by/distribute by的key必须是select语句的输出列，即列的别名。 在ODPSSQL解析中， order by/sort by/distribute by是后于select操作的，因此它们只能接受select语句的输出列为key。
JOIN操作：ODPS的JOIN支持多路间接，但不支持笛卡尔积，即无on条件的链接。连接条件，只允许and连接的 等值条件，并且最多支持16路join操作。只有在MAPJOIN中，可以使用不等值连 接或者使用or连接多个条件。 MAPJOIN HINT：当一个大表和一个或多个小表做join时，可以使用mapjoin，性能比普通的join要快很多。mapjoin的基本原理是：在小数据量情况下， SQL会将用户指定的小表全部加载到执行join操作的程序的内存中，从而加快join的执行速度。需要注意，使用mapjoin时： left outer join的左表必须是大表； right outer join的右表必须是大表； inner join左表或右表均可以作为大表； full outer join不能使用mapjoin； mapjoin支持小表为子查询； 使用mapjoin时需要引用小表或是子查询时，需要引用别名； 在mapjoin中，可以使用不等值连接或者使用or连接多个条件； 目前ODPS在mapjoin中最多支持指定6张小表，否则报语法错误； 如果使用mapjoin，则所有小表占用的 内存总和不得超过512MB； ODPS SQL不支持支持在普通join的on条件中使用不等值表达式、or 逻辑等复杂的join条件，但是在 mapjoin中可以进行如上操作 select /* + mapjoin(a) */ a.shop_name,b.id,b.price from shop a join sale_detail b on a.name= b.name; select /*+ mapjoin(a) */a.total_price,b.total_pricefrom shop a join sale_detail b on a.total_price < b.total_price or a.total_price + b.total_price < 500;
CONV：用途：进制转换函数，string conv(string input, bigint from_base, bigint to_base)参数说明 input：以string表示的要转换的整数值，接受bigint，double的隐式转换。 from_base，to_base：以十进制表示的进制的值，可接受的的值为2，8，10，16。接受string及 double的隐式转换。 返回值：String类型。任一输入为NULL，返回NULL。转换过程以64位精度工作，溢出时报异常。输入如果是负值，即以"-"开头，报异常。 如果输入的是小数，则会转为整数值后进行进制转换，小数部分会被舍弃。 TRUNC函数声明：用途：将输入值number截取到指定小数点位置。double trunc(double number[, bigint decimal_places])参数说明： number：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其 他类型抛异常。 decimal_places：Bigint类型常量，要截取到的小数点位置，其他类型参数会隐式转为bigint，省略 此参数时默认到截取到个位数。 返回值：返回值类型为Double。若number或decimal_places为 NULL，返回NULL。 截取掉的部分补0。 decimal_places可以是负数，负数会从小数点向左开始截取，并且不保留小数部分；如果 decimal_places超过了整数部分长度，返回0。 CHAR_MATCHCOUNT：用途：用于计算str1中有多少个字符出现在str2中。字符串函数函数声明：bigint char_matchcount(string str1, string str2)参数说明： str1，str2：String类型，必须为有效的UTF-8字符串，如果对比中发现有无效字符则函数返回负值。 返回值：Bigint类型。任一输入为NULL返回NULL。 char_matchcount('abd', 'aabc') = 2 -- str1中得两个字符串'a', 'b'在str2中出现过 CHR：用途：将给定ASCII码ascii转换成字符。string chr(bigint ascii)参数说明： ascii：Bigint类型ASCII值，若输入为string类型或double类型会隐式转换到bigint类型后参与运算，其它类型抛异常。 返回值：String类型。参数范围是0~255，超过此范围会引发异常。输入值为NULL返回NULL。 CONCAT：用途：返回值是将参数中的所有字符串连接在一起的结果。string concat(string a, string b...)参数说明： a，b等为String类型，若输入为bigint，double或datetime类型会隐式转换为string后参与运算，其 它类型报异常。 返回值：String类型。如果没有参数或者某个参数为NULL，结果均返回NULL。 INSTR：用途：计算一个子串str2在字符串str1中的位置。bigint instr(string str1, string str2[, bigint start_position[, bigint nth_appearance]])参数说明： str1：String类型，搜索的字符串，若输入为bigint，double或datetime类型会隐式转换为string后 参与运算，其它类型报异常。 str2：String类型，要搜索的子串，若输入为bigint，double或datetime类型会隐式转换为string后 参与运算，其它类型报异常。 start_position：Bigint类型，其它类型会抛异常，表示从str1的第几个字符开始搜索，默认起始位置 是第一个字符位置1。开始位置如果小于等于0会引发异常。 nth_appearance：Bigint类型，大于0，表示子串在字符串中的第nth_appearance次匹配的位置 ，如果nth_appearance为其它类型或小于等于0会抛异常。 返回值：Bigint类型 备注 如果在str1中未找到str2，返回0。 任一输入参数为NULL返回NULL 如果str2为空串时总是能匹配成功，因此instr('abc', '') 会返回1 instr('Tech on the net', 'e') = 2 instr('Tech on the net', 'e', 1, 1) = 2 instr('Tech on the net', 'e', 1, 2) = 11 instr('Tech on the net', 'e', 1, 3) = 14 IS_ENCODING：用途：判断输入字符串str是否可以从指定的一个字符集from_encoding转为另一个字符集to_encoding。可用于判断输入是否为"乱码"， 通常的用法是将from_encoding设为"utf-8"，to_encoding设为"gbk"。boolean is_encoding(string str, string from_encoding, string to_encoding) 参数说明： str：String类型，输入为NULL返回NULL。空字符串则可以被认为属于任何字符集。 from_encoding，to_encoding：String类型，源及目标字符集。输入为NULL返回NULL。 返回值：Boolean类型，如果str能够成功转换，则返回true，否则返回false is_encoding('测试', 'utf-8', 'gbk') = true is_encoding('測試', 'utf-8', 'gbk') = true -- gbk字库中有这两个繁体字 is_encoding('測試', 'utf-8', 'gb2312') = false -- gb2312库中不包括这两个字 KEYVALUE：用途: 将srcStr（源字符串）按split1分成“key-value”对，按split2将key-value对分开，返回“key”所对应的value。 KEYVALUE(STRING srcStr，STRING split1，STRING split2， STRING key) KEYVALUE(STRING srcStr， STRING key) //split1 = ";"，split2 = ":" 参数说明： srcStr 输入待拆分的字符串。 key：string类型。源字符串按照split1和split2拆分后，根据该key值的指定，返回其对应的value。 split1, split2：用来作为分隔符的字符串，按照指定的这两个分隔符拆分源字符串。如果表达式中没 有指定这两项，默认split1为’;’, split2为’:’。当某个被split1拆分后的字符串中有多个split2时 ，返回结果未定义； 返回值: String类型。 Split1或split2为NULL时，返回NULL。 srcStr,key为NULL或者没有匹配的key时，返回NULL。 如果有多个key-value匹配，返回第一个匹配上的key对应的value。 keyvalue('0:1\;1:2', 1) = '2' -源字符串为“0:1\;1:2”，因为没有指定split1和split2，默认split1为";"，split2为“:”。经过split1拆分后，key-value对为 ： 0:1\,1:2 经过split2拆分后变成： 0 1/ 1 2 返回key为1所对应的value值，为2。 keyvalue("\;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\;", "\ ;",":","tf") = "21910" -源字符串为 “\;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\;”，按照 split1“\;”拆分后，得出的key-value对为： decreaseStore:1，xcard:1，isB2C:1，tf:21910，cart:1，shipping:2，pf:0，market:shoes，instPayAmount:0 按照split2":"拆分后变成： decreaseStore 1 xcard 1 isB2C 1 tf 21910 cart 1 shipping 2 pf 0 market shoes instPayAmount 0 key值为“tf”,返回其对应的value:21910。 keyvalue("阿里云=飞天=2;飞天=数据平台", ";","=", "阿里云") 返回NULL，请用户避免这种用法。 REGEXP_EXTRACT：用途：将字符串source按照pattern正则表达式的规则拆分，返回第occurrence个group的字符。 string regexp_extract(string source, string pattern[, bigint occurrence])参数说明： source：String类型，待搜索的字符串。 pattern：String类型常量，pattern为空串时抛异常，pattern中如果没有指定group，抛异常。 occurrence：Bigint类型常量，必须>=0，其它类型或小于0时抛异常，不指定时默认为1，表示返回 第一个group。若occurrence = 0，返回满足整个pattern的子串。 返回值：String类型，任一输入为NULL返回NULL。 regexp_extract('foothebar', 'foo(.*?)(bar)', 1) = the regexp_extract('foothebar', 'foo(.*?)(bar)', 2) = bar regexp_extract('foothebar', 'foo(.*?)(bar)', 0) = foothebar regext_extract('8d99d8', '8d(\\d+)d8') = 99 -- 如果是在ODPS客户端上提交正则计算的SQL，需要使用两个"\"作为转移字符 regexp_extract('foothebar', 'foothebar') -- 异常返回，pattern中没有指定group REGEXP_INSTR：用途：返回字符串source从start_position开始，和pattern第n次(nth_occurrence)匹配的子串的起始/结束位置。任一输入参数为NULL时返回NULL。 bigint regexp_instr(string source, string pattern[, bigint start_position[, bigint nth_occurrence[, bigint return_option]])参数说明： source：String类型，待搜索的字符串。 pattern：String类型常量，pattern为空串时抛异常。 start_position：Bigint类型常量，搜索的开始位置。不指定时默认值为1，其它类型或小于等于0的 值会抛异常。 nth_occurrence：Bigint类型常量，不指定时默认值为1，表示搜索第一次出现的位置。小于等于0或 者其它类型抛异常。 return_option：Bigint类型常量，值为0或1，其它类型或不允许的值会抛异常。0表示返回匹配的开 始位置，1表示返回匹配的结束位置。 返回值：Bigint类型。视return_option指定的类型返回匹配的子串在source中的开始或结束位置。 regexp_instr("i love www.taobao.com", "o[[:alpha:]]{1}", 3, 2) = 14 REGEXP_REPLACE：用途：将source字符串中第occurrence次匹配pattern的子串替换成指定字符串replace_string后返回。 string regexp_replace(string source, string pattern, string replace_string[, bigint occurrence])参数说明：source：String类型，要替换的字符串。 pattern：String类型常量，要匹配的模式，pattern为空串时抛异常。 replace_string：String类型，将匹配的pattern替换成的字符串。 occurrence：Bigint类型常量，必须大于等于0，表示将第几次匹配替换成replace_string，为0时表 示替换掉所有的匹配子串。 其它类型或小于0抛异常。可缺省，默认值为0。 返回值：String类型，当引用不存在的组时，不进行替换。当输入source，pattern，occurrence参数为NULL时返回NULL， 若replace_string为NULL且pattern有匹配，返回NULL，replace_string为NULL但 pattern不匹配，则返回原串 。 regexp_replace("123.456.7890", "([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})", "(\\1)\\2-\\3", 0) = "(123)456-7890" regexp_replace("abcd", "(.)", "\\1 ", 0) = "a b c d " regexp_replace("abcd", "(.)", "\\1 ", 1) = "a bcd" regexp_replace("abcd", "(.)", "\\2", 1) = "abcd" -- 因为pattern中只定义了一个组，引用的第二个组不存在， -- 请避免这样使用，引用不存在的组的结果未定义。 regexp_replace("abcd", "(.*)(.)$", "\\2", 0) = "d" regexp_replace("abcd", "a", "\\1", 0) = "bcd" -- 因为在pattern中没有组的定义，所以\1引用了不存在的组， -- 请避免这样使用，引用不存在的组的结果未定义。 REGEXP_SUBSTR：用途：从start_position位置开始，source中第nth_occurrence次匹配指定模式pattern的子串。 string regexp_substr(string source, string pattern[, bigint start_position[, bigint nth_occurrence]])参数说明：source：String类型，搜索的字符串。 pattern：String类型常量，要匹配的模型，pattern为空串时抛异常。 start_position：Bigint常量，必须大于0。其它类型或小于等于0时抛异常，不指定时默认为1，表示 从source的第一个字符开始匹配。不指定时默认为1， 表示从source的第一个字符开始匹配。 nth_occurrence：Bigint常量，必须大于0，其它类型或小于等于0时抛异常。不指定时默认为1，表 示返回第一次匹配的子串。不指定时默认为1， 表示返回第一次匹配的子串。 返回值：String类型。任一输入参数为NULL返回NULL。没有匹配时返回NULL。 regexp_substr ("I love aliyun very much", "a[[:alpha:]]{5}") = "aliyun" regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) = " have" regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 2) = " 2" REGEXP_COUNT：用途：计算source中从start_position开始，匹配指定模式pattern的子串的次数。 bigint regexp_count(string source, string pattern[, bigint start_position])参数说明： source：String类型，搜索的字符串，其它类型报异常。 pattern：String类型常量，要匹配的模型，pattern为空串时抛异常，其它类型报异常。 start_position：Bigint类型常量，必须大于0。其它类型或小于等于0时抛异常，不指定时默认为 1，表示从source的第一个字符开始匹配。 返回值：Bigint类型。没有匹配时返回0。任一输入参数为NULL返回NULL。 regexp_count('abababc', 'a.c') = 1 regexp_count('abcde', '[[:alpha:]]{2}', 3) = 1regexp_count('abababc', 'a.c') = 1 regexp_count('abcde', '[[:alpha:]]{2}', 3) = 1 SPLIT_PART：用途：依照分隔符separator拆分字符串str，返回从第start部分到第end部分的子串(闭区间)。 string split_part(string str, string separator, bigint start[, bigint end])参数说明： Str：String类型，要拆分的字符串。如果是bigint，double或者datetime类型会隐式转换到string类 型后参加运算，其它类型报异常。 Separator：String类型常量，拆分用的分隔符，可以是一个字符，也可以是一个字符串，其它类型 会引发异常。 start：Bigint类型常量，必须大于0。非常量或其它类型抛异常。返回段的开始编号(从1开始)，如果 没有指定end，则返回start指定的段。 end：Bigint类型常量，大于等于start，否则抛异常。返回段的截止编号，非常量或其他类型会引发 异常。可省略，缺省时表示最后一部分。 返回值：String类型。若任意参数为NULL，返回NULL；若separator为空串，返回原字符串str。 如果separator不存在于str中，且start指定为1，返回整个str。若输入为空串，输出为空串。 如果start的值大于切分后实际的分段数，例如：字符串拆分完有6个片段，但start大于6，返回 空串""。 若end大于片段个数，按片段个数处理。 split_part('a,b,c,d', ',', 1) = 'a' split_part('a,b,c,d', ',', 1, 2) = 'a,b' split_part('a,b,c,d', ',', 10) = '' SUBSTR：用途：返回字符串str从start_position开始长度为length的子串。 string substr(string str, bigint start_position[, bigint length])参数说明： str：String类型，若输入为bigint，double或者datetime类型会隐式转换为string后参与运算，其它 类型报异常。 start_position：Bigint类型，当start_position为负数时表示开始位置是从字符串的结尾往前倒数 ，最后一个字符是-1，起始位置为1。其它类型抛异常。 length：Bigint类型，大于0，其它类型或小于等于0抛异常。子串的长度。 返回值：String类型。若任一输入为NULL，返回NULL。 substr("abc", 2) = "bc" substr("abc", 2, 1) = "b" DATEPART：用途：提取日期date中指定的时间单位datepart的值。 bigint datepart(datetime date, string datepart)参数说明： date：Datetime类型，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异 常。 datepart：String类型常量。支持扩展的日期格式。若datepart不符合指定格式或者其它类型则会发 生异常。 返回值：Bigint类型。若任一输入参数为NULL，返回NULL。 datepart('2013-06-08 01:10:00', 'yyyy') = 2013 datepart('2013-06-08 01:10:00', 'mm') = 6 窗口函数：ODPS SQL中可以使用窗口函数进行灵活的分析处理工作，窗口函数只能出现在select子句中，窗口函数中不要 嵌套使用窗口函数和聚合函数， 窗口函数不可以和同级别的聚合函数一起使用。目前在一个ODPS SQL语句中，可以使用至多5个窗口函数。窗口函数的语法 window_func() over (partition by col1, [col2…] [order by col1 [asc|desc][, col2[asc|desc]…]] windowing_clause) partition by部分用来指定开窗的列。分区列的值相同的行被视为在同一个窗口内。现阶段，同一窗 口内最多包含1亿行数据，否则运行时报错。 order by用来指定数据在一个窗口内如何排序 windowing_clause部分可以用rows指定开窗方式，有两种方式： rows between x preceding|following and y preceding|following表示窗口范围是从前或后x行到前或后y行。 rows x preceding|following窗口范围是从前或后第x行到当前行。 ** x，y必须为大于等于0的整数常量 ，限定范围0 ~ 10000，值为0时表示当前行。必须指定order by才可以用rows方式指定窗口范围。 并非所有的窗口函数都可以用rows指定开窗方式，支持这种用法的窗口函数有avg、count、 max、min、stddev和sum。 select id,count(*) over(partition by sex) as sexnum from tb 在Over子句加上了Order By，来完成一个平均数累加，如果不使用窗口函数，那一定是游标，循环等麻烦的方式
DENSE_RANK：用途：计算连续排名。col2相同的行数据获得的排名相同。 bigint dense_rank() over(partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…])参数说明： partition by col1[, col2..]：指定开窗口的列。 order by col1 [asc|desc], col2[asc|desc]：指定排名依据的值。 返回值：Bigint类型。 RANK：用途：计算排名。col2相同的行数据获得排名顺序下降。 bigint rank() over(partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…])参数说明： partition by col2[, col2..]：指定开窗口的列。 order by col1 [asc|desc], col2[asc|desc]：指定排名依据的值。 返回值：Bigint类型。 PERCENT_RANK：用途：计算一组数据中某行的相对排名。 参数说明： partition by col1[, col2..]：指定开窗口的列。 order by col1 [asc|desc], col2[asc|desc]：指定排名依据的值。 返回值：Double类型，值域为[0, 1]，相对排名的计算方式为为：(rank-1)/(number of rows -1)。 目前限制单个窗口内的行数不超过10,000,000条 ROW_NUMBER：用途：计算行号，从1开始。 row_number() over(partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…])参数说明： partition by col1[, col2..]：指定开窗口的列。 order by col1 [asc|desc], col2[asc|desc]：指定结果返回时的排序的值。 返回值：Bigint类型。 有代表性的 ROW_NUMBER()函数，该函数通常被用于分页，该函数从1开始不断递增，可以和Partition By一起使用，当穿越分区边界时，Row_Number重置为1
select class1, score, rank() over (partition by class1 order by score desc ) rk1, dense_rank() over (partition by class1 order by score desc ) rk2, row_number() over (partition by class1 order by score desc ) rk3 from zyy_test1;
dense_rank、percent_rank、rank和 row_number 都 是排名函数，区别在于 dense_rank 是 连续排名， 会对相同数值，输出相同的序号，但下一个序号， 间断 ， rank 遇到排名并列时， 下一列排名跳空 。 会对相同数值，输出相同的序号，而且下一个 序号不间断 ； percent_rank ，介于0和1直接的小数形式表示，计算方法是(rank - 1) / (n-1)，其中rank为行的序号，n为组的行数。 row_number 会对所有数值输出不同的序号，序号 唯一连续 ；
LAG：用途：按偏移量取当前行之前第几行的值，如当前行号为rn，则取行号为rn-offset的值。 lag(expr，bigint offset, default) over(partition by col1[, col2…] [order by col1 [asc|desc][, col2[asc|desc]…]])参数说明： expr：任意类型。 offset：Bigint类型常量，输入为string，double到bigint的隐式转换，offset > 0。 default：当offset指定的范围越界时的缺省值，常量，默认值为NULL。 partition by col1[, col2..]：指定开窗口的列。 order by col1 [asc|desc], col2[asc|desc]：指定返回结果的排序方式。 返回值：同expr类型。 LEAD：用途：按偏移量取当前行之后第几行的值，如当前行号为rn则取行号为rn+offset的值。 lead(expr, bigint offset, default) over(partition by col1[, col2…] [order by col1 [asc|desc][, col2[asc|desc]…]])参数说明： expr：任意类型。 offset：Bigint类型常量，输入为string，double到bigint的隐式转换，offset > 0。 default：当offset指一的范围越界时的缺省值，常量。 partition by col1[, col2..]：指定开窗口的列。 order by col1 [asc|desc], col2[asc|desc]：指定返回结果的排序方式。 返回值：同expr类型。 分析函数是LEAD()和 LAG() ，这两个分析函数经过Order By子句排序后，可以在当前行访问上N行（LAG）或下N行（LEAD）的数据 LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值, 与LAG相反 -- 组内排序后，向后或向前偏移 -- 如果省略掉第三个参数，默认为NULL，否则补上。 select dp_id, mt, payment, LAG(mt, 2 ) over (partition by dp_id order by mt) mt_newb from test2; -- 组内排序后，向后或向前偏移 -- 如果省略掉第三个参数，默认为NULL，否则补上。 select dp_id, mt,payment,LEAD(mt, 2 , '1111-11' ) over (partition by dp_id order by mt) mt_new from test2;
first_value:  取分组内排序后， 截止到当前行 ，第一个值 last_value:  取分组内排序后， 截止到当前行 ，最后一个值 -- FIRST_VALUE 获得组内当前行往前的首个值-- LAST_VALUE 获得组内当前行往前的最后一个值-- FIRST_VALUE(DESC) 获得组内全局的最后一个值 select dp_id, mt, payment, FIRST_VALUE(payment) over (partition by dp_id order by mt) payment_g_first, LAST_VALUE(payment) over (partition by dp_id order by mt) payment_g_last, FIRST_VALUE(payment) over (partition by dp_id order by mt desc ) payment_g_last_global from test2 ORDER BY dp_id,mt;



CLUSTER_SAMPLE：用途：分组抽样。 boolean cluster_sample(bigint x[, bigint y]) over(partition by col1[, col2..])参数说明： x：Bigint类型常量，x>=1。若指定参数y，x表示将一个窗口分为x份；否则，x表示在一个窗口中抽 取x行记录(即有x行返回值为true)。x为NULL时，返回值为NULL。 y：Bigint类型常量，y>=1，y<=x。表示从一个窗口分的x份中抽取y份记录(即y份记录返回值为 true)。y为NULL时，返回值为NULL。 partition by col1[, col2]：指定开窗口的列。 返回值：Boolean类型。 示例，如表test_tbl中有key，value两列，key为分组字段，值有groupa，groupb两组，value为值，如下 +------------+--------------------+ | key | value | +------------+--------------------+ | groupa | -1.34764165478145 | | groupa | 0.740212609046718 | | groupa | 0.167537127858695 | | groupa | 0.630314566185241 | | groupa | 0.0112401388646925 | | groupa | 0.199165745875297 | | groupa | -0.320543343353587 | | groupa | -0.273930924365012 | | groupa | 0.386177958942063 | | groupa | -1.09209976687047 | | groupb | -1.10847690938643 | | groupb | -0.725703978381499 | | groupb | 1.05064697475759 | | groupb | 0.135751224393789 | | groupb | 2.13313102040396 | | groupb | -1.11828960785008 | | groupb | -0.849235511508911 | | groupb | 1.27913806620453 | | groupb | -0.330817716670401 | | groupb | -0.300156896191195 | | groupb | 2.4704244205196 | | groupb | -1.28051882084434 | +------------+--------------------+ 想要从每组中抽取约10%的值，可以用以下ODPS SQL完成： select key, value from ( select key, value, cluster_sample(10, 1) over(partition by key) as flag from tbl) sub where flag = true; +--------+--------------------+ | key | value | +--------+--------------------+ | groupa | -1.34764165478145 | | groupb | -0.725703978381499 | | groupb | 2.4704244205196 | +-----+-----------------------+ WM_CONCAT：用途：用指定的spearator做分隔符，链接str中的值。 string wm_concat(string separator, string str)参数说明： separator：String类型常量，分隔符。其他类型或非常量将引发异常。 str：String类型，若输入为bigint，double或者datetime类型会隐式转换为string后参与运算，其它 类型报异常。 返回值：String类型。 对语句"select wm_concat(',', name) from test_src;"，若test_src为空集合，这ODPS SQL条 语句返回NULL值。 聚合函数，其输入与输出是多对一的关系，即将多条输入记录聚合成一条输 出值。可以与SQL中的group by语句联用。 COALESCE：用途：返回列表中第一个非NULL的值，如果列表中所有的值都是NULL则返回NULL。 coalesce(expr1, expr2, ...)参数说明： expri是要测试的值。所有这些值类型必须相同或为NULL，否则会引发异常。 返回值：返回值类型和参数类型相同。 DECODE：用途：实现if-then-else分支选择的功能。 decode(expression, search, result[, search, result]...[, default])参数说明： expression：要比较的表达式。 search：和expression进行比较的搜索项。 result：search和expression的值匹配时的返回值。 default：可选项，如果所有的搜索项都不匹配，则返回此default值，如果未指定，则会返回NULL。 返回值：返回匹配的search；如果没有匹配，返回default；如果没有指定default，返回NULL。 select decode(customer_id,1, 'Taobao',2, 'Alipay',3, 'Aliyun',NULL, 'N/A','Others') as result from sale_detail; 上面的decode函数实现了下面if-then-else语句中的功能： if customer_id = 1 then result := 'Taobao'; elsif customer_id = 2 then result := 'Alipay'; elsif customer_id = 3 then result := 'Aliyun';...else result := 'Others';end if; 但需要用户注意的是，通常情况下ODPS SQL在计算NULL = NULL时返回NULL，但在decode函数中 ，NULL与NULL的值是相等的。 在上述事例中，当customer_id的值为NULL时，decode函数返回"N/A"。 GREATEST：用途：返回输入参数中最大的一个。 greatest(var1, var2, …)参数说明： var1，var2可以为bigint，double，datetime或者string。若所有值都为NULL则返回NULL。 返回值: 输入参数中的最大值，当不存在隐式转换时返回同输入参数类型。 NULL为最小值。 当输入参数类型不同时，double，bigint，string之间的比较转为double；string，datetime的比较 转为datetime。不允许其它的隐式转换。 ORDINAL：用途：将输入变量按从小到大排序后，返回nth指定的位置的值。 ordinal(bigint nth, var1, var2, …)参数说明： nth：Bigint类型，指定要返回的位置，为NULL时返回NULL。 var1，var2：类型可以为bigint，double，datetime或者string。 返回值： 排在第nth位的值，当不存在隐式转换时返回同输入参数类型。 有类型转换时，double，bigint，string之间的转换返回double。string，datetime之间的转换返回 datetime。不允许其它的隐式转换。 NULL为最小。 ordinal(3, 1, 3, 2, 5, 2, 4, 6) = 2 LEAST：用途：返回输入参数中最小的一个。 least(var1, var2, …)参数说明： var1，var2可以为bigint，double，datetime或者string。若所有值都为NULL则返回NULL。 返回值： 输入参数中的最小值，当不存在隐式转换时返回同输入参数类型。 NULL为最小。 有类型转换时，double，bigint，string之间的转换返回double。string，datetime之间的转换返回 datetime。不允许其它的隐式类型转换。 UUID：用途：返回一个随机ID，形式示例："29347a88-1e57-41ae-bb68-a9edbdd94212"。string uuid() SAMPLE：用途：对所有读入的column_name的值，sample根据x，y的设置做采样，并过滤掉不满足采样条件的行。 boolean sample(x, y, column_name)参数说明： x，y：Bigint类型，表示哈希为x份，取第y份。y可省略，省略时取第一份，如果省略参数中的y，则必须同时省略column_name。x，y为整型常量， 大于0，其它类型或小于等于0时抛异常，若y>x也 抛异常。x，y任一输入为NULL时返回NULL。 column_name是采样的目标列。column_name可以省略，省略时根据x，y的值随机采样。任意类 型，列的值可以为NULL。不做隐式类型转换。 如果column_name为常量NULL会报异常。 返回值：Boolean类型。 为了避免NULL值带来的数据倾斜，因此对于column_name中为NULL的值，会在x份中进行均 匀哈希。如果不加column_name，则数据量比较少时输出不一定均匀， 在这种情况下建议加上 column_name，以获得比较好的输出结果。 select * from tbla where sample (4, 1 , cola) = true; -- 表示数值会根据cola hash为4份，取第1份 select * from tbla where sample (4, 2) = true; -- 表示数值会对每行数据做随机哈希分配为4份，取第2份 CASE WHEN表达式：ODPS提供两种case when语法格式，如下所述： case value when (_condition1) then result1 when (_condition2) then result2 ... else resultn end case when (_condition1) then result1 when (_condition2) then result2 when (_condition3) then result3 ... else resultn end case when表达式可以根据表达式value的计算结果灵活返回不同的值， 如以下语句根据shop_name的不同情况得出所属区域 select case when shop_name is null then 'default_region' when shop_name like 'hang%' then 'zj_region' end as region from sale_detail; 如果result类型只有bigint，double，统一转double再返回； 如果result类型中有string类型，统一转string再返回，如果不能转则报错(如boolean型)； 除此之外不允许其它类型之间的转换；

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！