函数
逻辑运算符
| 运算符 | 描述 | 示例 |
|---|---|---|
| AND | 逻辑与 | a AND b |
| OR | 逻辑或 | a OR b |
| NOT | 逻辑非 | NOT a |
NULL
如果AND表达式中有一边或者两边都是null，那么整个AND表达式的结果将会是null。如果AND表达式中至少有一边的值是false，那么整个AND表达式的值都是false。
例如：
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表达式的一边或者两边都是null，那么整个OR表达式的值就是null。如果OR表达式中只要有一边的值为true，那么整个OR表达式的值就是true。
例如：
SELECT CAST(null AS boolean) OR true; -- true
SELECT CAST(null AS boolean) OR false; -- null
SELECT CAST(null AS boolean) OR CAST(null AS boolean); -- null下表说明了AND和OR表达式的计算规则：
| a | b | a AND b | a OR b |
|---|---|---|---|
TRUE | TRUE | TRUE | TRUE |
TRUE | FALSE | FALSE | TRUE |
TRUE | NULL | NULL | TRUE |
FALSE | TRUE | FALSE | TRUE |
FALSE | FALSE | FALSE | FALSE |
FALSE | NULL | FALSE | NULL |
NULL | TRUE | NULL | TRUE |
NULL | FALSE | FALSE | NULL |
NULL | NULL | NULL | NULL |
NULL的NOT表达式的结果还是NULL，如下所示：
SELECT NOT CAST(null AS boolean); -- null下表说明了NOT表达式的计算规则：
| a | NOT a |
|---|---|
TRUE | FALSE |
FALSE | TRUE |
NULL | NULL |
比较函数和运算符
运算符
| 运算符 | 描述 |
|---|---|
< | 小于 |
> | 大于 |
<= | 小于等于 |
>= | 大于等于 |
= | 等于 |
<> | 不等于 |
!= | 不等于 |
范围运算符
BETWEEN运算符检测值是否在指定范围内。它使用语法value BETWEEN min AND max：
SELECT 3 BETWEEN 2 AND 6; -- true上面的语句等效于下面的语句：
SELECT 3 >= 2 AND 3 <= 6; -- trueNOT BETWEEN运算符检测某个值不在指定范围内。它使用语法value NOT BETWEEN min AND max：
SELECT 3 NOT BETWEEN 2 AND 6; -- false上面的语句等效于下面的语句：
SELECT 3 < 2 OR 3 > 6; -- false
NULL在BETWEEN或者NOT BETWEEN语句中使用标准进行计算，应用于等效表达式的计算规则：
例如：
SELECT NULL BETWEEN 2 AND 4; -- null
SELECT 2 BETWEEN NULL AND 6; -- null
SELECT 2 BETWEEN 1 AND NULL; -- false
SELECT 8 BETWEEN NULL AND 6; -- falseBETWEEN和NOT BETWEEN运算符也可用于评估任何可排序类型。
例如：
SELECT 'Paul' BETWEEN 'John' AND 'Ringo'; -- true请注意，值、最小值和最大值参数
BETWEEN和NOT BETWEEN必须是同一类型。例如，如果您询问John是否在2.3和35.2之间，它将产生错误。
NULL和NOT NULL
IS NULL和IS NOT NULL运算符检测值是否为空。适用于所有数据类型。
实时逻辑运算
IS DISTINCT FROM
A IS DISTINCT FROM B如果A和B的值不完全相同则返回TRUE。 如果A和B的值相同返回FALSE。 在这里NULL视为已知值，返回TRUE。
IS NOT DISTINCT FROM
A IS NOT DISTINCT FROM B如果A和B的值不完全相同则返回FALSE。 如果A和B的值相同返回TRUE。 在这里NULL视为已知值，返回FALSE。
直观的给出一个表格：
| 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 |
最大和最小
非SQL标准函数，但属于常用扩展。
greatest(value1, value2, ..., valueN) 返回提供值中最大的。
least(value1, value2, ..., valueN) 返回提供值中最小的。
与大多数其他函数一样，如果任何一个参数为null，它们将返回null。
ALL、ANY和SOME比较
ALL、ANY和SOME量词可以按以下方式与比较运算符一起使用：
expression operator quantifier ( subquery )例如：
SELECT 'hello' = ANY (VALUES 'hello', 'world'); -- true
SELECT 21 < ALL (VALUES 19, 20, 21); -- false
SELECT 42 >= SOME (SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43); -- true量词和比较运算符组合的含义：
| 表示 | 意义 |
|---|---|
A = ALL(...) | 结果为true当...的时候A等于所有值。 |
A <> ALL(...) | 结果为true当...的时候A不匹配任何值。 |
A < ALL(...) | 结果为true当...的时候A小于最小值。 |
A = ANY(...) | 结果为true当...的时候A等于任何一个值。这种形式相当于A IN (...). |
A <> ANY(...) | 结果为true当...的时候A与一个或多个值不匹配。 |
A < ANY(...) | 结果为true当...的时候A小于最大值。 |
ANY 任意，ALL 全部，SOME 其中的一些。
ANY和SOME意思相同，可以互换使用。
LIKE比较
LIKE运算符可用于将值与模板进行比较：
column [NOT] LIKE 'pattern' ESCAPE 'character';匹配字符区分大小写，该模板支持两种匹配符号：
_匹配任何单个字符%匹配零个或多个字符
通常，它经常在中用作一个条件WHERE声明。例子是查找所有大洲的查询E，它返回：Europe
SELECT * FROM (VALUES 'America', 'Asia', 'Africa', 'Europe', 'Australia', 'Antarctica') AS t (continent)
WHERE continent LIKE 'E%';可以通过添加以下内容来否定结果NOT，并得到所有其他大陆，所有不开始E：
SELECT * FROM (VALUES 'America', 'Asia', 'Africa', 'Europe', 'Australia', 'Antarctica') AS t (continent)
WHERE continent NOT LIKE 'E%';如果只有一个特定的字符要匹配，可以使用_每个字符的符号。下面的查询使用了两个下划线，生成结果是：Asia
SELECT * FROM (VALUES 'America', 'Asia', 'Africa', 'Europe', 'Australia', 'Antarctica') AS t (continent)
WHERE continent LIKE 'A__a';通配符_和%必须进行转义，以允许将它们作为文本进行匹配。这可以通过指定ESCAPE要使用的字符：
SELECT 'South_America' LIKE 'South\_America' ESCAPE '\';上述查询返回true因为转义的下划线符号匹配。
条件表达式
CASE
标准的SQL中CASE表达式有两种形式。简单从左向右查找表达式的每个值直到找出相等的表达式：
CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END返回匹配值的结果。如果没有匹配到任何值，则返回ELSE子句的结果；如果没有ELSE子句，则返回空。示例：
SELECT a,
CASE a
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'many'
END查找从左向右判断每个condition的布尔值， 直到判断为真，返回匹配结果：
CASE
WHEN condition THEN result
[ WHEN ... ]
[ ELSE result ]
END如果判断条件都不成立，则返回ELSE子句的结果；如果没有ELSE子句，则返回空。示例：
SELECT a, b,
CASE
WHEN a = 1 THEN 'aaa'
WHEN b = 2 THEN 'bbb'
ELSE 'ccc'
ENDIF
IF表达式有两种形式一种仅提供true_value，另一种同时提供true_value和false_value
- if(
condition,true_value)
如果condition为真，则计算并返回true_value，否则返回 null，且不计算true_value。
- if(
condition,true_value,false_value)
如果condition为真，则计算并返回true_value，否则计算并返回false_value 。
SELECT IF(1 > 0, 'true') AS result; --true
SELECT IF(1 > 0, 'true', 'false') AS result; --trueCOALESCE
coalesce(value[, ...])返回参数列表中的第一个非空值。 与CASE表达式相似，仅在必要时计算参数。
SELECT COALESCE(NULL, 0); -- 0
SELECT COALESCE(NULL, NULL); -- NULLCOALESCE函数需要许多参数，并返回第一个非NULL参数。如果所有参数都为NULL，则COALESCE函数返回NULL。
NULLIF
nullif(value1, value2)如果 value1与value2相等，返回空；否则返回value1 。
案例1
select nullif (0,0); -- 0案例2
select nullif (1,2); -- null案例3
select nullif ('hubble','hubble'); -- null案例4
select nullif ('hubble','hubble1'); --hubble案例5
select nullif (0，NULL); -- 0案例6
select nullif (null,0); --nullNULIF(0,0)返回NULL，因为0等于0。
NULLIF(1,2)返回1，这是第一个参数，因为1不等于2。
NULLIF('hubble'，'hubble')返回NULL，因为两个参数是相同的字符串。
NULLIF('hubble'，'hubble1')返回hubble，因为两个字符串不相等。
NULLIF(1，NULL)返回0，因为0不等于NULL。
NULLIF(NULL，0)返回第一个参数，即NULL，因为NULL不等于0。
TRY
try(expression)计算表达式，如果发生错误返回null。
如果希望查询生成NULL或者默认值，而不是在遇到损坏或无效数据时失败，该TRY函数可能很有用。若要指定默认值，请将TRY函数与COALESCE函数结合使用。
下列错误使用TRY处理：
- 被零除
- 无效的转换或函数参数
- 数值超出范围
例如，包含一些无效数据的源表：
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返回NULL值：
SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping; origin_zip
------------
94131
NULL
94025
08544
(4 rows)TRY和COALESCE结合使用指定默认值：
SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping; per_package
-------------
4
14
0
19
(4 rows)Lambda表达式
Lambda表达式使用->表示：
x -> x + 1
(x, y) -> x + y
x -> regexp_like(x, 'a+')
x -> x[1] / x[2]
x -> IF(x > 0, x, -x)
x -> COALESCE(x, 0)
x -> CAST(x AS JSON)
x -> x + TRY(1 / 0)大多数SQL表达式都可以用在lambda体中，但有一些例外：
- 不支持子查询：
x -> 2 + (SELECT 3) - 不支持聚合：
x -> max(y)
例子：
获取数组列的平方元素transform()：
SELECT numbers,
transform(numbers, n -> n * n) as squared_numbers
FROM (
VALUES
(ARRAY[1, 2]),
(ARRAY[3, 4]),
(ARRAY[5, 6, 7])
) AS t(numbers); numbers | squared_numbers
-----------+-----------------
[1, 2] | [1, 4]
[3, 4] | [9, 16]
[5, 6, 7] | [25, 36, 49]
(3 rows)该函数transform()还可用于将数组元素安全地转换为字符串：
SELECT transform(prices, n -> TRY_CAST(n AS VARCHAR) || '$') as price_tags
FROM (
VALUES
(ARRAY[100, 200]),
(ARRAY[30, 4])
) AS t(prices); price_tags
--------------
[100$, 200$]
[30$, 4$]
(2 rows)除了正在操作的数组列之外，其他列也可以在 lambda表达式中捕获。以下语句展示了此功能，使用transform()用于计算线性函数f(x) = ax + b的值：
SELECT xvalues,
a,
b,
transform(xvalues, x -> a * x + b) as linear_function_values
FROM (
VALUES
(ARRAY[1, 2], 10, 5),
(ARRAY[3, 4], 4, 2)
) AS t(xvalues, a, b); xvalues | a | b | linear_function_values
---------+----+---+------------------------
[1, 2] | 10 | 5 | [15, 25]
[3, 4] | 4 | 2 | [14, 18]
(2 rows)使用any_match()查找包含至少一个大于100的值的数组元素：
SELECT numbers
FROM (
VALUES
(ARRAY[1,NULL,3]),
(ARRAY[10,20,30]),
(ARRAY[100,200,300])
) AS t(numbers)
WHERE any_match(numbers, n -> COALESCE(n, 0) > 100);
-- [100, 200, 300]通过以下方式将字符串中的第一个单词大写regexp_replace()：
SELECT regexp_replace('once upon a time ...', '^(\w)(\w*)(\s+.*)$',x -> upper(x[1]) || x[2] || x[3]);
-- Once upon a time ...Lambda表达式也可以应用于聚合函数。以下语句是一个示例，该示例使用reduce_agg()对列的所有元素的总和进行过于复杂的计算：
SELECT reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b) sum_values
FROM (
VALUES (1), (2), (3), (4), (5)
) AS t(value);
-- 15转换函数
CAST
cast(value AS type) → type
显式转换值的类型。 可以将varchar类型的值转为数字类型，反过来转换也可以。
SELECT CAST(100 AS VARCHAR); --100
SELECT CAST('100' AS INT); --100
SELECT CAST("10:00:10" AS TIME); -- 10:00:10当字符串cast为char(n)，不足的字符用空格填充，多的字符被截断
select length(cast('hello world' as char(100))); -- 100try_cast(value AS type) → type与 cast() 相似，区别是转换失败返回null。
SELECT TRY_CAST('test' AS int) ; -- null
SELECT TRY_CAST('123' AS int) ; -- 123 SELECT
CASE WHEN TRY_CAST('test' AS int) IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
--Cast failed
SELECT
CASE WHEN TRY_CAST('123' AS int) IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
-- Cast succeededFORMAT
format(format, args...) → varchar
使用指定的格式字符串和参数返回格式化字符串。
例如：
SELECT format('%s%%', 123); -- '123%'
SELECT format('%.5f', pi()); -- '3.14159'
SELECT format('%03d', 8); -- '008'
SELECT format('%,.2f', 1234567.89); -- '1,234,567.89'
SELECT format('%-7s,%7s', 'hello', 'world'); -- 'hello , world'
SELECT format('%2$s %3$s %1$s', 'a', 'b', 'c'); -- 'b c a'
SELECT format('%1$tA, %1$tB %1$te, %1$tY', date '2006-07-04'); -- 'Tuesday, July 4, 2006'format_number(number) → varchar值转化为存储容量。
例如：
SELECT format_number(123456); -- '123K'
SELECT format_number(1000000); -- '1M'PARSE_DATA_SIZE
parse_data_size(string)
将容量转换成纯数字值。
例如：
SELECT parse_data_size('1B'); -- 1
SELECT parse_data_size('1kB'); -- 1024
SELECT parse_data_size('1MB'); -- 1048576
SELECT parse_data_size('2.3MB'); -- 2411724parse_data_size支持以下单位：
| Unit | Description | Value |
|---|---|---|
| B | Bytes | 1 |
| kB | Kilobytes | 1024 |
| MB | Megabytes | 10242 |
| GB | Gigabytes | 10243 |
| TB | Terabytes | 10244 |
| PB | Petabytes | 10245 |
| EB | Exabytes | 10246 |
| ZB | Zettabytes | 10247 |
| YB | Yottabytes | 10248 |
TYPEOF
typeof(expr) → varchar
返回所提供表达式的类型名称。
例如：
SELECT typeof(123); -- integer
SELECT typeof('cat'); -- varchar(3)
SELECT typeof(cos(2) + 1.5); -- double数学函数和运算符
数学运算符
| 运算符 | 描述 |
|---|---|
+ | 加法 |
- | 减法 |
* | 乘法 |
/ | 除法（整数除法进行截断） |
% | 模数（余数） |
数学函数
abs(x) → 与输入类型相同返回
x的绝对值。
select abs(-11.19); -- 11.19cbrt(x) → double
返回x的立方根。
select cbrt(27.0); -- 3.0ceil(x) → 与输入类型相同是
ceiling()的同名方法。ceiling(x) → 与输入类型相同
返回x的向上取整的数值。
select ceil(23.7); -- 24degrees(x) → double将弧度转为角度。
select degrees(0.5); -- 28.64788975654116e() → double返回Euler数的常数
select e(); -- 2.718281828459045exp(x) → double返回Euler数提升到
x的幂。
select exp(1.0); -- 2.718281828459045floor(x) → 与输入类型相同返回
x向下舍入到最接近的整数。
select floor(-42.8); -- -43ln(x) → double返回
x的自然对数。
select ln(2.0); -- 0.6931471805599453log(b, x) → double
返回以b为底的对数x。
select log(2, 64); -- 6.0log2(x) → double
返回以2为底的对数x。
select log2(8); -- 3.8log10(x) → double
返回以10为底的对数x。
select log10(100); -- 2.0mod(n, m) → 与输入类型相同
返回n除以m的模数(余数)。
select mod(8,3); -- 2pi() → double
返回圆周率。
select pi(); -- 3.141592653589793pow(x, p) → double是
power()的同名方法。power(x, p) → double返回
x的p次方。
select power(2, 16); -- 65536.0radians(x) → double
将角度转换为弧度。
select radians(90); -- 1.5707963267948966round(x) → 与输入类型相同
返回x四舍五入到最接近的整数。
select round(125.315); -- 125round(x, d) → 与输入类型相同
返回x四舍五入到d小数位数。
select round(125.315, 2); -- 125.320sign(x)→ 与输入类型相同
函数返回x的正负号，即：
- 如果参数为0，则为0，
- 如果参数大于0，则为1，
- 如果参数小于0，则为-1。
对于浮点参数，该函数还会返回：
- 如果参数是NaN，则为NaN，
- 如果参数是 +Infinity，则为1，
- 如果参数是 -Infinity，则为-1。
select sign(-2.6); -- -1sqrt(x) → double
返回x的平方根。
select sqrt(4); -- 2.0truncate(x) → double
截取函数，舍去小数点后的数字，返回x中整数部分。
select truncate(124.344); -- 124width_bucket(x, bound1, bound2, n) → bigint返回一个桶，这个桶是在一个有
n个桶， 上界为bound1，下界为bound2的柱图中x将被赋予的那个桶。若输入在范围外部，则返回0或者n+1。
SELECT width_bucket(5.3, 0.2, 10.6, 5); -- 3width_bucket(x, bins) → bigint
返回一个桶，它是在给定数组列出所有的桶中x将被赋予的那个桶，bins数组必须double数组，并且是升序排序的。若输入在范围外部，则返回0或者n+1。
select width_bucket(5.3, array [0.2,1.2,4.2,6.2,8.2,10.2]); -- 3随机函数
rand() → double
是random()的同名方法。
random() → double
返回0.0 <= x < 1.0范围内的伪随机值。
select random(); -- 0.10773738703313251random(n) → 与输入类型相同
返回 0 到 n（不包括）之间的伪随机数。
select random(10); -- 6random(integer,integer) → integer
返回指定两个值之间的伪随机数。
select random(10, 20); -- 15random(smallint,smallint) → smallint返回指定两个值之间的伪随机数。
random(tinyint,tinyint) → tinyint返回指定两个值之间的伪随机数。
三角函数
所有三角函数的参数都是以弧度表示。参考单位转换函数degrees() 和 radians()。
acos(x) → double
返回x的反余弦值。
select acos(0.87); -- 0.5155940062460905asin(x) → double
返回x的反正弦值。
select asin(1); -- 1.5707963267948966atan(x) → double
返回x的反正切值。
select atan(1); -- 0.7853981633974483atan2(y, x) → double
返回y/x的反正切值。
select atan2(2, 1); -- 1.1071487177940904cos(x) → double
返回x的余弦值。
select cos(2.11); -- -0.5134528123039594cosh(x) → double
返回x的双曲余弦值。
select cosh(1.3); -- 1.9709142303266285sin(x) → double返回
x的正弦值。
select sin(1); -- 0.8414709848078965tan(x) → double
返回x的正切值。
select tan(1); -- 1.5574077246549023tanh(x) → double
返回x的双曲正切值。
select tanh(1); -- 0.7615941559557649浮点函数
infinity() → double
返回表示正无穷大的常量Infinity。
select infinity(); -- Infinityis_finite(x) → boolean
返回x是否是有限的。
select is_finite(3); -- trueis_infinite(x) → boolean
返回x是否是无限的。
select is_infinite(3); -- falseis_nan(x) → boolean
返回x是否不是数字。
select is_nan(nan()); -- truenan() → double
返回表示非数字的常量NaN。
select nan(); -- NaN转换函数
from_base(string, radix) → bigint
返回被解释为基数的字符串的值。
select from_base('100', 2); -- 4to_base(x, radix) → varchar
将数字转换为具备给定基数的文本表示。
select to_base(4, 2); -- 100统计函数
cosine_similarity(x, y) → double
返回稀疏向量x和y之间的余弦相似度：
SELECT cosine_similarity(MAP(ARRAY['a'], ARRAY[1.0]), MAP(ARRAY['a'], ARRAY[2.0])); -- 1.0wilson_interval_lower(successes, trials, z) → double
返回Bernoulli方程计算的Wilson评分的下限。
select wilson_interval_lower(1, 5, 1.96); -- 0.036223160969787456wilson_interval_upper(successes, trials, z) → double
返回Bernoulli方程计算的Wilson评分的上限。
select wilson_interval_upper(1, 5, 1.96); -- 0.6244717358814612累计分布函数
beta_cdf(a, b, v) → double
贝塔累计分布函数概率。a、b 参数必须是正实数，值 v 必须是实数。值 v 必须位于区间 [0, 1] 内。
select beta_cdf(3,4,0.0004); -- 1.278848368599041E-9inverse_beta_cdf(a, b, p) → double
贝塔累计分布函数逆概率。a、b 参数必须是正实数值。概率 p 必须位于区间 [0, 1] 内。
select inverse_beta_cdf(2, 5, 0.95); -- 0.5818034093775719inverse_normal_cdf(mean, sd, p) → double
计算一个普通累计分布函数逆概率通过平均值和标准差，平均值必须是实数，标准差必须是实数，标准差必须是正实数。概率 p 必须位于区间 [0, 1] 内。
select inverse_normal_cdf(2, 5, 0.95); -- 10.224268134757361normal_cdf(mean, sd, v) → double
使用平均值和标准差计算普通累计分布函数概率。平均值和值 v 必须是实数值，标准差必须是正实数值。
select normal_cdf(2, 5, 0.95); -- 0.4168338365175577位运算函数
bit_count(x, bits) → bigint
计算x的二进制补码中的位数（视为有符号整数）。
SELECT bit_count(9, 64); -- 2
SELECT bit_count(9, 8); -- 2
SELECT bit_count(-7, 64); -- 62
SELECT bit_count(-7, 8); -- 6bitwise_and(x, y) → bigint
按位与，返回x和y二进制补码的按位与。
例如：19(二进制：10011) 和25(二进制： 11001) 的按位与的运算结果为17(二进制：10001)
SELECT bitwise_and(19,25); -- 17bitwise_not(x) → bigint
按位非，返回x和y二进制补码的按位非。NOT x = -x - 1
例如：
SELECT bitwise_not(-12); -- 11
SELECT bitwise_not(19); -- -20
SELECT bitwise_not(25); -- -26bitwise_or(x, y) → bigint
按位或，返回x和y二进制补码的按位或。
例如：19(二进制：10011) 和25(二进制： 11001) 的按位或的运算结果为27(二进制：11011)
SELECT bitwise_or(19,25); -- 27bitwise_xor(x, y) → bigint
按位异或，返回x和y二进制补码的按位异或。
例如：19(二进制：10011) 和25(二进制11001) 的按位或的运算结果为10(二进制：01010)
SELECT bitwise_xor(19,25); -- 10bitwise_left_shift(value, shift) → 与输入类型相同
对指定数值，逻辑左移位数。
例如：
将1(二进制：001) 向左移动两位结果为4(二进制： 00100) 。
SELECT bitwise_left_shift(1, 2); -- 4将5(二进制：0101) 向左移动两位结果为20(二进制： 010100) 。
SELECT bitwise_left_shift(5, 2); -- 20将value向左移动0位结果总为原来的值。
SELECT bitwise_left_shift(20, 0); -- 20
SELECT bitwise_left_shift(42, 0); -- 42将0向左移动shift位结果都为0。
SELECT bitwise_left_shift(0, 1); -- 0
SELECT bitwise_left_shift(0, 2); -- 0bitwise_right_shift(value, shift) → 与输入类型相同
对指定数值，逻辑右移位数。
例如：
将8(二进制：1000) 向右移动三位结果为1(二进制： 001) 。
SELECT bitwise_right_shift(8, 3); -- 1将9(二进制：1001) 向右移动一位结果为4(二进制： 100) 。
SELECT bitwise_right_shift(9, 1); -- 4将value向右移动0位结果总为原来的值。
SELECT bitwise_right_shift(20, 0); -- 20
SELECT bitwise_right_shift(42, 0); -- 42将value向右移动64或更多位结果都为0。
SELECT bitwise_right_shift( 12, 64); -- 0
SELECT bitwise_right_shift(-45, 64); -- 0将0向右移动shift位结果都为0。
SELECT bitwise_right_shift(0, 1); -- 0
SELECT bitwise_right_shift(0, 2); -- 0bitwise_right_shift_arithmetic(value, shift) → 与输入类型相同
对指定数值，算数右移位数。
当向右移动小于64位时，结果与bitwise_right_shift()相同。
向右移动64或更多位时，当value为正数时结果为0，负数时结果为-1。
SELECT bitwise_right_shift_arithmetic( 12, 64); -- 0
SELECT bitwise_right_shift_arithmetic(-45, 64); -- -1数值常量与计算
数值常量
使用DECIMAL 'aaaaaaa.bbbbbbbb'语法定义数值类型。
| 数值例子 | 数据类型 |
|---|---|
| DECIMAL '0' | DECIMAL(1) |
| DECIMAL '12345' | DECIMAL(5) |
| DECIMAL '0000012345.1234500000' | DECIMAL(20, 10) |
数值计算精度
支持标准数学运算符。下表说明了结果的精度和宽度计算规则。假设x是DECIMAL(xp，xs)类型，y是DECIMAL（yp，ys）类型。
| 操作 | 结果的精度 | 结果的宽度 |
|---|---|---|
x + y或x - y | min(38,1 + min(xs, ys) + min(xp - xs, yp - ys)) | max(xs, ys) |
x * y | min(38, xp + yp) | xs + ys |
x / y | min(38,xp + ys + max(0, ys-xs)) | max(xs, ys) |
x % y | min(xp - xs, yp - ys) + max(xs, bs) | max(xs, ys) |
如果运算的数学结果不能精确地用结果数据类型的精度和比例表示，将会引发异常
Value is out of range。
在具有不同精度的数值计算时，最好先将值类型的精度和范围统一，对于一个接近最大精度38的类型，可能会导致错误，如一个值类型DECIMAL(38,0)，一个值DECIMAL(38,1)，计算结果可能超出最大精度引发错误。
字符串函数和运算符
字符串运算符
使用运算符 || 完成字符串连接。
使用运算符 LIKE 用于模板匹配。
字符串函数
注意
这些函数假定输入字符串包含有效的UTF-8编码的Unicode代码点。不会显式检查UTF-8数据是否有效，对于无效的UTF-8数据，函数可能会返回错误的结果。可以使用
from_utf8来更正无效的UTF-8数据。 此外，这些函数对Unicode代码点进行运算，而不是对用户可见的'字符'（或'字形群集'）进行运算。某些语言将多个代码点组合成单个用户感观字符（这是语言书写系统的基本单位），但是函数会将每个代码点视为单独的单位。
lower和upper函数不执行某些语言所需的区域设置相关、上下文相关或一对多映射。具体而言，对于立陶宛语、土耳其语和阿塞拜疆语，这将返回不正确的结果。
chr(n) → varchar
以单个字符串的形式返回Unicode代码点n。
select chr(100); -- dcodepoint(string) → integer
返回string的唯一字符的Unicode编码点。
select codepoint('d'); -- 100concat(string1, ..., stringN) → varchar
返回string1、string2、...、stringN的连接结果。该函数提供与 SQL 标准连接运算符(||)相同的功能。
select concat('hello', 'hubble'); -- hellohubbleconcat_ws(string0, string1, ..., stringN) → varchar
连接字符串string1, string2, ..., stringN使用string0作为分隔符，将多个字符串进行拼接。如果string0为空，则返回值为空。连接时跳过分隔符后面的参数中提供的任何空值。
select concat_ws(',', 'hello', 'hubble'); -- hello,hubblehamming_distance(string1, string2) → bigint
返回string1和string2的汉明距离，即对应字符不同的位置数。请注意，这两个字符串的长度必须相同。
select hamming_distance('abcde','edcba'); -- 4length(string) → bigint返回
string字符串的长度。
select length('abcde'); -- 5levenshtein_distance(string1, string2) → bigint
返回String1和String2的Levenshtein编辑距离，即将String1更改为String2所需的最小单字符编辑数（插入、删除或替换）。
select levenshtein_distance('apple','epplea');lower(string) → varchar
转换string为小写。
select lower('HELLo!'); -- hello!lpad(string, size, padstring) → varchar
从左边对字符串string使用指定的字符padstring进行拼接，直到长度达到size。如果size小于string的长度，结果被截断为size个字符。size不得为负，且padstring不能为空。
select lpad('kmy', 5, 'zda'); -- zdkmyltrim(string) → varchar
删除字符串string所有前导空格。
select ltrim(' hello!'); -- hello!position(substring IN string) → bigint
返回字符串string中子字符串substring 第一次出现的位置，位置从1开始，如果未找到则返回0。
select position('l' in 'hello'); -- 3replace(string, search) → varchar
将string中所有的search内容移除。
select replace('hello', 'l'); -- heoreplace(string, search, replace) → varchar
将string中的search内容替换为replace。
select replace('hello', 'l', 'm'); -- hemmoreverse(string) → varchar
返回string逆序后的字符串。
select reverse('hello'); -- ollehrpad(string, size, padstring) → varchar
从右边对字符串string使用指定的字符padstring进行拼接，直到长度达到size，并返回拼接后的字符串。如果size小于string的长度，结果被截断为size个字符。size不得为负，且padstring不能为空。
select rpad('kmy',5, 'zda'); -- kmyzdrtrim(string) → varchar
删除字符串string所有后置空格。
select ltrim('hello! '); -- hello!soundex(char) → varchar
将字符串编码为SOUNDEX值。以评估两个字符串在发音时的相似性。
字符对应规则：
| 字符 | 对应数字 |
|---|---|
| a、e、h、i、o、u、w、y | 0 |
| b、f、p、v | 1 |
| c、g、j、k、q、s、x、z | 2 |
| d、t | 3 |
| l | 4 |
| m、n | 5 |
| r | 6 |
- 提取字符串的首字母作为soundex的第一个值。
- 按照上面的字母对应规则，将后面的字母逐个替换为数字。如果有连续的相等的数字，只保留一个，其余的都删除掉。并去除所有的0。
- 如果结果超过4位，取前四位。如果结果不足4位向后补0。
select soundex('Miller'); -- M460split(string, delimiter) -> array(varchar)
将字符串string按分隔符delimiter进行分隔，并返回数组。
select split('a:b:c:d', ':'); -- [a, b, c, d]split(string, delimiter, limit) -> array(varchar)
将字符串string按分隔符delimiter进行分隔，并返回按limit大小限制的数组。数组中的最后一个元素包含字符串中的所有剩余内容。limit必须是正数。
select split('a:b:c:d', ':', 2); -- [a, b:c:d]split_part(string, delimiter, index) → varchar
将字符串string按分隔符delimiter进行分隔，并返回分隔后数组下标为index的子串。index从1开始，超出范围返回null。
select split_part('a:b:c:d', ':', 2); -- b
select split_part('a:b:c:d', ':', 5); -- NULLsplit_to_map(string, entryDelimiter, keyValueDelimiter) → map(varchar, varchar)
将string按entryDelimiter和keyValueDelimiter拆分并返回map。entryDelimiter将字符串分解为key-value对，keyValueDelimiter将每对key-value分隔成key和value。
select split_to_map('zhang:18,li:17', ',', ':'); -- {li=17, zhang=18}split_to_multimap(string, entryDelimiter, keyValueDelimiter) -> map(varchar, array(varchar))
将string按entryDelimiter和keyValueDelimiter拆分并返回map，其中包含每个唯一key的value数组。entryDelimiter将字符串分解为key-value对，keyValueDelimiter将每对key-value分隔成key和value。每个key的value的顺序与它们在string中出现的顺序相同。
select split_to_multimap('zhang:18,li:17,zhang:20,wang:19', ',', ':');
-- {zhang=[18, 20], wang=[19], li=[17]} strpos(string, substring) → bigint
返回字符串string中子字符串substring第一次出现的位置。位置以 1 开始 ，如果未找到则返回 0。
select strpos('hello!', 'l'); -- 3strpos(string, substring, instance) → bigint
返回字符串string中子字符串substring第instance次出现的位置。当instance为负数时，将从string的末尾开始搜索。
位置以 1 开始 ，如果未找到则返回 0。
select strpos('hello!', 'l', -2); -- 3starts_with(string, substring) → boolean
判断子字符串substring是否是字符串string的前缀。
select starts_with('hello!', 'hel'); -- truesubstr(string, start) → varchar
是substring()的同名方法。
substr(string, start, length) → varchar
是substring()的同名方法。
substring(string, start) → varchar
返回字符串string从start位置开始到结束的子串。位置以 1 开始，如果start<0，则start位置从字符串的末尾开始倒数。
select substring('abcde', 3); -- cdesubstring(string, start, length) → varchar
返回字符串string从start位置开始长度为length的子串。位置以 1 开始，如果start<0，则start位置从字符串的末尾开始倒数。
select substring('abcde', 3, 2); -- cdtranslate(source, from, to) → varchar
将source字符串中，符合from的字符，替换为to，并返回。如果from 字符串包含重复项，则仅使用第一个。如果source字符串中不存在该字符from，source字符串将被复制而无需替换。如果字符串中匹配字符的索引from 超出字符串to的长度，则该字符将从结果字符串source中省略。
SELECT translate('abcd', '', ''); -- 'abcd'
SELECT translate('abcd', 'a', 'z'); -- 'zbcd'
SELECT translate('abcda', 'a', 'z'); -- 'zbcdz'
SELECT translate('Palhoça', 'ç','c'); -- 'Palhoca'
SELECT translate('abcd', 'b', U&'\+01F600'); -- a<U+1F600>cd
SELECT translate('abcd', 'a', ''); -- 'bcd'
SELECT translate('abcd', 'a', 'zy'); -- 'zbcd'
SELECT translate('abcd', 'ac', 'z'); -- 'zbd'
SELECT translate('abcd', 'aac', 'zq'); -- 'zbd'trim(string) → varchar
删除字符串string所有的前后空格。
select trim(' hello hubble! '); -- hello hubble!trim([ [ specification ] [ string ] FROM ] source) → varchar
删除指定的任何前导和/或尾随字符。
SELECT trim('!' FROM '!foo!'); -- 'foo'
SELECT trim(LEADING FROM ' abcd'); -- 'abcd'
SELECT trim(BOTH '$' FROM '$var$'); -- 'var'
SELECT trim(TRAILING 'ER' FROM upper('worker')); -- 'WORK'upper(string) → varchar
转换string为大写。
select upper('hello hubble!'); -- HELLO HUBBLE!word_stem(word) → varchar
返回英语中word的词干。
select word_stem('greating'); -- greatword_stem(word, lang) → varchar
返回lang语言中word的词干。
select word_stem('ultramoderne', 'fr'); -- ultramodernUnicode函数
normalize(string) → varchar
用UNICODE NFC标准化形式转换字符串。
select normalize('e'); -- e normalize(string, form) → varchar
用指定的标准化形式转换字符串。form必须是以下关键字之一：
| form | 描述 |
|---|---|
NFD | 正则分解 |
NFC | 正则分解，随后跟正则分解 |
NFKD | 兼容性分解 |
NFKC | 兼容性分解，后跟正则分解 |
select normalize('é', NFC); -- é to_utf8(string) → varbinary
字符串转 UTF-8 表示形式。
select to_utf8('panda'); -- 70 61 6e 64 61from_utf8(binary) → varchar
解码 UTF-8 编码的字符串binary。无效的 UTF-8 序列被替换为Unicode替换字符U+FFFD.
select from_utf8(X'70 61 6e 64 61'); -- pandafrom_utf8(binary, replace) → varchar
解码 UTF-8 编码的字符串binary。无效的 UTF-8 序列被替换为replace。替换字符串replace必须是单个字符或为空（在这种情况下，无效字符将被删除）。
select from_utf8(X'70 61 6e 64 61 b1', '!'); -- panda!正则表达式函数
注意
所有正则表达式函数都使用Java模式语法，但有一些值得注意的例外：
当使用多行模式（通过
(?m)标志启用）时，仅将\n识别为行终止符。此外，不支持(?d)标志，不得使用该标志。不区分大小写的匹配（通过
(?i)标志启用）始终以支持Unicode的方式执行。不过，不支持上下文相关和局部相关的匹配。此外，不支持(?u)标志，不得使用该标志。不支持代理项对。例如，
\uD800\uDC00不被视为U+10000，必须将其指定为\x{10000}。对于没有基字符的不占位标记，会错误地处理边界(\b)。在字符类（如
[A-Z123]）中不支持\Q和\E，而应将其视为字面量。支持Unicode字符类(
\p{prop})，但有如下差异：必须删除名称中的所有下划线。例如，使用
OldItalic代替Old_Italic。必须直接指定脚本，而不使用
Is、script=或sc=前缀。示例：\p{Hiragana}必须使用
In前缀指定块。不支持block=和blk=前缀。示例：\p{Mongolian}必须直接指定类别，而不使用
Is、general_category=或gc=前缀。示例：\p{L}必须直接指定二进制属性，而不使用
Is。示例：\p{NoncharacterCodePoint}
regexp_count(string, pattern) -> bigint
返回正则表达式pattern在string中出现次数。
例如：
SELECT regexp_count('1a 2b 14m', '\s*[a-z]+\s*'); -- 3regexp_extract_all(string, pattern) -> array(varchar)
返回正则表达式pattern在string中匹配的子字符串。
例如：
SELECT regexp_extract_all('1a 2b 14m', '\d+'); -- [1, 2, 14]regexp_extract_all(string, pattern, group) -> array(varchar)
查找string中出现的所有正则表达式pattern实例，并返回捕获组编号group。
例如：
SELECT regexp_extract_all('1a 2b 14m', '(\d+)([a-z]+)', 2); -- ['a', 'b', 'm']regexp_extract(string, pattern) → varchar
返回正则表达式pattern在string中匹配的第一个子字符串。
例如：
SELECT regexp_extract('1a 2b 14m', '\d+'); -- 1regexp_extract(string, pattern, group) → varchar
查找string中出现的第一个正则表达式pattern实例，并返回捕获组编号group。
SELECT regexp_extract('1a 2b 14m', '(\d+)([a-z]+)', 2); -- 'a'regexp_like(string, pattern) → boolean
计算正则表达式pattern并确定它是否包含在string中。
该函数与LIKE运算符类似，不过只需在string中包含模板，而无需匹配整个string。换句话说，该函数执行的是包含运算，而不是匹配运算。可以通过使用^和$定位模板来匹配整个字符串。
例如：
SELECT regexp_like('1a 2b 14m', '\d+b'); -- trueregexp_position(string, pattern) → integer
返回正则表达式pattern在string中第一个匹配项的索引（从1开始计数）。如果未找到，则返回 -1。
例如：
SELECT regexp_position('I have 23 apples, 5 pears and 13 oranges', '\b\d+\b'); -- 8regexp_position(string, pattern, start) → integer
返回正则表达式pattern在string中第一个匹配项的索引，从start开始（包括start）。如果未找到，则返回 -1。
例如：
SELECT regexp_position('I have 23 apples, 5 pears and 13 oranges', '\b\d+\b', 5); -- 8
SELECT regexp_position('I have 23 apples, 5 pears and 13 oranges', '\b\d+\b', 12); -- 19regexp_position(string, pattern, start, occurrence) → integer
返回正则表达式pattern在string中第n个匹配项的索引occurrence，从start开始（包括start）。如果未找到，则返回 -1。
例如：
SELECT regexp_position('I have 23 apples, 5 pears and 13 oranges', '\b\d+\b', 12, 1); -- 19
SELECT regexp_position('I have 23 apples, 5 pears and 13 oranges', '\b\d+\b', 12, 2); -- 31
SELECT regexp_position('I have 23 apples, 5 pears and 13 oranges', '\b\d+\b', 12, 3); -- -1regexp_replace(string, pattern) → varchar
从string中删除由正则表达式pattern匹配的子字符串的每个实例。
例如：
SELECT regexp_replace('1a 2b 14m', '\d+[ab] '); -- '14m'regexp_replace(string, pattern, replacement) → varchar
将string中由正则表达式pattern匹配的子字符串的每个实例替换为replacement。捕获组可以使用$g（对于编号的组）或${name}（对于命名的组）在replacement中引用。替换时，可以通过使用反斜杠(\$)进行转义来包含符号($)。
例如：
SELECT regexp_replace('1a 2b 14m', '(\d+)([ab]) ', '3c$2 '); -- '3ca 3cb 14m'regexp_replace(string, pattern, function) → varchar
使用function替换string中由正则表达式pattern匹配的子字符串的每个实例。会针对每个匹配项调用lambda expression <lambda> function，其中以数组形式传入捕获组。捕获组编号从1开始；整个匹配没有组（如果需要，可以使用圆括号将整个表达式括起来）。
例如：
SELECT regexp_replace('new york', '(\w)(\w*)', x -> upper(x[1]) || lower(x[2])); --'New York'regexp_split(string, pattern) -> array(varchar)
使用正则表达式pattern拆分string并返回一个数组。保留尾随空字符串。
例如：
SELECT regexp_split('1a 2b 14m', '\s*[a-z]+\s*'); -- [1, 2, 14, ]二进制函数和运算符
二进制运算符
|| 运算符执行连接。
二进制函数
concat(binary1, ..., binaryN) → varbinary
返回 binary1、binary2、...、binaryN 的连接结果。该函数提供与 SQL 标准连接运算符 (||) 相同的功能。
select concat(X'32335F', x'00141f'); -- 32 33 5f 00 14 1flength(binary) → bigint
返回 binary 的长度，以字节为单位。
select length(x'00141f'); -- 3lpad(binary, size, padbinary) → varbinary
使用 padbinary 将 binary 左填充至 size 个字节。如果 size 小于 binary 的长度，结果将被截断至 size 个字符。size 不得为负数，并且 padbinary 必须为非空值。
select lpad(x'15245F', 11, x'15487F'); -- 15 48 7f 15 48 7f 15 48 15 24 5frpad(binary, size, padbinary) → varbinary使用
padbinary将binary右填充至size个字节。如果size小于binary的长度，结果将被截断至size个字符。size不得为负数，并且padbinary必须为非空值。
select rpad(x'15245F', 11, x'15487F'); -- 15 24 5f 15 48 7f 15 48 7f 15 48substr(binary, start) → varbinary
从起始位置 start 开始返回 binary 的其余部分，以字节为单位。位置从 1 开始。负起始位置表示相对于字符串的末尾。
select substr(x'15245F', 2); -- 24 5fsubstr(binary, start, length) → varbinary
从起始位置 start 开始从 binary 返回长度为 length 的子字符串，以字节为单位。位置从 1 开始。负起始位置表示相对于字符串的末尾。
select substr(x'15245F', 1, 3); -- 15 24 5freverse(binary) → varbinary
返回binary字节的逆序。
select reverse(x'15245F'); -- 5f 24 15Base64编码函数
Base64函数实现中指定的编码RFC 4648。
from_base64(string) → varbinary
从以 base64 编码的 string 解码二进制数据。
select from_base64('hellohubble'); -- 85 e9 65 a2 1b 9b 6e 57to_base64(binary) → varchar
将 binary 编码为 base64字符串表示形式。
select to_base64(x'85e965a21b9b6e57'); -- hellohubblc=from_base64url(string) → varbinary
使用 URL 安全字母表从以 base64 编码的 string 解码二进制数据。
select from_base64url('ABCD'); -- 00 10 83to_base64url(binary) → varchar
使用 URL 安全字母表将 binary 编码为 base64 字符串表示形式。
select to_base64url(x'001083'); -- ABCDfrom_base32(string) → varbinary
从以 base32 编码的 string解码二进制数据。
select from_base32('ABCD'); -- 00 44to_base32(binary) → varchar
将 binary 编码为 base32字符串表示形式。
select to_base32(x'0044'); -- ABCA====十六进制编码函数
from_hex(string) → varbinary
从以十六进制编码的 string 解码二进制数据。
select from_hex('FFFF'); -- ff ffto_hex(binary) → varchar
将 binary 编码为十六进制字符串表示形式。
select to_hex(x'ffff'); -- FFFF整数编码函数
from_big_endian_32(binary) → integer
解码 32位二进制补码 big-endian binary。输入必须正好是4个字节。
select from_big_endian_32(to_big_endian_32(10)); -- 10to_big_endian_32(integer) → varbinary
以 32位二进制补码 big-endian 格式对 integer 进行编码。
select to_big_endian_32(10); -- 00 00 00 0afrom_big_endian_64(binary) → bigint
解码 64 位二进制补码 big-endian binary。输入必须正好是8个字节。
select from_big_endian_64(to_big_endian_64(10)); -- 10to_big_endian_64(bigint) → varbinary
以 64位二进制补码 big-endian 格式对 bigint 进行编码。
select to_big_endian_64(10); -- 00 00 00 00 00 00 00 0a浮点编码函数
from_ieee754_32(binary) → real
对采用 IEEE 754 单精度浮点格式的 32 位 big-endian binary 进行解码。输入必须正好是4个字节。
select from_ieee754_32(to_ieee754_32(10)); -- 10to_ieee754_32(real) → varbinary
根据 IEEE 754 单精度浮点格式将 real 编码为 32 位 big-endian 二进制数。
select to_ieee754_32(10); -- 41 20 00 00 from_ieee754_64(binary) → double
对采用 IEEE 754 双精度浮点格式的 64 位 big-endian binary 进行解码。输入必须正好是8个字节。
select from_ieee754_64(to_ieee754_64(10)); -- 10to_ieee754_64(double) → varbinary
根据 IEEE 754 双精度浮点格式将 double 编码为 64 位 big-endian 二进制数。
select to_ieee754_64(10); -- 40 24 00 00 00 00 00 00哈希函数
crc32(binary) → bigint计算
binary的 CRC-32 值。对于通用哈希，请使用xxhash64，因为它速度更快并且能生成质量更好的哈希值。
select crc32(from_base64('aaaaaa')); -- 90176811 md5(binary) → varbinary计算
binary的 MD5 哈希值。
select md5(from_base64('aaaaaa'));
-- 1d 2d 3a be d3 6f 04 52 bf 93 fd 57 51 60 c6 38 sha1(binary) → varbinary计算
binary的 SHA1 哈希值。
select sha1(from_base64('aaaaaa'));
-- 23 f0 b5 a9 bc c1 8e a7 a3 6e 09 b2 27 6a df 22 9d 91 4c c5 sha256(binary) → varbinary计算
binary的 SHA256 哈希值。
select sha256(from_base64('aaaaaa'));
-- 9c ee 7d aa e7 f8 d5 73 78 9b ef a3 35 b5 5d 5a
53 6d 64 45 8c 0d 29 ec fa 1d 99 94 8c 16 fd 00 sha512(binary) → varbinary计算
binary的 SHA512 哈希值。
select sha512(from_base64('aaaaaa'));
-- 81 d1 a5 77 1b f1 c8 af f0 f0 1c cd d0 e2 78 6c
6e 8a 23 cd 08 cb 69 d4 b3 9a e7 af 9f af 1f 5e
a2 18 be dd 9e 8d 50 6a 14 5c 28 84 ad db 56 53
dd 73 7a 1c bf d6 dd a3 a7 75 d3 10 6c fe 55 f3spooky_hash_v2_32(binary) → varbinary计算
binary的 32 位 SpookyHashV2 哈希值。
select spooky_hash_v2_32(from_base64('aaaaaa')) -- a1 6e 2c 23 spooky_hash_v2_64(binary) → varbinary计算
binary的 64 位 SpookyHashV2 哈希值。
select spooky_hash_v2_64(from_base64('aaaaaa')) ;
-- bf b3 00 b2 a1 6e 2c 23 xxhash64(binary) → varbinary计算
binary的 xxhash64 哈希值。
select xxhash64(from_base64('aaaaaa'));
-- c2 d4 6c 79 1a 94 80 b7murmur3(binary) → varbinary计算
binary的 128 位 MurmurHash3 哈希值。
SELECT murmur3(from_base64('aaaaaa'));
-- ba 58 55 63 55 69 b4 2f 49 20 37 2c a0 e3 96 efHMAC函数
hmac_md5(binary, key) → varbinary使用给定的
key计算binary的 HMAC 值（采用 md5）。
select hmac_md5(x'555555', x'aa'); -- 81 a8 79 3f 89 f9 33 fa c8 1e 08 64 c4 d1 0b 80hmac_sha1(binary, key) → varbinary使用给定的
key计算binary的 HMAC 值（采用 sha1）。
select hmac_sha1(x'555555', x'aa');
-- ef 7c 81 0e 2e 72 dd 70 f8 68 a1 62 92 04 2a e0 7c 62 2b dchmac_sha256(binary, key) → varbinary使用给定的
key计算binary的 HMAC 值（采用 sha256）。
select hmac_sha256(x'555555', x'aa');
-- ef 7c 81 0e 2e 72 dd 70 f8 68 a1 62 92 04 2a e0 7c 62 2b dchmac_sha512(binary, key) → varbinary使用给定的
key计算binary的 HMAC 值（采用 sha512）。
select hmac_sha512(x'555555', x'aa');
-- eb 4b 9c bc 9f 57 67 2f a7 de 56 dc f1 8b 61 b8
36 e9 ab 5a 4d 5f b9 27 ab c0 b3 21 43 19 67 7a
fa 87 46 a8 12 4c 3e f4 ed 64 0d 06 35 82 f2 e5
d7 e8 1a 41 56 31 8f 6b 1b 93 a8 58 73 c9 af a5JSON函数和运算符
转换为JSON
以下类型可以转换为 JSON：
BOOLEANTINYINTSMALLINTINTEGERBIGINTREALDOUBLEVARCHAR
此外，满足以下要求时，可以将 ARRAY、MAP或ROW类型转换为JSON ：
ARRAY当数组的元素类型是支持的类型之一时，可以转换类型。MAP当映射的键类型是VARCHAR并且映射的值类型是受支持的类型时，可以转换类型，ROW当行的每个字段类型都是受支持的类型时，可以转换类型。
注意
具有支持的字符串类型的转换操作将输入视为字符串，而不是作为 JSON 进行验证。这意味着使用无效 JSON 的字符串类型输入的转换操作会导致成功转换为无效 JSON。
相反，可以考虑使用
json_parse()函数从字符串创建经过验证的 JSON。
下面通过示例展示了使用这些类型转换为 JSON 的行为：
SELECT CAST(NULL AS JSON); -- NULL
SELECT CAST(1 AS JSON); -- JSON '1'
SELECT CAST(9223372036854775807 AS JSON); -- JSON '9223372036854775807'
SELECT CAST('abc' AS JSON); -- JSON '"abc"'
SELECT CAST(true AS JSON); -- JSON 'true'
SELECT CAST(1.234 AS JSON); -- JSON '1.234'
SELECT CAST(ARRAY[1, 23, 456] AS JSON); -- JSON '[1,23,456]'
SELECT CAST(ARRAY[1, NULL, 456] AS JSON); -- JSON '[1,null,456]'
SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON); -- JSON '[[1,23],[456]]'
SELECT CAST(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[1, 23, 456]) AS JSON); -- JSON '{"k1":1,"k2":23,"k3":456}'
SELECT CAST(CAST(ROW(123, 'abc', true) AS
ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON); -- JSON '{"v1":123,"v2":"abc","v3":true}'注意
NULL到
JSON的转换并不能简单地实现。从独立的NULL进行转换将产生一个SQLNULL，而不是JSON 'null'。不过，在从包含NULL的数组或Map进行转换时，生成的JSON将包含null。
从JSON进行转换
支持转换为BOOLEAN、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、DOUBLE或VARCHAR。当数组的元素类型为支持的类型之一或Map的键类型为VARCHAR且Map的值类型为支持的类型之一时，支持转换为ARRAY和MAP。下面通过示例展示了转换的行为：
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS
ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS
ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}注意
JSON数组可以具有混合元素类型，JSON Map可以有混合值类型。这使得在某些情况下无法将其转换为SQL数组和Map。为了解决该问题，Hubble支持对数组和Map进行部分转换：
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON));
-- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']注意
在从
JSON转换为ROW时，支持 JSON 数组和 JSON 对象。
JSON函数
is_json_scalar(json) → boolean
确定json是否为标量（即JSON数字、JSON字符串、true、false或null）：
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- falsejson_array_contains(json, value) → boolean
确定json（包含JSON数组的字符串）中是否存在value：
SELECT json_array_contains('[1, 2, 3]', 2); -- truejson_array_get(json_array, index) → json
警告
该函数的语义已被破坏。如果提取的元素是字符串，它将被转换为未正确使用引号括起来的无效
JSON值（值不会被括在引号中，任何内部引号不会被转义）。我们建议不要使用该函数。无法在不影响现有用法的情况下修正该函数，并且可能会在将来的版本中删除该函数。
将指定索引处的元素返回到json_array中。索引从 0 开始：
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'该函数还支持负索引，以便获取从数组的末尾开始索引的元素：
SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'如果指定索引处的元素不存在，该函数将返回NULL：
SELECT json_array_get('[]', 0); -- NULL
SELECT json_array_get('["a", "b", "c"]', 10); -- NULL
SELECT json_array_get('["c", "b", "a"]', -10); -- NULLjson_array_length(json) → bigint
返回json（包含JSON数组的字符串）的数组长度：
SELECT json_array_length('[1, 2, 3]'); -- 3json_extract(json, json_path) → json
计算json（包含JSON的字符串）上的类似于 JSONPath表达式json_path并将结果作为 JSON字符串返回：
SELECT json_extract(json, '$.store.book');
SELECT json_extract(json, '$.store[book]');
SELECT json_extract(json, '$.store["book name"]');json_extract_scalar(json, json_path) → varchar
与json_extract()类似，但将结果值作为字符串返回（而不是编码为JSON）。json_path引用的值必须是常量（布尔值、数字或字符串）：
SELECT json_extract_scalar('[1, 2, 3]', '$[2]');
SELECT json_extract_scalar(json, '$.store.book[0].author');json_format(json) → varchar
返回从输入JSON值序列化的JSON文本。这是json_parse()的反函数：
SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]'
SELECT json_format(JSON '"a"'); -- '"a"'注意
json_format()和CAST(json AS VARCHAR)具有完全不同的语义。
json_format()将输入JSON值序列化为 RFC 7159 标准的 JSON 文本。JSON值可以是JSON对象、JSON数组、JSON字符串、JSON数字、true、false或null：SELECT json_format(JSON '{"a": 1, "b": 2}'); -- '{"a":1,"b":2}'
SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]'
SELECT json_format(JSON '"abc"'); -- '"abc"'
SELECT json_format(JSON '42'); -- '42'
SELECT json_format(JSON 'true'); -- 'true'
SELECT json_format(JSON 'null'); -- 'null'
CAST(json AS VARCHAR)将JSON值转换为对应的SQL VARCHAR值。对于JSON字符串、JSON数字、true、false或null，转换行为与对应的SQL类型相同。JSON 对象和 JSON 数组无法转换为 VARCHAR：SELECT CAST(JSON '{"a": 1, "b": 2}' AS VARCHAR); -- ERROR!
SELECT CAST(JSON '[1, 2, 3]' AS VARCHAR); -- ERROR!
SELECT CAST(JSON '"abc"' AS VARCHAR); -- 'abc' (the double quote is gone)
SELECT CAST(JSON '42' AS VARCHAR); -- '42'
SELECT CAST(JSON 'true' AS VARCHAR); -- 'true'
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
json_parse(string) → json
返回从输入 JSON文本反序列化的 JSON 值。这是json_format()的反函数：
SELECT json_parse('[1, 2, 3]'); -- JSON '[1,2,3]'
SELECT json_parse('"abc"'); -- JSON '"abc"'注意
json_parse()和CAST(string AS JSON)具有完全不同的语义。
json_parse()期望输入符合 RFC 7159 标准的 JSON 文本，并返回从该 JSON 文本反序列化的 JSON 值。JSON 值可以是JSON对象、JSON数组、JSON字符串、JSON数字、true、false或null：SELECT json_parse('not_json'); -- ERROR!
SELECT json_parse('{"a": 1, "b": 2}'); -- JSON '{"a": 1, "b": 2}'
SELECT json_parse('[1, 2, 3]'); -- JSON '[1,2,3]'
SELECT json_parse('"abc"'); -- JSON '"abc"'
SELECT json_parse('42'); -- JSON '42'
SELECT json_parse('true'); -- JSON 'true'
SELECT json_parse('null'); -- JSON 'null'
CAST(string AS JSON)接受任何 VARCHAR 值作为输入，并返回其值为输入字符串的 JSON 串：SELECT CAST(
not_jsonAS JSON); -- JSON '"not_json"'SELECT CAST('["a": 1, "b": 2]' AS JSON); -- JSON '"[\"a\": 1, \"b\": 2]"'
SELECT CAST('[1, 2, 3]' AS JSON); -- JSON '"[1, 2, 3]"'
SELECT CAST('"abc"' AS JSON); -- JSON '"\"abc\""'
SELECT CAST('42' AS JSON); -- JSON '"42"'
SELECT CAST('true' AS JSON); -- JSON '"true"'
SELECT CAST('null' AS JSON); -- JSON '"null"'
json_size(json, json_path) → bigint
与json_extract()类似，但返回值的大小。对于对象或数组，该大小为成员数量，标量值的大小为 0：
SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x'); -- 2
SELECT json_size('{"x": [1, 2, 3]}', '$.x'); -- 3
SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a'); -- 0日期和时间函数和运算符
日期和时间运算符
| 运算符 | 示例 | 结果 |
|---|---|---|
+ | date '2021-08-08' + interval '2' day | 2021-08-10 |
+ | time '01:00' + interval '3' hour | 04:00:00.000 |
+ | timestamp '2021-08-08 01:00' + interval '29' hour | 2021-08-09 06:00:00.000 |
+ | timestamp '2021-10-31 01:00' + interval '1' month | 2021-11-30 01:00:00.000 |
+ | interval '2' day + interval '3' hour | 2 03:00:00.000 |
+ | interval '3' year + interval '5' month | 3-5 |
- | date '2021-08-08' - interval '2' day | 2021-08-06 |
- | time '01:00' - interval '3' hour | 22:00:00.000 |
- | timestamp '2021-08-08 01:00' - interval '29' hour | 2021-08-06 20:00:00.000 |
- | timestamp '2021-10-31 01:00' - interval '1' month | 2021-09-30 01:00:00.000 |
- | interval '2' day - interval '3' hour | 1 21:00:00.000 |
- | interval '3' year - interval '5' month | 2-7 |
时区转换
运算符：AT TIME ZONE，用于设置时间戳的时区:
SELECT timestamp '2021-10-31 01:00 UTC';
-- 2021-10-31 01:00:00.000 UTC
SELECT timestamp '2021-10-31 01:00 UTC' AT TIME ZONE 'Asia/Shanghai';
-- 2021-10-30 18:00:00.000 Asia/Shanghai日期时间函数
current_date -> date返回查询开始时的当前日期。
select current_date; -- 2021-10-30current_time -> time with time zone
返回查询开始时带时区的当前时间。
select current_time; -- 20:38:35.205-07:00current_timestamp -> timestamp with time zone
返回查询开始时带时区的当前时间戳。具有3亚秒精度的数字。
select current_timestamp; -- 2021-10-30 20:39:49.572 Asia/Shanghaicurrent_timestamp(p) -> timestamp with time zone
返回查询开始时带时区的当前时间戳。具有p亚秒精度的数字。
select current_timestamp; -- 2021-10-30 20:39:49.34 Asia/Shanghaicurrent_timezone() -> varchar
以 IANA定义的格式（如Asia/Shanghai）或相对于UTC的固定偏移量（如+08:35）返回当前时区。
select current_timezone(); -- Asia/Shanghaidate(x) -> date
这是CAST(x AS date)的别名。
last_day_of_month(x) -> date
返回该月的最后一天。
select last_day_of_month(date '2021-10-30'); -- 2021-10-31from_iso8601_timestamp(string) -> timestamp(3) with time zone
将以ISO 8601格式表示的string（可选时间和时区）解析为timestamp(3) with time zone。时间默认为00:00:00.000，时区默认为会话时区：
SELECT from_iso8601_timestamp('2020-05-11');
-- 2020-05-11 00:00:00.000 Asia/Shanghai
SELECT from_iso8601_timestamp('2020-05-11T11:15:05');
-- 2020-05-11 11:15:05.000 Asia/Shanghaifrom_iso8601_timestamp_nanos(string) -> timestamp(9) with time zone
解析ISO 8601格式的日期和时间string。时区默认为会话时区：
SELECT from_iso8601_timestamp_nanos('2020-05-11T11:15:05');
-- 2020-05-11 11:15:05.000000000 Asia/Shanghaifrom_iso8601_date(string) -> date
解析ISO 8601格式的日期string为date。该日期可以是日历日期、使用 ISO 周编号的周日期，或者是年份和年份中的某一天的组合：
SELECT from_iso8601_date('2020-05-11'); -- 2020-05-11
SELECT from_iso8601_date('2020-W10'); -- 2020-03-02
SELECT from_iso8601_date('2020-123'); -- 2020-05-02at_timezone(timestamp, zone) -> timestamp(p) with time zone
将时区分量timestamp精确地更改p为 zone同时保留时间戳。
select at_timezone(timestamp '2020-07-22 15:00:15', '+8'); -- 2020-07-21 23:00:15 -08:00with_timezone(timestamp, zone) -> timestamp(p) with time zone
返回带有时区的时间戳timestamp精确到p和zone。
select at_timezone(timestamp '2020-07-22 15:00:15', '+8'); -- 2020-07-22 15:00:15 +08:00from_unixtime(unixtime) -> timestamp(3) with time zone
将UNIX时间戳unixtime作为带时区的时间戳返回。unixtime是从1970-01-01 00:00:00开始经历的秒数。
select from_unixtime(1635523200); -- 2021-10-30 00:00:00.000 Asia/Shanghaifrom_unixtime(unixtime, zone) -> timestamp(3) with time zone
将 UNIX时间戳unixtime作为带时区的时间戳返回，其中使用zone作为时区。unixtime是从1970-01-01 00:00:00开始经历的秒数。
select from_unixtime(1635523200, '-8'); -- 2021-10-29 08:00:00.000 -08:00from_unixtime(unixtime, hours, minutes) -> timestamp(3) with time zone
将UNIX时间戳unixtime作为带时区的时间戳返回，其中使用hours和minutes作为时区偏移量。unixtime是从1970-01-01 00:00:00开始经历的秒数。
select from_unixtime(1635523200, +8, +1); -- 2021-10-30 00:01:00.000 +08:01from_unixtime_nanos(unixtime) -> timestamp(9) with time zone
将UNIX时间戳unixtime作为带时区的时间戳返回。unixtime是从1970-01-01 00:00:00开始经历的秒数：
SELECT from_unixtime_nanos(1635523200); -- 1970-01-01 08:00:01.635523200 Asia/Shanghai
SELECT from_unixtime_nanos(DECIMAL '1234'); -- 1970-01-01 08:00:00.000001234 Asia/Shanghai
SELECT from_unixtime_nanos(DECIMAL '1234.499'); -- 1970-01-01 00:00:00.000001234 Asia/Shanghai
SELECT from_unixtime_nanos(DECIMAL '-1234'); -- 1969-12-31 23:59:59.999998766 Asia/Shanghailocaltime -> time
返回查询开始时的当前时间。
select localtime; -- 14:11:05.225localtimestamp -> timestamp
返回查询开始时的当前时间戳。具有3亚秒精度的数字。
select localtimestamp; -- 2021-10-30 14:11:34.981localtimestamp(p) -> timestamp
返回查询开始时的当前时间戳。具有p亚秒精度的数字：
SELECT localtimestamp(6); -- 2020-10-30 14:14:28.235548now() → timestamp(3) with time zone
这是current_timestamp()的别名。
to_iso8601(x) → varchar
将x格式化为 ISO 8601字符串。x可以是date、timestamp 或 timestamp with time zone。
select to_iso8601(date '2020-07-25'); -- 2020-07-25
select to_iso8601(timestamp '2020-07-25 15:22:15.214'); -- 2020-07-25T15:22:15.214+08:00to_milliseconds(interval) → bigint
以毫秒为单位返回以天和秒为单位的间隔interval。
select to_milliseconds(interval '8' day to second); -- 691200000to_unixtime(timestamp) → double
将timestamp作为UNIX时间戳返回。
select to_unixtime(cast('2020-10-30 14:32:15.147' as timestamp)); -- 1.604039535147E9注意
以下SQL标准函数不使用括号：
current_datecurrent_timecurrent_timestamplocaltimelocaltimestamp
date_trunc截断函数
date_trunc函数支持以下单位：
| 单位 | 截断值示例 |
|---|---|
second | 2021-08-22 03:04:05.000 |
minute | 2021-08-22 03:04:00.000 |
hour | 2021-08-22 03:00:00.000 |
day | 2021-08-22 00:00:00.000 |
week | 2021-08-20 00:00:00.000 |
month | 2021-08-01 00:00:00.000 |
quarter | 2021-07-01 00:00:00.000 |
year | 2021-01-01 00:00:00.000 |
上面的示例使用时间戳2021-08-22 03:04:05.321作为输入。
date_trunc(unit, x) -> 与输入类型相同
返回x截断至unit后的值：
SELECT date_trunc('day' , TIMESTAMP '2022-10-20 05:10:00'); -- 2022-10-20 00:00:00.000
SELECT date_trunc('month' , TIMESTAMP '2022-10-20 05:10:00'); -- 2022-10-01 00:00:00.000
SELECT date_trunc('year', TIMESTAMP '2022-10-20 05:10:00'); -- 2022-01-01 00:00:00.000间隔函数
本节中的函数支持以下间隔单位：
| 单位 | 说明 |
|---|---|
millisecond | 毫秒 |
second | 秒 |
minute | 分钟 |
hour | 小时 |
day | 天 |
week | 周 |
month | 月 |
quarter | 季度 |
year | 年 |
date_add(unit, value, timestamp)->与输入类型相同
向timestamp添加类型为unit的间隔value。可以使用负值做减法。
SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00'); -- 2020-03-01 00:01:26.000
SELECT date_add('hour', 9, TIMESTAMP '2020-03-01 00:00:00'); -- 2020-03-01 09:00:00.000
SELECT date_add('day', -1, TIMESTAMP '2020-03-01 00:00:00 UTC'); -- 2020-02-29 00:00:00.000 UTCdate_diff(unit, timestamp1, timestamp2) -> 与输入类型相同
以unit为单位返回timestamp2 - timestamp1的值。
SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00');
-- 86400
SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC');
-- 24
SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02');
-- 1
SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789');
-- 86400
SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789');
-- 86400123parse_duration持续时间函数
parse_duration函数支持以下单位：
| 单位 | 说明 |
|---|---|
ns | 纳秒 |
us | 微秒 |
ms | 毫秒 |
s | 秒 |
m | 分钟 |
h | 小时 |
d | 天 |
parse_duration(string) → interval
将格式为value unit的string解析为一个区间，其中value是以unit为单位的小数值：
SELECT parse_duration('42.8ms'); -- 0 00:00:00.043
SELECT parse_duration('3.81 d'); -- 3 19:26:24.000
SELECT parse_duration('5m'); -- 0 00:05:00.000human_readable_seconds(double) → varchar
将双精度值seconds格式化为可读的字符串，其中包含weeks, days, hours, minutes，及seconds：
SELECT human_readable_seconds(96); -- 1 minute, 36 seconds
SELECT human_readable_seconds(3762); -- 1 hour, 2 minutes, 42 seconds
SELECT human_readable_seconds(56363463); -- 93 weeks, 1 day, 8 hours, 31 minutes, 3 secondsMySQL日期函数
该部分中的函数使用与MySQL date_parse和str_to_date函数兼容的格式字符串。下表根据MySQL手册说明了格式说明符：
| 说明符 | 说明 |
|---|---|
%a | 工作日简称(Sun … Sat) |
%b | 月份简称 (Jan … Dec) |
%c | 以数字表示的月份 (1 … 12) [4] |
%D | 带英文后缀的一个月中的第几日（0th、1st、2nd、3rd、…） |
%d | 以数字表示的一个月中的第几日(01 … 31) [4] |
%e | 以数字表示的一个月中的第几日(1 … 31) [4] |
%f | 微秒（打印6位：000000 … 999000；解析1–9位：0 … 999999999）[1] |
%H | 小时(00 … 23) |
%h | 小时(01 … 12) |
%I | 小时(01 … 12) |
%i | 以数字表示的分钟(00 … 59) |
%j | 一年中的某日(001 … 366) |
%k | 小时(0 … 23) |
%l | 小时(1 … 12) |
%M | 月份名称(January … December) |
%m | 以数字表示的月份(01 … 12) [4] |
%p | AM或PM |
%r | 12小时制时间（hh:mm:ss，后跟AM或PM） |
%S | 秒(00 … 59) |
%s | 秒(00 … 59) |
%T | 24小时制时间(hh:mm:ss) |
%U | 周(00 … 53)，其中星期日为一周中的第一天 |
%u | 周(00 … 53)，其中星期一为一周中的第一天 |
%V | 周(01 … 53)，其中星期日为一周中的第一天；与%X配合使用 |
%v | 周(01 … 53)，其中星期一为一周中的第一天；与%x配合使用 |
%W | 周日名称(Sunday … Saturday) |
%w | 星期几(0 … 6)，其中星期日是一周中的第一天 [3] |
%X | 周所在的年份，其中星期日是一周中的第一天，以四位数字表示，与%V配合使用 |
%x | 周所在的年份，其中星期一是一周中的第一天，以四位数字表示，与%v配合使用 |
%Y | 年份，以四位数字表示 |
%y | 年份，以两位数字表示 [2] |
%% | %字符 |
%x | x，用于上面未列出的任何x |
- 时间戳被截断至毫秒。
- 在进行解析时，两位数的年份格式采用的范围为
1970至2069，因此“70”将产生年份1970，但“69”将产生2069。 - 尚不支持此说明符。考虑使用
day_of_week()（该函数使用1-7，而不使用0-6）。 - ([1], [2], [3], [4]) 该说明符不支持使用
0作为月份或日。
警告
当前不支持以下说明符：
%D %U %u %V %w %X
date_format(timestamp, format) → varchar
使用format将timestamp格式化为字符串：
SELECT date_format(TIMESTAMP '2022-10-20 05:10:00', '%m-%d-%Y %H'); -- 10-20-2022 05date_parse(string, format) → timestamp
使用format将string解析为时间戳：
SELECT date_parse('2022/10/20/05', '%Y/%m/%d/%H'); -- 2022-10-20 05:00:00.000
SELECT date(date_parse('20221021', '%Y%m%d'));-- 2022-10-21extract提取函数
extract函数支持以下字段：
| 字段 | 说明 |
|---|---|
YEAR | year() |
QUARTER | quarter() |
MONTH | month() |
WEEK | week() |
DAY | day() |
DAY_OF_MONTH | day() |
DAY_OF_WEEK | day_of_week() |
DOW | day_of_week() |
DAY_OF_YEAR | day_of_year() |
DOY | day_of_year() |
YEAR_OF_WEEK | year_of_week() |
YOW | year_of_week() |
HOUR | hour() |
MINUTE | minute() |
SECOND | second() |
TIMEZONE_HOUR | timezone_hour() |
TIMEZONE_MINUTE | timezone_minute() |
extract函数支持的类型因要提取的字段而异。大多数字段都支持所有日期和时间类型。
extract(field FROM x) → bigint
返回field自x：
SELECT extract(YEAR FROM TIMESTAMP '2022-10-20 05:10:00'); -- 2022注意
该SQL标准函数使用特殊的语法来指定参数。
便捷提取函数
day(x) → bigint
从x返回一个月中的第几日。
select day(date '2022-10-20'); -- 20day_of_month(x) → bigint
这是day(x)的别名。
day_of_week(x) → bigint
从x返回星期几(ISO)。值的范围为1（星期一）至7（星期日）。
select day_of_week(date '2022-10-20'); -- 4day_of_year(x) → bigint
从x返回一年中的第几日。值的范围为1至366。
select day_of_year(date '2022-10-20'); -- 293dow(x) → bigint
从x返回星期几(ISO)。值的范围为1（星期一）至7（星期日）。
这是day_of_week()的别名。
select dow(date '2022-10-20'); -- 4doy(x) → bigint从
x返回一年中的第几日。值的范围为1至366。这是
day_of_year()的别名。
select doy(date '2022-10-20'); -- 293hour(x) → bigint
从x返回一天中的第几个小时。值的范围为0至23。
select hour(timestamp '2022-10-20 05:10:15.100'); -- 5millisecond(x) → bigint
从x返回一秒中的第几个毫秒。
select millisecond(timestamp '2022-10-20 05:10:15.100'); -- 100minute(x) → bigint
从x返回一小时中的第几分钟。
select minute(timestamp '2022-10-20 05:10:15.100'); -- 10month(x) → bigint
从x返回一年中的某个月份。
select month(timestamp '2022-10-20 05:10:15.100'); -- 10quarter(x) → bigint
从x返回一年中的某个季度。值的范围为1至4。
select quarter(timestamp '2022-10-20 05:10:15.100'); -- 4second(x) → bigint
从x返回一分中的第几秒。
select second(timestamp '2022-10-20 05:10:15.100'); -- 15timezone_hour(timestamp) → bigint
从timestamp返回时区偏移量的小时数。
select timezone_hour(timestamp '2022-10-20 05:10:15.100'); -- 8timezone_minute(timestamp) → bigint
从timestamp返回时区偏移量的分钟数。
select timezone_minute(timestamp '2021-10-30 00:01:00.000 +08:01'); -- 1week(x) → bigint
从x返回一年中的第几周(ISO)。值的范围为1至53。
select week(timestamp '2022-10-20 05:10:15.100'); -- 42week_of_year(x) → bigint
这是week()的别名。
select week_of_year(timestamp'2022-10-20 05:10:15.100'); -- 42year(x) → bigint
从x返回ISO周的年份。
select year(timestamp '2022-10-20 05:10:15.100'); -- 2022聚合函数
聚合函数对一组值进行操作以计算得出单个结果。
除了 count()、count_if()、max_by()、min_by() 和 approx_distinct() 之外，所有这些聚合函数都忽略空值，并且对于没有输入行或所有值都是 NULL 的情况都返回 NULL。例如，sum() 返回 NULL 而不是0，avg() 在计数中不包括 NULL 值。可以使用 coalesce 函数将 NULL 转换为0。
聚合期间排序
某些聚合函数array_agg()会根据输入值的顺序产生不同的结果。可以通过在聚合函数中编写 ORDER BY 子句来指定此顺序：
array_agg(x ORDER BY y DESC)
array_agg(x ORDER BY x, y, z)聚合期间过滤
关键字FILTER可用于从聚合处理中删除具有使用WHERE子句表达的条件的行。在聚合中之前会对每一行进行评估，并且所有聚合函数都支持它。
aggregate_function(...) FILTER (WHERE <condition>)示例数据
--建表
create table fruit (num integer, name varchar, price integer);
--插入数据
insert into fruit values (1,'peach',5), (2,'apple',2), (3,'orange',6), (4,'watermelon',10);--建表
create table sample_collection(id integer, time_cost integer, weight decimal(5,2));
--插入数据
insert into sample_collection values
(1,5,86.38),
(2,10,281.17),
(3,15,89.91),
(4,20,17.5),
(5,25,88.76),
(6,30,83.94),
(7,35,44.26),
(8,40,17.4),
(9,45,5.6),
(10,50,145.68);一般聚合函数
arbitrary(x) → [same as input]
返回 x 的任意非 NULL值（如果存在）。
select arbitrary(price) from fruit; -- 5array_agg(x) → array<[same as input]>
返回通过输入 x 元素创建的数组。
select array_agg(price) from fruit; -- [5, 2, 6, 10]avg(x) → double
返回所有输入值的平均值（算术平均值）。
select avg(price) from fruit; -- 5.75avg(time interval type) → time interval type
返回所有输入值的平均间隔长度。
select avg(last_login) from (values ('admin',interval '0 06:15:30' day to second),('user1',interval '0 07:15:30' day to second),('user2',interval '0 08:15:30' day to second)) as login_log(user,last_login);
-- 0 07:15:30.000 假设有日志表记录用户距离上次登录的时间，那么这个结果表明平均登录时间间隔为0天7小时15分钟30秒bool_and(boolean) → boolean
如果所有输入值都为TRUE返回 TRUE ，否则返回 FALSE。
select bool_and(isorno) from (values ('01',true), ('02',false)) as items(num, isorno); -- false
select bool_and(isorno) from (values ('01',true), ('02',true)) as items(num, isorno); -- truebool_or(boolean) → boolean如果任一输入值为
TRUE，则返回TRUE，否则返回FALSE。
select bool_or(isorno) from (values ('01',false), ('02',false)) as items(num, isorno); -- false
select bool_or(isorno) from (values ('01',true), ('02',false)) as items(num, isorno); -- truechecksum(x) → varbinary
返回给定值的不区分顺序的校验和。
select checksum(price) from fruit; -- 7e 16 c0 1c 24 1c d6 4a count(*) → bigint
返回输入行的数量。
select count(*) from fruit; -- 4count(x) → bigint
返回非 NULL输入值的数量。
select count(name) from fruit; -- 4count_if(x) → bigint
返回 TRUE 输入值的数量。该函数等价于 count(CASE WHEN x THEN 1 END)。
select count_if(price > 7) from fruit; -- 1every(boolean) → booleanbool_and()的别名。geometric_mean(x) → double
返回所有输入值的几何平均值。
select geometric_mean(price) from fruit; -- 4.949232003839765listagg(x, separator) → varchar
返回串联的输入值，由separator字符串分隔。
LISTAGG( expression [, separator] [ON OVERFLOW overflow_behaviour])
WITHIN GROUP (ORDER BY sort_item, [...])如果separator未指定，则空字符串将用作separator。
该函数的最简单形式如下所示：
SELECT listagg (name, ',') WITHIN GROUP (ORDER BY name) from fruit;
-- apple,orange,peach,watermelon 如果函数输出的长度超过1048576字节，溢出行为默认会抛出错误：
SELECT listagg(value, ',' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY value) csv_value
FROM (VALUES 'a', 'b', 'c') t(value);在函数输出的长度超过1048576字节的情况下，还存在截断输出的可能性WITH COUNT或者WITHOUT COUNT：
SELECT LISTAGG(value, ',' ON OVERFLOW TRUNCATE '.....' WITH COUNT) WITHIN GROUP (ORDER BY value)
FROM (VALUES 'a', 'b', 'c') t(value);如果未指定，则截断填充字符串默认为'...'。
这个聚合函数也可以用在涉及到分组的场景中：
SELECT id, LISTAGG(value, ',') WITHIN GROUP (ORDER BY o) csv_value
FROM (VALUES
(100, 1, 'a'),
(200, 3, 'c'),
(200, 2, 'b')
) t(id, o, value)
GROUP BY id
ORDER BY id;
id | csv_value
-----+-----------
100 | a
200 | b,c当前LISTAGG函数的实现不支持窗口框架。
max(x)→ [与输入相同]
返回所有输入值中的最大值。
select max(price) from fruit; -- 10max(x, n) → array<[与 x 相同]>
返回 x 的所有输入值中的前 n 个最大值。
select max(price, 2) from fruit; -- [10, 6]max_by(x, y) → [与 x 相同]
返回与所有输入值中 y 的最大值相关联的 x 值。
select max_by(name, price) from fruit; -- watermelonmax_by(x, y, n) → array<[与 x 相同]>
返回与 y 降序排列时 y 的所有输入值中前 n 个最大值相关联的 n 个 x 值。
select max_by(name, price, 2) from fruit; -- [watermelon, orange]min(x) → [与输入相同]
返回所有输入值中的最小值。
select min(price) from fruit; -- 2min(x, n) → array<[与 x 相同]>
返回 x 的所有输入值中的前 n 个最小值。
select min(price) from fruit; -- 2min_by(x, y) → [与 x 相同]
返回与所有输入值中 y 的最小值相关联的 x 值。
select min_by(name, price) from fruit; -- applemin_by(x, y, n) → array<[与 x 相同]>
返回与 y 升序排列时 y 的所有输入值中前 n 个最小值相关联的 n 个 x 值。
select min_by(name, price, 2) from fruit; -- [apple, peach] sum(x) → [与输入相同]返回所有输入值的总和。
select sum(price) from fruit; -- 23按位聚合函数
bitwise_and_agg(x) -> bigint
返回以二进制补码表示的所有输入值的按位与运算结果。
select bitwise_and_agg(x) from (values (31),(32)) as t(x); -- 0bitwise_or_agg (x) -> bigint
返回以二进制补码表示的所有输入值的按位或运算结果。
select bitwise_or_agg(x) from (values (31),(32)) as t(x); -- 63映射聚合函数
histogram(x) -> map(K, bigint)
返回一个映射，其中包含每个输入值出现的次数。
select histogram(x) from (values (15),(10),(21),(15),(15),(8),(7),(21)) as t(x);
-- {8=1, 10=1, 21=2, 7=1, 15=3}map_agg(key, value) -> map(K, V)
返回通过输入 key/value 对创建的映射。
select map_agg(name,price) from fruit; -- {orange=6, peach=5, apple=2, watermelon=10}map_union(x(K, V)) -> map(K, V)
返回所有输入映射的并集。如果在多个输入映射中找到某个键，则生成的映射中该键的值来自任意输入映射。
select map_union(x) from (values (map(array['banana'],array[10.0])), (map(array['banana'],array[2.0])), (map(array['apple'],array[7.0]))) as t(x); -- {banana=10.0, apple=7.0}multimap_agg(key, value) -> map(K, array(V))
返回通过输入 key/value 对创建的多重映射。每个键可以关联多个值。
select multimap_agg(key, value) from (values ('banana',10),('banana',8),('banana',8),('apple',5) ) as t(key,value); -- {banana=[10, 8, 8], apple=[5]}近似聚合函数
approx_distinct(x) → bigint
返回不重复输入值的近似数量。 本函数给出 count(DISTINCT x) 的近似值。 如果所有输入值都为空则返回0。
本函数会产生2.3%的误差， （近似正态）误差分布的标准偏差会覆盖全部数据集。 对于任意指定的输入， 不保证误差上限。
select approx_distinct(price) from fruit; -- 4approx_distinct(x, e) → bigint
返回不重复输入值的近似数量。 本函数给出 count(DISTINCT x) 的近似值。 如果所有输入值都为空则返回0。
本函数会产生不超过 e 的误差， （近似正态）误差分布的标准偏差会覆盖全部数据集。 对于任意指定的输入， 不保证误差上限。 目前的函数实现要求 e在[0.01150, 0.26000]范围之间。
select approx_distinct(weight, 0.0040625) from sample_collection; -- 10
select approx_distinct(weight, 0.26) from sample_collection; -- 8approx_most_frequent(buckets, value, capacity) → map([与 value 相同], bigint)
近似计算最多元素buckets的最高频繁值。函数的近似估计使我们能够以较少的内存获取频繁的值。更大的capacity在牺牲内存容量的情况下提高了底层算法的准确性。返回值是一个映射，其中包含具有相应估计频率的顶部元素。
函数的误差取决于值的排列及其基数。我们可以将容量设置为与底层数据的基数相同，以实现最少的错误。
buckets和capacity必须是bigint类型。value可以是数字或字符串类型。
该函数使用A. Metwalley、D. Agrawl 和 A. Abbadi在论文 Efficient Computation of Frequent and Top-k Elements in Data Streams 中提出的流汇总数据结构。
approx_percentile(x, percentage) → [与 x 相同]
返回在给定 percentage 时 x 的所有输入值的近似百分位数。percentage 值必须介于 0 和 1 之间，并且对于所有输入行是一个常量。
select approx_percentile(x, 0.5) from (values (2), (3), (7), (8), (9)) as t(x); --7approx_percentile(x, percentages) → array([与 x 相同])
返回 x 的所有输入值在每个指定百分比处的近似百分位数。percentages 数组的每个元素必须介于 0 和 1 之间，并且该数组对于所有输入行必须是一个常量。
select approx_percentile(x, array[0.1,0.2,0.3,0.5]) from (values (2), (3), (7), (8), (9)) as t(x);
--[2, 3, 3, 7]approx_percentile(x, w, percentage) → [与 x 相同]
返回在百分比 p 处 x 的所有输入值（使用每项权重 w）的近似加权百分位数。权重必须是一个整数值，最小为 1。它实际上是百分位集中值 x 的重复计数。p 值必须介于 0 和 1 之间，并且对于所有输入行是一个常量。
select approx_percentile(x, 5, 0.5) from (values (2), (3), (7), (8), (9)) as t(x); -- 7approx_percentile(x, w, percentages) → array<[与 x 相同]>
返回在数组中指定的每个给定百分比处 x 的所有输入值（使用每项权重 w）的近似加权百分位数。权重必须是一个整数值，最小为 1。它实际上是百分位集中值 x 的重复计数。数组的每个元素必须介于 0 和 1 之间，并且该数组对于所有输入行必须是一个常量。
select approx_percentile(x, 5, array[0.1,0.2,0.3,0.5]) from (values (2), (3), (7), (8), (9)) as t(x);
-- [2, 3, 3, 7]numeric_histogram(buckets, value) → map<double, double>计算所有
value的近似直方图（存储桶的数量达buckets个）。该函数相当于numeric_histogram()的变体，接受weight，每项权重为1。
select numeric_histogram(20, x) from ( values (2), (3), (7), (8), (9)) as t(x);
-- {2.0=1.0, 3.0=1.0, 7.0=1.0, 8.0=1.0, 9.0=1.0}numeric_histogram(buckets, value, weight) → map<double, double>计算所有
value（每项权重为weight）的近似直方图（存储桶的数量达buckets个）。算法大致基于：
Yael Ben-Haim and Elad Tom-Tov, "A streaming parallel decision tree algorithm",
J. Machine Learning Research 11 (2010), pp. 849--872.select numeric_histogram(20, x, 4) from (values (2), (3), (7), (8), (9)) as t(x);
-- {2.0=4.0, 3.0=4.0, 7.0=4.0, 8.0=4.0, 9.0=4.0} buckets 必须是 bigint。value 和weight 必须是数字。
统计聚合函数
corr(y, x) → double
返回输入值的相关系数。
select corr(y, x) from (values (1,5), (2,6), (3,7), (4,8)) as t(x,y); -- 1.0covar_pop(y, x) → double
返回输入值的总体协方差。
select covar_pop(y, x) from (values (1,5), (2,6), (3,7), (4,8)) as t(x,y); -- 1.25covar_samp(y, x) → double
返回输入值的样本协方差。
select covar_samp(y, x) from (values (1,5), (2,6), (3,7), (4,8)) as t(x,y); -- 1.6666666kurtosis(x) → double
返回所有输入值的超值峰度。使用以下表达式的无偏估计：
kurtosis(x) = n(n+1)/((n-1)(n-2)(n-3))sum[(x_i-mean)^4]/stddev(x)^4-3(n-1)^2/((n-2)(n-3))select kurtosis(x) from (values (1), (2), (3), (4)) as t(x); -- -1.1999999999999993regr_intercept(y, x) → double
返回输入值的线性回归截距。y 为非独立值。x 为独立值。
select regr_intercept(y, x) from (values (1,5), (2,6), (3,7), (4,8)) as t(x,y); -- 4.0regr_slope(y, x) → double
返回输入值的线性回归斜率。y 为非独立值。x 为独立值。
select regr_slope(y, x) from (values (1,5), (2,6), (3,7), (4,8)) as t(x,y); -- 1.0skewness(x) → double
返回所有输入值的偏度。
select skewness(x) from (values (1), (2), (3), (4)) as t(x); -- 0.0stddev(x) → double
这是 stddev_samp() 的别名。
stddev_pop(x) → double
返回所有输入值的总体标准差。
select stddev_pop(x) from (values (1), (2), (3), (4)) as t(x); -- 1.118033988749895stddev_samp(x) → double
返回所有输入值的样本标准差。
select stddev_samp(x) from (values (1), (2), (3), (4)) as t(x); -- 1.2909944487358056variance(x) → double
这是 var_samp() 的别名。
var_pop(x) → double
返回所有输入值的总体方差。
select var_pop(x) from (values (1), (2), (3), (4)) as t(x); -- 1.25var_samp(x) → double
返回输入值的样本方差。
select var_samp(x) from (values (1), (2), (3), (4)) as t(x); -- 1.6666666666666667lambda聚合函数
reduce_agg(inputValue T , initialState S , inputFunction(S , T , S) , combineFunction(S , S , S)) → S
将所有输入值缩减为单个值。会为每个非 NULL 输入值调用 inputFunction。除了接受输入值之外，inputFunction 还接受当前状态（最初为 initialState）并返回新状态。会调用 combineFunction 将两个状态合并成一个新的状态。返回最终状态：
SELECT id, reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b)
FROM (
VALUES
(1, 3),
(1, 4),
(1, 5),
(2, 6),
(2, 7)
) AS t(id, value)
GROUP BY id;
-- (1, 12)
-- (2, 13)
SELECT id, reduce_agg(value, 1, (a, b) -> a * b, (a, b) -> a * b)
FROM (
VALUES
(1, 3),
(1, 4),
(1, 5),
(2, 6),
(2, 7)
) AS t(id, value)
GROUP BY id;
-- (1, 60)
-- (2, 42)状态类型必须是布尔型、整型、浮点型或日期/时间/间隔型。
窗口函数
窗口函数对查询结果中的行执行计算。窗口函数在HAVING子句之后、ORDER BY子句之前运行。调用窗口函数需要使用特殊的语法（使用OVER子句来指定窗口）。
例如，以下查询按价格对每个售货员的订单进行排序：
SELECT orderkey, clerk, totalprice,
rank() OVER (PARTITION BY clerk
ORDER BY totalprice DESC) AS rnk
FROM orders
ORDER BY clerk, rnk聚合函数
通过添加OVER子句，可以将所有aggregate用作窗口函数。会为当前行的窗口框架中的每个行计算聚合函数。
例如，以下查询为每个售货员生成按天滚动的订单价格总和：
SELECT clerk, orderdate, orderkey, totalprice,
sum(totalprice) OVER (PARTITION BY clerk
ORDER BY orderdate) AS rolling_sum
FROM orders
ORDER BY clerk, orderdate, orderkey示例数据
为便于理解各函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表emp，并添加数据，命令示例如下：
create table if not exists emp
(empno integer,
ename varchar,
job varchar,
mgr integer,
hiredate TIMESTAMP,
sal integer,
comm integer,
deptno integer)
WITH (
format = 'MULTIDELIMIT',
textfile_field_separator = ','
);
load data local inpath 'emp.txt' into table emp;emp.txt 中的数据如下：
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10排序函数
cume_dist() → bigint
返回一组值中某个值的累积分布。结果是窗口分区的窗口排序中的行前面或与之对等的行数量除以窗口分区中的总行数。因此，排序中的任何绑定值都将计算为相同的分布值。
例如：将所有职工根据部门（deptno）分组（作为开窗列），计算薪水（sal）在同一组内的前百分之几。
select
deptno,
ename,
sal,
round(cume_dist() over (partition by deptno order by sal desc)* 100, 2) as cume_dist
from emp order by deptno;返回结果如下：
deptno | ename | sal | cume_dist
--------+--------+------+-----------
10 | KING | 5000 | 33.33
10 | JACCKA | 5000 | 33.33
10 | CLARK | 2450 | 66.67
10 | WELAN | 2450 | 66.67
10 | MILLER | 1300 | 100.0
10 | TEBAGE | 1300 | 100.0
20 | SCOTT | 3000 | 40.0
20 | FORD | 3000 | 40.0
20 | JONES | 2975 | 60.0
20 | ADAMS | 1100 | 80.0
20 | SMITH | 800 | 100.0
30 | BLAKE | 2850 | 16.67
30 | ALLEN | 1600 | 33.33
30 | TURNER | 1500 | 50.0
30 | WARD | 1250 | 83.33
30 | MARTIN | 1250 | 83.33
30 | JAMES | 950 | 100.0dense_rank() → bigint
返回某个值在一组值中的排名。这与rank()类似，只是绑定值不在序列中产生间隙。
例如：将所有职工根据部门（deptno）分组（作为开窗列），每个组内根据薪水（sal）做降序排序，获得职工自己组内的序号。
select
deptno,
ename,
sal,
dense_rank() over (partition by deptno order by sal desc) as nums
from emp order by deptno;返回结果如下：
deptno | ename | sal | nums
--------+--------+------+------
10 | KING | 5000 | 1
10 | JACCKA | 5000 | 1
10 | CLARK | 2450 | 2
10 | WELAN | 2450 | 2
10 | MILLER | 1300 | 3
10 | TEBAGE | 1300 | 3
20 | SCOTT | 3000 | 1
20 | FORD | 3000 | 1
20 | JONES | 2975 | 2
20 | ADAMS | 1100 | 3
20 | SMITH | 800 | 4
30 | BLAKE | 2850 | 1
30 | ALLEN | 1600 | 2
30 | TURNER | 1500 | 3
30 | WARD | 1250 | 4
30 | MARTIN | 1250 | 4
30 | JAMES | 950 | 5ntile(n) → bigint将每个窗口分区的行分到
n个桶中，范围为1至n（最大）。桶值最多相差1。如果分区中的行数未平均分成桶数，则从第一个桶开始每个桶分配一个剩余值。例如，对于
6行和4个桶，桶值如下：112234。例如：将所有职工根据部门按薪水（sal）从高到低切分为3组，并获得职工自己所在组的序号。
select
deptno,
ename,
sal,
ntile(3) over (partition by deptno order by sal desc) as nt3
from emp order by deptno;返回结果如下：
deptno | ename | sal | nt3
--------+--------+------+-----
10 | KING | 5000 | 1
10 | JACCKA | 5000 | 1
10 | CLARK | 2450 | 2
10 | WELAN | 2450 | 2
10 | MILLER | 1300 | 3
10 | TEBAGE | 1300 | 3
20 | SCOTT | 3000 | 1
20 | FORD | 3000 | 1
20 | JONES | 2975 | 2
20 | ADAMS | 1100 | 2
20 | SMITH | 800 | 3
30 | BLAKE | 2850 | 1
30 | ALLEN | 1600 | 1
30 | TURNER | 1500 | 2
30 | WARD | 1250 | 2
30 | MARTIN | 1250 | 3
30 | JAMES | 950 | 3percent_rank() → double返回某个值在一组值中的百分比排名。结果是
(r - 1) / (n - 1)，其中r是该行的rank()，n是窗口分区中的总行数。例如：计算员工薪水在组内的百分比排名。
select
deptno,
ename,
sal,
percent_rank() over (partition by deptno order by sal desc) as sal_new
from emp order by deptno;返回结果如下：
deptno | ename | sal | sal_new
--------+--------+------+---------
10 | KING | 5000 | 0.0
10 | JACCKA | 5000 | 0.0
10 | CLARK | 2450 | 0.4
10 | WELAN | 2450 | 0.4
10 | MILLER | 1300 | 0.8
10 | TEBAGE | 1300 | 0.8
20 | SCOTT | 3000 | 0.0
20 | FORD | 3000 | 0.0
20 | JONES | 2975 | 0.5
20 | ADAMS | 1100 | 0.75
20 | SMITH | 800 | 1.0
30 | BLAKE | 2850 | 0.0
30 | ALLEN | 1600 | 0.2
30 | TURNER | 1500 | 0.4
30 | WARD | 1250 | 0.6
30 | MARTIN | 1250 | 0.6
30 | JAMES | 950 | 1.0rank() → bigint返回某个值在一组值中的排序。排序是该行之前与该行不对等的行数加一。因此，排序中的绑定值将在序列中产生间隙。会对每个窗口分区进行排序。
例如：将所有职工根据部门（deptno）分组（作为开窗列），每个组内根据薪水（sal）做降序排序，获得职工自己组内的序号。
select
deptno,
ename,
sal,
rank() over (partition by deptno order by sal desc) as nums
from emp order by deptno;返回结果如下：
deptno | ename | sal | nums
--------+--------+------+------
10 | KING | 5000 | 1
10 | JACCKA | 5000 | 1
10 | CLARK | 2450 | 3
10 | WELAN | 2450 | 3
10 | MILLER | 1300 | 5
10 | TEBAGE | 1300 | 5
20 | SCOTT | 3000 | 1
20 | FORD | 3000 | 1
20 | JONES | 2975 | 3
20 | ADAMS | 1100 | 4
20 | SMITH | 800 | 5
30 | BLAKE | 2850 | 1
30 | ALLEN | 1600 | 2
30 | TURNER | 1500 | 3
30 | WARD | 1250 | 4
30 | MARTIN | 1250 | 4
30 | JAMES | 950 | 6row_number() → bigint根据窗口分区内行的排序，从1开始返回每行的唯一序列号。
例如：将所有职工根据部门（deptno）分组（作为开窗列），每个组内根据薪水（sal）做降序排序，获得职工在自己组内的序号。
select
deptno,
ename,
sal,
row_number() over (partition by deptno order by sal desc) as nums
from emp order by deptno;返回结果如下：
deptno | ename | sal | nums
--------+--------+------+------
10 | KING | 5000 | 1
10 | JACCKA | 5000 | 2
10 | CLARK | 2450 | 3
10 | WELAN | 2450 | 4
10 | MILLER | 1300 | 5
10 | TEBAGE | 1300 | 6
20 | SCOTT | 3000 | 1
20 | FORD | 3000 | 2
20 | JONES | 2975 | 3
20 | ADAMS | 1100 | 4
20 | SMITH | 800 | 5
30 | BLAKE | 2850 | 1
30 | ALLEN | 1600 | 2
30 | TURNER | 1500 | 3
30 | WARD | 1250 | 4
30 | MARTIN | 1250 | 5
30 | JAMES | 950 | 6值函数
first_value(x) → [与输入相同]
返回窗口的第一个值。
例如：将所有职工根据部门分组，返回每组中的第一行数据。
- 不指定
order by：
select
deptno,
ename,
sal,
first_value(sal) over (partition by deptno) as first_value
from emp order by deptno;返回结果如下：
deptno | ename | sal | first_value
--------+--------+------+-------------
10 | CLARK | 2450 | 2450
10 | KING | 5000 | 2450
10 | MILLER | 1300 | 2450
10 | JACCKA | 5000 | 2450
10 | WELAN | 2450 | 2450
10 | TEBAGE | 1300 | 2450
20 | SMITH | 800 | 800
20 | JONES | 2975 | 800
20 | SCOTT | 3000 | 800
20 | ADAMS | 1100 | 800
20 | FORD | 3000 | 800
30 | ALLEN | 1600 | 1600
30 | WARD | 1250 | 1600
30 | MARTIN | 1250 | 1600
30 | BLAKE | 2850 | 1600
30 | TURNER | 1500 | 1600
30 | JAMES | 950 | 1600- 指定
order by：
select
deptno,
ename,
sal,
first_value(sal) over (partition by deptno order by sal desc) as first_value
from emp order by deptno;返回结果如下：
deptno | ename | sal | first_value
--------+--------+------+-------------
10 | KING | 5000 | 5000
10 | JACCKA | 5000 | 5000
10 | CLARK | 2450 | 5000
10 | WELAN | 2450 | 5000
10 | MILLER | 1300 | 5000
10 | TEBAGE | 1300 | 5000
20 | SCOTT | 3000 | 3000
20 | FORD | 3000 | 3000
20 | JONES | 2975 | 3000
20 | ADAMS | 1100 | 3000
20 | SMITH | 800 | 3000
30 | BLAKE | 2850 | 2850
30 | ALLEN | 1600 | 2850
30 | TURNER | 1500 | 2850
30 | WARD | 1250 | 2850
30 | MARTIN | 1250 | 2850
30 | JAMES | 950 | 2850last_value(x) → [与输入相同]返回窗口的最后一个值。
例如：将所有职工根据部门分组，返回每组中的最后一行数据。
不指定
order by，当前窗口为第一行到最后一行的范围，返回当前窗口的最后一行的值。：
select
deptno,
ename,
sal,
last_value(sal) over (partition by deptno) as last_value
from emp order by deptno;返回结果如下：
deptno | ename | sal | last_value
--------+--------+------+------------
10 | CLARK | 2450 | 1300
10 | KING | 5000 | 1300
10 | MILLER | 1300 | 1300
10 | JACCKA | 5000 | 1300
10 | WELAN | 2450 | 1300
10 | TEBAGE | 1300 | 1300
20 | SMITH | 800 | 3000
20 | JONES | 2975 | 3000
20 | SCOTT | 3000 | 3000
20 | ADAMS | 1100 | 3000
20 | FORD | 3000 | 3000
30 | ALLEN | 1600 | 950
30 | WARD | 1250 | 950
30 | MARTIN | 1250 | 950
30 | BLAKE | 2850 | 950
30 | TURNER | 1500 | 950
30 | JAMES | 950 | 950- 指定
order by，当前窗口为第一行到当前行的范围。返回当前窗口的当前行的值：
select
deptno,
ename,
sal,
last_value(sal) over (partition by deptno order by sal desc) as last_value
from emp order by deptno;返回结果如下：
deptno | ename | sal | last_value
--------+--------+------+------------
10 | KING | 5000 | 5000
10 | JACCKA | 5000 | 5000
10 | CLARK | 2450 | 2450
10 | WELAN | 2450 | 2450
10 | MILLER | 1300 | 1300
10 | TEBAGE | 1300 | 1300
20 | SCOTT | 3000 | 3000
20 | FORD | 3000 | 3000
20 | JONES | 2975 | 2975
20 | ADAMS | 1100 | 1100
20 | SMITH | 800 | 800
30 | BLAKE | 2850 | 2850
30 | ALLEN | 1600 | 1600
30 | TURNER | 1500 | 1500
30 | WARD | 1250 | 1250
30 | MARTIN | 1250 | 1250
30 | JAMES | 950 | 950nth_value(x, offset)→ [与输入相同]
返回相对于窗口开头的指定偏移处的值。偏移从1开始。偏移可以是任何标量表达式。如果偏移为NULL或大于窗口中的值的数量，则返回NULL。如果偏移为零或负数，则产生错误。
例如：将所有职工根据部门分组，返回每组中的第6行数据。
- 不指定
order by，当前窗口为第一行到最后一行的范围，返回当前窗口第6行的值。
select
deptno,
ename,
sal,
nth_value(sal, 6) over (partition by deptno) as nth_value
from emp order by deptno;返回结果如下（当前窗口没有第6行的，返回NULL）：
deptno | ename | sal | nth_value
--------+--------+------+-----------
10 | CLARK | 2450 | 1300
10 | KING | 5000 | 1300
10 | MILLER | 1300 | 1300
10 | JACCKA | 5000 | 1300
10 | WELAN | 2450 | 1300
10 | TEBAGE | 1300 | 1300
20 | SMITH | 800 | NULL
20 | JONES | 2975 | NULL
20 | SCOTT | 3000 | NULL
20 | ADAMS | 1100 | NULL
20 | FORD | 3000 | NULL
30 | ALLEN | 1600 | 950
30 | WARD | 1250 | 950
30 | MARTIN | 1250 | 950
30 | BLAKE | 2850 | 950
30 | TURNER | 1500 | 950
30 | JAMES | 950 | 950- 指定
order by，当前窗口为第一行到当前行的范围，返回当前窗口第6行的值。
select
deptno,
ename,
sal,
nth_value(sal, 6) over (partition by deptno order by sal) as nth_value
from emp order by deptno;返回结果如下（当前窗口没有第6行的，返回NULL）：
deptno | ename | sal | nth_value
--------+--------+------+-----------
10 | MILLER | 1300 | NULL
10 | TEBAGE | 1300 | NULL
10 | CLARK | 2450 | NULL
10 | WELAN | 2450 | NULL
10 | KING | 5000 | 5000
10 | JACCKA | 5000 | 5000
20 | SMITH | 800 | NULL
20 | ADAMS | 1100 | NULL
20 | JONES | 2975 | NULL
20 | SCOTT | 3000 | NULL
20 | FORD | 3000 | NULL
30 | JAMES | 950 | NULL
30 | WARD | 1250 | NULL
30 | MARTIN | 1250 | NULL
30 | TURNER | 1500 | NULL
30 | ALLEN | 1600 | NULL
30 | BLAKE | 2850 | 2850lead(x[, offset[, default_value]]) → [与输入相同]
返回窗口中当前行之后offset行处的值。偏移从0（当前行）开始。偏移可以是任何标量表达式。默认offset为1。如果偏移为NULL或大于窗口，则返回default_value，如果未指定该值，则返回null。该lead()函数要求指定窗口顺序。不得指定窗口框架。
例如：将所有职工根据部门（deptno）分组（作为开窗列），每位员工的薪水（sal）做偏移。
select
deptno,
ename,
sal,
lead(sal,
1) over (partition by deptno order by sal) as sal_new
from emp order by deptno;返回结果如下：
deptno | ename | sal | sal_new
--------+--------+------+---------
10 | MILLER | 1300 | 1300
10 | TEBAGE | 1300 | 2450
10 | CLARK | 2450 | 2450
10 | WELAN | 2450 | 5000
10 | KING | 5000 | 5000
10 | JACCKA | 5000 | NULL
20 | SMITH | 800 | 1100
20 | ADAMS | 1100 | 2975
20 | JONES | 2975 | 3000
20 | SCOTT | 3000 | 3000
20 | FORD | 3000 | NULL
30 | JAMES | 950 | 1250
30 | WARD | 1250 | 1250
30 | MARTIN | 1250 | 1500
30 | TURNER | 1500 | 1600
30 | ALLEN | 1600 | 2850
30 | BLAKE | 2850 | NULLlag(x[, offset[, default_value]]) → [与输入相同]
返回窗口中当前行之前offset行处的值。偏移从0（当前行）开始。偏移可以是任何标量表达式。默认offset为1。如果偏移为NULL或大于窗口，则返回default_value，如果未指定该值，则返回null。该lag()函数要求指定窗口顺序。不得指定窗口框架。
例如：将所有职工根据部门（deptno）分组（作为开窗列），每位员工的薪水（sal）做偏移。
select
deptno,
ename,
sal,
lag(sal, 1) over (partition by deptno order by sal) as sal_new
from emp order by deptno;返回结果如下：
deptno | ename | sal | sal_new
--------+--------+------+---------
10 | MILLER | 1300 | NULL
10 | TEBAGE | 1300 | 1300
10 | CLARK | 2450 | 1300
10 | WELAN | 2450 | 2450
10 | KING | 5000 | 2450
10 | JACCKA | 5000 | 5000
20 | SMITH | 800 | NULL
20 | ADAMS | 1100 | 800
20 | JONES | 2975 | 1100
20 | SCOTT | 3000 | 2975
20 | FORD | 3000 | 3000
30 | JAMES | 950 | NULL
30 | WARD | 1250 | 950
30 | MARTIN | 1250 | 1250
30 | TURNER | 1500 | 1250
30 | ALLEN | 1600 | 1500
30 | BLAKE | 2850 | 1600数组函数和运算符
下标运算符： []
[] 运算符用于访问数组中的一个元素，其索引从 1 开始：
select my_array[1] AS first_element连接运算符： ||
|| 运算符用于将数组与一个相同类型的数组或元素进行连接：
SELECT ARRAY [1] || ARRAY [2]; -- [1, 2]
SELECT ARRAY [1] || 2; -- [1, 2]
SELECT 2 || ARRAY [1]; -- [2, 1]数组函数
all_match(array(T), function(T, boolean)) → boolean
返回数组的所有元素是否都匹配给定的谓词。true如果所有元素都匹配谓词则返回（特殊情况是数组为空时）；false如果一个或多个元素不匹配；NULL如果谓词函数返回NULL一个或多个元素以及true所有其他元素。
any_match(array(T), function(T, boolean)) → boolean
返回数组的任何元素是否匹配给定的谓词。true如果一个或多个元素与谓词匹配则返回；false如果没有元素匹配（特殊情况是数组为空）；NULL如果谓词函数返回NULL一个或多个元素以及false 所有其他元素。
array_distinct(x) → array
删除数组 x 中的重复值。
select array_distinct(array [1,2,5,6,8,4,5,1,2,6]); -- [1, 2, 5, 6, 8, 4]array_intersect(x, y) → array
返回 x 与 y 的交集中的元素构成的数组，不含重复元素。
select array_intersect(array [1,2,3,4,8], array [6,5,2,3,1]); -- [1, 2, 3]array_union(x, y) → array
返回 x 与 y 的并集中的元素构成的数组，不含重复元素。
select array_union(array [1,2,3,4,8], array [6,5,2,3,1]); -- [1, 2, 3, 4, 8, 6, 5]array_except(x, y) → array
返回位于 x 但不位于 y 中的元素构成的数组，不含重复元素。
select array_except(array [1,2,3,4,8], array [6,5,2,3,1]); -- [4, 8]array_join(x, delimiter, null_replacement) → varchar
使用分隔符和一个用于替换 NULL 的可选字符串连接给定数组的元素。
select array_join(array [1,2,3,null,5,6], '|', '0'); -- 1|2|3|0|5|6 array_max(x) → x
返回输入数组中的最大值。
select array_max(array [1,2,3,4,8]); -- 8array_min(x) → x
返回输入数组中的最小值。
select array_min(array [1,2,3,4,8]); -- 1array_position(x, element) → bigint
返回数组 x 中 element 第一次出现的位置（如果没有找到，则返回 0）。
select array_position(array [1,2,3,4,8,3,2,1], 2); -- 2array_remove(x, element) → array
删除数组 x 中所有等于 element 的元素。
select array_remove(array [1,2,3,4,8,3,2,1], 2); -- [1, 3, 4, 8, 3, 1]array_sort(x) → array
对数组 x 进行排序并返回该数组。x 的元素必须是可排序的。NULL元素将被放置在返回的数组的末尾。
select array_sort(array [1,2,3,4,8,3,null,2,1]); -- [1, 1, 2, 2, 3, 3, 4, 8, NULL]array_sort(array(T), function(T, T, int)) → array(T)
基于给定的比较函数 function 对 array 进行排序并将其返回。比较函数将接受两个可以为 NULL 的参数来表示 array 中两个可以为 NULL 的元素。当第一个可以为 NULL 的元素小于、等于或大于第二个可以为 NULL 的元素时，该函数返回 -1、0 或 1。如果比较函数返回其他值（包括 NULL），查询将失败并产生一个错误：
SELECT array_sort(ARRAY[3, 2, 5, 1, 2],
(x, y) -> IF(x < y, 1, IF(x = y, 0, -1)));
-- [5, 3, 2, 2, 1]
SELECT array_sort(ARRAY['bc', 'ab', 'dc'],
(x, y) -> IF(x < y, 1, IF(x = y, 0, -1)));
-- ['dc', 'bc', 'ab']
SELECT array_sort(ARRAY[3, 2, null, 5, null, 1, 2],
-- sort null first with descending order
(x, y) -> CASE WHEN x IS NULL THEN -1
WHEN y IS NULL THEN 1
WHEN x < y THEN 1
WHEN x = y THEN 0
ELSE -1 END);
-- [null, null, 5, 3, 2, 2, 1]
SELECT array_sort(ARRAY[3, 2, null, 5, null, 1, 2],
-- sort null last with descending order
(x, y) -> CASE WHEN x IS NULL THEN 1
WHEN y IS NULL THEN -1
WHEN x < y THEN 1
WHEN x = y THEN 0
ELSE -1 END);
-- [5, 3, 2, 2, 1, null, null]
SELECT array_sort(ARRAY['a', 'abcd', 'abc'],
-- sort by string length
(x, y) -> IF(length(x) < length(y), -1,
IF(length(x) = length(y), 0, 1)));
-- ['a', 'abc', 'abcd']
SELECT array_sort(ARRAY[ARRAY[2, 3, 1], ARRAY[4, 2, 1, 4], ARRAY[1, 2]],
-- sort by array length
(x, y) -> IF(cardinality(x) < cardinality(y), -1,
IF(cardinality(x) = cardinality(y), 0, 1)));
-- [[1, 2], [2, 3, 1], [4, 2, 1, 4]]arrays_overlap(x, y) → boolean
测试数组 x 和 y 是否有任何共同的非 NULL元素。如果没有共同的非 NULL元素，但任一数组包含 NULL，则返回 NULL。
select arrays_overlap(array [1,2,3], array [3,4,5]); -- true
select arrays_overlap(array [1,2,3], array [4,5,6]); -- falsecardinality(x) → bigint
返回数组 x 的基数（大小）。
select cardinality(array [1,2,3,4,8,3,2,1]); -- 8concat(array1, array2, ..., arrayN) → array连接数组array1、array2、...arrayN。该函数提供与 SQL标准连接运算符 (||) 相同的功能。
select concat(array [1,2,3], array [0], array [7,8]); -- [1, 2, 3, 0, 7, 8]combinations(array(T), n) → array(array(T))
返回输入数组的 n 元素子组。如果输入数组没有重复项，则 combinations 返回n元素子集：
SELECT combinations(ARRAY['foo', 'bar', 'baz'], 2);
-- [['foo', 'bar'], ['foo', 'baz'], ['bar', 'baz']]
SELECT combinations(ARRAY[1, 2, 3], 2);
-- [[1, 2], [1, 3], [2, 3]]
SELECT combinations(ARRAY[1, 2, 2], 2);
-- [[1, 2], [1, 2], [2, 2]]子组的顺序是确定的，但未经指定。子组中元素的顺序是确定的，但未经指定。n 不得大于 5，生成的子组的总大小必须小于 100000。
contains(x, element) → boolean
如果数组 x 包含 element，则返回 true。
select contains(array [1,2,3,4,8,3,2,1], 2); -- truecontains_sequence(x, seq) → boolean
如果数组x包含所有数组seq作为子序列（所有值都以相同的连续顺序），则返回 true。
select contains_sequence(array [1,2,3], array [1,2]); -- trueelement_at(array(E), index) → E
返回 array 在给定 index 处的元素。如果 index > 0，则该函数提供与 SQL 标准下标运算符 ([]) 相同的功能。如果 index < 0，则 element_at 按照从最后一个到第一个的顺序访问元素。
select element_at(array['a','b','c','d','e'], 3); -- 'c'filter(array(T), function(T, boolean)) -> array(T)
通过 function 针对其返回 true 的 array 的元素构造一个数组。
SELECT filter(ARRAY[], x -> true); -- []
SELECT filter(ARRAY[5, -6, NULL, 7], x -> x > 0); -- [5, 7]
SELECT filter(ARRAY[5, NULL, 7, NULL], x -> x IS NOT NULL); -- [5, 7]flatten(x) → array
以串联的方式将 array(array(T)) 展开为 array(T)。
ngrams(array(T), n) -> array(array(T))
返回 array 的 n-gram（包含 n 个相邻元素的子序列）。结果中 n-gram 的顺序未经指定。
SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 2); -- [['foo', 'bar'], ['bar', 'baz'], ['baz', 'foo']]
SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 3); -- [['foo', 'bar', 'baz'], ['bar', 'baz', 'foo']]
SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 4); -- [['foo', 'bar', 'baz', 'foo']]
SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 5); -- [['foo', 'bar', 'baz', 'foo']]
SELECT ngrams(ARRAY[1, 2, 3, 4], 2); -- [[1, 2], [2, 3], [3, 4]]none_match(array(T), function(T, boolean)) → boolean
返回数组中是否没有元素与给定谓词匹配。true如果没有元素与谓词匹配则返回（特殊情况是数组为空时）；false如果一个或多个元素匹配； NULL如果谓词函数返回NULL一个或多个元素以及false所有其他元素。
SELECT none_match(ARRAY[1, 2, 3, 4], x -> x < 0); -- truereduce(array(T), initialState S, inputFunction(S, T, S), outputFunction(S, R)) → R
返回从 array 简化得到的单个值。会按顺序为 array 中的每个元素调用 inputFunction。除了接受元素之外，inputFunction 还接受当前状态（最初为 initialState）并返回新状态。会调用 outputFunction 以将最终状态转换为结果值。该函数可能是恒等函数 (i -> i)：
SELECT reduce(ARRAY[], 0,
(s, x) -> s + x,
s -> s);
-- 0
SELECT reduce(ARRAY[5, 20, 50], 0,
(s, x) -> s + x,
s -> s);
-- 75
SELECT reduce(ARRAY[5, 20, NULL, 50], 0,
(s, x) -> s + x,
s -> s);
-- NULL
SELECT reduce(ARRAY[5, 20, NULL, 50], 0,
(s, x) -> s + coalesce(x, 0),
s -> s);
-- 75
SELECT reduce(ARRAY[5, 20, NULL, 50], 0,
(s, x) -> IF(x IS NULL, s, s + x),
s -> s);
-- 75
SELECT reduce(ARRAY[2147483647, 1], BIGINT '0',
(s, x) -> s + x,
s -> s);
-- 2147483648
-- calculates arithmetic average
SELECT reduce(ARRAY[5, 6, 10, 20],
CAST(ROW(0.0, 0) AS ROW(sum DOUBLE, count INTEGER)),
(s, x) -> CAST(ROW(x + s.sum, s.count + 1) AS
ROW(sum DOUBLE, count INTEGER)),
s -> IF(s.count = 0, NULL, s.sum / s.count));
-- 10.25repeat(element, count) → array
将 element 重复 count 次。
select repeat(4, 5); -- [4, 4, 4, 4, 4] reverse(x) → array
返回一个数组，该数组中元素的顺序与数组 x 相反。
select reverse(array['a','b','c','d','e']); -- [e, d, c, b, a] sequence(start, stop) -> array(bigint)
生成一个从 start 到 stop 的整数序列，如果 start 小于等于 stop，则以 1 为单位递增，否则以 -1 为单位递增。
select sequence(5, 10); -- [5, 6, 7, 8, 9, 10]sequence(start, stop, step) -> array(bigint)
生成一个从 start 到 stop 的整数序列，以 step 为单位递增。
select sequence(1, 10, 2); -- [1, 3, 5, 7, 9]sequence(start, stop) -> array(date)
生成一个从 start 日期到 stop 日期的日期序列，如果 start 日期小于等于 stop 日期，则以 1 天为单位递增，否则以 -1 天为单位递增。
select sequence(date'2022-10-25', date'2022-10-30');
-- [2022-10-25, 2022-10-26, 2022-10-27, 2022-10-28, 2022-10-29, 2022-10-30]sequence(start, stop, step) -> array(date)
生成一个从 start 到 stop 的序列，以 step 为单位递增。step 的类型可以是 INTERVAL DAY TO SECOND 或 INTERVAL YEAR TO MONTH。
select sequence(date'2022-10-25', date'2022-10-30', INTERVAL '2' DAY);
-- [2022-10-25, 2022-10-27, 2022-10-29]sequence(start, stop, step) -> array(timestamp)
生成一个从 start 到 stop的时间戳序列，以 step 为单位递增。step 的类型可以是 INTERVAL DAY TO SECOND 或 INTERVAL YEAR TO MONTH。
select sequence(timestamp'2022-10-25 15:00:00', timestamp'2022-10-25 15:30:00', INTERVAL '10' MINUTE);
-- [2022-10-25 15:00:00, 2022-10-25 15:10:00, 2022-10-25 15:20:00, 2022-10-25 15:30:00]shuffle(x) → array
生成给定数组 x 的随机排列。
select shuffle(array['a','b','c','d','e']); -- [b, a, e, c, d]slice(x, start, length) → array
从索引 start 开始（如果 start 为负数，则从末尾开始）生成数组 x 的子集，其长度为 length。
select slice(array['a','b','c','d','e'], 2, 3); -- [b, c, d]trim_array(x, n) → array
从数组末尾移除指定数量的元素。
SELECT trim_array(ARRAY[1, 2, 3, 4], 1); -- [1, 2, 3]
SELECT trim_array(ARRAY[1, 2, 3, 4], 2); -- [1, 2]transform(array(T), function(T, U)) -> array(U)
返回一个数组，该数组是对 array 的每个元素应用 function 的结果：
SELECT transform(ARRAY[], x -> x + 1); -- []
SELECT transform(ARRAY[5, 6], x -> x + 1); -- [6, 7]
SELECT transform(ARRAY[5, NULL, 6], x -> coalesce(x, 0) + 1); -- [6, 1, 7]
SELECT transform(ARRAY['x', 'abc', 'z'], x -> x || '0'); -- ['x0', 'abc0', 'z0']
SELECT transform(ARRAY[ARRAY[1, NULL, 2], ARRAY[3, NULL]],
a -> filter(a, x -> x IS NOT NULL)); -- [[1, 2], [3]]zip(array1, array2[, ...]) -> array(row)
将给定的数组按元素合并到单个行数组中。第 N 个参数的第 M 个元素将是第 M 个输出元素的第 N 个字段。如果参数的长度不一致，则使用 NULL 填充缺少的值：
SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); -- [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]zip_with(array(T), array(U), function(T, U, R)) -> array(R)
使用 function 将两个给定的数组按元素合并到单个数组中。如果一个数组较短，在应用 function 之前在其末尾添加 NULL 以匹配较长数组的长度：
SELECT zip_with(ARRAY[1, 3, 5], ARRAY['a', 'b', 'c'], (x, y) -> (y, x));
-- [ROW('a', 1), ROW('b', 3), ROW('c', 5)]
SELECT zip_with(ARRAY[1, 2], ARRAY[3, 4], (x, y) -> x + y);
-- [4, 6]
SELECT zip_with(ARRAY['a', 'b', 'c'], ARRAY['d', 'e', 'f'], (x, y) -> concat(x, y));
-- ['ad', 'be', 'cf']
SELECT zip_with(ARRAY['a'], ARRAY['d', null, 'f'], (x, y) -> coalesce(x, y));
-- ['a', null, 'f']Map函数和运算符
下标运算符： []
[]运算符用于从map中检索给定键对应的值：
SELECT name_to_age_map['Bob'] AS bob_age;map函数
cardinality(x) → bigint
返回map x的基数（大小）。
select cardinality(map(array['num1','num2'], array[11,12])); -- 2element_at(map(K, V), key) → V
返回给定的key的值，如果键不包含在map中，则返回NULL。
select element_at(map(array['num1','num2'],array[11,12]),'num1'); -- 11
select element_at(map(array['num1','num2'],array[11,12]),'num3'); -- NULLmap() → map<unknown, unknown>
返回一个空map：
SELECT map(); -- {}map(array(K), array(V)) -> map(K, V)
返回使用给定的键/值数组创建的map：
SELECT map(ARRAY[1,3], ARRAY[2,4]); -- {1 -> 2, 3 -> 4}另请参见map_agg和multimap_agg，以了解如何创建作为聚合的map。
map_from_entries(array(row(K, V))) -> map(K, V)
返回从给定的项数组创建的map：
SELECT map_from_entries(ARRAY[(1, 'x'), (2, 'y')]); -- {1 -> 'x', 2 -> 'y'}multimap_from_entries(array(row(K, V))) -> map(K, array(V))
返回从给定的项数组创建的多重映射。每个键可以关联多个值：
SELECT multimap_from_entries(ARRAY[(1, 'x'), (2, 'y'), (1, 'z')]); -- {1 -> ['x', 'z'], 2 -> ['y']}map_entries(map(K, V)) -> array(row(K, V))
返回一个包含给定的map中所有项的数组：
SELECT map_entries(MAP(ARRAY[1, 2], ARRAY['x', 'y'])); -- [ROW(1, 'x'), ROW(2, 'y')]map_concat(map1(K, V), map2(K, V), ..., mapN(K, V)) -> map(K, V)
返回所有给定的map的并集。如果在多个给定的map中找到某个键，则在生成的map中该键的值来自这些map中的最后一个map。
SELECT map_filter(MAP(ARRAY[], ARRAY[]), (k, v) -> true);
-- {}
SELECT map_filter(MAP(ARRAY[10, 20, 30], ARRAY['a', NULL, 'c']), (k, v) -> v IS NOT NULL);
-- {10 -> a, 30 -> c}
SELECT map_filter(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[20, 3, 15]), (k, v) -> v > 10);
-- {k1 -> 20, k3 -> 15}map_filter(map(K, V), function(K, V, boolean)) -> map(K, V)
通过function针对其返回true的map的项构造一个map：
SELECT map_filter(MAP(ARRAY[], ARRAY[]), (k, v) -> true); -- {}
SELECT map_filter(MAP(ARRAY[10, 20, 30], ARRAY['a', NULL, 'c']), (k, v) -> v IS NOT NULL);
-- {10 -> a, 30 -> c}
SELECT map_filter(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[20, 3, 15]), (k, v) -> v > 10);
-- {k1 -> 20, k3 -> 15}map_keys(x(K, V)) -> array(K)
返回map x中的所有键。
select map_keys(map(array['num1','num2'],array[11,12])); -- [num1, num2]map_values(x(K, V)) -> array(V)
返回map x中的所有值。
select map_keys(map(array['num1','num2'],array[11,12])); -- [11, 12]map_zip_with(map(K, V1), map(K, V2), function(K, V1, V2, V3)) -> map(K, V3)
通过向具有相同键的一对值应用function，将两个给定的map合并为单个map。对于仅出现在一个map中的键，会传入NULL以用作缺失的键的值：
SELECT map_zip_with(MAP(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']),
MAP(ARRAY[1, 2, 3], ARRAY['d', 'e', 'f']),
(k, v1, v2) -> concat(v1, v2));
-- {1 -> ad, 2 -> be, 3 -> cf}
SELECT map_zip_with(MAP(ARRAY['k1', 'k2'], ARRAY[1, 2]),
MAP(ARRAY['k2', 'k3'], ARRAY[4, 9]),
(k, v1, v2) -> (v1, v2));
-- {k1 -> ROW(1, null), k2 -> ROW(2, 4), k3 -> ROW(null, 9)}
SELECT map_zip_with(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 8, 27]),
MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]),
(k, v1, v2) -> k || CAST(v1 / v2 AS VARCHAR));
-- {a -> a1, b -> b4, c -> c9}transform_keys(map(K1, V), function(K1, V, K2)) -> map(K2, V)
返回一个向map的每个项应用function并转换键的map：
SELECT transform_keys(MAP(ARRAY[], ARRAY[]), (k, v) -> k + 1);
-- {}
SELECT transform_keys(MAP(ARRAY [1, 2, 3], ARRAY ['a', 'b', 'c']),
(k, v) -> k + 1);
-- {2 -> a, 3 -> b, 4 -> c}
SELECT transform_keys(MAP(ARRAY ['a', 'b', 'c'], ARRAY [1, 2, 3]),
(k, v) -> v * v);
-- {1 -> 1, 4 -> 2, 9 -> 3}
SELECT transform_keys(MAP(ARRAY ['a', 'b'], ARRAY [1, 2]),
(k, v) -> k || CAST(v as VARCHAR));
-- {a1 -> 1, b2 -> 2}
SELECT transform_keys(MAP(ARRAY [1, 2], ARRAY [1.0, 1.4]),
(k, v) -> MAP(ARRAY[1, 2], ARRAY['one', 'two'])[k]);
-- {one -> 1.0, two -> 1.4}transform_values(map(K, V1), function(K, V1, V2)) -> map(K, V2)
返回一个向map的每个项应用function并转换值的map：
SELECT transform_values(MAP(ARRAY[], ARRAY[]), (k, v) -> v + 1);
-- {}
SELECT transform_values(MAP(ARRAY [1, 2, 3], ARRAY [10, 20, 30]),
(k, v) -> v + k);
-- {1 -> 11, 2 -> 22, 3 -> 33}
SELECT transform_values(MAP(ARRAY [1, 2, 3], ARRAY ['a', 'b', 'c']),
(k, v) -> k * k);
-- {1 -> 1, 2 -> 4, 3 -> 9}
SELECT transform_values(MAP(ARRAY ['a', 'b'], ARRAY [1, 2]),
(k, v) -> k || CAST(v as VARCHAR));
-- {a -> a1, b -> b2}
SELECT transform_values(MAP(ARRAY [1, 2], ARRAY [1.0, 1.4]),
(k, v) -> MAP(ARRAY[1, 2], ARRAY['one', 'two'])[k]
|| '_' || CAST(v AS VARCHAR));
-- {1 -> one_1.0, 2 -> two_1.4}URL函数
提取函数
URL提取函数从HTTP URL（或任何符合 RFC 2396 标准的有效URI）中提取组成部分。支持以下语法：
[protocol:][//host[:port]][path][?query][#fragment]提取的组成部分不包含:或?等URI语法分隔符。
url_extract_fragment(url) → varchar从
url返回片断标识符。
select url_extract_fragment('http://www.example.com:80/stu/index.html?name=xxx&age=25#teacher');
-- teacherurl_extract_host(url) → varchar
从url返回主机。
select url_extract_host('http://www.example.com:80/stu/index.html?name=xxx&age=25#teacher');
-- www.example.comurl_extract_parameter(url, name) → varchar
返回URL中的参数,即?query,query中参数name对应的值。
从url返回第一个名为name的查询字符串参数的值。按照 1866#section-8.2.1 中指定的典型方式来处理参数提取。
select url_extract_parameter('http://www.example.com:80/stu/index.html?name=xxx&age=25#teacher','age');
-- 25url_extract_path(url) → varchar
从url返回路径。
select url_extract_path('http://www.example.com:80/stu/index.html?name=xxx&age=25#teacher');
-- /stu/index.htmlurl_extract_port(url) → bigint
从url返回端口号。
select url_extract_port('http://www.example.com:80/stu/index.html?name=xxx&age=25#teacher');
-- 80url_extract_protocol(url) → varchar
从url返回协议。
SELECT url_extract_protocol('http://localhost:8080/req_path'); -- http
SELECT url_extract_protocol('https://127.0.0.1:8080/req_path'); -- https
SELECT url_extract_protocol('ftp://path/file'); -- ftpurl_extract_query(url) → varchar
从url返回查询字符串。
select url_extract_query('http://www.example.com:80/stu/index.html?name=xxx&age=25#teacher');
-- name=xxx&age=25 编码函数
url_encode(value) → varchar
通过对value进行编码来对其进行转义，以便可以安全地将其包含在URL查询参数名称和值中：
- 不对字母数字字符进行编码。
- 不对字符
.、-、*和_进行编码。 - 将ASCII空格字符编码为
+。 - 将所有其他字符都转换为UTF-8，将字节编码为字符串
%XX，其中XX是UTF-8字节的大写十六进制值。
select url_encode('http://www.example.com:80/stu/index.html?name=xxx&age=25#teacher');
-- http%3A%2F%2Fwww.example.com%3A80%2Fstu%2Findex.html%3Fname%3Dxxx%26age%3D25%23teacherurl_decode(value) → varchar
对URL编码value进行反转义。该函数是url_encode的反函数。
select url_decode('http%3A%2F%2Fwww.example.com%3A80%2Fstu%2Findex.html%3Fname%3Dxxx%26age%3D25%23teacher');
-- http://www.example.com:80/stu/index.html?name=xxx&age=25#teacher UUID函数
uuid() → uuid
返回伪随机生成的 UUID 类型（类型4）。
select uuid(); -- 88db06ad-31d6-4bb1-9794-85ea69533d63颜色函数
bar(x, width) → varchar
使用默认的low_color 红色和 high_color 绿色呈现 ANSI 条形图中的单个条形。例如，将 x 值 25% 和 width 值 40 传递给该函数，则呈现一个 10 字符红色条形图，后跟 30 个空格，从而创建一个 40 字符条形图。
bar(x, width, low_color, high_color) → varchar
呈现 ANSI 条形图中具有指定 width 的单个行。参数 x 是处于 [0,1] 范围之内的 double 值。处于范围 [0,1] 之外的 x 值将被截断为值 0 或 1。low_color 和 high_color 捕获颜色以用于水平条形图的两端。例如，如果 x 为 0.5，width 为 80，low_color 为 0xFF0000，high_color 为 0x00FF00，则该函数返回一个 40 字符条形，其颜色从红色 (0xFF0000) 变为黄色 (0xFFFF00)，使用空格对 80 字符条形的其余部分进行填充。
color(string) → color
从格式为“#000”的 4 字符字符串捕获解码的 RGB 值，返回相应的颜色。输入字符串应该为 varchar，其中包含 CSS 样式的短 RGB 字符串，或者为 black、red、green、yellow、blue、magenta、cyan 和 white 之一。
color(x, low, high, low_color, high_color) → color
返回一个介于 low_color 和 high_color 之间的颜色，使用 double 参数 x、low 和 high 计算得出一个小数，然后将该小数传给下面显示的 color(fraction, low_color, high_color) 函数。如果 x 处于 low 和 high 定义的范围之外，则对其值进行截断，以使其处于该范围之内。
color(x, low_color, high_color) → color
根据介于 0 和 1.0 之间的 double 参数 x 返回一个介于 low_color 和 high_color 之间的颜色。参数 x 是处于 [0,1] 范围之内的 double 值。处于范围 [0,1] 之外的 x 值将被截断为值 0 或 1。
render(x, color) → varchar
使用特定的颜色（使用 ANSI 颜色代码）呈现值 x。x 可以为 double、bigint 或 varchar类型。
select render(true), render(false);render(b) → varchar
接受 boolean值 b 并使用 ANSI 颜色代码将绿色呈现为true或将红色呈现为false。
rgb(red, green, blue) → color
返回一个颜色值，捕获三个作为 int参数（范围为 0 至 255）提供的分量颜色值的 RGB 值：red、green、blue。
会话信息
current_user → varchar
返回当前运行查询的用户。
select current_user; -- hubblecurrent_groups() → varchar
返回运行查询的当前用户的组列表。
select current_groups(); -- []current_catalog → varchar
返回表示当前目录名称的字符串。
select current_catalog; -- hubblecurrent_schema → varchar
返回表示当前非限定模式名称的字符串。
select current_schema; -- examp注意
这是SQL标准的一部分，不使用圆括号。