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

显式类型转换

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

为bigint，因此，小数部分会被截断，例如cast(“1.6” as bigint) = 1；

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

as double) = 1.0；

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

Datetime类型之间的转换；

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

boolean类型转换到string类型，可使用函数to_char， 详细介绍请参考 TO_CHAR ，而

to_date函数同样支持从string类型到datetime类型的转换，详细介绍请参考 TO_DATE ；

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

断等情况。

隐式类型转换及其作用域

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

运算符-关系操作符

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<=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