[C++知识库]presto函数大全

官网链接：https://prestodb.io/docs/0.215/functions.html

逻辑运算符

逻辑与操作：and

语法: A AND B
操作类型：boolean
说明：如果A和B均为TRUE，则为TRUE；否则为FALSE。如果A为NULL或B为NULL，则为NULL

select 3 = 1 and 2 = 2;
-- false
select 3 = 3 and 2 = 2;
-- true

逻辑或操作：or

语法: A OR B
操作类型：boolean
说明：如果A为TRUE，或者B为TRUE，或者A和B均为TRUE，则为TRUE；否则为FALSE

select 3 = 1 or 2 = 2;
-- true
select 3 = 3 and 2 = 2;
-- true
select 3 = 1 or 2 = 1;
-- false

逻辑非操作：not

语法: NOT A
操作类型：boolean
说明：如果A为FALSE，或者A为NULL，则为TRUE；否则为FALSE

select not 3 = 1;
-- true
select not 2 = 2;
-- true

比较函数和操作符

小于比较：<

语法: A < B
操作类型：所有基本类型
描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于表达式B，则为TRUE；否则为FALSE

select 3 < 1;
-- false
select 1 < 2;
-- true
select null < 2;
-- NULL
select 1 < NULL;
-- NULL

大于比较：>

语法: A > B
操作类型: 所有基本类型
描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于表达式B，则为TRUE；否则为FALSE

select 3 > 1;
-- true
select 1 > 2;
-- false
select null > 2;
-- NULL
select 1 > NULL;
-- NULL

小于等于比较：<=

语法: A <= B
操作类型: 所有基本类型
描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于或者等于表达式B，则为TRUE；否则为FALSE

select 3 <= 1;
-- false
select 1 <= 2;
-- true
select null <= 2;
-- NULL
select 1 <= NULL;
-- NULL

大于等于比较：>=

语法: A >= B
操作类型: 所有基本类型
描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于或者等于表达式B，则为TRUE；否则为FALSE

select 3 >= 1;
-- true
select 1 >= 2;
-- false
select null >= 2;
-- NULL
select 1 >= NULL;
-- NULL

等值比较：=

语法：A=B
操作类型：所有基本类型
描述: 如果表达式A与表达式B相等，则为TRUE；否则为FALSE

select 3 = 1;
-- false
select 2 = 2;
-- true
select null = 2;
-- NULL

不等值比较: <>

语法: A <> B
操作类型: 所有基本类型
描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE

select 3 <> 1;
-- true
select 2 <> 2;
-- false
select null <> 2;
-- NULL

范围比较：between

语法: A between MIN and MAX
操作类型: 所有基本类型
描述: 如果表达式A或MIN或MAX有一个为NULL，返回NULL；如果表达式A在min和max之间(前闭后闭)，则为TRUE；否则为FALSE

select 3 between 1 and 5;
-- true
select 2 between 2 and 3;
-- true
select 2 between 1 and 2;
-- true
select 2 between 3 and 5;
-- false
select null between 2 and 3;
-- NULL
select 'back' between 'ai' and 'red';
-- true

语法: not A between MIN and MAX
操作类型: 所有基本类型
描述: 如果表达式A或min或max有一个为NULL，返回NULL；如果表达式A在min和max之间(前闭后闭)，则为FALSE；否则为TRUE

select not 3 between 1 and 5;
-- false
select not 2 between 2 and 3;
-- false
select not 2 between 1 and 2;
-- false
select not 2 between 3 and 5;
-- true
select null between 2 and 3;
-- NULL
select not 'back' between 'ai' and 'red';
-- false

空值判断：is null

语法: A IS NULL
操作类型: 所有类型
描述: 如果表达式A的值为NULL，则为TRUE；否则为FALSE

select null is null;
-- true
select 0 is null;
-- false
select '' is null;
-- false

非空判断：is not null

语法: A IS NOT NULL
操作类型: 所有类型
描述: 如果表达式A的值为NULL，则为FALSE；否则为TRUE

select null is not null;
-- false
select 0 is not null;
-- true
select '' is not null;
-- true

like比较：like

语法: A LIKE B
操作类型: strings
描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。

select 'presto' like 'p%';
-- true
select 'presto' like '_____';
-- false
select 'presto' like '______';
-- true
select 'fuyun' like null;
-- NULL

最大值：greatest

语法: greatest(value1, value2, …, valueN)
操作类型: DOUBLE, BIGINT, VARCHAR, TIMESTAMP, TIMESTAMP WITH TIME ZONE, DATE
描述: 如果输入参数存在NULL，则返回NULL，其他返回最大值，所有输入参数数据类型必须保持一致。

