3.5. S3 选择操作（技术预览）

算术运算符

^ * % / + - ( )

select (int(_1)+int(_2))*int(_9) from s3object;

算术运算符

% modulo

select count(*) from s3object where cast(_1 as int)%2 == 0;

算术运算符

^ power-of

从 s3object 选择 cast（2^10，为 int）；

比较运算符

> < >= ⇐ == !=

select _1,_2 from s3object where (int(_1)+int(_3))>int(_5);

逻辑运算符

AND 或 NOT

select count(*) from s3object where not (int(1)>123 and int(_5)<200);

逻辑运算符

is null

为表达式中的 null 返回 true/false

逻辑运算符和 NULL

is not null

为表达式中的 null 返回 true/false

逻辑运算符和 NULL

未知状态

查看 null-handle，并观察使用 NULL 的逻辑操作的结果。查询返回 0。

select count(*) from s3object where null and (3>2);

带有 NULL 的算术运算符

未知状态

查看 null-handle，并观察使用 NULL 的二进制操作的结果。查询返回 0。

select count(*) from s3object where (null+1) and (3>2);

与 NULL 进行比较

未知状态

回顾空客户端并观察与 NULL 比较操作的结果。查询返回 0。

select count(*) from s3object where (null*1.5) != 3;

缺少列

未知状态

select count(*) from s3object where _1 is null;

projection 列

与 if or then or else 类似

选择条件

when (1+1==(2+1)*3) then ‘case_1’ when 4*3)==(12 then ‘case_2’ else ‘case_else’ end, age*2 from s3object;

逻辑运算符

coalesce 返回第一个非null 参数

select coalesce(nullif(5,5),nullif(1,1.0),age+12) from s3object;

逻辑运算符

如果两个参数都相等，则 nullif 返回 null，否则第一个参数为nullif (1,1)=NULL nullif (null,1)=NULL nullif (2,1)=2

select nullif(cast(_1 as int),cast(_2 as int)) from s3object;

逻辑运算符

{expression} in ( .. {expression} ..)

select count(*) from s3object where ‘ben’ in (trim(_5),substring(_1,char_length(_1)-3,3),last_name);

逻辑运算符

{expression} 和 {expression} 之间的 {expression}

select countprincipal from stdin where substring (_3,char_length (_3),1) between "x" and trim (_1) and substring (_3,char_length (_3)-1,1)== ":";

逻辑运算符

{expression} like {match-pattern}

select count() from s3object where first_name like ‘%de_’; select count() from s3object where _1 like "%a[r-s];

casting operator

select cast(123 as int)%2 from s3object;

casting operator

select cast(123.456 as float)%2 from s3object;

casting operator

select cast(‘ABC0-9’ as string),cast(substr(‘ab12cd’,3,2) as int)*4 from s3object;

casting operator

select cast(substring(‘publish on 2007-01-01’,12,10) as timestamp) from s3object;

非 AWS casting operator

select int(_1),int( 1.2 + 3.4) from s3object;

非 AWS casting operator

select float(1.2) from s3object;

非 AWS casting operator

select timestamp(‘1999:10:10-12:23:44’) from s3object;

聚合功能

sun

select sum(int(_1)) from s3object;

聚合功能

avg

select avg(cast(_1 a float) + cast(_2 as int)) from s3object;

聚合功能

分钟

select avg(cast(_1 a float) + cast(_2 as int)) from s3object;

聚合功能

max

select max(float(_1)),min(int(_5)) from s3object;

聚合功能

数量

select count(*) from s3object where (int(1)+int(_3))>int(_5);

时间戳功能

extract

select count(*) from s3object where extract(‘year’,timestamp(_2)) > 1950 and extract(‘year’,timestamp(_1)) < 1960;

时间戳功能

dateadd

select count(0) from s3object where datediff(‘year’,timestamp(_1),dateadd(‘day’,366,timestamp(_1))) == 1;

时间戳功能

datediff

select count(0) from s3object where datediff(‘month’,timestamp(_1),timestamp(_2))) == 2;

时间戳功能

utcnow

select count(0) from s3object where datediff(‘hours’,utcnow(),dateadd(‘day’,1,utcnow())) == 24

字符串函数

子字符串

select count(0) from s3object where int(substring(_1,1,4))>1950 and int(substring(_1,1,4))<1960;

字符串函数

trim

select trim(‘ foobar ‘) from s3object;

字符串函数

trim

select trim(trailing from ‘ foobar ‘) from s3object;

字符串函数

trim

select trim(leading from ‘ foobar ‘) from s3object;

字符串函数

trim

select trim(both ‘12’ from ‘1112211foobar22211122’) from s3objects;

字符串函数

lower 或 upper

select trim(both ‘12’ from ‘1112211foobar22211122’) from s3objects;

字符串函数

char_length, character_length

select count(*) from s3object where char_length(_3)==3;

复杂的查询

select sum(cast(_1 as int)),max(cast(_3 as int)), substring(‘abcdefghijklm’, (2-1)*3+sum(cast(_1 as int))/sum(cast(_1 as int))+1, (count() + count(0))/count(0)) from s3object;

别名支持

select int(_1) as a1, int(_2) as a2 , (a1+a2) as a3 from s3object where a3>100 and a3<300;