S3 从对象中选择内容

算术运算符

^ * % / + - ( )

从 s3object; 中选择 (int (_ 1) + int (_ 2)) * int (_ 9)

算术运算符

模百分比

从 s3object 中选择计数 (*) ，其中 cast (_1 as int) %2 = 0;

算术运算符

^ 次幂

从 s3object; 中选择强制类型转换 (2 ^ 10 作为 int)

比较运算符

> < >= <= = !=

从 s3object 中选择 _1 ， _2 ，其中 (int (_ 1) + int (_ 3)) >int (_ 5);

逻辑运算符

AND 或 NOT

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

逻辑运算符

为空

针对表达式中的空指示返回 true/false

逻辑运算符和 NULL

不为空

针对表达式中的空指示返回 true/false

逻辑运算符和 NULL

未知状态

查看空句柄并观察具有 NULL 的逻辑操作的结果。 查询将返回 0。

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

具有 NULL 的算术运算符

未知状态

查看空句柄并观察具有 NULL 的二进制操作的结果。 查询将返回 0。

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

与 NULL 比较

未知状态

查看 null 句柄并观察具有 NULL 的比较操作的结果。 查询将返回 0。

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

缺少列

未知状态

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

投影列

类似于 if 或 then 或 else

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

投影列

类似于 switch/case 缺省值

逻辑运算符

coalesce 返回第一个非空参数

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} between {expression} and {expression}

select _1 from s3object where cast(_1 as int) between 800 and 900;
select count(*) 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]";

逻辑运算符

强制类型转换运算符

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

强制类型转换运算符

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

强制类型转换运算符

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

强制类型转换运算符

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

非 AWS 强制类型转换操作程序

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

非 AWS 强制类型转换操作程序

select float(1.2) from s3object;

非 AWS 强制类型转换操作程序

select to_timestamp('1999-10-10T12:23:44Z') from s3object;

汇总函数

总和

select sum(int(_1)) from s3object;

汇总函数

平均值

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

汇总函数

最小值

select min( int(_1) * int(_5) ) from s3object;

汇总函数

最大值

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

汇总函数

计数

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

时间戳记函数

提取

select count(*) from s3object where extract(year from to_timestamp(_2)) > 1950 and extract(year from to_timestamp(_1)) < 1960;

时间戳记函数

日期添加

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

时间戳记函数

达迪夫

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

时间戳记函数

乌特克努

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

时间戳记函数

to_string

字符串函数

子串

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

字符串函数

修剪

select trim(‘ foobar ‘) from s3object;

字符串函数

修剪

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

字符串函数

修剪

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

字符串函数

修剪

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

字符串函数

下或上

select lower(‘ABcD12#$e’) from s3object;

字符串函数

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;