select greatest(9, 1, null, 5);
-- NULL
select greatest(9, 1, 9, 5);
-- 9

最小值：least

语法: least(value1, value2, …, valueN)
操作类型: DOUBLE, BIGINT, VARCHAR, TIMESTAMP, TIMESTAMP WITH TIME ZONE, DATE
描述: 如果输入参数存在NULL，则返回NULL，其他返回最小值，所有输入参数数据类型必须保持一致。

select least(9, 1, null, 5);
-- NULL
select least(9, 1, 9, 5);
-- 1

比较量词： ALL, ANY and SOME

ANY和SOME含义相同，可以互换使用
举例：

select 'hello' = any (values 'hello', 'world'); 
-- true
select 'hello' = all (values 'hello', 'world'); 
-- false
select 25 <= all (values 23, 34, 45);
-- false
select 21 <= any (values 23, 34, 45);
-- true
select 34 >= some (select 34 union all select 45 union all select 56);
-- true

条件表达式

多分支判断：case

标准的SQL CASE 表达式有两种模式。 “简单模式”从左向右查找表达式的每个 value ， 直到找出相等的 expression:

CASE expression
   WHEN value THEN result
   [ WHEN ... ]
   [ ELSE result ]
END

返回匹配 value 的 result 。 如果没有匹配到任何值，则返回 ELSE 子句的 result ； 如果没有 ELSE 子句，则返回空。
示例：

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

“查找模式”从左向右判断每个 condition的布尔值， 直到判断为真，返回匹配 result：

CASE
    WHEN condition THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

如果判断条件都不成立，则返回ELSE 子句的result； 如果没有 ELSE 子句，则返回空。示例:

select a, b,
       case
           when a = 1 then 'aaa'
           when b = 2 then 'bbb'
           else 'ccc'
       end

单分支判断：if

IF 函数是语言结构， 它与下面的 CASE 表达式功能相同：

CASE
    WHEN condition THEN true_value
    [ ELSE false_value ]
END

if(condition, true_value)
如果 condition为真，返回true_value； 否则返回空， true_value不进行计算。
if(condition, true_value, false_value)
如果 condition为真，返回 true_value； 否则计算并返回 false_value。

非空查找：coalesce

语法：coalesce(value[, …])
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select coalesce(null, 63, 65);
-- 63

判断两值是否相等：nullif

语法：nullif(value1, value2)
说明：如果 value1 与 value2 相等，返回空；否则返回value1 。

select nullif('value1', 'value2');
-- value1
select nullif('value1', 'value1');
-- NULL

异常值处理：try

try(expression)
通过返回null来判断表达式并处理某些类型的错误。

在某些查询中，可以通过TRY功能指定NULL或默认值来判断查询是否成功。
try功能可以与COALESCE功能结合使用。
try可以用来处理以下错误

• 除数为0
• cast或function参数无效
• 数值超出范围

示例：

SELECT * FROM shipping;

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

未使用try的失败查询

SELECT CAST(origin_zip AS BIGINT) FROM shipping;
查询失败: Can not cast 'P332a' to BIGINT

引入 TRY的NULL值：

SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;

 origin_zip
------------------
      94131
      NULL
      94025
      08544
(4 rows)

未使用try的失败查询

SELECT total_cost / packages AS per_package FROM shipping;
查询失败:/ by zero

使用TRY和 COALESCE的默认值：

SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping;

  per_package
----------------
          4
         14
          0
         19
(4 rows)

转换函数

Presto会将数字和字符值隐式转换成正确的类型。 Presto不会把字符和数字类型相互转换。 例如，一个查询期望得到一个varchar类型的值， Presto不会自动将bigint类型的值转换为varchar 类型。

如果有必要，可以将值显式转换为指定类型。

数据类型转换：cast

语法：cast(value AS type)
操作类型：T
说明：显式转换一个值的类型。 可以将varchar类型的值转为数字类型，反过来转换也可以。

select cast('2022-02-27' as date);
-- 2022-02-27
select cast('2022-03-27 23:30:56' as timestamp);
-- 2022-03-27 23:30:56

数据类型转换：try_cast

语法：try_cast(value AS type)
操作类型：T
说明：显式转换一个值的类型。 可以将varchar类型的值转为数字类型，反过来转换也可以。与 cast() 相似，区别是转换失败返回null。

select try_cast('2022-03-27' as timestamp);
-- 2022-03-27 00:00:00
select try_cast('2022-03-27' as bigint);
-- NULL

查看字段数据类型：typeof

