3.5. S3 选择操作

算术运算符

^ * % / + - ( )

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

算术运算符

% modulo

select count cycles 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 OR 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;

projection 列

与 switch/case 类似

选择 case cast (_1 as int)+ 1 when 2 then "a" when 3 then "b" other "c" end 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 cycles from s3object where 'ben' in (trim (_5),substring （_1,char_length (_1)-3,3),last_name）;

逻辑运算符

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

select _1 from s3object where cast (_1 as int) between 800 和 900; select count cycles from stdin where substring (_3,char_length (_3),1) between "x" and trim (_1)和 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 to_timestamp ('1999-10-10T12:23:44Z') from s3object;

聚合功能

sun

select sum(int(_1)) from s3object;

聚合功能

avg

select avg (cast (_1 as 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 cycles from s3object where extract (year from to_timestamp (_2))> 1950 and extract (year from to_timestamp (_1))< 1960;

时间戳功能

dateadd

select count (0) from s3object where date_diff (year,to_timestamp (_1),date_add （day,366,to_timestamp (_1))）= 1;

时间戳功能

datediff

select count (0) from s3object where date_diff (month,to_timestamp (_1),to_timestamp (_2))= 2;

时间戳功能

utcnow

select count (0) from s3object where date_diff (hour,utcnow （),date_add (day,1,utcnow （)））= 24

时间戳功能

to_string

select to_string (to_timestamp ("2009-09-17T17:56:06.234567Z"), "yyyyMMdd-H:m:s") from s3object;

字符串函数

子字符串

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

字符串函数

子字符串

来自负数的子字符串被视为第一个有效

select substring ("123456789" from -4) from s3object;

字符串函数

子字符串

来自零的子字符串用于越界数字，就像(first,last)一样有效。

select substring ("123456789" from 0 for 100) from s3object;

字符串函数

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 s3object;

字符串函数

lower 或 upper

select lower ('ABcD12#$e') from s3object;

字符串函数

char_length, character_length

select count cycles 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;