Playground  Resume

Presto Functions

Operator

Logical Operators

Operator 説明
AND 両方の値がtrueであればtrue a and b
OR どちらかでもtrueであればtrue a or b
NOT trueであればfalse, falseであればtrue NOT a

nullとの比較時は以下のようになっています。

/*AND*/
select cast(null as boolean) and true; -- null
select cast(null as boolean) and false; -- false
select cast(null as boolean) and cast(null as boolean); -- null

/*OR*/
select cast(null as boolean) or cast(null as boolean); -- null
select cast(null as boolean) or false; -- null
select cast(null as boolean) or true; -- true

/*NOT*/
select NOT cast(null as boolean); -- null

Comparison Functions and Operators

Comparison Operators
Operator 説明
< より小さい
> より大きい
<= 以下
>= 以上
= 同値
<> 異なる値
!= 異なる値
Range Operator: between

以下のような構文でvaluemin以上かつmax以下かをチェックすることが出来ます。
返却結果はboolean型になります。
value between min and max

select 3 between 2 and 6;; -- true
select 3 NOT between 2 and 6; -- false

/*nullが使用された場合*/
select null between 2 and 4; -- null
select 2 between null and 6; -- null

/*文字列を使用する場合*/
select 'Paul' between 'John' and 'Ringo'; -- true
IS NULL and IS NOT NULL
select null is null; -- true
select 3.0 is null; -- false
IS DISTINCT from and IS NOT DISTINCT from

prestoではIS DISTINCT FROMIS NOT DISTINCT FROMという構文があり、これを用いてもnullとの比較を行うことが出来ます。

a b a = b a <> b a DISTINCT b a NOT DISTINCT b
1 1 true false false true
1 2 false true true false
1 null null null true false
null null null null false true
select 1 is distinct from 1; -- false
select 1 is distinct from 2; -- true

/*nullとの比較*/
select 1 is distinct from null; -- true
select null is distinct from null; -- false
select null is NOT distinct from null; -- true
GREATEST and LEAST

引数のうち最も大きな(小さな)値を返却します。文字列に対しても使用することが可能です。

select greatest(1, 2); -- 2
select greatest(1, 2, 3); -- 3
select least(1, 2); -- 1
select least(1, 2, 3); -- 1

/*文字列を使用*/
select greatest('a', 'b'); -- b
select least('a', 'b'); -- a
Quantified Comparison Predicates: ALL, ANY and SOME

サブクエリとの比較時に用いるALLANYSOMEについて紹介します。

/*ANY: サブクエリ内に'hello'が1つ以上存在するためtrueが返却されます*/
select 'hello' = any (values 'hello', 'world'); -- true

/*ALL: サブクエリ内の全ての値が「21より大きい」という条件を満たしていないためfalseが返却されます*/
select 21 < all (values 19, 20, 21); -- false

/*SOME: ANYと同じ動作をします*/
select 42 >= some (select 41 union all select 42 union all select 43); -- true

Conditional Expressions

CASE
一般的なSQLと同様にCASE文をサポートしています。

select a,
       case a
           when 1 then 'one'
           when 2 then 'two'
           else 'many'
       end

IF
CASEをよりシンプルに書く方法としてIFが存在します。
if(condition, true_value) conditionの結果がtrueの時にtrue_valuefalseの時はnullを返却します
if(condition, true_value, false_value) conditionの結果がtrueの時にtrue_valuefalseの時にfalse_valueを返却します

select if(1=1, 'foo'); -- foo
select if(1=2, 'foo'); -- null

select if(1=1, 'foo', 'bar'); -- foo
select if(1=2, 'foo', 'bar'); -- bar

COALESCE
引数を左から評価し、null以外の値が見つかったら、その値を返却します。 全ての引数の値がnullの場合はnullを返却します。

select coalesce(null, 1, 2); -- 1
select coalesce(null, 'foo'); -- foo

select coalesce(null, null); -- null

nullIF
nullif(value1, value2)
引数は必ず2つ必要で、value1とvalue2が同じ値であればnullを返却します。
異なる場合はvalue1を返却します。

select nullif(1, 1); -- null

select nullif(1, 2); -- 1
select nullif(2, 1); -- 2

TRY
try(expression)
exporessionを評価してエラーが発生した場合はnullを返却します。
例えば、以下のようなshippingテーブルが存在したとします。