语法：typeof(expr)
操作类型：T
说明：返回提供的表达式的类型的名称。

select typeof(123);
-- integer
select typeof('cat');
-- varchar(3)
select typeof(cos(2) + 1.5);
-- double

运算符

数学函数

绝对值：abs

语法：abs(x)
返回类型：和输入相同
说明：返回x的绝对值

select abs(-1);
-- 1
select abs(1);
-- 1

立方根：cbrt

语法：cbrt(x)
返回类型：double
返回x的立方根

select cbrt(27);
-- 3

向上取整：ceil、ceiling

语法：ceil(x)
返回类型：和输入相同
说明：返回x的向上取整的数值，是ceiling()的同名方法

select ceil(3.3);
-- 4
select ceiling(3.3);
-- 4
select ceil(-3.3);
-- -3

余弦相似度：cosine_similarity

语法：cosine_similarity(x, y)
返回值：double
说明：返回稀疏向量x和y之间的余弦相似度：

SELECT cosine_similarity(MAP(ARRAY['a'], ARRAY[1.0]), MAP(ARRAY['a'], ARRAY[2.0])); 
-- 1.0
SELECT cosine_similarity(MAP(ARRAY['a', 'b'], ARRAY[2.0, 3.0]), MAP(ARRAY['a', 'b'], ARRAY[4.0, 3.0]))
-- 0.9429903335828895

将角度x以弧度转换为度：degrees

语法：degrees(x)
返回值：double
说明：将角度x以弧度转换为度

欧拉常数：e

语法：e()
返回值：double
说明：返回欧拉常数

select e();
-- 2.7182818284590455

自然指数：exp

语法：exp(x)
返回值：double
说明：返回欧拉常数e的x次幂

select exp(1);
-- 2.7182818284590455
select exp(2);
-- 7.38905609893065

向下取整：floor

语法：floor(x)
返回值：和输入相同
说明：x四舍五入到最接近的整数

select floor(3.7)
-- 3

from_base

语法：from_base(string, radix) → bigint
返回类型：bigint
说明：Returns the value of string interpreted as a base-radix number

自然对数：ln

语法：ln(x)
返回类型：double
说明：返回x的自然对数

select ln(2.7182818284590455)
-- 1

以2为底的对数：log2

语法：log2(x) → double
返回类型：double
说明：返回以2为底的对数

select log2(4);
-- 2

以10为为底的对数：log10

语法：log10(x)
返回类型：double
说明：返回以10为底的对数

select log10(100);
-- 2

对数函数：log

语法：log(x, b) → double
返回类型：double
说明：返回以b为底的x的对数

select log(4, 2);
-- 0.5
select log(2, 4);
-- 2

求余：mod

语法：mod(n, m) → [same as input]
返回类型：和输入相同
说明：返回n除以b的余数

select mod(-4, 3);
-- -1

常数π：pi

语法：pi() → double
返回类型：double
说明：返回常数π

select pi();
-- 3.141592653589793

幂运算函数：pow

语法：pow(x, p) power(x, p)
返回类型：double
说明：返回x的p次方

select pow(3, 2);
-- 9
select power(3, 2);
-- 9

将角度x以度为单位转换为弧度：radians

语法：radians(x)
返回类型：double
说明：将角度x以度为单位转换为弧度

随机数：rand

语法：rand() → double
返回类型：double
说明：返回0.0 <= x <1.0范围内的伪随机值，random()的另一种表达``

select rand();
select rand(2);

四舍五入取整：round

语法：round(x)
返回类型：和输入相同
说明：返回x四舍五入到最接近的整数

select round(4.5)
-- 5
select round(4.4)
-- 4

判断正负数值：sign

语法：sign(x) → [same as input]
返回类型：int
说明：返回x的正负数值，即：

• x为0，返回0
• x大于0，返回1
• x小于0，返回1
• x为NULL，返回NULL

平方根

语法：sqrt(x)
返回数据类型：double
说明：返回x的平方根

select sqrt(4);
-- 2

to_base

语法：to_base(x, radix)
返回类型：varchar
说明：Returns the base-radix representation of x.

去掉小数取整：truncate

语法：truncate(x)
返回类型：bigint
说明：舍弃x的小数位数，返回整数

select truncate(4.9);
-- 4

统计函数：width_bucket

语法：width_bucket(x, bound1, bound2, n)
返回类型：bigint
说明：Returns the bin number of x in an equi-width histogram with the specified bound1 and bound2 bounds and n number of buckets.

width_bucket

