Dataworks - MaxCompute SQL规范 v1.0 - 《Bigdata 大数据相关知识学习》

分区类型

MaxCompute2.0对分区类型的支持进行了扩充，目前MaxCompute支持Tinyint、Smallint、Int、Bigint、Varchar和String分区类型。
create table parttest (a bigint) partitioned by (pt bigint);
insert into parttest partition(pt) select 1, 2 from dual;
insert into parttest partition(pt) select 1, 10 from dual;
select * from parttest where pt >= ‘2’;
执行上述语句后，返回的结果只有一行，因为10被当作字符串和2比较，所以没能返回。

分区使用限制
—创建一个二级分区表，以日期为一级分区，地域为二级分区
create table src (key string, value bigint) partitioned by (pt string,region string);
查询时，Where条件过滤中使用分区列作为过滤条件。
select from src where pt=’20170601’and region=’hangzhou’;
—正确使用方式。MaxCompute在生成查询计划时只会将’20170601’分区下region为’hangzhou’二级分区的数据纳入输入中。select from src where pt = 20170601;
— 错误的使用方式。在这样的使用方式下，MaxCompute并不能保障分区过滤机制的有效性。pt是String类型，当String类型与Bigint（20170601）比较时，MaxCompute会将二者转换为Double类型，此时有可能会有精度损失。
分对分区操作的SQL的运行效率则较低，会给您带来较高的计费，例如输出到动态分区（DYNAMIC PARTITION）。
对于部分MaxCompute的操作命令，处理分区表和非分区表时的语法有差别，详情请参见表操作和 INSERT操作。

输出到动态分区（DYNAMIC PARTITION）
动态分区语法
insert overwrite table tablename partition (partcol1, partcol2 …) select_statement from from_statement;

select_statement字段中，后面的字段将提供目标表动态分区值。如目标表就一级动态分区，则select_statement最后一个字段值即为目标表的动态分区值。
如果目标表有多级分区，在运行Insert语句时允许指定部分分区为静态，但是静态分区必须是高级分区。
动态生成的分区值不允许为NULL，也不支持含特殊字符和中文，否则会引发异常。

动态分区的示例如下：
create table total_revenues (revenue bigint) partitioned by (region string);
insert overwrite table total_revenues partition(region)
select total_price as revenue, region
from sale_detail;
按照上述写法，在SQL运行之前，您无法得知会产生哪些分区。只有在select运行结束后，才能由region字段产生的值确定会产生哪些分区，这也是动态分区名字的由来。
其他示例如下：
create table sale_detail_dypart like sale_detail;—创建示例目标表。
示例一：
insert overwrite table sale_detail_dypart partition (sale_date, region)
select shop_name,customer_id,total_price,sale_date,region from sale_detail;
— 成功返回。
动态分区示例

在使用INSERT OVERWRITE语句插入到一张分区表时，您可以在分区中指定一个分区列名但不提供具体的值，并在SELECT子句中提供对应分区列的值。

示例二：
insert overwrite table sale_detail_dypart partition (sale_date=’2013’, region)
select shop_name,customer_id,total_price,region from sale_detail;
— 成功返回，多级分区，指定一级分区。
示例三：
insert overwrite table sale_detail_dypart partition (sale_date=’2013’, region)
select shop_name,customer_id,total_price from sale_detail;
— 失败返回，动态分区插入时，动态分区列必须在select列表中。
示例四：
insert overwrite table sales partition (region=’china’, sale_date)
select shop_name,customer_id,total_price,region from sale_detail;
— 失败返回，不能仅指定低级子分区，而动态插入高级分区。

如果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
u_name中既有“三”又有“猫”的记录，请运用 and条件
SELECT FROM [user] WHERE u_name LIKE ‘%三%’ AND u_name LIKE ‘%猫%’
[ ]：表示括号内所列字符中的一个（类似正则表达式）
SELECT FROM [user] WHERE u_name LIKE ‘[张李王]三’
将找出“张三”、“李三”、“王三”（而不是“张李王三”）；
要匹配（%）或（）要用转义符表示（‘%’）或（‘_’）。
A RLIKE B ==》 A是字符串，B是字符串常量正则表达式
A<>B（A不等于B）如果A或B为NULL，返回NULL；如果A不等于B，返回TRUE，否则返回FALSE。

*在进行部分关系运算之前，您需要首先进行类型转换，否则可能返回null。如下举例，’2019-02-16 00:00:01’为DATETIME类型，而’2019-02-16’为STRING类型，在进行比较关系运算前需首先完成显示类型转换。
select cast(‘2019-02-16 00:00:01’ AS string) > ‘2019-02-16’;
select cast(‘2019-02-16 00:00:02’ AS datetime) > ‘2019-02-16 00:00:01’;
由于DOUBLE值存在一定的精度差相减取绝对值绝对值足够小时，认为两个DOUBLE数值相等
ABS是MaxCompute提供的内建函数**，意为取绝对值
abs(0.9999999999 - 1.0000000000) < 0.000000001
— 0.9999999999和1.0000000000为10位精度，而0.000000001为9位精度。
— 此时可以认为0.9999999999和1.0000000000相等。

STRING和BIGINT做数值比较时，两边都会转换成DOUBLE类型，在比较过程中会有精度丢失。建议cast string as bigint再比较

说明如果A和B为BIGINT类型，返回结果为DOUBLE类型。*
select age+10, age-10, age%10, -age, ageage, age/10 from user;

STRING类型在参与运算前会进行隐式类型转换，转换为DOUBLE类型。
BIGINT和DOUBLE类型共同参与计算时，会将BIGINT隐式转换为DOUBLE再进行计算，返回结果为DOUBLE类型。
STRING和BIGINT做数值比较时，两边都会转换成DOUBLE类型，在比较过程中会有精度丢失。建议cast string as bigint再比较。