origin_state | origin_zip | packages | total_cost
--------------+------------+----------+------------
California   |      94131 |       25 |        100
California   |      XXXXX |        5 |         72
California   |      94025 |        0 |        155
New Jersey   |      08544 |      225 |        490
(4 rows)

origin_zipカラムに文字型の値が存在しているため、cast文でINTEGER型に変換しようとするとクエリは失敗します。   こういった場合にTRYを使用することで、該当部分をnullに置き換える事ができます。

select try(cast(origin_zip as bigint)) from shipping;
origin_zip
------------
     94131
null
     94025
     08544
(4 rows)

更にCOALESCEと組み合わせることで、nullの部分を他の値に置き換えることも可能です。

select coalesce(TRY(cast(origin_zip as bigint)), 0) from shipping;
origin_zip
------------
     94131
         0
     94025
     08544
(4 rows)

Conversion Functions

Conversion Functions

castの他にtry_castという関数が存在します。
これはcastの際にエラーでクエリを失敗させたくない時などに使用します。  

select cast('foo' as integer); -- Can not cast 'foo' to INT

select try_cast('foo' as integer); -- null
Miscellaneous

typeof(expr) | varchar typeof関数ではexprを評価した結果の型を文字列として返却します。

select typeof(123); -- integer
select typeof('cat'); -- varchar(3)
select typeof(1.5); -- double
select typeof(true); -- boolean
select typeof(null); -- unknown

Mathematical Functions and Operators

Mathematical Operators

四則演算と剰余をサポートしています。

Operator 説明
+ 加算
- 減算
* 乗算
/ 除算
% 剰余
Mathematical Functions
Operator 返却結果の型 説明
abs(x) [same as input] xの絶対値を返却します
cbrt(x) double xの立方根を返却します
ceil(x) [same as input] ceiling(x)のエイリアスです
ceiling(x) [same as input] xを切り上げた値を返却します
cosine_similarity(x, y) double xとyのコサイン類似度を返却します
degrees(x) double xをラジアンとして角度へ変換します
e() double 自然対数を返却します
exp(x) double e()^xを返却します
floor(x) [same as input] xを切り捨てた値を返却します
from_base(string, radix) bigint TODO
ln(x) double 自然対数を返却します
log2(x) double 2を底としたxの対数を返却します
log10(x) double 10を底としたxの対数を返却します
log(x, b) double bを底としたxの対数を返却します
mod(n, m) [same as input] nをmで割った余りを返却します
pi() double πを返却します
pow(x, p) double power()のエイリアスです
power(x, p) double x^pを返却します
radians(x) double xを角度としてラジアンに変換します
rand() double random()のエイリアスです
random() double 0.0 <= x < 1.0の値をランダムで返却します0.0 <= x < 1.0の値をランダムで返却します
random(n) [same as input] 0 < x < nの値をランダムで返却します
round(x) [same as input] 丸め込みを行い近似値を返却します
round(x, d) [same as input] dで小数点以下の桁数を指定し、丸め込みを行い近似値を返却します
sign(x) [same as input] xが0であれば0、0より大きければ1、0より小さければ-1を返却します
sqrt(x) double xの平方根を返却します
to_base(x, radix) varchar TODO
truncate(x) double 小数点以下を切り捨てた値を返却します
width_bucket(x, bound1, bound2, n) bigint TODO
width_bucket(x, bins) bigint TODO
Trigonometric Functions
Operator 返却結果の型 説明
acos(x) double xのarc cosineを返却します
asin(x) double xのarc sineを返却します
atan(x) double xのarc tangentを返却します
atan2(y, x) double y/xのarc tangentを返却します
cos(x) double xのcosineを返却します
cosh(x) double xのhyperbolic cosineを返却します
sin(x) double xのsineを返却します
tan(x) double xのtangentを返却します
tanh(x) double xのhyperbolic tangentを返却します
Floating Point Functions
Operator 返却結果の型 説明
infinity() double 正の∞を返却します
is_finite(x) boolean xが有限であればtrue、異なればfalseを返却します
is_infinite(x) boolean xが∞であればtrue、異なればfalseを返却します
is_nan(x) boolean xがNaN(not-a-number)であればtrue、異なればfalseを返却します
nan() double NaNを返却します

全部の関数を書くぞ!と思い立ち書き始めてみましたが、あまりにも多いので一旦中断…!気になる関数とかがあれば追加していきたと思います。