语法：width_bucket(x, bins)
返回类型：bigint
说明：Returns the bin number of x according to the bins specified by the array bins. The bins parameter must be an array of doubles and is assumed to be in sorted ascending order

三角函数

所有三角函数的参数都是以弧度表示。参考单位转换函数degrees()和 radians()

反余弦：acos

语法：acos(x)
返回类型：double
说明：返回x的反余弦

反正弦：asin

语法：asin(x)
返回类型：double
说明：返回x的反正弦

反正切

语法：atan(x)
返回类型double
说明：返回x的反正切

反正切

语法：atan2(y, x)
返回类型：double
说明：返回y / x的反正切

余弦：cos

语法：cos(x)
返回类型：double
说明：返回x的余弦值

双曲余弦：cosh

语法：cosh(x)
返回类型：double
说明：返回x的双曲余弦

正弦：sin

语法：sin(x)
返回类型：double
说明：返回x的正弦

正切值：tan

语法：tan(x)
返回类型：double
说明：返回x的正切值

双曲正切：tanh

语法：tanh(x)
返回类型：double
说明：返回x的双曲正切

浮点函数

infinity

语法：select infinity();
返回类型：double
说明：Returns the constant representing positive infinity.

is_finite

语法：is_finite(x)
返回类型：boolean
说明：确定x是否有限

is_infinite

语法：is_infinite(x)
返回类型：boolean
说明：Determine if x is finite.

is_nan

语法：is_nan(x)
返回类型：boolean
说明：Determine if x is finite.

nan

语法：nan()
返回类型：double
说明：Returns the constant representing not-a-number.

位函数

bit_count(x, bits) → bigint
Count the number of bits set in x (treated as bits-bit signed integer) in 2’s complement representation:

select bit_count(9, 64); -- 2
select bit_count(9, 8); -- 2
select bit_count(-7, 64); -- 62
select bit_count(-7, 8); -- 6

bitwise_and(x, y) → bigint
Returns the bitwise AND of x and y in 2’s complement representation.

bitwise_not(x) → bigint
Returns the bitwise NOT of x in 2’s complement representation.

bitwise_or(x, y) → bigint
Returns the bitwise OR of x and y in 2’s complement representation.

bitwise_xor(x, y) → bigint
Returns the bitwise XOR of x and y in 2’s complement representation.

See also bitwise_and_agg() and bitwise_or_agg().

十进制函数和运算符

十进制文字
使用DECIMAL 'xxxxxxx.yyyyyyy’语法定义DECIMAL类型的文字。

文字的DECIMAL类型的精度将等于文字数字（包括尾随和前导零）。 比例将等于小数部分的数字数（包括尾随零）。

二进制算术十进制运算符
支持标准数学运算符。
下表说明了结果的精度和尺度计算规则。 假设x的类型为DECIMAL(xp，xs)，y的类型为DECIMAL(yp，ys)。

如果操作的数学结果不能用结果数据类型的精度和比例精确表示，则会出现异常情况 - 值超出范围。

当以不同的比例和精度对十进制类型进行操作时，这些值首先被强制为一个常用的超类型。 对于接近最大可表示精度（38）的类型，当其中一个操作数不符合公共超类型时，可能导致值超出范围误差。 例如，十进制（38，0）和十进制（38，1）的公共超类型是十进制（38,1），但是适合十进制（38，0）的某些值不能表示为十进制（38，1）。

比较运算符
所有标准比较运算符和BETWEEN运算符都支持DECIMAL类型

一元十进制运算符
-运算符执行否定。 结果类型与参数类型相同。

字符串函数

使用运算符： || 完成字符串连接

select '0' || 'a';
-- 0a

字符串Unicode值：codepoint

语法：codepoint(string)
返回类型：integer
说明：返回字符串的Unicode值

select codepoint('0');
-- 48
select codepoint('a');
-- 97

字符串拼接：concat

语法：concat(string1, …, stringN)
返回类型：varchar
说明：返回string1, string2, …, stringN 的拼接，此功能与标准SQL的连接运算符（||）功能相同

select concat('a', 'b');
-- ab

以某个作为分隔符拼接字符串：cancat_ws

语法：concat_ws(连接符, string1, …, stringN)
返回类型：varchar
说明：返回以某个连接符将string1, string2, …, stringN 的拼接

select concat_ws('-', 'A', 'a');
-- A-a

求字符串长度：length

语法：length(string)
返回类型：bigint
说明：以字符形式返回字符串的长度

select length('abc');
-- 3

两个字符串编辑距离：levenshtein_distance

语法：levenshtein_distance(string1, string2)
返回类型：bigint
说明：返回string1和string2的编辑距离，即将string1更改为string2所需的单个字符编辑（插入，删除或替换）的最小数量

select levenshtein_distance('abc', 'abcds');
-- 2
select levenshtein_distance('abc', 'bcds');
-- 3

转换为小写：lower

语法：lower(string)
返回类型：varchar
说明：将字符串转换为小写

select lower('ABC');
-- abc
select lower('AbBC');
-- abbc

转换为大写：upper

语法：upper(string)
返回类型：varchar
说明：将字符串转换为大写

select upper('abc');
-- ABC
select upper('AbBC');
-- ABBC

字符串左边补齐：lpad

语法：lpad(string, size, padstring)
返回类型：varchar
说明：在参数一的左边拼接参数三，直到字符串长度达到达到size。如果有size小于参数一，则将参数一剪切为长度size的字符串。

select lpad('AbBC', 10, 'ro');
-- rororoAbBC
select lpad('AbBC', 3, 'ro');
-- AbB

字符串右边补齐：rpad

语法：rpad(string, size, padstring)
返回类型：varchar
说明：在参数一的右边拼接参数三，直到字符串长度达到达到size。如果有size小于参数一，则将参数一剪切为长度size的字符串。

select rpad('AbBC', 10, 'ro');
-- AbBCrororo
select rpad('AbBC', 3, 'ro');
-- AbB

去除前面空格：ltrim

语法：ltrim(string)
返回类型：varchar
说明：从字符串中删除前导空格

select ltrim(' abc');
-- abc

去除后面的空格：rtrim

语法：rtrim(string)
返回类型：varchar
说明：从字符串中删除后面空格

select rtrim('abc ');
-- abc

去除前后的空格：trim

语法：trim(string)
返回类型：varchar
说明：从字符串中删除前后空格

select trim(' abc ');
-- abc

字符串替换：replace

语法：replace(string, search)
返回类型：varchar
说明：从字符串中删除所有search实例

语法：replace(string, search, replace)
返回类型：varchar
说明：用replace字符串替换所有search实例

select replace('abc-avc', '-');
-- abcavc
select replace('abc-avc', '-', '|');
-- abc|avc

字符串反转：reverse

语法：reverse(string)
返回类型：varchar
说明：返回字符的反向排列

select reverse('abc');
-- cba

字符串分割：split

语法：split(string, delimiter)
返回类型：array
说明：通过delimiter分割字符串并返回一个数组

语法：split(string, delimiter, limit)
返回类型：array
说明：通过delimiter分割字符串，并返回按limit大小限制的数组。 数组中的最后一个元素包含字符串中的所有剩余内容。limit必须是正数

select split('fu-yun', '-');
-- ['fu','yun']
select split('fu-yun-csdn', '-', 2);
-- ['fu','yun-csdn']

字符串分割并返回索引值：split_part

语法：split_part(string, delimiter, index)
返回类型：varchar
说明：通过delimiter分割字符串并返回字段索引，字段索引以1开头。如果索引大于字段数，则返回null

select split_part('fu-yun-csdn', '-', 2);
-- yun
select split_part('fu-yun-csdn', '-', 4);
-- NULL

字符串分割返回map：split_to_map

语法：split_to_map(string, entryDelimiter, keyValueDelimiter)
返回类型：map
说明：通过entryDelimiter和keyValueDelimiter拆分字符串并返回map。 entryDelimiter将字符串分解成key-value对。keyValueDelimiter将每对分割成key和value

select split_to_map('name:fuyun,age:28', ',', ':');
-- {name=fuyun, age=28}

字符串的位置起始：strpos

语法：strpos(string, substring)
返回类型：bigint
说明：返回字符串中子字符串的第一个实例的起始位置。位置以1开头。如果没有，返回0

select strpos('name:fuyun,age:28', ':');
-- 5

字符串的位置起始：position

语法：position(substring IN string)
返回类型：bigint
说明：返回字符串中子字符串的第一个实例的起始位置。位置以1开头。如果没有，返回`0

select position(':' in 'name:fuyun,age:28');
-- 5

字符串截取：substr

语法：substr(string, start)
返回类型：varchar
说明：从start位置开始返回字符串的其余部分。位置从1开始。如果start为负，则起始位置代表从字符串的末尾开始倒数

语法：substr(string, start, length)
返回类型：varchar
说明：从start位置开始返回长度为length的字符串的子串。位置从1开始。如果start为负，则起始位置代表从字符串的末尾开始倒数

select substr('fuyun', 3);
-- yun
select substr('fuyun', 1, 2);
-- fu
select substr('fuyun', -3, 3);
-- yun

未完待续~~~