内置函数
聚合函数
| 函数 | 描述 |
|---|---|
| any(expr) | 如果 `expr` 中至少有一个值为 true，则返回 true。 |
| any_value(expr[, isIgnoreNull]) | 返回一组行中 `expr` 的某个值。如果 `isIgnoreNull` 为 true，则仅返回非空值。 |
| approx_count_distinct(expr[, relativeSD]) | 返回 HyperLogLog++ 估计的基数。`relativeSD` 定义允许的最大相对标准偏差。 |
| approx_percentile(col, percentage [, accuracy]) | 返回数值或 ANSI 区间列 `col` 的近似 `percentile`，它是排序后的 `col` 值（从小到大排序）中的最小值，使得不超过 `percentage` 的 `col` 值小于该值或等于该值。percentage 的值必须介于 0.0 和 1.0 之间。`accuracy` 参数（默认值：10000）是一个正数，用于控制近似精度，但会增加内存消耗。`accuracy` 值越高，精度越高，`1.0/accuracy` 是近似的相对误差。当 `percentage` 是一个数组时，percentage 数组的每个值都必须介于 0.0 和 1.0 之间。在这种情况下，返回列 `col` 在给定 percentage 数组处的近似百分位数数组。 |
| array_agg(expr) | 收集并返回一个非唯一元素列表。 |
| avg(expr) | 返回从一组值计算的平均值。 |
| bit_and(expr) | 返回所有非空输入值的按位与，如果没有，则返回 null。 |
| bit_or(expr) | 返回所有非空输入值的按位或，如果没有，则返回 null。 |
| bit_xor(expr) | 返回所有非空输入值的按位异或，如果没有，则返回 null。 |
| bitmap_construct_agg(child) | 返回一个位图，其中包含从子表达式的所有值设置的位的位置。子表达式很可能是 bitmap_bit_position()。 |
| bitmap_or_agg(child) | 返回一个位图，该位图是子表达式中所有位图的按位或。输入应该是从 bitmap_construct_agg() 创建的位图。 |
| bool_and(expr) | 如果 `expr` 的所有值都为 true，则返回 true。 |
| bool_or(expr) | 如果 `expr` 中至少有一个值为 true，则返回 true。 |
| collect_list(expr) | 收集并返回一个非唯一元素列表。 |
| collect_set(expr) | 收集并返回一组唯一元素。 |
| corr(expr1, expr2) | 返回一组数字对之间的皮尔逊相关系数。 |
| count(*) | 返回检索到的总行数，包括包含 null 的行。 |
| count(expr[, expr...]) | 返回提供的表达式均为非空的行数。 |
| count(DISTINCT expr[, expr...]) | 返回提供的表达式唯一且非空的行数。 |
| count_if(expr) | 返回表达式的 `TRUE` 值的数量。 |
| count_min_sketch(col, eps, confidence, seed) | 返回具有给定 esp、置信度和种子的列的 count-min sketch。结果是一个字节数组，可以在使用之前反序列化为 `CountMinSketch`。Count-min sketch 是一种概率数据结构，用于使用亚线性空间进行基数估计。 |
| covar_pop(expr1, expr2) | 返回一组数字对的总体协方差。 |
| covar_samp(expr1, expr2) | 返回一组数字对的样本协方差。 |
| every(expr) | 如果 `expr` 的所有值都为 true，则返回 true。 |
| first(expr[, isIgnoreNull]) | 返回一组行中 `expr` 的第一个值。如果 `isIgnoreNull` 为 true，则仅返回非空值。 |
| first_value(expr[, isIgnoreNull]) | 返回一组行中 `expr` 的第一个值。如果 `isIgnoreNull` 为 true，则仅返回非空值。 |
| grouping(col) | 指示 GROUP BY 中指定的列是否已聚合，在结果集中返回 1 表示已聚合，返回 0 表示未聚合。 |
| grouping_id([col1[, col2 ..]]) | 返回分组级别，等于 `(grouping(c1) << (n-1)) + (grouping(c2) << (n-2)) + ... + grouping(cn)` |
| histogram_numeric(expr, nb) | 使用 nb 个 bin 对数值 'expr' 计算直方图。返回值是一个 (x,y) 对数组，表示直方图 bin 的中心。随着 'nb' 值的增加，直方图近似值变得更精细，但可能会在异常值周围产生伪影。在实践中，20-40 个直方图 bin 似乎效果很好，对于偏斜或较小的数据集，需要更多 bin。请注意，此函数创建的直方图的 bin 宽度不均匀。它不保证直方图的均方误差，但在实践中与 R/S-Plus 统计计算包生成的直方图相当。注意：返回值中 'x' 字段的输出类型是从聚合函数中使用的输入值传播的。 |
| hll_sketch_agg(expr, lgConfigK) | 返回 HllSketch 的可更新二进制表示形式。`lgConfigK`（可选）K 的以 2 为底的对数，其中 K 是 HllSketch 的桶或槽的数量。 |
| hll_union_agg(expr, allowDifferentLgConfigK) | 返回唯一值的估计数量。`allowDifferentLgConfigK`（可选）允许合并具有不同 lgConfigK 值的草图（默认为 false）。 |
| kurtosis(expr) | 返回从一组值计算的峰度值。 |
| last(expr[, isIgnoreNull]) | 返回一组行中 `expr` 的最后一个值。如果 `isIgnoreNull` 为 true，则仅返回非空值。 |
| last_value(expr[, isIgnoreNull]) | 返回一组行中 `expr` 的最后一个值。如果 `isIgnoreNull` 为 true，则仅返回非空值。 |
| max(expr) | 返回 `expr` 的最大值。 |
| max_by(x, y) | 返回与 `y` 的最大值关联的 `x` 的值。 |
| mean(expr) | 返回从一组值计算的平均值。 |
| median(col) | 返回数值或 ANSI 区间列 `col` 的中位数。 |
| min(expr) | 返回 `expr` 的最小值。 |
| min_by(x, y) | 返回与 `y` 的最小值关联的 `x` 的值。 |
| mode(col) | 返回 `col` 中最常见的值。忽略 NULL 值。如果所有值都为 NULL，或者有 0 行，则返回 NULL。 |
| percentile(col, percentage [, frequency]) | 返回数值或 ANSI 区间列 `col` 在给定百分比处的精确百分位数值。百分比的值必须介于 0.0 和 1.0 之间。频率的值应为正整数。 |
| percentile(col, array(percentage1 [, percentage2]...) [, frequency]) | 返回数值列 `col` 在给定百分比处的精确百分位数值数组。百分比数组的每个值都必须介于 0.0 和 1.0 之间。频率的值应为正整数。 |
| percentile_approx(col, percentage [, accuracy]) | 返回数值或 ANSI 区间列 `col` 的近似 `percentile`，它是排序后的 `col` 值（从小到大排序）中的最小值，使得不超过 `percentage` 的 `col` 值小于该值或等于该值。percentage 的值必须介于 0.0 和 1.0 之间。`accuracy` 参数（默认值：10000）是一个正数，用于控制近似精度，但会增加内存消耗。`accuracy` 值越高，精度越高，`1.0/accuracy` 是近似的相对误差。当 `percentage` 是一个数组时，percentage 数组的每个值都必须介于 0.0 和 1.0 之间。在这种情况下，返回列 `col` 在给定 percentage 数组处的近似百分位数数组。 |
| regr_avgx(y, x) | 返回一组中非空对的自变量的平均值，其中 `y` 是因变量，`x` 是自变量。 |
| regr_avgy(y, x) | 返回一组中非空对的因变量的平均值，其中 `y` 是因变量，`x` 是自变量。 |
| regr_count(y, x) | 返回一组中非空数字对的数量，其中 `y` 是因变量，`x` 是自变量。 |
| regr_intercept(y, x) | 返回一组中非空对的单变量线性回归线的截距，其中 `y` 是因变量，`x` 是自变量。 |
| regr_r2(y, x) | 返回一组中非空对的决定系数，其中 `y` 是因变量，`x` 是自变量。 |
| regr_slope(y, x) | 返回一组中非空对的线性回归线的斜率，其中 `y` 是因变量，`x` 是自变量。 |
| regr_sxx(y, x) | 返回一组中非空对的 REGR_COUNT(y, x) * VAR_POP(x)，其中 `y` 是因变量，`x` 是自变量。 |
| regr_sxy(y, x) | 返回一组中非空对的 REGR_COUNT(y, x) * COVAR_POP(y, x)，其中 `y` 是因变量，`x` 是自变量。 |
| regr_syy(y, x) | 返回一组中非空对的 REGR_COUNT(y, x) * VAR_POP(y)，其中 `y` 是因变量，`x` 是自变量。 |
| skewness(expr) | 返回从一组值计算的偏度值。 |
| some(expr) | 如果 `expr` 中至少有一个值为 true，则返回 true。 |
| std(expr) | 返回从一组值计算的样本标准偏差。 |
| stddev(expr) | 返回从一组值计算的样本标准偏差。 |
| stddev_pop(expr) | 返回从一组值计算的总体标准偏差。 |
| stddev_samp(expr) | 返回从一组值计算的样本标准偏差。 |
| sum(expr) | 返回从一组值计算的总和。 |
| try_avg(expr) | 返回从一组值计算的平均值，如果溢出，则结果为 null。 |
| try_sum(expr) | 返回从一组值计算的总和，如果溢出，则结果为 null。 |
| var_pop(expr) | 返回从一组值计算的总体方差。 |
| var_samp(expr) | 返回从一组值计算的样本方差。 |
| variance(expr) | 返回从一组值计算的样本方差。 |
示例
-- any
SELECT any(col) FROM VALUES (true), (false), (false) AS tab(col);
+--------+
|any(col)|
+--------+
| true|
+--------+
SELECT any(col) FROM VALUES (NULL), (true), (false) AS tab(col);
+--------+
|any(col)|
+--------+
| true|
+--------+
SELECT any(col) FROM VALUES (false), (false), (NULL) AS tab(col);
+--------+
|any(col)|
+--------+
| false|
+--------+
-- any_value
SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);
+--------------+
|any_value(col)|
+--------------+
| 10|
+--------------+
SELECT any_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
+--------------+
|any_value(col)|
+--------------+
| NULL|
+--------------+
SELECT any_value(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
+--------------+
|any_value(col)|
+--------------+
| 5|
+--------------+
-- approx_count_distinct
SELECT approx_count_distinct(col1) FROM VALUES (1), (1), (2), (2), (3) tab(col1);
+---------------------------+
|approx_count_distinct(col1)|
+---------------------------+
| 3|
+---------------------------+
-- approx_percentile
SELECT approx_percentile(col, array(0.5, 0.4, 0.1), 100) FROM VALUES (0), (1), (2), (10) AS tab(col);
+-------------------------------------------------+
|approx_percentile(col, array(0.5, 0.4, 0.1), 100)|
+-------------------------------------------------+
| [1, 1, 0]|
+-------------------------------------------------+
SELECT approx_percentile(col, 0.5, 100) FROM VALUES (0), (6), (7), (9), (10) AS tab(col);
+--------------------------------+
|approx_percentile(col, 0.5, 100)|
+--------------------------------+
| 7|
+--------------------------------+
SELECT approx_percentile(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+--------------------------------+
|approx_percentile(col, 0.5, 100)|
+--------------------------------+
| INTERVAL '1' MONTH|
+--------------------------------+
SELECT approx_percentile(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
+--------------------------------------------+
|approx_percentile(col, array(0.5, 0.7), 100)|
+--------------------------------------------+
| [INTERVAL '01' SE...|
+--------------------------------------------+
-- array_agg
SELECT array_agg(col) FROM VALUES (1), (2), (1) AS tab(col);
+-----------------+
|collect_list(col)|
+-----------------+
| [1, 2, 1]|
+-----------------+
-- avg
SELECT avg(col) FROM VALUES (1), (2), (3) AS tab(col);
+--------+
|avg(col)|
+--------+
| 2.0|
+--------+
SELECT avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
+--------+
|avg(col)|
+--------+
| 1.5|
+--------+
-- bit_and
SELECT bit_and(col) FROM VALUES (3), (5) AS tab(col);
+------------+
|bit_and(col)|
+------------+
| 1|
+------------+
-- bit_or
SELECT bit_or(col) FROM VALUES (3), (5) AS tab(col);
+-----------+
|bit_or(col)|
+-----------+
| 7|
+-----------+
-- bit_xor
SELECT bit_xor(col) FROM VALUES (3), (5) AS tab(col);
+------------+
|bit_xor(col)|
+------------+
| 6|
+------------+
-- bitmap_construct_agg
SELECT substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6) FROM VALUES (1), (2), (3) AS tab(col);
+--------------------------------------------------------------------+
|substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6)|
+--------------------------------------------------------------------+
| 070000|
+--------------------------------------------------------------------+
SELECT substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6) FROM VALUES (1), (1), (1) AS tab(col);
+--------------------------------------------------------------------+
|substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6)|
+--------------------------------------------------------------------+
| 010000|
+--------------------------------------------------------------------+
-- bitmap_or_agg
SELECT substring(hex(bitmap_or_agg(col)), 0, 6) FROM VALUES (X '10'), (X '20'), (X '40') AS tab(col);
+----------------------------------------+
|substring(hex(bitmap_or_agg(col)), 0, 6)|
+----------------------------------------+
| 700000|
+----------------------------------------+
SELECT substring(hex(bitmap_or_agg(col)), 0, 6) FROM VALUES (X '10'), (X '10'), (X '10') AS tab(col);
+----------------------------------------+
|substring(hex(bitmap_or_agg(col)), 0, 6)|
+----------------------------------------+
| 100000|
+----------------------------------------+
-- bool_and
SELECT bool_and(col) FROM VALUES (true), (true), (true) AS tab(col);
+-------------+
|bool_and(col)|
+-------------+
| true|
+-------------+
SELECT bool_and(col) FROM VALUES (NULL), (true), (true) AS tab(col);
+-------------+
|bool_and(col)|
+-------------+
| true|
+-------------+
SELECT bool_and(col) FROM VALUES (true), (false), (true) AS tab(col);
+-------------+
|bool_and(col)|
+-------------+
| false|
+-------------+
-- bool_or
SELECT bool_or(col) FROM VALUES (true), (false), (false) AS tab(col);
+------------+
|bool_or(col)|
+------------+
| true|
+------------+
SELECT bool_or(col) FROM VALUES (NULL), (true), (false) AS tab(col);
+------------+
|bool_or(col)|
+------------+
| true|
+------------+
SELECT bool_or(col) FROM VALUES (false), (false), (NULL) AS tab(col);
+------------+
|bool_or(col)|
+------------+
| false|
+------------+
-- collect_list
SELECT collect_list(col) FROM VALUES (1), (2), (1) AS tab(col);
+-----------------+
|collect_list(col)|
+-----------------+
| [1, 2, 1]|
+-----------------+
-- collect_set
SELECT collect_set(col) FROM VALUES (1), (2), (1) AS tab(col);
+----------------+
|collect_set(col)|
+----------------+
| [1, 2]|
+----------------+
-- corr
SELECT corr(c1, c2) FROM VALUES (3, 2), (3, 3), (6, 4) as tab(c1, c2);
+------------------+
| corr(c1, c2)|
+------------------+
|0.8660254037844387|
+------------------+
-- count
SELECT count(*) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
+--------+
|count(1)|
+--------+
| 4|
+--------+
SELECT count(col) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
+----------+
|count(col)|
+----------+
| 3|
+----------+
SELECT count(DISTINCT col) FROM VALUES (NULL), (5), (5), (10) AS tab(col);
+-------------------+
|count(DISTINCT col)|
+-------------------+
| 2|
+-------------------+
-- count_if
SELECT count_if(col % 2 = 0) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
+-------------------------+
|count_if(((col % 2) = 0))|
+-------------------------+
| 2|
+-------------------------+
SELECT count_if(col IS NULL) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
+-----------------------+
|count_if((col IS NULL))|
+-----------------------+
| 1|
+-----------------------+
-- count_min_sketch
SELECT hex(count_min_sketch(col, 0.5d, 0.5d, 1)) FROM VALUES (1), (2), (1) AS tab(col);
+---------------------------------------+
|hex(count_min_sketch(col, 0.5, 0.5, 1))|
+---------------------------------------+
| 00000001000000000...|
+---------------------------------------+
-- covar_pop
SELECT covar_pop(c1, c2) FROM VALUES (1,1), (2,2), (3,3) AS tab(c1, c2);
+------------------+
| covar_pop(c1, c2)|
+------------------+
|0.6666666666666666|
+------------------+
-- covar_samp
SELECT covar_samp(c1, c2) FROM VALUES (1,1), (2,2), (3,3) AS tab(c1, c2);
+------------------+
|covar_samp(c1, c2)|
+------------------+
| 1.0|
+------------------+
-- every
SELECT every(col) FROM VALUES (true), (true), (true) AS tab(col);
+----------+
|every(col)|
+----------+
| true|
+----------+
SELECT every(col) FROM VALUES (NULL), (true), (true) AS tab(col);
+----------+
|every(col)|
+----------+
| true|
+----------+
SELECT every(col) FROM VALUES (true), (false), (true) AS tab(col);
+----------+
|every(col)|
+----------+
| false|
+----------+
-- first
SELECT first(col) FROM VALUES (10), (5), (20) AS tab(col);
+----------+
|first(col)|
+----------+
| 10|
+----------+
SELECT first(col) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------+
|first(col)|
+----------+
| NULL|
+----------+
SELECT first(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------+
|first(col)|
+----------+
| 5|
+----------+
-- first_value
SELECT first_value(col) FROM VALUES (10), (5), (20) AS tab(col);
+----------------+
|first_value(col)|
+----------------+
| 10|
+----------------+
SELECT first_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------------+
|first_value(col)|
+----------------+
| NULL|
+----------------+
SELECT first_value(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------------+
|first_value(col)|
+----------------+
| 5|
+----------------+
-- grouping
SELECT name, grouping(name), sum(age) FROM VALUES (2, 'Alice'), (5, 'Bob') people(age, name) GROUP BY cube(name);
+-----+--------------+--------+
| name|grouping(name)|sum(age)|
+-----+--------------+--------+
| NULL| 1| 7|
|Alice| 0| 2|
| Bob| 0| 5|
+-----+--------------+--------+
-- grouping_id
SELECT name, grouping_id(), sum(age), avg(height) FROM VALUES (2, 'Alice', 165), (5, 'Bob', 180) people(age, name, height) GROUP BY cube(name, height);
+-----+-------------+--------+-----------+
| name|grouping_id()|sum(age)|avg(height)|
+-----+-------------+--------+-----------+
| NULL| 2| 2| 165.0|
|Alice| 0| 2| 165.0|
|Alice| 1| 2| 165.0|
| NULL| 3| 7| 172.5|
| Bob| 1| 5| 180.0|
| Bob| 0| 5| 180.0|
| NULL| 2| 5| 180.0|
+-----+-------------+--------+-----------+
-- histogram_numeric
SELECT histogram_numeric(col, 5) FROM VALUES (0), (1), (2), (10) AS tab(col);
+-------------------------+
|histogram_numeric(col, 5)|
+-------------------------+
| [{0, 1.0}, {1, 1....|
+-------------------------+
-- hll_sketch_agg
SELECT hll_sketch_estimate(hll_sketch_agg(col, 12)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
+--------------------------------------------+
|hll_sketch_estimate(hll_sketch_agg(col, 12))|
+--------------------------------------------+
| 3|
+--------------------------------------------+
-- hll_union_agg
SELECT hll_sketch_estimate(hll_union_agg(sketch, true)) FROM (SELECT hll_sketch_agg(col) as sketch FROM VALUES (1) tab(col) UNION ALL SELECT hll_sketch_agg(col, 20) as sketch FROM VALUES (1) tab(col));
+------------------------------------------------+
|hll_sketch_estimate(hll_union_agg(sketch, true))|
+------------------------------------------------+
| 1|
+------------------------------------------------+
-- kurtosis
SELECT kurtosis(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
+-------------------+
| kurtosis(col)|
+-------------------+
|-0.7014368047529618|
+-------------------+
SELECT kurtosis(col) FROM VALUES (1), (10), (100), (10), (1) as tab(col);
+-------------------+
| kurtosis(col)|
+-------------------+
|0.19432323191698986|
+-------------------+
-- last
SELECT last(col) FROM VALUES (10), (5), (20) AS tab(col);
+---------+
|last(col)|
+---------+
| 20|
+---------+
SELECT last(col) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------+
|last(col)|
+---------+
| NULL|
+---------+
SELECT last(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------+
|last(col)|
+---------+
| 5|
+---------+
-- last_value
SELECT last_value(col) FROM VALUES (10), (5), (20) AS tab(col);
+---------------+
|last_value(col)|
+---------------+
| 20|
+---------------+
SELECT last_value(col) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------------+
|last_value(col)|
+---------------+
| NULL|
+---------------+
SELECT last_value(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------------+
|last_value(col)|
+---------------+
| 5|
+---------------+
-- max
SELECT max(col) FROM VALUES (10), (50), (20) AS tab(col);
+--------+
|max(col)|
+--------+
| 50|
+--------+
-- max_by
SELECT max_by(x, y) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
+------------+
|max_by(x, y)|
+------------+
| b|
+------------+
-- mean
SELECT mean(col) FROM VALUES (1), (2), (3) AS tab(col);
+---------+
|mean(col)|
+---------+
| 2.0|
+---------+
SELECT mean(col) FROM VALUES (1), (2), (NULL) AS tab(col);
+---------+
|mean(col)|
+---------+
| 1.5|
+---------+
-- median
SELECT median(col) FROM VALUES (0), (10) AS tab(col);
+-----------+
|median(col)|
+-----------+
| 5.0|
+-----------+
SELECT median(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+--------------------+
| median(col)|
+--------------------+
|INTERVAL '0-5' YE...|
+--------------------+
-- min
SELECT min(col) FROM VALUES (10), (-1), (20) AS tab(col);
+--------+
|min(col)|
+--------+
| -1|
+--------+
-- min_by
SELECT min_by(x, y) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
+------------+
|min_by(x, y)|
+------------+
| a|
+------------+
-- mode
SELECT mode(col) FROM VALUES (0), (10), (10) AS tab(col);
+---------+
|mode(col)|
+---------+
| 10|
+---------+
SELECT mode(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+-------------------+
| mode(col)|
+-------------------+
|INTERVAL '10' MONTH|
+-------------------+
SELECT mode(col) FROM VALUES (0), (10), (10), (null), (null), (null) AS tab(col);
+---------+
|mode(col)|
+---------+
| 10|
+---------+
-- percentile
SELECT percentile(col, 0.3) FROM VALUES (0), (10) AS tab(col);
+-----------------------+
|percentile(col, 0.3, 1)|
+-----------------------+
| 3.0|
+-----------------------+
SELECT percentile(col, array(0.25, 0.75)) FROM VALUES (0), (10) AS tab(col);
+-------------------------------------+
|percentile(col, array(0.25, 0.75), 1)|
+-------------------------------------+
| [2.5, 7.5]|
+-------------------------------------+
SELECT percentile(col, 0.5) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+-----------------------+
|percentile(col, 0.5, 1)|
+-----------------------+
| INTERVAL '0-5' YE...|
+-----------------------+
SELECT percentile(col, array(0.2, 0.5)) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '10' SECOND) AS tab(col);
+-----------------------------------+
|percentile(col, array(0.2, 0.5), 1)|
+-----------------------------------+
| [INTERVAL '0 00:0...|
+-----------------------------------+
-- percentile_approx
SELECT percentile_approx(col, array(0.5, 0.4, 0.1), 100) FROM VALUES (0), (1), (2), (10) AS tab(col);
+-------------------------------------------------+
|percentile_approx(col, array(0.5, 0.4, 0.1), 100)|
+-------------------------------------------------+
| [1, 1, 0]|
+-------------------------------------------------+
SELECT percentile_approx(col, 0.5, 100) FROM VALUES (0), (6), (7), (9), (10) AS tab(col);
+--------------------------------+
|percentile_approx(col, 0.5, 100)|
+--------------------------------+
| 7|
+--------------------------------+
SELECT percentile_approx(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+--------------------------------+
|percentile_approx(col, 0.5, 100)|
+--------------------------------+
| INTERVAL '1' MONTH|
+--------------------------------+
SELECT percentile_approx(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
+--------------------------------------------+
|percentile_approx(col, array(0.5, 0.7), 100)|
+--------------------------------------------+
| [INTERVAL '01' SE...|
+--------------------------------------------+
-- regr_avgx
SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| 2.75|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (1, null) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (null, 1) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| 3.0|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| 3.0|
+---------------+
-- regr_avgy
SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| 1.75|
+---------------+
SELECT regr_avgy(y, x) FROM VALUES (1, null) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgy(y, x) FROM VALUES (null, 1) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_avgy(y, x)|
+------------------+
|1.6666666666666667|
+------------------+
SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| 1.5|
+---------------+
-- regr_count
SELECT regr_count(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 4|
+----------------+
SELECT regr_count(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 3|
+----------------+
SELECT regr_count(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 2|
+----------------+
-- regr_intercept
SELECT regr_intercept(y, x) FROM VALUES (1,1), (2,2), (3,3) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| 0.0|
+--------------------+
SELECT regr_intercept(y, x) FROM VALUES (1, null) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| NULL|
+--------------------+
SELECT regr_intercept(y, x) FROM VALUES (null, 1) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| NULL|
+--------------------+
-- regr_r2
SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_r2(y, x)|
+------------------+
|0.2727272727272726|
+------------------+
SELECT regr_r2(y, x) FROM VALUES (1, null) AS tab(y, x);
+-------------+
|regr_r2(y, x)|
+-------------+
| NULL|
+-------------+
SELECT regr_r2(y, x) FROM VALUES (null, 1) AS tab(y, x);
+-------------+
|regr_r2(y, x)|
+-------------+
| NULL|
+-------------+
SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_r2(y, x)|
+------------------+
|0.7500000000000001|
+------------------+
SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+-------------+
|regr_r2(y, x)|
+-------------+
| 1.0|
+-------------+
-- regr_slope
SELECT regr_slope(y, x) FROM VALUES (1,1), (2,2), (3,3) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| 1.0|
+----------------+
SELECT regr_slope(y, x) FROM VALUES (1, null) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| NULL|
+----------------+
SELECT regr_slope(y, x) FROM VALUES (null, 1) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| NULL|
+----------------+
-- regr_sxx
SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_sxx(y, x)|
+------------------+
|2.7499999999999996|
+------------------+
SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxx(y, x)|
+--------------+
| 2.0|
+--------------+
SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxx(y, x)|
+--------------+
| 2.0|
+--------------+
-- regr_sxy
SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_sxy(y, x)|
+------------------+
|0.7499999999999998|
+------------------+
SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxy(y, x)|
+--------------+
| 1.0|
+--------------+
SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxy(y, x)|
+--------------+
| 1.0|
+--------------+
-- regr_syy
SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_syy(y, x)|
+------------------+
|0.7499999999999999|
+------------------+
SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_syy(y, x)|
+------------------+
|0.6666666666666666|
+------------------+
SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_syy(y, x)|
+--------------+
| 0.5|
+--------------+
-- skewness
SELECT skewness(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
+------------------+
| skewness(col)|
+------------------+
|1.1135657469022013|
+------------------+
SELECT skewness(col) FROM VALUES (-1000), (-100), (10), (20) AS tab(col);
+-------------------+
| skewness(col)|
+-------------------+
|-1.1135657469022011|
+-------------------+
-- some
SELECT some(col) FROM VALUES (true), (false), (false) AS tab(col);
+---------+
|some(col)|
+---------+
| true|
+---------+
SELECT some(col) FROM VALUES (NULL), (true), (false) AS tab(col);
+---------+
|some(col)|
+---------+
| true|
+---------+
SELECT some(col) FROM VALUES (false), (false), (NULL) AS tab(col);
+---------+
|some(col)|
+---------+
| false|
+---------+
-- std
SELECT std(col) FROM VALUES (1), (2), (3) AS tab(col);
+--------+
|std(col)|
+--------+
| 1.0|
+--------+
-- stddev
SELECT stddev(col) FROM VALUES (1), (2), (3) AS tab(col);
+-----------+
|stddev(col)|
+-----------+
| 1.0|
+-----------+
-- stddev_pop
SELECT stddev_pop(col) FROM VALUES (1), (2), (3) AS tab(col);
+-----------------+
| stddev_pop(col)|
+-----------------+
|0.816496580927726|
+-----------------+
-- stddev_samp
SELECT stddev_samp(col) FROM VALUES (1), (2), (3) AS tab(col);
+----------------+
|stddev_samp(col)|
+----------------+
| 1.0|
+----------------+
-- sum
SELECT sum(col) FROM VALUES (5), (10), (15) AS tab(col);
+--------+
|sum(col)|
+--------+
| 30|
+--------+
SELECT sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
+--------+
|sum(col)|
+--------+
| 25|
+--------+
SELECT sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
+--------+
|sum(col)|
+--------+
| NULL|
+--------+
-- try_avg
SELECT try_avg(col) FROM VALUES (1), (2), (3) AS tab(col);
+------------+
|try_avg(col)|
+------------+
| 2.0|
+------------+
SELECT try_avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
+------------+
|try_avg(col)|
+------------+
| 1.5|
+------------+
SELECT try_avg(col) FROM VALUES (interval '2147483647 months'), (interval '1 months') AS tab(col);
+------------+
|try_avg(col)|
+------------+
| NULL|
+------------+
-- try_sum
SELECT try_sum(col) FROM VALUES (5), (10), (15) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| 30|
+------------+
SELECT try_sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| 25|
+------------+
SELECT try_sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| NULL|
+------------+
SELECT try_sum(col) FROM VALUES (9223372036854775807L), (1L) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| NULL|
+------------+
-- var_pop
SELECT var_pop(col) FROM VALUES (1), (2), (3) AS tab(col);
+------------------+
| var_pop(col)|
+------------------+
|0.6666666666666666|
+------------------+
-- var_samp
SELECT var_samp(col) FROM VALUES (1), (2), (3) AS tab(col);
+-------------+
|var_samp(col)|
+-------------+
| 1.0|
+-------------+
-- variance
SELECT variance(col) FROM VALUES (1), (2), (3) AS tab(col);
+-------------+
|variance(col)|
+-------------+
| 1.0|
+-------------+
窗口函数
| 函数 | 描述 |
|---|---|
| cume_dist() | 计算一个值相对于分区中所有值的排名。 |
| dense_rank() | 计算一个值在一组值中的排名。结果是 1 加上先前分配的排名值。与函数 rank 不同，dense_rank 不会在排名序列中产生间隔。 |
| lag(input[, offset[, default]]) | 返回窗口中当前行之前的第 `offset` 行的 `input` 值。`offset` 的默认值为 1，`default` 的默认值为 null。如果第 `offset` 行的 `input` 值为 null，则返回 null。如果没有这样的偏移行（例如，当偏移量为 1 时，窗口的第一行没有任何前一行），则返回 `default`。 |
| lag(input[, offset[, default]]) | 返回窗口中当前行之后的第 `offset` 行的 `input` 值。`offset` 的默认值为 1，`default` 的默认值为 null。如果第 `offset` 行的 `input` 值为 null，则返回 null。如果没有这样的偏移行（例如，当偏移量为 1 时，窗口的最后一行没有任何后续行），则返回 `default`。 |
| nth_value(input[, offset]) | 返回窗口框架中从开头算起第 `offset` 行的 `input` 值。偏移量从 1 开始。如果 ignoreNulls=true，则在查找第 `offset` 行时将跳过空值。否则，每一行都计入 `offset`。如果没有这样的第 `offset` 行（例如，当偏移量为 10 时，窗口框架的大小小于 10），则返回 null。 |
| ntile(n) | 将每个窗口分区的行划分为 `n` 个桶，范围从 1 到最多 `n`。 |
| percent_rank() | 计算一组值中某个值的百分比排名。 |
| rank() | 计算一组值中某个值的排名。结果是 1 加上分区排序中当前行之前或等于当前行的行数。这些值将在序列中产生间隙。 |
| row_number() | 根据窗口分区内的行排序，为每一行分配一个唯一的顺序号，从 1 开始。 |
示例
-- cume_dist
SELECT a, b, cume_dist() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+--------------------------------------------------------------------------------------------------------------+
| a| b|cume_dist() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+--------------------------------------------------------------------------------------------------------------+
| A1| 1| 0.6666666666666666|
| A1| 1| 0.6666666666666666|
| A1| 2| 1.0|
| A2| 3| 1.0|
+---+---+--------------------------------------------------------------------------------------------------------------+
-- dense_rank
SELECT a, b, dense_rank(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+--------------------------------------------------------------------------------------------------------------+
| a| b|DENSE_RANK() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+--------------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 1|
| A1| 2| 2|
| A2| 3| 1|
+---+---+--------------------------------------------------------------------------------------------------------------+
-- lag
SELECT a, b, lag(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+-----------------------------------------------------------------------------------------------------------+
| a| b|lag(b, 1, NULL) OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN -1 FOLLOWING AND -1 FOLLOWING)|
+---+---+-----------------------------------------------------------------------------------------------------------+
| A1| 1| NULL|
| A1| 1| 1|
| A1| 2| 1|
| A2| 3| NULL|
+---+---+-----------------------------------------------------------------------------------------------------------+
-- lead
SELECT a, b, lead(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+----------------------------------------------------------------------------------------------------------+
| a| b|lead(b, 1, NULL) OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)|
+---+---+----------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 2|
| A1| 2| NULL|
| A2| 3| NULL|
+---+---+----------------------------------------------------------------------------------------------------------+
-- nth_value
SELECT a, b, nth_value(b, 2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+------------------------------------------------------------------------------------------------------------------+
| a| b|nth_value(b, 2) OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+------------------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 1|
| A1| 2| 1|
| A2| 3| NULL|
+---+---+------------------------------------------------------------------------------------------------------------------+
-- ntile
SELECT a, b, ntile(2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+----------------------------------------------------------------------------------------------------------+
| a| b|ntile(2) OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+----------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 1|
| A1| 2| 2|
| A2| 3| 1|
+---+---+----------------------------------------------------------------------------------------------------------+
-- percent_rank
SELECT a, b, percent_rank(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+----------------------------------------------------------------------------------------------------------------+
| a| b|PERCENT_RANK() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+----------------------------------------------------------------------------------------------------------------+
| A1| 1| 0.0|
| A1| 1| 0.0|
| A1| 2| 1.0|
| A2| 3| 0.0|
+---+---+----------------------------------------------------------------------------------------------------------------+
-- rank
SELECT a, b, rank(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+--------------------------------------------------------------------------------------------------------+
| a| b|RANK() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+--------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 1|
| A1| 2| 3|
| A2| 3| 1|
+---+---+--------------------------------------------------------------------------------------------------------+
-- row_number
SELECT a, b, row_number() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+--------------------------------------------------------------------------------------------------------------+
| a| b|row_number() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+--------------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 2|
| A1| 2| 3|
| A2| 3| 1|
+---+---+--------------------------------------------------------------------------------------------------------------+
数组函数
| 函数 | 描述 |
|---|---|
| array(expr, ...) | 返回一个包含给定元素的数组。 |
| array_append(array, element) | 将元素添加到作为第一个参数传递的数组的末尾。元素的类型应与数组元素的类型相似。空元素也会附加到数组中。但如果传递的数组为 NULL，则输出为 NULL。 |
| array_compact(array) | 从数组中删除空值。 |
| array_contains(array, value) | 如果数组包含该值，则返回 true。 |
| array_distinct(array) | 从数组中删除重复值。 |
| array_except(array1, array2) | 返回 array1 中但不在 array2 中的元素数组，不包含重复项。 |
| array_insert(x, pos, val) | 将 val 放入数组 x 的索引 pos 处。数组索引从 1 开始。最大负索引为 -1，函数将新元素插入当前最后一个元素之后。如果索引大于数组大小，则在数组末尾追加数组；如果索引为负数，则在数组开头添加“null”元素。 |
| array_intersect(array1, array2) | 返回 array1 和 array2 交集中的元素数组，不包含重复项。 |
| array_join(array, delimiter[, nullReplacement]) | 使用分隔符和可选的字符串连接给定数组的元素以替换空值。如果未为 nullReplacement 设置任何值，则过滤掉任何空值。 |
| array_max(array) | 返回数组中的最大值。对于 double/float 类型，NaN 大于任何非 NaN 元素。跳过 NULL 元素。 |
| array_min(array) | 返回数组中的最小值。对于 double/float 类型，NaN 大于任何非 NaN 元素。跳过 NULL 元素。 |
| array_position(array, element) | 返回数组的第一个匹配元素的（从 1 开始的）索引（long 类型），如果没有找到匹配项，则返回 0。 |
| array_prepend(array, element) | 将元素添加到作为第一个参数传递的数组的开头。元素的类型应与数组元素的类型相同。空元素也会添加到数组的开头。但如果传递的数组为 NULL，则输出为 NULL。 |
| array_remove(array, element) | 从数组中删除所有等于 element 的元素。 |
| array_repeat(element, count) | 返回包含 element count 次的数组。 |
| array_union(array1, array2) | 返回 array1 和 array2 并集中的元素数组，不包含重复项。 |
| arrays_overlap(a1, a2) | 如果 a1 包含至少一个也存在于 a2 中的非空元素，则返回 true。如果数组没有公共元素，并且它们都是非空的，并且其中任何一个包含空元素，则返回 null，否则返回 false。 |
| arrays_zip(a1, a2, ...) | 返回一个合并的结构体数组，其中第 N 个结构体包含所有输入数组的第 N 个值。 |
| flatten(arrayOfArrays) | 将数组数组转换为单个数组。 |
| get(array, index) | 返回给定（从 0 开始）索引处的数组元素。如果索引指向数组边界之外，则此函数返回 NULL。 |
| sequence(start, stop, step) | 生成一个从 start 到 stop（包括）的元素数组，增量为 step。返回元素的类型与参数表达式的类型相同。支持的类型有：byte、short、integer、long、date、timestamp。start 和 stop 表达式必须解析为相同的类型。如果 start 和 stop 表达式解析为“date”或“timestamp”类型，则 step 表达式必须解析为“interval”或“year-month interval”或“day-time interval”类型，否则解析为与 start 和 stop 表达式相同的类型。 |
| shuffle(array) | 返回给定数组的随机排列。 |
| slice(x, start, length) | 从索引 start（数组索引从 1 开始，如果 start 为负数，则从末尾开始）开始，使用指定的长度对数组 x 进行子集化。 |
| sort_array(array[, ascendingOrder]) | 根据数组元素的自然顺序，按升序或降序对输入数组进行排序。对于 double/float 类型，NaN 大于任何非 NaN 元素。空元素将按升序放置在返回数组的开头，或按降序放置在返回数组的末尾。 |
示例
-- array
SELECT array(1, 2, 3);
+--------------+
|array(1, 2, 3)|
+--------------+
| [1, 2, 3]|
+--------------+
-- array_append
SELECT array_append(array('b', 'd', 'c', 'a'), 'd');
+----------------------------------+
|array_append(array(b, d, c, a), d)|
+----------------------------------+
| [b, d, c, a, d]|
+----------------------------------+
SELECT array_append(array(1, 2, 3, null), null);
+----------------------------------------+
|array_append(array(1, 2, 3, NULL), NULL)|
+----------------------------------------+
| [1, 2, 3, NULL, N...|
+----------------------------------------+
SELECT array_append(CAST(null as Array<Int>), 2);
+---------------------+
|array_append(NULL, 2)|
+---------------------+
| NULL|
+---------------------+
-- array_compact
SELECT array_compact(array(1, 2, 3, null));
+-----------------------------------+
|array_compact(array(1, 2, 3, NULL))|
+-----------------------------------+
| [1, 2, 3]|
+-----------------------------------+
SELECT array_compact(array("a", "b", "c"));
+-----------------------------+
|array_compact(array(a, b, c))|
+-----------------------------+
| [a, b, c]|
+-----------------------------+
-- array_contains
SELECT array_contains(array(1, 2, 3), 2);
+---------------------------------+
|array_contains(array(1, 2, 3), 2)|
+---------------------------------+
| true|
+---------------------------------+
-- array_distinct
SELECT array_distinct(array(1, 2, 3, null, 3));
+---------------------------------------+
|array_distinct(array(1, 2, 3, NULL, 3))|
+---------------------------------------+
| [1, 2, 3, NULL]|
+---------------------------------------+
-- array_except
SELECT array_except(array(1, 2, 3), array(1, 3, 5));
+--------------------------------------------+
|array_except(array(1, 2, 3), array(1, 3, 5))|
+--------------------------------------------+
| [2]|
+--------------------------------------------+
-- array_insert
SELECT array_insert(array(1, 2, 3, 4), 5, 5);
+-------------------------------------+
|array_insert(array(1, 2, 3, 4), 5, 5)|
+-------------------------------------+
| [1, 2, 3, 4, 5]|
+-------------------------------------+
SELECT array_insert(array(5, 4, 3, 2), -1, 1);
+--------------------------------------+
|array_insert(array(5, 4, 3, 2), -1, 1)|
+--------------------------------------+
| [5, 4, 3, 2, 1]|
+--------------------------------------+
SELECT array_insert(array(5, 3, 2, 1), -4, 4);
+--------------------------------------+
|array_insert(array(5, 3, 2, 1), -4, 4)|
+--------------------------------------+
| [5, 4, 3, 2, 1]|
+--------------------------------------+
-- array_intersect
SELECT array_intersect(array(1, 2, 3), array(1, 3, 5));
+-----------------------------------------------+
|array_intersect(array(1, 2, 3), array(1, 3, 5))|
+-----------------------------------------------+
| [1, 3]|
+-----------------------------------------------+
-- array_join
SELECT array_join(array('hello', 'world'), ' ');
+----------------------------------+
|array_join(array(hello, world), )|
+----------------------------------+
| hello world|
+----------------------------------+
SELECT array_join(array('hello', null ,'world'), ' ');
+----------------------------------------+
|array_join(array(hello, NULL, world), )|
+----------------------------------------+
| hello world|
+----------------------------------------+
SELECT array_join(array('hello', null ,'world'), ' ', ',');
+-------------------------------------------+
|array_join(array(hello, NULL, world), , ,)|
+-------------------------------------------+
| hello , world|
+-------------------------------------------+
-- array_max
SELECT array_max(array(1, 20, null, 3));
+--------------------------------+
|array_max(array(1, 20, NULL, 3))|
+--------------------------------+
| 20|
+--------------------------------+
-- array_min
SELECT array_min(array(1, 20, null, 3));
+--------------------------------+
|array_min(array(1, 20, NULL, 3))|
+--------------------------------+
| 1|
+--------------------------------+
-- array_position
SELECT array_position(array(312, 773, 708, 708), 708);
+----------------------------------------------+
|array_position(array(312, 773, 708, 708), 708)|
+----------------------------------------------+
| 3|
+----------------------------------------------+
SELECT array_position(array(312, 773, 708, 708), 414);
+----------------------------------------------+
|array_position(array(312, 773, 708, 708), 414)|
+----------------------------------------------+
| 0|
+----------------------------------------------+
-- array_prepend
SELECT array_prepend(array('b', 'd', 'c', 'a'), 'd');
+-----------------------------------+
|array_prepend(array(b, d, c, a), d)|
+-----------------------------------+
| [d, b, d, c, a]|
+-----------------------------------+
SELECT array_prepend(array(1, 2, 3, null), null);
+-----------------------------------------+
|array_prepend(array(1, 2, 3, NULL), NULL)|
+-----------------------------------------+
| [NULL, 1, 2, 3, N...|
+-----------------------------------------+
SELECT array_prepend(CAST(null as Array<Int>), 2);
+----------------------+
|array_prepend(NULL, 2)|
+----------------------+
| NULL|
+----------------------+
-- array_remove
SELECT array_remove(array(1, 2, 3, null, 3), 3);
+----------------------------------------+
|array_remove(array(1, 2, 3, NULL, 3), 3)|
+----------------------------------------+
| [1, 2, NULL]|
+----------------------------------------+
-- array_repeat
SELECT array_repeat('123', 2);
+--------------------+
|array_repeat(123, 2)|
+--------------------+
| [123, 123]|
+--------------------+
-- array_union
SELECT array_union(array(1, 2, 3), array(1, 3, 5));
+-------------------------------------------+
|array_union(array(1, 2, 3), array(1, 3, 5))|
+-------------------------------------------+
| [1, 2, 3, 5]|
+-------------------------------------------+
-- arrays_overlap
SELECT arrays_overlap(array(1, 2, 3), array(3, 4, 5));
+----------------------------------------------+
|arrays_overlap(array(1, 2, 3), array(3, 4, 5))|
+----------------------------------------------+
| true|
+----------------------------------------------+
-- arrays_zip
SELECT arrays_zip(array(1, 2, 3), array(2, 3, 4));
+------------------------------------------+
|arrays_zip(array(1, 2, 3), array(2, 3, 4))|
+------------------------------------------+
| [{1, 2}, {2, 3}, ...|
+------------------------------------------+
SELECT arrays_zip(array(1, 2), array(2, 3), array(3, 4));
+-------------------------------------------------+
|arrays_zip(array(1, 2), array(2, 3), array(3, 4))|
+-------------------------------------------------+
| [{1, 2, 3}, {2, 3...|
+-------------------------------------------------+
-- flatten
SELECT flatten(array(array(1, 2), array(3, 4)));
+----------------------------------------+
|flatten(array(array(1, 2), array(3, 4)))|
+----------------------------------------+
| [1, 2, 3, 4]|
+----------------------------------------+
-- get
SELECT get(array(1, 2, 3), 0);
+----------------------+
|get(array(1, 2, 3), 0)|
+----------------------+
| 1|
+----------------------+
SELECT get(array(1, 2, 3), 3);
+----------------------+
|get(array(1, 2, 3), 3)|
+----------------------+
| NULL|
+----------------------+
SELECT get(array(1, 2, 3), -1);
+-----------------------+
|get(array(1, 2, 3), -1)|
+-----------------------+
| NULL|
+-----------------------+
-- sequence
SELECT sequence(1, 5);
+---------------+
| sequence(1, 5)|
+---------------+
|[1, 2, 3, 4, 5]|
+---------------+
SELECT sequence(5, 1);
+---------------+
| sequence(5, 1)|
+---------------+
|[5, 4, 3, 2, 1]|
+---------------+
SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month);
+----------------------------------------------------------------------+
|sequence(to_date(2018-01-01), to_date(2018-03-01), INTERVAL '1' MONTH)|
+----------------------------------------------------------------------+
| [2018-01-01, 2018...|
+----------------------------------------------------------------------+
SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval '0-1' year to month);
+--------------------------------------------------------------------------------+
|sequence(to_date(2018-01-01), to_date(2018-03-01), INTERVAL '0-1' YEAR TO MONTH)|
+--------------------------------------------------------------------------------+
| [2018-01-01, 2018...|
+--------------------------------------------------------------------------------+
-- shuffle
SELECT shuffle(array(1, 20, 3, 5));
+---------------------------+
|shuffle(array(1, 20, 3, 5))|
+---------------------------+
| [5, 1, 20, 3]|
+---------------------------+
SELECT shuffle(array(1, 20, null, 3));
+------------------------------+
|shuffle(array(1, 20, NULL, 3))|
+------------------------------+
| [1, NULL, 20, 3]|
+------------------------------+
-- slice
SELECT slice(array(1, 2, 3, 4), 2, 2);
+------------------------------+
|slice(array(1, 2, 3, 4), 2, 2)|
+------------------------------+
| [2, 3]|
+------------------------------+
SELECT slice(array(1, 2, 3, 4), -2, 2);
+-------------------------------+
|slice(array(1, 2, 3, 4), -2, 2)|
+-------------------------------+
| [3, 4]|
+-------------------------------+
-- sort_array
SELECT sort_array(array('b', 'd', null, 'c', 'a'), true);
+-----------------------------------------+
|sort_array(array(b, d, NULL, c, a), true)|
+-----------------------------------------+
| [NULL, a, b, c, d]|
+-----------------------------------------+
映射函数
| 函数 | 描述 |
|---|---|
| element_at(array, index) | 返回给定（从 1 开始）索引处的数组元素。如果索引为 0，Spark 将抛出错误。如果 index < 0，则从最后一个元素访问到第一个元素。如果索引超过数组长度且 `spark.sql.ansi.enabled` 设置为 false，则该函数返回 NULL。如果 `spark.sql.ansi.enabled` 设置为 true，则它会为无效索引抛出 ArrayIndexOutOfBoundsException。 |
| element_at(map, key) | 返回给定键的值。如果映射中不包含该键，则该函数返回 NULL。 |
| map(key0, value0, key1, value1, ...) | 使用给定的键/值对创建映射。 |
| map_concat(map, ...) | 返回所有给定映射的并集。 |
| map_contains_key(map, key) | 如果映射包含该键，则返回 true。 |
| map_entries(map) | 返回给定映射中所有条目的无序数组。 |
| map_from_arrays(keys, values) | 使用一对给定的键/值数组创建映射。keys 中的所有元素都不应为空。 |
| map_from_entries(arrayOfEntries) | 返回从给定的条目数组创建的映射。 |
| map_keys(map) | 返回一个包含映射键的无序数组。 |
| map_values(map) | 返回一个包含映射值的无序数组。 |
| str_to_map(text[, pairDelim[, keyValueDelim]]) | 使用分隔符将文本拆分为键/值对后创建映射。默认分隔符是 ',' 用于 `pairDelim`，':' 用于 `keyValueDelim`。`pairDelim` 和 `keyValueDelim` 都被视为正则表达式。 |
| try_element_at(array, index) | 返回给定（从 1 开始）索引处的数组元素。如果索引为 0，Spark 将抛出错误。如果 index < 0，则从最后一个元素访问到第一个元素。如果索引超过数组长度，则该函数始终返回 NULL。 |
| try_element_at(map, key) | 返回给定键的值。如果映射中不包含该键，则该函数始终返回 NULL。 |
示例
-- element_at
SELECT element_at(array(1, 2, 3), 2);
+-----------------------------+
|element_at(array(1, 2, 3), 2)|
+-----------------------------+
| 2|
+-----------------------------+
SELECT element_at(map(1, 'a', 2, 'b'), 2);
+------------------------------+
|element_at(map(1, a, 2, b), 2)|
+------------------------------+
| b|
+------------------------------+
-- map
SELECT map(1.0, '2', 3.0, '4');
+--------------------+
| map(1.0, 2, 3.0, 4)|
+--------------------+
|{1.0 -> 2, 3.0 -> 4}|
+--------------------+
-- map_concat
SELECT map_concat(map(1, 'a', 2, 'b'), map(3, 'c'));
+--------------------------------------+
|map_concat(map(1, a, 2, b), map(3, c))|
+--------------------------------------+
| {1 -> a, 2 -> b, ...|
+--------------------------------------+
-- map_contains_key
SELECT map_contains_key(map(1, 'a', 2, 'b'), 1);
+------------------------------------+
|map_contains_key(map(1, a, 2, b), 1)|
+------------------------------------+
| true|
+------------------------------------+
SELECT map_contains_key(map(1, 'a', 2, 'b'), 3);
+------------------------------------+
|map_contains_key(map(1, a, 2, b), 3)|
+------------------------------------+
| false|
+------------------------------------+
-- map_entries
SELECT map_entries(map(1, 'a', 2, 'b'));
+----------------------------+
|map_entries(map(1, a, 2, b))|
+----------------------------+
| [{1, a}, {2, b}]|
+----------------------------+
-- map_from_arrays
SELECT map_from_arrays(array(1.0, 3.0), array('2', '4'));
+---------------------------------------------+
|map_from_arrays(array(1.0, 3.0), array(2, 4))|
+---------------------------------------------+
| {1.0 -> 2, 3.0 -> 4}|
+---------------------------------------------+
-- map_from_entries
SELECT map_from_entries(array(struct(1, 'a'), struct(2, 'b')));
+---------------------------------------------------+
|map_from_entries(array(struct(1, a), struct(2, b)))|
+---------------------------------------------------+
| {1 -> a, 2 -> b}|
+---------------------------------------------------+
-- map_keys
SELECT map_keys(map(1, 'a', 2, 'b'));
+-------------------------+
|map_keys(map(1, a, 2, b))|
+-------------------------+
| [1, 2]|
+-------------------------+
-- map_values
SELECT map_values(map(1, 'a', 2, 'b'));
+---------------------------+
|map_values(map(1, a, 2, b))|
+---------------------------+
| [a, b]|
+---------------------------+
-- str_to_map
SELECT str_to_map('a:1,b:2,c:3', ',', ':');
+-----------------------------+
|str_to_map(a:1,b:2,c:3, ,, :)|
+-----------------------------+
| {a -> 1, b -> 2, ...|
+-----------------------------+
SELECT str_to_map('a');
+-------------------+
|str_to_map(a, ,, :)|
+-------------------+
| {a -> NULL}|
+-------------------+
-- try_element_at
SELECT try_element_at(array(1, 2, 3), 2);
+---------------------------------+
|try_element_at(array(1, 2, 3), 2)|
+---------------------------------+
| 2|
+---------------------------------+
SELECT try_element_at(map(1, 'a', 2, 'b'), 2);
+----------------------------------+
|try_element_at(map(1, a, 2, b), 2)|
+----------------------------------+
| b|
+----------------------------------+
日期和时间戳函数
| 函数 | 描述 |
|---|---|
| add_months(start_date, num_months) | 返回 `start_date` 之后 `num_months` 个月的日期。 |
| convert_timezone([sourceTz, ]targetTz, sourceTs) | 将没有时区的 timestamp `sourceTs` 从 `sourceTz` 时区转换为 `targetTz`。 |
| curdate() | 返回查询评估开始时的当前日期。同一查询中所有对 curdate 的调用都返回相同的值。 |
| current_date() | 返回查询评估开始时的当前日期。同一查询中所有对 current_date 的调用都返回相同的值。 |
| current_date | 返回查询评估开始时的当前日期。 |
| current_timestamp() | 返回查询评估开始时的当前时间戳。同一查询中所有对 current_timestamp 的调用都返回相同的值。 |
| current_timestamp | 返回查询评估开始时的当前时间戳。 |
| current_timezone() | 返回当前会话的本地时区。 |
| date_add(start_date, num_days) | 返回 `start_date` 之后 `num_days` 天的日期。 |
| date_diff(endDate, startDate) | 返回从 `startDate` 到 `endDate` 的天数。 |
| date_format(timestamp, fmt) | 将 `timestamp` 转换为日期格式 `fmt` 指定的字符串值。 |
| date_from_unix_date(days) | 从 1970-01-01 以来的天数创建日期。 |
| date_part(field, source) | 提取日期/时间戳或时间间隔源的一部分。 |
| date_sub(start_date, num_days) | 返回 `start_date` 之前 `num_days` 天的日期。 |
| date_trunc(fmt, ts) | 返回截断到格式模型 `fmt` 指定的单位的时间戳 `ts`。 |
| dateadd(start_date, num_days) | 返回 `start_date` 之后 `num_days` 天的日期。 |
| datediff(endDate, startDate) | 返回从 `startDate` 到 `endDate` 的天数。 |
| datepart(field, source) | 提取日期/时间戳或时间间隔源的一部分。 |
| day(date) | 返回日期/时间戳的月份中的哪一天。 |
| dayofmonth(date) | 返回日期/时间戳的月份中的哪一天。 |
| dayofweek(date) | 返回日期/时间戳的星期几（1 = 星期日，2 = 星期一，...，7 = 星期六）。 |
| dayofyear(date) | 返回日期/时间戳的年份中的哪一天。 |
| extract(field FROM source) | 提取日期/时间戳或时间间隔源的一部分。 |
| from_unixtime(unix_time[, fmt]) | 以指定的 `fmt` 返回 `unix_time`。 |
| from_utc_timestamp(timestamp, timezone) | 给定一个时间戳，如“2017-07-14 02:40:00.0”，将其解释为 UTC 时间，并将该时间呈现为给定时区中的时间戳。例如，“GMT+1”将产生“2017-07-14 03:40:00.0”。 |
| hour(timestamp) | 返回字符串/时间戳的小时部分。 |
| last_day(date) | 返回该日期所属月份的最后一天。 |
| localtimestamp() | 返回查询评估开始时没有时区的当前时间戳。同一查询中所有对 localtimestamp 的调用都返回相同的值。 |
| localtimestamp | 返回查询评估开始时会话时区的当前本地日期时间。 |
| make_date(year, month, day) | 根据年、月和日字段创建日期。如果配置 `spark.sql.ansi.enabled` 为 false，则该函数在输入无效时返回 NULL。否则，它将抛出错误。 |
| make_dt_interval([days[, hours[, mins[, secs]]]]) | 根据天、小时、分钟和秒创建 DayTimeIntervalType 时间间隔。 |
| make_interval([years[, months[, weeks[, days[, hours[, mins[, secs]]]]]]]) | 根据年、月、周、天、小时、分钟和秒创建间隔。 |
| make_timestamp(year, month, day, hour, min, sec[, timezone]) | 根据年、月、日、小时、分钟、秒和时区字段创建时间戳。结果数据类型与配置 `spark.sql.timestampType` 的值一致。如果配置 `spark.sql.ansi.enabled` 为 false，则该函数在输入无效时返回 NULL。否则，它将抛出错误。 |
| make_timestamp_ltz(year, month, day, hour, min, sec[, timezone]) | 根据年、月、日、小时、分钟、秒和时区字段创建当前时间戳（使用本地时区）。如果配置 `spark.sql.ansi.enabled` 为 false，则该函数在输入无效时返回 NULL。否则，它将抛出错误。 |
| make_timestamp_ntz(year, month, day, hour, min, sec) | 根据年、月、日、小时、分钟、秒字段创建本地日期时间。如果配置 `spark.sql.ansi.enabled` 为 false，则该函数在输入无效时返回 NULL。否则，它将抛出错误。 |
| make_ym_interval([years[, months]]) | 根据年、月创建年月间隔。 |
| minute(timestamp) | 返回字符串/时间戳的分钟部分。 |
| month(date) | 返回日期/时间戳的月份部分。 |
| months_between(timestamp1, timestamp2[, roundOff]) | 如果 `timestamp1` 晚于 `timestamp2`，则结果为正数。如果 `timestamp1` 和 `timestamp2` 在同一个月同一天，或者两者都是该月的最后一天，则忽略时间。否则，将根据每月 31 天计算差异，并舍入到 8 位小数，除非 roundOff=false。 |
| next_day(start_date, day_of_week) | 返回晚于 `start_date` 且名称如指示的第一个日期。如果至少有一个输入参数为 NULL，则该函数返回 NULL。当两个输入参数都不为 NULL 且 day_of_week 为无效输入时，如果 `spark.sql.ansi.enabled` 设置为 true，则该函数抛出 IllegalArgumentException，否则返回 NULL。 |
| now() | 返回查询评估开始时的当前时间戳。 |
| quarter(date) | 返回日期的季度，范围为 1 到 4。 |
| second(timestamp) | 返回字符串/时间戳的秒部分。 |
| session_window(time_column, gap_duration) | 根据指定时间戳的列和间隔持续时间生成会话窗口。有关详细说明和示例，请参阅结构化流指南文档中的“时间窗口类型”。 |
| timestamp_micros(microseconds) | 根据自 UTC 时间戳以来的微秒数创建时间戳。 |
| timestamp_millis(milliseconds) | 根据自 UTC 时间戳以来的毫秒数创建时间戳。 |
| timestamp_seconds(seconds) | 根据自 UTC 时间戳以来的秒数（可以是小数）创建时间戳。 |
| to_date(date_str[, fmt]) | 使用 `fmt` 表达式将 `date_str` 表达式解析为日期。如果输入无效，则返回 null。默认情况下，如果省略 `fmt`，则遵循转换为日期的强制转换规则。 |
| to_timestamp(timestamp_str[, fmt]) | 使用 `fmt` 表达式将 `timestamp_str` 表达式解析为时间戳。如果输入无效，则返回 null。默认情况下，如果省略 `fmt`，则遵循转换为时间戳的强制转换规则。结果数据类型与配置 `spark.sql.timestampType` 的值一致。 |
| to_timestamp_ltz(timestamp_str[, fmt]) | 使用 `fmt` 表达式将 `timestamp_str` 表达式解析为具有本地时区的时间戳。如果输入无效，则返回 null。默认情况下，如果省略 `fmt`，则遵循转换为时间戳的强制转换规则。 |
| to_timestamp_ntz(timestamp_str[, fmt]) | 使用 `fmt` 表达式将 `timestamp_str` 表达式解析为没有时区的时间戳。如果输入无效，则返回 null。默认情况下，如果省略 `fmt`，则遵循转换为时间戳的强制转换规则。 |
| to_unix_timestamp(timeExp[, fmt]) | 返回给定时间的 UNIX 时间戳。 |
| to_utc_timestamp(timestamp, timezone) | 给定一个时间戳，例如“2017-07-14 02:40:00.0”，将其解释为给定时区中的时间，并将该时间渲染为 UTC 中的时间戳。例如，“GMT+1”将产生“2017-07-14 01:40:00.0”。 |
| trunc(date, fmt) | 返回 `date`，其中日期的时间部分截断为格式模型 `fmt` 指定的单位。 |
| try_to_timestamp(timestamp_str[, fmt]) | 使用 `fmt` 表达式将 `timestamp_str` 表达式解析为时间戳。无论是否启用了 ANSI SQL 模式，该函数在输入无效时始终返回 null。默认情况下，如果省略 `fmt`，则遵循转换为时间戳的强制转换规则。结果数据类型与配置 `spark.sql.timestampType` 的值一致。 |
| unix_date(date) | 返回自 1970-01-01 以来的天数。 |
| unix_micros(timestamp) | 返回自 1970-01-01 00:00:00 UTC 以来的微秒数。 |
| unix_millis(timestamp) | 返回自 1970-01-01 00:00:00 UTC 以来的毫秒数。截断更高的精度级别。 |
| unix_seconds(timestamp) | 返回自 1970-01-01 00:00:00 UTC 以来的秒数。截断更高的精度级别。 |
| unix_timestamp([timeExp[, fmt]]) | 返回当前或指定时间的 UNIX 时间戳。 |
| weekday(date) | 返回日期/时间戳的星期几（0 = 星期一，1 = 星期二，...，6 = 星期日）。 |
| weekofyear(date) | 返回给定日期的年份周数。一周被认为是从星期一开始，第 1 周是具有大于 3 天的第一周。 |
| window(time_column, window_duration[, slide_duration[, start_time]]) | 给定指定时间戳的列，将行分组到一个或多个时间窗口中。窗口开始时间包含在内，但窗口结束时间不包含在内，例如，12:05 将位于窗口 [12:05,12:10) 中，但不在 [12:00,12:05) 中。窗口可以支持微秒精度。不支持按月顺序排列的窗口。有关详细说明和示例，请参阅结构化流指南文档中的“事件时间上的窗口操作”。 |
| window_time(window_column) | 从时间/会话窗口列中提取时间值，该值可用作窗口的事件时间值。提取的时间是 (window.end - 1)，这反映了聚合窗口具有独占上限的事实 - [start, end)。有关详细说明和示例，请参阅结构化流指南文档中的“事件时间上的窗口操作”。 |
| year(date) | 返回日期/时间戳的年份部分。 |
示例
-- add_months
SELECT add_months('2016-08-31', 1);
+-------------------------+
|add_months(2016-08-31, 1)|
+-------------------------+
| 2016-09-30|
+-------------------------+
-- convert_timezone
SELECT convert_timezone('Europe/Brussels', 'America/Los_Angeles', timestamp_ntz'2021-12-06 00:00:00');
+-------------------------------------------------------------------------------------------+
|convert_timezone(Europe/Brussels, America/Los_Angeles, TIMESTAMP_NTZ '2021-12-06 00:00:00')|
+-------------------------------------------------------------------------------------------+
| 2021-12-05 15:00:00|
+-------------------------------------------------------------------------------------------+
SELECT convert_timezone('Europe/Brussels', timestamp_ntz'2021-12-05 15:00:00');
+------------------------------------------------------------------------------------------+
|convert_timezone(current_timezone(), Europe/Brussels, TIMESTAMP_NTZ '2021-12-05 15:00:00')|
+------------------------------------------------------------------------------------------+
| 2021-12-05 07:00:00|
+------------------------------------------------------------------------------------------+
-- curdate
SELECT curdate();
+--------------+
|current_date()|
+--------------+
| 2024-02-24|
+--------------+
-- current_date
SELECT current_date();
+--------------+
|current_date()|
+--------------+
| 2024-02-24|
+--------------+
SELECT current_date;
+--------------+
|current_date()|
+--------------+
| 2024-02-24|
+--------------+
-- current_timestamp
SELECT current_timestamp();
+--------------------+
| current_timestamp()|
+--------------------+
|2024-02-24 16:36:...|
+--------------------+
SELECT current_timestamp;
+--------------------+
| current_timestamp()|
+--------------------+
|2024-02-24 16:36:...|
+--------------------+
-- current_timezone
SELECT current_timezone();
+------------------+
|current_timezone()|
+------------------+
| Asia/Seoul|
+------------------+
-- date_add
SELECT date_add('2016-07-30', 1);
+-----------------------+
|date_add(2016-07-30, 1)|
+-----------------------+
| 2016-07-31|
+-----------------------+
-- date_diff
SELECT date_diff('2009-07-31', '2009-07-30');
+---------------------------------+
|date_diff(2009-07-31, 2009-07-30)|
+---------------------------------+
| 1|
+---------------------------------+
SELECT date_diff('2009-07-30', '2009-07-31');
+---------------------------------+
|date_diff(2009-07-30, 2009-07-31)|
+---------------------------------+
| -1|
+---------------------------------+
-- date_format
SELECT date_format('2016-04-08', 'y');
+--------------------------+
|date_format(2016-04-08, y)|
+--------------------------+
| 2016|
+--------------------------+
-- date_from_unix_date
SELECT date_from_unix_date(1);
+----------------------+
|date_from_unix_date(1)|
+----------------------+
| 1970-01-02|
+----------------------+
-- date_part
SELECT date_part('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456');
+-------------------------------------------------------+
|date_part(YEAR, TIMESTAMP '2019-08-12 01:00:00.123456')|
+-------------------------------------------------------+
| 2019|
+-------------------------------------------------------+
SELECT date_part('week', timestamp'2019-08-12 01:00:00.123456');
+-------------------------------------------------------+
|date_part(week, TIMESTAMP '2019-08-12 01:00:00.123456')|
+-------------------------------------------------------+
| 33|
+-------------------------------------------------------+
SELECT date_part('doy', DATE'2019-08-12');
+---------------------------------+
|date_part(doy, DATE '2019-08-12')|
+---------------------------------+
| 224|
+---------------------------------+
SELECT date_part('SECONDS', timestamp'2019-10-01 00:00:01.000001');
+----------------------------------------------------------+
|date_part(SECONDS, TIMESTAMP '2019-10-01 00:00:01.000001')|
+----------------------------------------------------------+
| 1.000001|
+----------------------------------------------------------+
SELECT date_part('days', interval 5 days 3 hours 7 minutes);
+-------------------------------------------------+
|date_part(days, INTERVAL '5 03:07' DAY TO MINUTE)|
+-------------------------------------------------+
| 5|
+-------------------------------------------------+
SELECT date_part('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
+-------------------------------------------------------------+
|date_part(seconds, INTERVAL '05:00:30.001001' HOUR TO SECOND)|
+-------------------------------------------------------------+
| 30.001001|
+-------------------------------------------------------------+
SELECT date_part('MONTH', INTERVAL '2021-11' YEAR TO MONTH);
+--------------------------------------------------+
|date_part(MONTH, INTERVAL '2021-11' YEAR TO MONTH)|
+--------------------------------------------------+
| 11|
+--------------------------------------------------+
SELECT date_part('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND);
+---------------------------------------------------------------+
|date_part(MINUTE, INTERVAL '123 23:55:59.002001' DAY TO SECOND)|
+---------------------------------------------------------------+
| 55|
+---------------------------------------------------------------+
-- date_sub
SELECT date_sub('2016-07-30', 1);
+-----------------------+
|date_sub(2016-07-30, 1)|
+-----------------------+
| 2016-07-29|
+-----------------------+
-- date_trunc
SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');
+-----------------------------------------+
|date_trunc(YEAR, 2015-03-05T09:32:05.359)|
+-----------------------------------------+
| 2015-01-01 00:00:00|
+-----------------------------------------+
SELECT date_trunc('MM', '2015-03-05T09:32:05.359');
+---------------------------------------+
|date_trunc(MM, 2015-03-05T09:32:05.359)|
+---------------------------------------+
| 2015-03-01 00:00:00|
+---------------------------------------+
SELECT date_trunc('DD', '2015-03-05T09:32:05.359');
+---------------------------------------+
|date_trunc(DD, 2015-03-05T09:32:05.359)|
+---------------------------------------+
| 2015-03-05 00:00:00|
+---------------------------------------+
SELECT date_trunc('HOUR', '2015-03-05T09:32:05.359');
+-----------------------------------------+
|date_trunc(HOUR, 2015-03-05T09:32:05.359)|
+-----------------------------------------+
| 2015-03-05 09:00:00|
+-----------------------------------------+
SELECT date_trunc('MILLISECOND', '2015-03-05T09:32:05.123456');
+---------------------------------------------------+
|date_trunc(MILLISECOND, 2015-03-05T09:32:05.123456)|
+---------------------------------------------------+
| 2015-03-05 09:32:...|
+---------------------------------------------------+
-- dateadd
SELECT dateadd('2016-07-30', 1);
+-----------------------+
|date_add(2016-07-30, 1)|
+-----------------------+
| 2016-07-31|
+-----------------------+
-- datediff
SELECT datediff('2009-07-31', '2009-07-30');
+--------------------------------+
|datediff(2009-07-31, 2009-07-30)|
+--------------------------------+
| 1|
+--------------------------------+
SELECT datediff('2009-07-30', '2009-07-31');
+--------------------------------+
|datediff(2009-07-30, 2009-07-31)|
+--------------------------------+
| -1|
+--------------------------------+
-- datepart
SELECT datepart('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456');
+----------------------------------------------------------+
|datepart(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+----------------------------------------------------------+
| 2019|
+----------------------------------------------------------+
SELECT datepart('week', timestamp'2019-08-12 01:00:00.123456');
+----------------------------------------------------------+
|datepart(week FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+----------------------------------------------------------+
| 33|
+----------------------------------------------------------+
SELECT datepart('doy', DATE'2019-08-12');
+------------------------------------+
|datepart(doy FROM DATE '2019-08-12')|
+------------------------------------+
| 224|
+------------------------------------+
SELECT datepart('SECONDS', timestamp'2019-10-01 00:00:01.000001');
+-------------------------------------------------------------+
|datepart(SECONDS FROM TIMESTAMP '2019-10-01 00:00:01.000001')|
+-------------------------------------------------------------+
| 1.000001|
+-------------------------------------------------------------+
SELECT datepart('days', interval 5 days 3 hours 7 minutes);
+----------------------------------------------------+
|datepart(days FROM INTERVAL '5 03:07' DAY TO MINUTE)|
+----------------------------------------------------+
| 5|
+----------------------------------------------------+
SELECT datepart('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
+----------------------------------------------------------------+
|datepart(seconds FROM INTERVAL '05:00:30.001001' HOUR TO SECOND)|
+----------------------------------------------------------------+
| 30.001001|
+----------------------------------------------------------------+
SELECT datepart('MONTH', INTERVAL '2021-11' YEAR TO MONTH);
+-----------------------------------------------------+
|datepart(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH)|
+-----------------------------------------------------+
| 11|
+-----------------------------------------------------+
SELECT datepart('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND);
+------------------------------------------------------------------+
|datepart(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND)|
+------------------------------------------------------------------+
| 55|
+------------------------------------------------------------------+
-- day
SELECT day('2009-07-30');
+---------------+
|day(2009-07-30)|
+---------------+
| 30|
+---------------+
-- dayofmonth
SELECT dayofmonth('2009-07-30');
+----------------------+
|dayofmonth(2009-07-30)|
+----------------------+
| 30|
+----------------------+
-- dayofweek
SELECT dayofweek('2009-07-30');
+---------------------+
|dayofweek(2009-07-30)|
+---------------------+
| 5|
+---------------------+
-- dayofyear
SELECT dayofyear('2016-04-09');
+---------------------+
|dayofyear(2016-04-09)|
+---------------------+
| 100|
+---------------------+
-- extract
SELECT extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456');
+---------------------------------------------------------+
|extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+---------------------------------------------------------+
| 2019|
+---------------------------------------------------------+
SELECT extract(week FROM timestamp'2019-08-12 01:00:00.123456');
+---------------------------------------------------------+
|extract(week FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+---------------------------------------------------------+
| 33|
+---------------------------------------------------------+
SELECT extract(doy FROM DATE'2019-08-12');
+-----------------------------------+
|extract(doy FROM DATE '2019-08-12')|
+-----------------------------------+
| 224|
+-----------------------------------+
SELECT extract(SECONDS FROM timestamp'2019-10-01 00:00:01.000001');
+------------------------------------------------------------+
|extract(SECONDS FROM TIMESTAMP '2019-10-01 00:00:01.000001')|
+------------------------------------------------------------+
| 1.000001|
+------------------------------------------------------------+
SELECT extract(days FROM interval 5 days 3 hours 7 minutes);
+---------------------------------------------------+
|extract(days FROM INTERVAL '5 03:07' DAY TO MINUTE)|
+---------------------------------------------------+
| 5|
+---------------------------------------------------+
SELECT extract(seconds FROM interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
+---------------------------------------------------------------+
|extract(seconds FROM INTERVAL '05:00:30.001001' HOUR TO SECOND)|
+---------------------------------------------------------------+
| 30.001001|
+---------------------------------------------------------------+
SELECT extract(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH);
+----------------------------------------------------+
|extract(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH)|
+----------------------------------------------------+
| 11|
+----------------------------------------------------+
SELECT extract(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND);
+-----------------------------------------------------------------+
|extract(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND)|
+-----------------------------------------------------------------+
| 55|
+-----------------------------------------------------------------+
-- from_unixtime
SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
+-------------------------------------+
|from_unixtime(0, yyyy-MM-dd HH:mm:ss)|
+-------------------------------------+
| 1970-01-01 09:00:00|
+-------------------------------------+
SELECT from_unixtime(0);
+-------------------------------------+
|from_unixtime(0, yyyy-MM-dd HH:mm:ss)|
+-------------------------------------+
| 1970-01-01 09:00:00|
+-------------------------------------+
-- from_utc_timestamp
SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul');
+------------------------------------------+
|from_utc_timestamp(2016-08-31, Asia/Seoul)|
+------------------------------------------+
| 2016-08-31 09:00:00|
+------------------------------------------+
-- hour
SELECT hour('2009-07-30 12:58:59');
+-------------------------+
|hour(2009-07-30 12:58:59)|
+-------------------------+
| 12|
+-------------------------+
-- last_day
SELECT last_day('2009-01-12');
+--------------------+
|last_day(2009-01-12)|
+--------------------+
| 2009-01-31|
+--------------------+
-- localtimestamp
SELECT localtimestamp();
+--------------------+
| localtimestamp()|
+--------------------+
|2024-02-24 16:36:...|
+--------------------+
-- make_date
SELECT make_date(2013, 7, 15);
+----------------------+
|make_date(2013, 7, 15)|
+----------------------+
| 2013-07-15|
+----------------------+
SELECT make_date(2019, 7, NULL);
+------------------------+
|make_date(2019, 7, NULL)|
+------------------------+
| NULL|
+------------------------+
-- make_dt_interval
SELECT make_dt_interval(1, 12, 30, 01.001001);
+-------------------------------------+
|make_dt_interval(1, 12, 30, 1.001001)|
+-------------------------------------+
| INTERVAL '1 12:30...|
+-------------------------------------+
SELECT make_dt_interval(2);
+-----------------------------------+
|make_dt_interval(2, 0, 0, 0.000000)|
+-----------------------------------+
| INTERVAL '2 00:00...|
+-----------------------------------+
SELECT make_dt_interval(100, null, 3);
+----------------------------------------+
|make_dt_interval(100, NULL, 3, 0.000000)|
+----------------------------------------+
| NULL|
+----------------------------------------+
-- make_interval
SELECT make_interval(100, 11, 1, 1, 12, 30, 01.001001);
+----------------------------------------------+
|make_interval(100, 11, 1, 1, 12, 30, 1.001001)|
+----------------------------------------------+
| 100 years 11 mont...|
+----------------------------------------------+
SELECT make_interval(100, null, 3);
+----------------------------------------------+
|make_interval(100, NULL, 3, 0, 0, 0, 0.000000)|
+----------------------------------------------+
| NULL|
+----------------------------------------------+
SELECT make_interval(0, 1, 0, 1, 0, 0, 100.000001);
+-------------------------------------------+
|make_interval(0, 1, 0, 1, 0, 0, 100.000001)|
+-------------------------------------------+
| 1 months 1 days 1...|
+-------------------------------------------+
-- make_timestamp
SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
+-------------------------------------------+
|make_timestamp(2014, 12, 28, 6, 30, 45.887)|
+-------------------------------------------+
| 2014-12-28 06:30:...|
+-------------------------------------------+
SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887, 'CET');
+------------------------------------------------+
|make_timestamp(2014, 12, 28, 6, 30, 45.887, CET)|
+------------------------------------------------+
| 2014-12-28 14:30:...|
+------------------------------------------------+
SELECT make_timestamp(2019, 6, 30, 23, 59, 60);
+---------------------------------------+
|make_timestamp(2019, 6, 30, 23, 59, 60)|
+---------------------------------------+
| 2019-07-01 00:00:00|
+---------------------------------------+
SELECT make_timestamp(2019, 6, 30, 23, 59, 1);
+--------------------------------------+
|make_timestamp(2019, 6, 30, 23, 59, 1)|
+--------------------------------------+
| 2019-06-30 23:59:01|
+--------------------------------------+
SELECT make_timestamp(null, 7, 22, 15, 30, 0);
+--------------------------------------+
|make_timestamp(NULL, 7, 22, 15, 30, 0)|
+--------------------------------------+
| NULL|
+--------------------------------------+
-- make_timestamp_ltz
SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887);
+-----------------------------------------------+
|make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887)|
+-----------------------------------------------+
| 2014-12-28 06:30:...|
+-----------------------------------------------+
SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, 'CET');
+----------------------------------------------------+
|make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, CET)|
+----------------------------------------------------+
| 2014-12-28 14:30:...|
+----------------------------------------------------+
SELECT make_timestamp_ltz(2019, 6, 30, 23, 59, 60);
+-------------------------------------------+
|make_timestamp_ltz(2019, 6, 30, 23, 59, 60)|
+-------------------------------------------+
| 2019-07-01 00:00:00|
+-------------------------------------------+
SELECT make_timestamp_ltz(null, 7, 22, 15, 30, 0);
+------------------------------------------+
|make_timestamp_ltz(NULL, 7, 22, 15, 30, 0)|
+------------------------------------------+
| NULL|
+------------------------------------------+
-- make_timestamp_ntz
SELECT make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887);
+-----------------------------------------------+
|make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887)|
+-----------------------------------------------+
| 2014-12-28 06:30:...|
+-----------------------------------------------+
SELECT make_timestamp_ntz(2019, 6, 30, 23, 59, 60);
+-------------------------------------------+
|make_timestamp_ntz(2019, 6, 30, 23, 59, 60)|
+-------------------------------------------+
| 2019-07-01 00:00:00|
+-------------------------------------------+
SELECT make_timestamp_ntz(null, 7, 22, 15, 30, 0);
+------------------------------------------+
|make_timestamp_ntz(NULL, 7, 22, 15, 30, 0)|
+------------------------------------------+
| NULL|
+------------------------------------------+
-- make_ym_interval
SELECT make_ym_interval(1, 2);
+----------------------+
|make_ym_interval(1, 2)|
+----------------------+
| INTERVAL '1-2' YE...|
+----------------------+
SELECT make_ym_interval(1, 0);
+----------------------+
|make_ym_interval(1, 0)|
+----------------------+
| INTERVAL '1-0' YE...|
+----------------------+
SELECT make_ym_interval(-1, 1);
+-----------------------+
|make_ym_interval(-1, 1)|
+-----------------------+
| INTERVAL '-0-11' ...|
+-----------------------+
SELECT make_ym_interval(2);
+----------------------+
|make_ym_interval(2, 0)|
+----------------------+
| INTERVAL '2-0' YE...|
+----------------------+
-- minute
SELECT minute('2009-07-30 12:58:59');
+---------------------------+
|minute(2009-07-30 12:58:59)|
+---------------------------+
| 58|
+---------------------------+
-- month
SELECT month('2016-07-30');
+-----------------+
|month(2016-07-30)|
+-----------------+
| 7|
+-----------------+
-- months_between
SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
+-----------------------------------------------------+
|months_between(1997-02-28 10:30:00, 1996-10-30, true)|
+-----------------------------------------------------+
| 3.94959677|
+-----------------------------------------------------+
SELECT months_between('1997-02-28 10:30:00', '1996-10-30', false);
+------------------------------------------------------+
|months_between(1997-02-28 10:30:00, 1996-10-30, false)|
+------------------------------------------------------+
| 3.9495967741935485|
+------------------------------------------------------+
-- next_day
SELECT next_day('2015-01-14', 'TU');
+------------------------+
|next_day(2015-01-14, TU)|
+------------------------+
| 2015-01-20|
+------------------------+
-- now
SELECT now();
+--------------------+
| now()|
+--------------------+
|2024-02-24 16:36:...|
+--------------------+
-- quarter
SELECT quarter('2016-08-31');
+-------------------+
|quarter(2016-08-31)|
+-------------------+
| 3|
+-------------------+
-- second
SELECT second('2009-07-30 12:58:59');
+---------------------------+
|second(2009-07-30 12:58:59)|
+---------------------------+
| 59|
+---------------------------+
-- session_window
SELECT a, session_window.start, session_window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:10:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, session_window(b, '5 minutes') ORDER BY a, start;
+---+-------------------+-------------------+---+
| a| start| end|cnt|
+---+-------------------+-------------------+---+
| A1|2021-01-01 00:00:00|2021-01-01 00:09:30| 2|
| A1|2021-01-01 00:10:00|2021-01-01 00:15:00| 1|
| A2|2021-01-01 00:01:00|2021-01-01 00:06:00| 1|
+---+-------------------+-------------------+---+
SELECT a, session_window.start, session_window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:10:00'), ('A2', '2021-01-01 00:01:00'), ('A2', '2021-01-01 00:04:30') AS tab(a, b) GROUP by a, session_window(b, CASE WHEN a = 'A1' THEN '5 minutes' WHEN a = 'A2' THEN '1 minute' ELSE '10 minutes' END) ORDER BY a, start;
+---+-------------------+-------------------+---+
| a| start| end|cnt|
+---+-------------------+-------------------+---+
| A1|2021-01-01 00:00:00|2021-01-01 00:09:30| 2|
| A1|2021-01-01 00:10:00|2021-01-01 00:15:00| 1|
| A2|2021-01-01 00:01:00|2021-01-01 00:02:00| 1|
| A2|2021-01-01 00:04:30|2021-01-01 00:05:30| 1|
+---+-------------------+-------------------+---+
-- timestamp_micros
SELECT timestamp_micros(1230219000123123);
+----------------------------------+
|timestamp_micros(1230219000123123)|
+----------------------------------+
| 2008-12-26 00:30:...|
+----------------------------------+
-- timestamp_millis
SELECT timestamp_millis(1230219000123);
+-------------------------------+
|timestamp_millis(1230219000123)|
+-------------------------------+
| 2008-12-26 00:30:...|
+-------------------------------+
-- timestamp_seconds
SELECT timestamp_seconds(1230219000);
+-----------------------------+
|timestamp_seconds(1230219000)|
+-----------------------------+
| 2008-12-26 00:30:00|
+-----------------------------+
SELECT timestamp_seconds(1230219000.123);
+---------------------------------+
|timestamp_seconds(1230219000.123)|
+---------------------------------+
| 2008-12-26 00:30:...|
+---------------------------------+
-- to_date
SELECT to_date('2009-07-30 04:17:52');
+----------------------------+
|to_date(2009-07-30 04:17:52)|
+----------------------------+
| 2009-07-30|
+----------------------------+
SELECT to_date('2016-12-31', 'yyyy-MM-dd');
+-------------------------------+
|to_date(2016-12-31, yyyy-MM-dd)|
+-------------------------------+
| 2016-12-31|
+-------------------------------+
-- to_timestamp
SELECT to_timestamp('2016-12-31 00:12:00');
+---------------------------------+
|to_timestamp(2016-12-31 00:12:00)|
+---------------------------------+
| 2016-12-31 00:12:00|
+---------------------------------+
SELECT to_timestamp('2016-12-31', 'yyyy-MM-dd');
+------------------------------------+
|to_timestamp(2016-12-31, yyyy-MM-dd)|
+------------------------------------+
| 2016-12-31 00:00:00|
+------------------------------------+
-- to_timestamp_ltz
SELECT to_timestamp_ltz('2016-12-31 00:12:00');
+-------------------------------------+
|to_timestamp_ltz(2016-12-31 00:12:00)|
+-------------------------------------+
| 2016-12-31 00:12:00|
+-------------------------------------+
SELECT to_timestamp_ltz('2016-12-31', 'yyyy-MM-dd');
+----------------------------------------+
|to_timestamp_ltz(2016-12-31, yyyy-MM-dd)|
+----------------------------------------+
| 2016-12-31 00:00:00|
+----------------------------------------+
-- to_timestamp_ntz
SELECT to_timestamp_ntz('2016-12-31 00:12:00');
+-------------------------------------+
|to_timestamp_ntz(2016-12-31 00:12:00)|
+-------------------------------------+
| 2016-12-31 00:12:00|
+-------------------------------------+
SELECT to_timestamp_ntz('2016-12-31', 'yyyy-MM-dd');
+----------------------------------------+
|to_timestamp_ntz(2016-12-31, yyyy-MM-dd)|
+----------------------------------------+
| 2016-12-31 00:00:00|
+----------------------------------------+
-- to_unix_timestamp
SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd');
+-----------------------------------------+
|to_unix_timestamp(2016-04-08, yyyy-MM-dd)|
+-----------------------------------------+
| 1460041200|
+-----------------------------------------+
-- to_utc_timestamp
SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul');
+----------------------------------------+
|to_utc_timestamp(2016-08-31, Asia/Seoul)|
+----------------------------------------+
| 2016-08-30 15:00:00|
+----------------------------------------+
-- trunc
SELECT trunc('2019-08-04', 'week');
+-----------------------+
|trunc(2019-08-04, week)|
+-----------------------+
| 2019-07-29|
+-----------------------+
SELECT trunc('2019-08-04', 'quarter');
+--------------------------+
|trunc(2019-08-04, quarter)|
+--------------------------+
| 2019-07-01|
+--------------------------+
SELECT trunc('2009-02-12', 'MM');
+---------------------+
|trunc(2009-02-12, MM)|
+---------------------+
| 2009-02-01|
+---------------------+
SELECT trunc('2015-10-27', 'YEAR');
+-----------------------+
|trunc(2015-10-27, YEAR)|
+-----------------------+
| 2015-01-01|
+-----------------------+
-- try_to_timestamp
SELECT try_to_timestamp('2016-12-31 00:12:00');
+-------------------------------------+
|try_to_timestamp(2016-12-31 00:12:00)|
+-------------------------------------+
| 2016-12-31 00:12:00|
+-------------------------------------+
SELECT try_to_timestamp('2016-12-31', 'yyyy-MM-dd');
+----------------------------------------+
|try_to_timestamp(2016-12-31, yyyy-MM-dd)|
+----------------------------------------+
| 2016-12-31 00:00:00|
+----------------------------------------+
SELECT try_to_timestamp('foo', 'yyyy-MM-dd');
+---------------------------------+
|try_to_timestamp(foo, yyyy-MM-dd)|
+---------------------------------+
| NULL|
+---------------------------------+
-- unix_date
SELECT unix_date(DATE("1970-01-02"));
+---------------------+
|unix_date(1970-01-02)|
+---------------------+
| 1|
+---------------------+
-- unix_micros
SELECT unix_micros(TIMESTAMP('1970-01-01 00:00:01Z'));
+---------------------------------+
|unix_micros(1970-01-01 00:00:01Z)|
+---------------------------------+
| 1000000|
+---------------------------------+
-- unix_millis
SELECT unix_millis(TIMESTAMP('1970-01-01 00:00:01Z'));
+---------------------------------+
|unix_millis(1970-01-01 00:00:01Z)|
+---------------------------------+
| 1000|
+---------------------------------+
-- unix_seconds
SELECT unix_seconds(TIMESTAMP('1970-01-01 00:00:01Z'));
+----------------------------------+
|unix_seconds(1970-01-01 00:00:01Z)|
+----------------------------------+
| 1|
+----------------------------------+
-- unix_timestamp
SELECT unix_timestamp();
+--------------------------------------------------------+
|unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss)|
+--------------------------------------------------------+
| 1708760216|
+--------------------------------------------------------+
SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
+--------------------------------------+
|unix_timestamp(2016-04-08, yyyy-MM-dd)|
+--------------------------------------+
| 1460041200|
+--------------------------------------+
-- weekday
SELECT weekday('2009-07-30');
+-------------------+
|weekday(2009-07-30)|
+-------------------+
| 3|
+-------------------+
-- weekofyear
SELECT weekofyear('2008-02-20');
+----------------------+
|weekofyear(2008-02-20)|
+----------------------+
| 8|
+----------------------+
-- window
SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, start;
+---+-------------------+-------------------+---+
| a| start| end|cnt|
+---+-------------------+-------------------+---+
| A1|2021-01-01 00:00:00|2021-01-01 00:05:00| 2|
| A1|2021-01-01 00:05:00|2021-01-01 00:10:00| 1|
| A2|2021-01-01 00:00:00|2021-01-01 00:05:00| 1|
+---+-------------------+-------------------+---+
SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '10 minutes', '5 minutes') ORDER BY a, start;
+---+-------------------+-------------------+---+
| a| start| end|cnt|
+---+-------------------+-------------------+---+
| A1|2020-12-31 23:55:00|2021-01-01 00:05:00| 2|
| A1|2021-01-01 00:00:00|2021-01-01 00:10:00| 3|
| A1|2021-01-01 00:05:00|2021-01-01 00:15:00| 1|
| A2|2020-12-31 23:55:00|2021-01-01 00:05:00| 1|
| A2|2021-01-01 00:00:00|2021-01-01 00:10:00| 1|
+---+-------------------+-------------------+---+
-- window_time
SELECT a, window.start as start, window.end as end, window_time(window), cnt FROM (SELECT a, window, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, window.start);
+---+-------------------+-------------------+--------------------+---+
| a| start| end| window_time(window)|cnt|
+---+-------------------+-------------------+--------------------+---+
| A1|2021-01-01 00:00:00|2021-01-01 00:05:00|2021-01-01 00:04:...| 2|
| A1|2021-01-01 00:05:00|2021-01-01 00:10:00|2021-01-01 00:09:...| 1|
| A2|2021-01-01 00:00:00|2021-01-01 00:05:00|2021-01-01 00:04:...| 1|
+---+-------------------+-------------------+--------------------+---+
-- year
SELECT year('2016-07-30');
+----------------+
|year(2016-07-30)|
+----------------+
| 2016|
+----------------+
JSON 函数
| 函数 | 描述 |
|---|---|
| from_json(jsonStr, schema[, options]) | 返回具有给定 `jsonStr` 和 `schema` 的结构值。 |
| get_json_object(json_txt, path) | 从 `path` 中提取 JSON 对象。 |
| json_array_length(jsonArray) | 返回最外层 JSON 数组中的元素数。 |
| json_object_keys(json_object) | 以数组形式返回最外层 JSON 对象的所有键。 |
| json_tuple(jsonStr, p1, p2, ..., pn) | 返回类似于 get_json_object 函数的元组，但它采用多个名称。所有输入参数和输出列类型均为字符串。 |
| schema_of_json(json[, options]) | 以 DDL 格式返回 JSON 字符串的架构。 |
| to_json(expr[, options]) | 返回具有给定结构值的 JSON 字符串 |
示例
-- from_json
SELECT from_json('{"a":1, "b":0.8}', 'a INT, b DOUBLE');
+---------------------------+
|from_json({"a":1, "b":0.8})|
+---------------------------+
| {1, 0.8}|
+---------------------------+
SELECT from_json('{"time":"26/08/2015"}', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
+--------------------------------+
|from_json({"time":"26/08/2015"})|
+--------------------------------+
| {2015-08-26 00:00...|
+--------------------------------+
SELECT from_json('{"teacher": "Alice", "student": [{"name": "Bob", "rank": 1}, {"name": "Charlie", "rank": 2}]}', 'STRUCT<teacher: STRING, student: ARRAY<STRUCT<name: STRING, rank: INT>>>');
+--------------------------------------------------------------------------------------------------------+
|from_json({"teacher": "Alice", "student": [{"name": "Bob", "rank": 1}, {"name": "Charlie", "rank": 2}]})|
+--------------------------------------------------------------------------------------------------------+
| {Alice, [{Bob, 1}...|
+--------------------------------------------------------------------------------------------------------+
-- get_json_object
SELECT get_json_object('{"a":"b"}', '$.a');
+-------------------------------+
|get_json_object({"a":"b"}, $.a)|
+-------------------------------+
| b|
+-------------------------------+
-- json_array_length
SELECT json_array_length('[1,2,3,4]');
+----------------------------+
|json_array_length([1,2,3,4])|
+----------------------------+
| 4|
+----------------------------+
SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
+------------------------------------------------+
|json_array_length([1,2,3,{"f1":1,"f2":[5,6]},4])|
+------------------------------------------------+
| 5|
+------------------------------------------------+
SELECT json_array_length('[1,2');
+-----------------------+
|json_array_length([1,2)|
+-----------------------+
| NULL|
+-----------------------+
-- json_object_keys
SELECT json_object_keys('{}');
+--------------------+
|json_object_keys({})|
+--------------------+
| []|
+--------------------+
SELECT json_object_keys('{"key": "value"}');
+----------------------------------+
|json_object_keys({"key": "value"})|
+----------------------------------+
| [key]|
+----------------------------------+
SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
+--------------------------------------------------------+
|json_object_keys({"f1":"abc","f2":{"f3":"a", "f4":"b"}})|
+--------------------------------------------------------+
| [f1, f2]|
+--------------------------------------------------------+
-- json_tuple
SELECT json_tuple('{"a":1, "b":2}', 'a', 'b');
+---+---+
| c0| c1|
+---+---+
| 1| 2|
+---+---+
-- schema_of_json
SELECT schema_of_json('[{"col":0}]');
+---------------------------+
|schema_of_json([{"col":0}])|
+---------------------------+
| ARRAY<STRUCT<col:...|
+---------------------------+
SELECT schema_of_json('[{"col":01}]', map('allowNumericLeadingZeros', 'true'));
+----------------------------+
|schema_of_json([{"col":01}])|
+----------------------------+
| ARRAY<STRUCT<col:...|
+----------------------------+
-- to_json
SELECT to_json(named_struct('a', 1, 'b', 2));
+---------------------------------+
|to_json(named_struct(a, 1, b, 2))|
+---------------------------------+
| {"a":1,"b":2}|
+---------------------------------+
SELECT to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
+-----------------------------------------------------------------+
|to_json(named_struct(time, to_timestamp(2015-08-26, yyyy-MM-dd)))|
+-----------------------------------------------------------------+
| {"time":"26/08/20...|
+-----------------------------------------------------------------+
SELECT to_json(array(named_struct('a', 1, 'b', 2)));
+----------------------------------------+
|to_json(array(named_struct(a, 1, b, 2)))|
+----------------------------------------+
| [{"a":1,"b":2}]|
+----------------------------------------+
SELECT to_json(map('a', named_struct('b', 1)));
+-----------------------------------+
|to_json(map(a, named_struct(b, 1)))|
+-----------------------------------+
| {"a":{"b":1}}|
+-----------------------------------+
SELECT to_json(map(named_struct('a', 1),named_struct('b', 2)));
+----------------------------------------------------+
|to_json(map(named_struct(a, 1), named_struct(b, 2)))|
+----------------------------------------------------+
| {"[1]":{"b":2}}|
+----------------------------------------------------+
SELECT to_json(map('a', 1));
+------------------+
|to_json(map(a, 1))|
+------------------+
| {"a":1}|
+------------------+
SELECT to_json(array(map('a', 1)));
+-------------------------+
|to_json(array(map(a, 1)))|
+-------------------------+
| [{"a":1}]|
+-------------------------+
数学函数
| 函数 | 描述 |
|---|---|
| expr1 % expr2 | 返回 `expr1`/`expr2` 的余数。 |
| expr1 * expr2 | 返回 `expr1`*`expr2`。 |
| expr1 + expr2 | 返回 `expr1`+`expr2`。 |
| expr1 - expr2 | 返回 `expr1`-`expr2`。 |
| expr1 / expr2 | 返回 `expr1`/`expr2`。它始终执行浮点除法。 |
| abs(expr) | 返回数字或间隔值的绝对值。 |
| acos(expr) | 返回 `expr` 的反余弦（也称为弧余弦），如同 `java.lang.Math.acos` 计算的那样。 |
| acosh(expr) | 返回 `expr` 的反双曲余弦。 |
| asin(expr) | 返回 `expr` 的反正弦（也称为弧正弦），如同 `java.lang.Math.asin` 计算的那样。 |
| asinh(expr) | 返回 `expr` 的反双曲正弦。 |
| atan(expr) | 返回 `expr` 的反正切（也称为弧正切），如同 `java.lang.Math.atan` 计算的那样 |
| atan2(exprY, exprX) | 返回平面正 x 轴与坐标 (`exprX`, `exprY`) 给出的点之间的弧度角，如同 `java.lang.Math.atan2` 计算的那样。 |
| atanh(expr) | 返回 `expr` 的反双曲正切。 |
| bin(expr) | 返回以二进制表示的长整型值 `expr` 的字符串表示形式。 |
| bround(expr, d) | 使用 HALF_EVEN 舍入模式返回舍入到 `d` 位小数的 `expr`。 |
| cbrt(expr) | 返回 `expr` 的立方根。 |
| ceil(expr[, scale]) | 返回向上舍入后不小于 `expr` 的最小数字。可以指定可选的 `scale` 参数来控制舍入行为。 |
| ceiling(expr[, scale]) | 返回向上舍入后不小于 `expr` 的最小数字。可以指定可选的 `scale` 参数来控制舍入行为。 |
| conv(num, from_base, to_base) | 将 `num` 从 `from_base` 转换为 `to_base`。 |
| cos(expr) | 返回 `expr` 的余弦，如同 `java.lang.Math.cos` 计算的那样。 |
| cosh(expr) | 返回 `expr` 的双曲余弦，如同 `java.lang.Math.cosh` 计算的那样。 |
| cot(expr) | 返回 `expr` 的余切，如同 `1/java.lang.Math.tan` 计算的那样。 |
| csc(expr) | 返回 `expr` 的余割，如同 `1/java.lang.Math.sin` 计算的那样。 |
| degrees(expr) | 将弧度转换为度。 |
| expr1 div expr2 | 将 `expr1` 除以 `expr2`。如果操作数为 NULL 或 `expr2` 为 0，则返回 NULL。结果将强制转换为长整型。 |
| e() | 返回欧拉数 e。 |
| exp(expr) | 返回 e 的 `expr` 次幂。 |
| expm1(expr) - 返回 exp(`expr`) | 1. |
| factorial(expr) | 返回 `expr` 的阶乘。`expr` 为 [0..20]。否则，为 null。 |
| floor(expr[, scale]) | 返回向下舍入后不大于 `expr` 的最大数字。可以指定可选的 `scale` 参数来控制舍入行为。 |
| greatest(expr, ...) | 返回所有参数中的最大值，跳过 null 值。 |
| hex(expr) | 将 `expr` 转换为十六进制。 |
| hypot(expr1, expr2) | 返回 sqrt(`expr1`**2 + `expr2`**2)。 |
| least(expr, ...) | 返回所有参数中的最小值，跳过 null 值。 |
| ln(expr) | 返回 `expr` 的自然对数（以 e 为底）。 |
| log(base, expr) | 返回以 `base` 为底的 `expr` 的对数。 |
| log10(expr) | 返回以 10 为底的 `expr` 的对数。 |
| log1p(expr) | 返回 log(1 + `expr`)。 |
| log2(expr) | 返回以 2 为底的 `expr` 的对数。 |
| expr1 mod expr2 | 返回 `expr1`/`expr2` 的余数。 |
| negative(expr) | 返回 `expr` 的负值。 |
| pi() | 返回圆周率 pi。 |
| pmod(expr1, expr2) | 返回 `expr1` mod `expr2` 的正值。 |
| positive(expr) | 返回 `expr` 的值。 |
| pow(expr1, expr2) | 返回 `expr1` 的 `expr2` 次幂。 |
| power(expr1, expr2) | 返回 `expr1` 的 `expr2` 次幂。 |
| radians(expr) | 将度转换为弧度。 |
| rand([seed]) | 返回一个随机值，该值在 [0, 1) 中具有独立且均匀分布 (i.i.d.) 的值。 |
| randn([seed]) | 返回一个随机值，该值具有从标准正态分布中提取的独立且均匀分布 (i.i.d.) 的值。 |
| random([seed]) | 返回一个随机值，该值在 [0, 1) 中具有独立且均匀分布 (i.i.d.) 的值。 |
| rint(expr) | 返回与参数值最接近且等于数学整数的双精度值。 |
| round(expr, d) | 使用 HALF_UP 舍入模式返回舍入到 `d` 位小数的 `expr`。 |
| sec(expr) | 返回 `expr` 的正割，如同 `1/java.lang.Math.cos` 计算的那样。 |
| shiftleft(base, expr) | 按位左移。 |
| sign(expr) | 当 `expr` 为负数、0 或正数时，分别返回 -1.0、0.0 或 1.0。 |
| signum(expr) | 当 `expr` 为负数、0 或正数时，分别返回 -1.0、0.0 或 1.0。 |
| sin(expr) | 返回 `expr` 的正弦，如同 `java.lang.Math.sin` 计算的那样。 |
| sinh(expr) | 返回 `expr` 的双曲正弦值，如同使用 `java.lang.Math.sinh` 计算一样。 |
| sqrt(expr) | 返回 `expr` 的平方根。 |
| tan(expr) | 返回 `expr` 的正切值，如同使用 `java.lang.Math.tan` 计算一样。 |
| tanh(expr) | 返回 `expr` 的双曲正切值，如同使用 `java.lang.Math.tanh` 计算一样。 |
| try_add(expr1, expr2) | 返回 `expr1` 和 `expr2` 的和，如果发生溢出，则结果为 null。可接受的输入类型与 `+` 运算符相同。 |
| try_divide(dividend, divisor) | 返回 `dividend`/`divisor`。它始终执行浮点除法。如果 `expr2` 为 0，则其结果始终为 null。`dividend` 必须是数字或区间。`divisor` 必须是数字。 |
| try_multiply(expr1, expr2) | 返回 `expr1`*`expr2`，如果发生溢出，则结果为 null。可接受的输入类型与 `*` 运算符相同。 |
| try_subtract(expr1, expr2) | 返回 `expr1`-`expr2`，如果发生溢出，则结果为 null。可接受的输入类型与 `-` 运算符相同。 |
| unhex(expr) | 将十六进制 `expr` 转换为二进制。 |
| width_bucket(value, min_value, max_value, num_bucket) | 返回在 `min_value` 到 `max_value` 范围内，具有 `num_bucket` 个桶的等宽直方图中，`value` 将被分配到的桶编号。 |
示例
-- %
SELECT 2 % 1.8;
+---------+
|(2 % 1.8)|
+---------+
| 0.2|
+---------+
SELECT MOD(2, 1.8);
+-----------+
|mod(2, 1.8)|
+-----------+
| 0.2|
+-----------+
-- *
SELECT 2 * 3;
+-------+
|(2 * 3)|
+-------+
| 6|
+-------+
-- +
SELECT 1 + 2;
+-------+
|(1 + 2)|
+-------+
| 3|
+-------+
-- -
SELECT 2 - 1;
+-------+
|(2 - 1)|
+-------+
| 1|
+-------+
-- /
SELECT 3 / 2;
+-------+
|(3 / 2)|
+-------+
| 1.5|
+-------+
SELECT 2L / 2L;
+-------+
|(2 / 2)|
+-------+
| 1.0|
+-------+
-- abs
SELECT abs(-1);
+-------+
|abs(-1)|
+-------+
| 1|
+-------+
SELECT abs(INTERVAL -'1-1' YEAR TO MONTH);
+----------------------------------+
|abs(INTERVAL '-1-1' YEAR TO MONTH)|
+----------------------------------+
| INTERVAL '1-1' YE...|
+----------------------------------+
-- acos
SELECT acos(1);
+-------+
|ACOS(1)|
+-------+
| 0.0|
+-------+
SELECT acos(2);
+-------+
|ACOS(2)|
+-------+
| NaN|
+-------+
-- acosh
SELECT acosh(1);
+--------+
|ACOSH(1)|
+--------+
| 0.0|
+--------+
SELECT acosh(0);
+--------+
|ACOSH(0)|
+--------+
| NaN|
+--------+
-- asin
SELECT asin(0);
+-------+
|ASIN(0)|
+-------+
| 0.0|
+-------+
SELECT asin(2);
+-------+
|ASIN(2)|
+-------+
| NaN|
+-------+
-- asinh
SELECT asinh(0);
+--------+
|ASINH(0)|
+--------+
| 0.0|
+--------+
-- atan
SELECT atan(0);
+-------+
|ATAN(0)|
+-------+
| 0.0|
+-------+
-- atan2
SELECT atan2(0, 0);
+-----------+
|ATAN2(0, 0)|
+-----------+
| 0.0|
+-----------+
-- atanh
SELECT atanh(0);
+--------+
|ATANH(0)|
+--------+
| 0.0|
+--------+
SELECT atanh(2);
+--------+
|ATANH(2)|
+--------+
| NaN|
+--------+
-- bin
SELECT bin(13);
+-------+
|bin(13)|
+-------+
| 1101|
+-------+
SELECT bin(-13);
+--------------------+
| bin(-13)|
+--------------------+
|11111111111111111...|
+--------------------+
SELECT bin(13.3);
+---------+
|bin(13.3)|
+---------+
| 1101|
+---------+
-- bround
SELECT bround(2.5, 0);
+--------------+
|bround(2.5, 0)|
+--------------+
| 2|
+--------------+
SELECT bround(25, -1);
+--------------+
|bround(25, -1)|
+--------------+
| 20|
+--------------+
-- cbrt
SELECT cbrt(27.0);
+----------+
|CBRT(27.0)|
+----------+
| 3.0|
+----------+
-- ceil
SELECT ceil(-0.1);
+----------+
|CEIL(-0.1)|
+----------+
| 0|
+----------+
SELECT ceil(5);
+-------+
|CEIL(5)|
+-------+
| 5|
+-------+
SELECT ceil(3.1411, 3);
+---------------+
|ceil(3.1411, 3)|
+---------------+
| 3.142|
+---------------+
SELECT ceil(3.1411, -3);
+----------------+
|ceil(3.1411, -3)|
+----------------+
| 1000|
+----------------+
-- ceiling
SELECT ceiling(-0.1);
+-------------+
|ceiling(-0.1)|
+-------------+
| 0|
+-------------+
SELECT ceiling(5);
+----------+
|ceiling(5)|
+----------+
| 5|
+----------+
SELECT ceiling(3.1411, 3);
+------------------+
|ceiling(3.1411, 3)|
+------------------+
| 3.142|
+------------------+
SELECT ceiling(3.1411, -3);
+-------------------+
|ceiling(3.1411, -3)|
+-------------------+
| 1000|
+-------------------+
-- conv
SELECT conv('100', 2, 10);
+----------------+
|conv(100, 2, 10)|
+----------------+
| 4|
+----------------+
SELECT conv(-10, 16, -10);
+------------------+
|conv(-10, 16, -10)|
+------------------+
| -16|
+------------------+
-- cos
SELECT cos(0);
+------+
|COS(0)|
+------+
| 1.0|
+------+
-- cosh
SELECT cosh(0);
+-------+
|COSH(0)|
+-------+
| 1.0|
+-------+
-- cot
SELECT cot(1);
+------------------+
| COT(1)|
+------------------+
|0.6420926159343306|
+------------------+
-- csc
SELECT csc(1);
+------------------+
| CSC(1)|
+------------------+
|1.1883951057781212|
+------------------+
-- degrees
SELECT degrees(3.141592653589793);
+--------------------------+
|DEGREES(3.141592653589793)|
+--------------------------+
| 180.0|
+--------------------------+
-- div
SELECT 3 div 2;
+---------+
|(3 div 2)|
+---------+
| 1|
+---------+
SELECT INTERVAL '1-1' YEAR TO MONTH div INTERVAL '-1' MONTH;
+------------------------------------------------------+
|(INTERVAL '1-1' YEAR TO MONTH div INTERVAL '-1' MONTH)|
+------------------------------------------------------+
| -13|
+------------------------------------------------------+
-- e
SELECT e();
+-----------------+
| E()|
+-----------------+
|2.718281828459045|
+-----------------+
-- exp
SELECT exp(0);
+------+
|EXP(0)|
+------+
| 1.0|
+------+
-- expm1
SELECT expm1(0);
+--------+
|EXPM1(0)|
+--------+
| 0.0|
+--------+
-- factorial
SELECT factorial(5);
+------------+
|factorial(5)|
+------------+
| 120|
+------------+
-- floor
SELECT floor(-0.1);
+-----------+
|FLOOR(-0.1)|
+-----------+
| -1|
+-----------+
SELECT floor(5);
+--------+
|FLOOR(5)|
+--------+
| 5|
+--------+
SELECT floor(3.1411, 3);
+----------------+
|floor(3.1411, 3)|
+----------------+
| 3.141|
+----------------+
SELECT floor(3.1411, -3);
+-----------------+
|floor(3.1411, -3)|
+-----------------+
| 0|
+-----------------+
-- greatest
SELECT greatest(10, 9, 2, 4, 3);
+------------------------+
|greatest(10, 9, 2, 4, 3)|
+------------------------+
| 10|
+------------------------+
-- hex
SELECT hex(17);
+-------+
|hex(17)|
+-------+
| 11|
+-------+
SELECT hex('Spark SQL');
+------------------+
| hex(Spark SQL)|
+------------------+
|537061726B2053514C|
+------------------+
-- hypot
SELECT hypot(3, 4);
+-----------+
|HYPOT(3, 4)|
+-----------+
| 5.0|
+-----------+
-- least
SELECT least(10, 9, 2, 4, 3);
+---------------------+
|least(10, 9, 2, 4, 3)|
+---------------------+
| 2|
+---------------------+
-- ln
SELECT ln(1);
+-----+
|ln(1)|
+-----+
| 0.0|
+-----+
-- log
SELECT log(10, 100);
+------------+
|LOG(10, 100)|
+------------+
| 2.0|
+------------+
-- log10
SELECT log10(10);
+---------+
|LOG10(10)|
+---------+
| 1.0|
+---------+
-- log1p
SELECT log1p(0);
+--------+
|LOG1P(0)|
+--------+
| 0.0|
+--------+
-- log2
SELECT log2(2);
+-------+
|LOG2(2)|
+-------+
| 1.0|
+-------+
-- mod
SELECT 2 % 1.8;
+---------+
|(2 % 1.8)|
+---------+
| 0.2|
+---------+
SELECT MOD(2, 1.8);
+-----------+
|mod(2, 1.8)|
+-----------+
| 0.2|
+-----------+
-- negative
SELECT negative(1);
+-----------+
|negative(1)|
+-----------+
| -1|
+-----------+
-- pi
SELECT pi();
+-----------------+
| PI()|
+-----------------+
|3.141592653589793|
+-----------------+
-- pmod
SELECT pmod(10, 3);
+-----------+
|pmod(10, 3)|
+-----------+
| 1|
+-----------+
SELECT pmod(-10, 3);
+------------+
|pmod(-10, 3)|
+------------+
| 2|
+------------+
-- positive
SELECT positive(1);
+-----+
|(+ 1)|
+-----+
| 1|
+-----+
-- pow
SELECT pow(2, 3);
+---------+
|pow(2, 3)|
+---------+
| 8.0|
+---------+
-- power
SELECT power(2, 3);
+-----------+
|POWER(2, 3)|
+-----------+
| 8.0|
+-----------+
-- radians
SELECT radians(180);
+-----------------+
| RADIANS(180)|
+-----------------+
|3.141592653589793|
+-----------------+
-- rand
SELECT rand();
+------------------+
| rand()|
+------------------+
|0.7211420708112387|
+------------------+
SELECT rand(0);
+------------------+
| rand(0)|
+------------------+
|0.7604953758285915|
+------------------+
SELECT rand(null);
+------------------+
| rand(NULL)|
+------------------+
|0.7604953758285915|
+------------------+
-- randn
SELECT randn();
+-------------------+
| randn()|
+-------------------+
|-0.8175603217732732|
+-------------------+
SELECT randn(0);
+------------------+
| randn(0)|
+------------------+
|1.6034991609278433|
+------------------+
SELECT randn(null);
+------------------+
| randn(NULL)|
+------------------+
|1.6034991609278433|
+------------------+
-- random
SELECT random();
+-----------------+
| rand()|
+-----------------+
|0.394205008255365|
+-----------------+
SELECT random(0);
+------------------+
| rand(0)|
+------------------+
|0.7604953758285915|
+------------------+
SELECT random(null);
+------------------+
| rand(NULL)|
+------------------+
|0.7604953758285915|
+------------------+
-- rint
SELECT rint(12.3456);
+-------------+
|rint(12.3456)|
+-------------+
| 12.0|
+-------------+
-- round
SELECT round(2.5, 0);
+-------------+
|round(2.5, 0)|
+-------------+
| 3|
+-------------+
-- sec
SELECT sec(0);
+------+
|SEC(0)|
+------+
| 1.0|
+------+
-- shiftleft
SELECT shiftleft(2, 1);
+---------------+
|shiftleft(2, 1)|
+---------------+
| 4|
+---------------+
-- sign
SELECT sign(40);
+--------+
|sign(40)|
+--------+
| 1.0|
+--------+
SELECT sign(INTERVAL -'100' YEAR);
+--------------------------+
|sign(INTERVAL '-100' YEAR)|
+--------------------------+
| -1.0|
+--------------------------+
-- signum
SELECT signum(40);
+----------+
|SIGNUM(40)|
+----------+
| 1.0|
+----------+
SELECT signum(INTERVAL -'100' YEAR);
+----------------------------+
|SIGNUM(INTERVAL '-100' YEAR)|
+----------------------------+
| -1.0|
+----------------------------+
-- sin
SELECT sin(0);
+------+
|SIN(0)|
+------+
| 0.0|
+------+
-- sinh
SELECT sinh(0);
+-------+
|SINH(0)|
+-------+
| 0.0|
+-------+
-- sqrt
SELECT sqrt(4);
+-------+
|SQRT(4)|
+-------+
| 2.0|
+-------+
-- tan
SELECT tan(0);
+------+
|TAN(0)|
+------+
| 0.0|
+------+
-- tanh
SELECT tanh(0);
+-------+
|TANH(0)|
+-------+
| 0.0|
+-------+
-- try_add
SELECT try_add(1, 2);
+-------------+
|try_add(1, 2)|
+-------------+
| 3|
+-------------+
SELECT try_add(2147483647, 1);
+----------------------+
|try_add(2147483647, 1)|
+----------------------+
| NULL|
+----------------------+
SELECT try_add(date'2021-01-01', 1);
+-----------------------------+
|try_add(DATE '2021-01-01', 1)|
+-----------------------------+
| 2021-01-02|
+-----------------------------+
SELECT try_add(date'2021-01-01', interval 1 year);
+---------------------------------------------+
|try_add(DATE '2021-01-01', INTERVAL '1' YEAR)|
+---------------------------------------------+
| 2022-01-01|
+---------------------------------------------+
SELECT try_add(timestamp'2021-01-01 00:00:00', interval 1 day);
+----------------------------------------------------------+
|try_add(TIMESTAMP '2021-01-01 00:00:00', INTERVAL '1' DAY)|
+----------------------------------------------------------+
| 2021-01-02 00:00:00|
+----------------------------------------------------------+
SELECT try_add(interval 1 year, interval 2 year);
+---------------------------------------------+
|try_add(INTERVAL '1' YEAR, INTERVAL '2' YEAR)|
+---------------------------------------------+
| INTERVAL '3' YEAR|
+---------------------------------------------+
-- try_divide
SELECT try_divide(3, 2);
+----------------+
|try_divide(3, 2)|
+----------------+
| 1.5|
+----------------+
SELECT try_divide(2L, 2L);
+----------------+
|try_divide(2, 2)|
+----------------+
| 1.0|
+----------------+
SELECT try_divide(1, 0);
+----------------+
|try_divide(1, 0)|
+----------------+
| NULL|
+----------------+
SELECT try_divide(interval 2 month, 2);
+---------------------------------+
|try_divide(INTERVAL '2' MONTH, 2)|
+---------------------------------+
| INTERVAL '0-1' YE...|
+---------------------------------+
SELECT try_divide(interval 2 month, 0);
+---------------------------------+
|try_divide(INTERVAL '2' MONTH, 0)|
+---------------------------------+
| NULL|
+---------------------------------+
-- try_multiply
SELECT try_multiply(2, 3);
+------------------+
|try_multiply(2, 3)|
+------------------+
| 6|
+------------------+
SELECT try_multiply(-2147483648, 10);
+-----------------------------+
|try_multiply(-2147483648, 10)|
+-----------------------------+
| NULL|
+-----------------------------+
SELECT try_multiply(interval 2 year, 3);
+----------------------------------+
|try_multiply(INTERVAL '2' YEAR, 3)|
+----------------------------------+
| INTERVAL '6-0' YE...|
+----------------------------------+
-- try_subtract
SELECT try_subtract(2, 1);
+------------------+
|try_subtract(2, 1)|
+------------------+
| 1|
+------------------+
SELECT try_subtract(-2147483648, 1);
+----------------------------+
|try_subtract(-2147483648, 1)|
+----------------------------+
| NULL|
+----------------------------+
SELECT try_subtract(date'2021-01-02', 1);
+----------------------------------+
|try_subtract(DATE '2021-01-02', 1)|
+----------------------------------+
| 2021-01-01|
+----------------------------------+
SELECT try_subtract(date'2021-01-01', interval 1 year);
+--------------------------------------------------+
|try_subtract(DATE '2021-01-01', INTERVAL '1' YEAR)|
+--------------------------------------------------+
| 2020-01-01|
+--------------------------------------------------+
SELECT try_subtract(timestamp'2021-01-02 00:00:00', interval 1 day);
+---------------------------------------------------------------+
|try_subtract(TIMESTAMP '2021-01-02 00:00:00', INTERVAL '1' DAY)|
+---------------------------------------------------------------+
| 2021-01-01 00:00:00|
+---------------------------------------------------------------+
SELECT try_subtract(interval 2 year, interval 1 year);
+--------------------------------------------------+
|try_subtract(INTERVAL '2' YEAR, INTERVAL '1' YEAR)|
+--------------------------------------------------+
| INTERVAL '1' YEAR|
+--------------------------------------------------+
-- unhex
SELECT decode(unhex('537061726B2053514C'), 'UTF-8');
+----------------------------------------+
|decode(unhex(537061726B2053514C), UTF-8)|
+----------------------------------------+
| Spark SQL|
+----------------------------------------+
-- width_bucket
SELECT width_bucket(5.3, 0.2, 10.6, 5);
+-------------------------------+
|width_bucket(5.3, 0.2, 10.6, 5)|
+-------------------------------+
| 3|
+-------------------------------+
SELECT width_bucket(-2.1, 1.3, 3.4, 3);
+-------------------------------+
|width_bucket(-2.1, 1.3, 3.4, 3)|
+-------------------------------+
| 0|
+-------------------------------+
SELECT width_bucket(8.1, 0.0, 5.7, 4);
+------------------------------+
|width_bucket(8.1, 0.0, 5.7, 4)|
+------------------------------+
| 5|
+------------------------------+
SELECT width_bucket(-0.9, 5.2, 0.5, 2);
+-------------------------------+
|width_bucket(-0.9, 5.2, 0.5, 2)|
+-------------------------------+
| 3|
+-------------------------------+
SELECT width_bucket(INTERVAL '0' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
+--------------------------------------------------------------------------+
|width_bucket(INTERVAL '0' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10)|
+--------------------------------------------------------------------------+
| 1|
+--------------------------------------------------------------------------+
SELECT width_bucket(INTERVAL '1' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
+--------------------------------------------------------------------------+
|width_bucket(INTERVAL '1' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10)|
+--------------------------------------------------------------------------+
| 2|
+--------------------------------------------------------------------------+
SELECT width_bucket(INTERVAL '0' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
+-----------------------------------------------------------------------+
|width_bucket(INTERVAL '0' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10)|
+-----------------------------------------------------------------------+
| 1|
+-----------------------------------------------------------------------+
SELECT width_bucket(INTERVAL '1' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
+-----------------------------------------------------------------------+
|width_bucket(INTERVAL '1' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10)|
+-----------------------------------------------------------------------+
| 2|
+-----------------------------------------------------------------------+
字符串函数
| 函数 | 描述 |
|---|---|
| ascii(str) | 返回 `str` 的第一个字符的数值。 |
| base64(bin) | 将参数从二进制 `bin` 转换为 base 64 字符串。 |
| bit_length(expr) | 返回字符串数据的位长度或二进制数据的位数。 |
| btrim(str) | 删除 `str` 中开头和结尾的空格字符。 |
| btrim(str, trimStr) | 删除 `str` 中开头和结尾的 `trimStr` 字符。 |
| char(expr) | 返回二进制等效于 `expr` 的 ASCII 字符。如果 n 大于 256，则结果等效于 chr(n % 256) |
| char_length(expr) | 返回字符串数据的字符长度或二进制数据的字节数。字符串数据的长度包括尾随空格。二进制数据的长度包括二进制零。 |
| character_length(expr) | 返回字符串数据的字符长度或二进制数据的字节数。字符串数据的长度包括尾随空格。二进制数据的长度包括二进制零。 |
| chr(expr) | 返回二进制等效于 `expr` 的 ASCII 字符。如果 n 大于 256，则结果等效于 chr(n % 256) |
| concat_ws(sep[, str | array(str)]+) | 返回由 `sep` 分隔的字符串的串联，跳过空值。 |
| contains(left, right) | 返回一个布尔值。如果在 left 中找到 right，则值为 True。如果任一输入表达式为 NULL，则返回 NULL。否则，返回 False。left 或 right 都必须是 STRING 或 BINARY 类型。 |
| decode(bin, charset) | 使用第二个参数字符集解码第一个参数。 |
| decode(expr, search, result [, search, result ] ... [, default]) | 按顺序将 expr 与每个搜索值进行比较。如果 expr 等于某个搜索值，则 decode 返回相应的 result。如果没有找到匹配项，则返回 default。如果省略 default，则返回 null。 |
| elt(n, input1, input2, ...) | 返回第 `n` 个输入，例如，当 `n` 为 2 时返回 `input2`。如果索引超过数组的长度并且 `spark.sql.ansi.enabled` 设置为 false，则该函数返回 NULL。如果 `spark.sql.ansi.enabled` 设置为 true，则对于无效索引，它会抛出 ArrayIndexOutOfBoundsException。 |
| encode(str, charset) | 使用第二个参数字符集编码第一个参数。 |
| endswith(left, right) | 返回一个布尔值。如果 left 以 right 结尾，则值为 True。如果任一输入表达式为 NULL，则返回 NULL。否则，返回 False。left 或 right 都必须是 STRING 或 BINARY 类型。 |
| find_in_set(str, str_array) | 返回逗号分隔列表 (`str_array`) 中给定字符串 (`str`) 的索引（从 1 开始）。如果未找到字符串或给定字符串 (`str`) 包含逗号，则返回 0。 |
| format_number(expr1, expr2) | 将数字 `expr1` 格式化为 '#,###,###.##'，四舍五入到 `expr2` 位小数。如果 `expr2` 为 0，则结果没有小数点或小数部分。`expr2` 也接受用户指定的格式。这应该像 MySQL 的 FORMAT 函数一样工作。 |
| format_string(strfmt, obj, ...) | 从 printf 风格的格式字符串返回格式化的字符串。 |
| initcap(str) | 返回 `str`，每个单词的首字母大写。所有其他字母均为小写。单词由空格分隔。 |
| instr(str, substr) | 返回 `substr` 在 `str` 中第一次出现的索引（从 1 开始）。 |
| lcase(str) | 返回 `str`，所有字符都转换为小写。 |
| left(str, len) | 返回字符串 `str` 中最左边的 `len` 个字符（`len` 可以是字符串类型），如果 `len` 小于或等于 0，则结果为空字符串。 |
| len(expr) | 返回字符串数据的字符长度或二进制数据的字节数。字符串数据的长度包括尾随空格。二进制数据的长度包括二进制零。 |
| length(expr) | 返回字符串数据的字符长度或二进制数据的字节数。字符串数据的长度包括尾随空格。二进制数据的长度包括二进制零。 |
| levenshtein(str1, str2[, threshold]) | 返回两个给定字符串之间的莱文斯坦距离。如果设置了 threshold 并且距离超过它，则返回 -1。 |
| locate(substr, str[, pos]) | 返回 `substr` 在 `str` 中位置 `pos` 之后第一次出现的位置。给定的 `pos` 和返回值都是从 1 开始的。 |
| lower(str) | 返回 `str`，所有字符都转换为小写。 |
| lpad(str, len[, pad]) | 返回 `str`，在左侧用 `pad` 填充到 `len` 的长度。如果 `str` 比 `len` 长，则返回值将缩短为 `len` 个字符或字节。如果未指定 `pad`，则如果 `str` 是字符串，则将在左侧用空格字符填充，如果是字节序列，则用零填充。 |
| ltrim(str) | 删除 `str` 中开头的空格字符。 |
| luhn_check(str ) | 根据 Luhn 算法检查数字字符串是否有效。此校验和函数广泛应用于信用卡号和政府识别号，以区分有效号码和错误输入的号码。 |
| mask(input[, upperChar, lowerChar, digitChar, otherChar]) | 屏蔽给定的字符串值。该函数将字符替换为“X”或“x”，将数字替换为“n”。这对于创建删除了敏感信息的表副本很有用。 |
| octet_length(expr) | 返回字符串数据的字节长度或二进制数据的字节数。 |
| overlay(input, replace, pos[, len]) | 将 `input` 中从 `pos` 开始且长度为 `len` 的部分替换为 `replace`。 |
| position(substr, str[, pos]) | 返回 `substr` 在 `str` 中位置 `pos` 之后第一次出现的位置。给定的 `pos` 和返回值都是从 1 开始的。 |
| printf(strfmt, obj, ...) | 从 printf 风格的格式字符串返回格式化的字符串。 |
| regexp_count(str, regexp) | 返回正则表达式模式 `regexp` 在字符串 `str` 中匹配的次数。 |
| regexp_extract(str, regexp[, idx]) | 提取 `str` 中与 `regexp` 表达式匹配的第一个字符串，并对应于正则表达式组索引。 |
| regexp_extract_all(str, regexp[, idx]) | 提取 `str` 中与 `regexp` 表达式匹配的所有字符串，并对应于正则表达式组索引。 |
| regexp_instr(str, regexp) | 在字符串中搜索正则表达式，并返回一个整数，该整数指示匹配子字符串的开始位置。位置从 1 开始，而不是从 0 开始。如果没有找到匹配项，则返回 0。 |
| regexp_replace(str, regexp, rep[, position]) | 将 `str` 中与 `regexp` 匹配的所有子字符串替换为 `rep`。 |
| regexp_substr(str, regexp) | 返回与字符串 `str` 中的正则表达式 `regexp` 匹配的子字符串。如果没有找到正则表达式，则结果为 null。 |
| repeat(str, n) | 返回重复给定字符串值 n 次的字符串。 |
| replace(str, search[, replace]) | 将所有出现的 `search` 替换为 `replace`。 |
| right(str, len) | 返回字符串 `str` 中最右边的 `len` 个字符（`len` 可以是字符串类型），如果 `len` 小于或等于 0，则结果为空字符串。 |
| rpad(str, len[, pad]) | 返回 `str`，在右侧用 `pad` 填充到 `len` 的长度。如果 `str` 比 `len` 长，则返回值将缩短为 `len` 个字符。如果未指定 `pad`，则如果 `str` 是字符串，则将在右侧用空格字符填充，如果是二进制字符串，则用零填充。 |
| rtrim(str) | 删除 `str` 中结尾的空格字符。 |
| sentences(str[, lang, country]) | 将 `str` 拆分为一个单词数组的数组。 |
| soundex(str) | 返回字符串的 Soundex 代码。 |
| space(n) | 返回一个由 `n` 个空格组成的字符串。 |
| split(str, regex, limit) | 围绕与 `regex` 匹配的出现位置拆分 `str`，并返回长度最多为 `limit` 的数组 |
| split_part(str, delimiter, partNum) | 按分隔符拆分 `str` 并返回拆分后的请求部分（从 1 开始）。如果任何输入为 null，则返回 null。如果 `partNum` 超出拆分部分的范围，则返回空字符串。如果 `partNum` 为 0，则抛出错误。如果 `partNum` 为负数，则从字符串的末尾开始倒计数。如果 `delimiter` 是空字符串，则不拆分 `str`。 |
| startswith(left, right) | 返回一个布尔值。如果 left 以 right 开头，则值为 True。如果任一输入表达式为 NULL，则返回 NULL。否则，返回 False。left 或 right 都必须是 STRING 或 BINARY 类型。 |
| substr(str, pos[, len]) | 返回 `str` 中从 `pos` 开始且长度为 `len` 的子字符串，或从 `pos` 开始且长度为 `len` 的字节数组切片。 |
| substr(str FROM pos[ FOR len]]) | 返回 `str` 中从 `pos` 开始且长度为 `len` 的子字符串，或从 `pos` 开始且长度为 `len` 的字节数组切片。 |
| substring(str, pos[, len]) | 返回 `str` 中从 `pos` 开始且长度为 `len` 的子字符串，或从 `pos` 开始且长度为 `len` 的字节数组切片。 |
| substring(str FROM pos[ FOR len]]) | 返回 `str` 中从 `pos` 开始且长度为 `len` 的子字符串，或从 `pos` 开始且长度为 `len` 的字节数组切片。 |
| substring_index(str, delim, count) | 返回 `str` 中 `count` 个分隔符 `delim` 之前的子字符串。如果 `count` 为正数，则返回最后一个分隔符（从左计数）左侧的所有内容。如果 `count` 为负数，则返回最后一个分隔符（从右计数）右侧的所有内容。函数 substring_index 在搜索 `delim` 时执行区分大小写的匹配。 |
| to_binary(str[, fmt]) | 根据提供的 `fmt` 将输入 `str` 转换为二进制值。`fmt` 可以是不区分大小写的字符串字面量“hex”、“utf-8”、“utf8”或“base64”。默认情况下，如果省略 `fmt`，则转换的二进制格式为“hex”。如果至少一个输入参数为 NULL，则该函数返回 NULL。 |
| to_char(numberExpr, formatExpr) | 根据 `formatExpr` 将 `numberExpr` 转换为字符串。如果转换失败，则抛出异常。格式可以包含以下字符（不区分大小写）：'0' 或 '9'：指定 0 到 9 之间的预期数字。格式字符串中的 0 或 9 序列与输入值中的数字序列匹配，生成的结果字符串长度与格式字符串中相应序列的长度相同。如果 0/9 序列包含的数字多于十进制值的匹配部分、以 0 开头且位于小数点之前，则结果字符串将在左侧填充零。否则，将使用空格填充。'.' 或 'D'：指定小数点的位置（可选，只允许使用一次）。',' 或 'G'：指定分组（千位）分隔符 (,) 的位置。每个分组分隔符的左侧和右侧必须有一个 0 或 9。'$'：指定 $ 货币符号的位置。此字符只能指定一次。'S' 或 'MI'：指定 '-' 或 '+' 符号的位置（可选，只允许在格式字符串的开头或结尾使用一次）。请注意，'S' 会为正值打印 '+'，但 'MI' 会打印空格。'PR'：只允许在格式字符串的末尾使用；指定如果输入值为负数，则结果字符串将用尖括号括起来。（'<1>'）。 |
| to_number(expr, fmt) | 根据字符串格式 `fmt` 将字符串 `expr` 转换为数字。如果转换失败，则抛出异常。格式可以包含以下字符（不区分大小写）：'0' 或 '9'：指定 0 到 9 之间的预期数字。格式字符串中的 0 或 9 序列与输入字符串中的数字序列匹配。如果 0/9 序列以 0 开头且位于小数点之前，则它只能匹配相同大小的数字序列。否则，如果序列以 9 开头或位于小数点之后，则它可以匹配大小相同或更小的数字序列。'.' 或 'D'：指定小数点的位置（可选，只允许使用一次）。',' 或 'G'：指定分组（千位）分隔符 (,) 的位置。每个分组分隔符的左侧和右侧必须有一个 0 或 9。`expr` 必须与数字大小相关的分组分隔符匹配。'$'：指定 $ 货币符号的位置。此字符只能指定一次。'S' 或 'MI'：指定 '-' 或 '+' 符号的位置（可选，只允许在格式字符串的开头或结尾使用一次）。请注意，'S' 允许 '-'，但 'MI' 不允许。'PR'：只允许在格式字符串的末尾使用；指定 `expr` 使用尖括号表示负数。（'<1>'）。 |
| to_varchar(numberExpr, formatExpr) | 根据 `formatExpr` 将 `numberExpr` 转换为字符串。如果转换失败，则抛出异常。格式可以包含以下字符（不区分大小写）：'0' 或 '9'：指定 0 到 9 之间的预期数字。格式字符串中的 0 或 9 序列与输入值中的数字序列匹配，生成的结果字符串长度与格式字符串中相应序列的长度相同。如果 0/9 序列包含的数字多于十进制值的匹配部分、以 0 开头且位于小数点之前，则结果字符串将在左侧填充零。否则，将使用空格填充。'.' 或 'D'：指定小数点的位置（可选，只允许使用一次）。',' 或 'G'：指定分组（千位）分隔符 (,) 的位置。每个分组分隔符的左侧和右侧必须有一个 0 或 9。'$'：指定 $ 货币符号的位置。此字符只能指定一次。'S' 或 'MI'：指定 '-' 或 '+' 符号的位置（可选，只允许在格式字符串的开头或结尾使用一次）。请注意，'S' 会为正值打印 '+'，但 'MI' 会打印空格。'PR'：只允许在格式字符串的末尾使用；指定如果输入值为负数，则结果字符串将用尖括号括起来。（'<1>'）。 |
| translate(input, from, to) | 通过将 `from` 字符串中出现的字符替换为 `to` 字符串中的对应字符来转换 `input` 字符串。 |
| trim(str) | 删除 `str` 中开头和结尾的空格字符。 |
| trim(BOTH FROM str) | 删除 `str` 中开头和结尾的空格字符。 |
| trim(LEADING FROM str) | 删除 `str` 中开头的空格字符。 |
| trim(TRAILING FROM str) | 删除 `str` 中结尾的空格字符。 |
| trim(trimStr FROM str) | 删除 `str` 中开头和结尾的 `trimStr` 字符。 |
| trim(BOTH trimStr FROM str) | 删除 `str` 中开头和结尾的 `trimStr` 字符。 |
| trim(LEADING trimStr FROM str) | 从 `str` 中删除前导 `trimStr` 字符。 |
| trim(TRAILING trimStr FROM str) | 从 `str` 中删除尾随 `trimStr` 字符。 |
| try_to_binary(str[, fmt]) | 这是 `to_binary` 的一个特殊版本，它执行相同的操作，但如果无法执行转换，则返回 NULL 值而不是引发错误。 |
| try_to_number(expr, fmt) | 根据字符串格式 `fmt` 将字符串 `expr` 转换为数字。如果字符串 `expr` 与预期格式不匹配，则返回 NULL。该格式遵循与 to_number 函数相同的语义。 |
| ucase(str) | 返回 `str`，并将所有字符更改为大写。 |
| unbase64(str) | 将参数从 base 64 字符串 `str` 转换为二进制。 |
| upper(str) | 返回 `str`，并将所有字符更改为大写。 |
示例
-- ascii
SELECT ascii('222');
+----------+
|ascii(222)|
+----------+
| 50|
+----------+
SELECT ascii(2);
+--------+
|ascii(2)|
+--------+
| 50|
+--------+
-- base64
SELECT base64('Spark SQL');
+-----------------+
|base64(Spark SQL)|
+-----------------+
| U3BhcmsgU1FM|
+-----------------+
SELECT base64(x'537061726b2053514c');
+-----------------------------+
|base64(X'537061726B2053514C')|
+-----------------------------+
| U3BhcmsgU1FM|
+-----------------------------+
-- bit_length
SELECT bit_length('Spark SQL');
+---------------------+
|bit_length(Spark SQL)|
+---------------------+
| 72|
+---------------------+
SELECT bit_length(x'537061726b2053514c');
+---------------------------------+
|bit_length(X'537061726B2053514C')|
+---------------------------------+
| 72|
+---------------------------------+
-- btrim
SELECT btrim(' SparkSQL ');
+----------------------+
|btrim( SparkSQL )|
+----------------------+
| SparkSQL|
+----------------------+
SELECT btrim(encode(' SparkSQL ', 'utf-8'));
+-------------------------------------+
|btrim(encode( SparkSQL , utf-8))|
+-------------------------------------+
| SparkSQL|
+-------------------------------------+
SELECT btrim('SSparkSQLS', 'SL');
+---------------------+
|btrim(SSparkSQLS, SL)|
+---------------------+
| parkSQ|
+---------------------+
SELECT btrim(encode('SSparkSQLS', 'utf-8'), encode('SL', 'utf-8'));
+---------------------------------------------------+
|btrim(encode(SSparkSQLS, utf-8), encode(SL, utf-8))|
+---------------------------------------------------+
| parkSQ|
+---------------------------------------------------+
-- char
SELECT char(65);
+--------+
|char(65)|
+--------+
| A|
+--------+
-- char_length
SELECT char_length('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT char_length(x'537061726b2053514c');
+----------------------------------+
|char_length(X'537061726B2053514C')|
+----------------------------------+
| 9|
+----------------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- character_length
SELECT character_length('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
SELECT character_length(x'537061726b2053514c');
+---------------------------------------+
|character_length(X'537061726B2053514C')|
+---------------------------------------+
| 9|
+---------------------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- chr
SELECT chr(65);
+-------+
|chr(65)|
+-------+
| A|
+-------+
-- concat_ws
SELECT concat_ws(' ', 'Spark', 'SQL');
+------------------------+
|concat_ws( , Spark, SQL)|
+------------------------+
| Spark SQL|
+------------------------+
SELECT concat_ws('s');
+------------+
|concat_ws(s)|
+------------+
| |
+------------+
SELECT concat_ws('/', 'foo', null, 'bar');
+----------------------------+
|concat_ws(/, foo, NULL, bar)|
+----------------------------+
| foo/bar|
+----------------------------+
SELECT concat_ws(null, 'Spark', 'SQL');
+---------------------------+
|concat_ws(NULL, Spark, SQL)|
+---------------------------+
| NULL|
+---------------------------+
-- contains
SELECT contains('Spark SQL', 'Spark');
+--------------------------+
|contains(Spark SQL, Spark)|
+--------------------------+
| true|
+--------------------------+
SELECT contains('Spark SQL', 'SPARK');
+--------------------------+
|contains(Spark SQL, SPARK)|
+--------------------------+
| false|
+--------------------------+
SELECT contains('Spark SQL', null);
+-------------------------+
|contains(Spark SQL, NULL)|
+-------------------------+
| NULL|
+-------------------------+
SELECT contains(x'537061726b2053514c', x'537061726b');
+----------------------------------------------+
|contains(X'537061726B2053514C', X'537061726B')|
+----------------------------------------------+
| true|
+----------------------------------------------+
-- decode
SELECT decode(encode('abc', 'utf-8'), 'utf-8');
+---------------------------------+
|decode(encode(abc, utf-8), utf-8)|
+---------------------------------+
| abc|
+---------------------------------+
SELECT decode(2, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic');
+----------------------------------------------------------------------------------+
|decode(2, 1, Southlake, 2, San Francisco, 3, New Jersey, 4, Seattle, Non domestic)|
+----------------------------------------------------------------------------------+
| San Francisco|
+----------------------------------------------------------------------------------+
SELECT decode(6, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic');
+----------------------------------------------------------------------------------+
|decode(6, 1, Southlake, 2, San Francisco, 3, New Jersey, 4, Seattle, Non domestic)|
+----------------------------------------------------------------------------------+
| Non domestic|
+----------------------------------------------------------------------------------+
SELECT decode(6, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle');
+--------------------------------------------------------------------+
|decode(6, 1, Southlake, 2, San Francisco, 3, New Jersey, 4, Seattle)|
+--------------------------------------------------------------------+
| NULL|
+--------------------------------------------------------------------+
SELECT decode(null, 6, 'Spark', NULL, 'SQL', 4, 'rocks');
+-------------------------------------------+
|decode(NULL, 6, Spark, NULL, SQL, 4, rocks)|
+-------------------------------------------+
| SQL|
+-------------------------------------------+
-- elt
SELECT elt(1, 'scala', 'java');
+-------------------+
|elt(1, scala, java)|
+-------------------+
| scala|
+-------------------+
SELECT elt(2, 'a', 1);
+------------+
|elt(2, a, 1)|
+------------+
| 1|
+------------+
-- encode
SELECT encode('abc', 'utf-8');
+------------------+
|encode(abc, utf-8)|
+------------------+
| [61 62 63]|
+------------------+
-- endswith
SELECT endswith('Spark SQL', 'SQL');
+------------------------+
|endswith(Spark SQL, SQL)|
+------------------------+
| true|
+------------------------+
SELECT endswith('Spark SQL', 'Spark');
+--------------------------+
|endswith(Spark SQL, Spark)|
+--------------------------+
| false|
+--------------------------+
SELECT endswith('Spark SQL', null);
+-------------------------+
|endswith(Spark SQL, NULL)|
+-------------------------+
| NULL|
+-------------------------+
SELECT endswith(x'537061726b2053514c', x'537061726b');
+----------------------------------------------+
|endswith(X'537061726B2053514C', X'537061726B')|
+----------------------------------------------+
| false|
+----------------------------------------------+
SELECT endswith(x'537061726b2053514c', x'53514c');
+------------------------------------------+
|endswith(X'537061726B2053514C', X'53514C')|
+------------------------------------------+
| true|
+------------------------------------------+
-- find_in_set
SELECT find_in_set('ab','abc,b,ab,c,def');
+-------------------------------+
|find_in_set(ab, abc,b,ab,c,def)|
+-------------------------------+
| 3|
+-------------------------------+
-- format_number
SELECT format_number(12332.123456, 4);
+------------------------------+
|format_number(12332.123456, 4)|
+------------------------------+
| 12,332.1235|
+------------------------------+
SELECT format_number(12332.123456, '##################.###');
+---------------------------------------------------+
|format_number(12332.123456, ##################.###)|
+---------------------------------------------------+
| 12332.123|
+---------------------------------------------------+
-- format_string
SELECT format_string("Hello World %d %s", 100, "days");
+-------------------------------------------+
|format_string(Hello World %d %s, 100, days)|
+-------------------------------------------+
| Hello World 100 days|
+-------------------------------------------+
-- initcap
SELECT initcap('sPark sql');
+------------------+
|initcap(sPark sql)|
+------------------+
| Spark Sql|
+------------------+
-- instr
SELECT instr('SparkSQL', 'SQL');
+--------------------+
|instr(SparkSQL, SQL)|
+--------------------+
| 6|
+--------------------+
-- lcase
SELECT lcase('SparkSql');
+---------------+
|lcase(SparkSql)|
+---------------+
| sparksql|
+---------------+
-- left
SELECT left('Spark SQL', 3);
+------------------+
|left(Spark SQL, 3)|
+------------------+
| Spa|
+------------------+
SELECT left(encode('Spark SQL', 'utf-8'), 3);
+---------------------------------+
|left(encode(Spark SQL, utf-8), 3)|
+---------------------------------+
| [53 70 61]|
+---------------------------------+
-- len
SELECT len('Spark SQL ');
+---------------+
|len(Spark SQL )|
+---------------+
| 10|
+---------------+
SELECT len(x'537061726b2053514c');
+--------------------------+
|len(X'537061726B2053514C')|
+--------------------------+
| 9|
+--------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- length
SELECT length('Spark SQL ');
+------------------+
|length(Spark SQL )|
+------------------+
| 10|
+------------------+
SELECT length(x'537061726b2053514c');
+-----------------------------+
|length(X'537061726B2053514C')|
+-----------------------------+
| 9|
+-----------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- levenshtein
SELECT levenshtein('kitten', 'sitting');
+----------------------------+
|levenshtein(kitten, sitting)|
+----------------------------+
| 3|
+----------------------------+
SELECT levenshtein('kitten', 'sitting', 2);
+-------------------------------+
|levenshtein(kitten, sitting, 2)|
+-------------------------------+
| -1|
+-------------------------------+
-- locate
SELECT locate('bar', 'foobarbar');
+-------------------------+
|locate(bar, foobarbar, 1)|
+-------------------------+
| 4|
+-------------------------+
SELECT locate('bar', 'foobarbar', 5);
+-------------------------+
|locate(bar, foobarbar, 5)|
+-------------------------+
| 7|
+-------------------------+
SELECT POSITION('bar' IN 'foobarbar');
+-------------------------+
|locate(bar, foobarbar, 1)|
+-------------------------+
| 4|
+-------------------------+
-- lower
SELECT lower('SparkSql');
+---------------+
|lower(SparkSql)|
+---------------+
| sparksql|
+---------------+
-- lpad
SELECT lpad('hi', 5, '??');
+---------------+
|lpad(hi, 5, ??)|
+---------------+
| ???hi|
+---------------+
SELECT lpad('hi', 1, '??');
+---------------+
|lpad(hi, 1, ??)|
+---------------+
| h|
+---------------+
SELECT lpad('hi', 5);
+--------------+
|lpad(hi, 5, )|
+--------------+
| hi|
+--------------+
SELECT hex(lpad(unhex('aabb'), 5));
+--------------------------------+
|hex(lpad(unhex(aabb), 5, X'00'))|
+--------------------------------+
| 000000AABB|
+--------------------------------+
SELECT hex(lpad(unhex('aabb'), 5, unhex('1122')));
+--------------------------------------+
|hex(lpad(unhex(aabb), 5, unhex(1122)))|
+--------------------------------------+
| 112211AABB|
+--------------------------------------+
-- ltrim
SELECT ltrim(' SparkSQL ');
+----------------------+
|ltrim( SparkSQL )|
+----------------------+
| SparkSQL |
+----------------------+
-- luhn_check
SELECT luhn_check('8112189876');
+----------------------+
|luhn_check(8112189876)|
+----------------------+
| true|
+----------------------+
SELECT luhn_check('79927398713');
+-----------------------+
|luhn_check(79927398713)|
+-----------------------+
| true|
+-----------------------+
SELECT luhn_check('79927398714');
+-----------------------+
|luhn_check(79927398714)|
+-----------------------+
| false|
+-----------------------+
-- mask
SELECT mask('abcd-EFGH-8765-4321');
+----------------------------------------+
|mask(abcd-EFGH-8765-4321, X, x, n, NULL)|
+----------------------------------------+
| xxxx-XXXX-nnnn-nnnn|
+----------------------------------------+
SELECT mask('abcd-EFGH-8765-4321', 'Q');
+----------------------------------------+
|mask(abcd-EFGH-8765-4321, Q, x, n, NULL)|
+----------------------------------------+
| xxxx-QQQQ-nnnn-nnnn|
+----------------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q');
+--------------------------------+
|mask(AbCD123-@$#, Q, q, n, NULL)|
+--------------------------------+
| QqQQnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#');
+--------------------------------+
|mask(AbCD123-@$#, X, x, n, NULL)|
+--------------------------------+
| XxXXnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q');
+--------------------------------+
|mask(AbCD123-@$#, Q, x, n, NULL)|
+--------------------------------+
| QxQQnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q');
+--------------------------------+
|mask(AbCD123-@$#, Q, q, n, NULL)|
+--------------------------------+
| QqQQnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q', 'd');
+--------------------------------+
|mask(AbCD123-@$#, Q, q, d, NULL)|
+--------------------------------+
| QqQQddd-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q', 'd', 'o');
+-----------------------------+
|mask(AbCD123-@$#, Q, q, d, o)|
+-----------------------------+
| QqQQdddoooo|
+-----------------------------+
SELECT mask('AbCD123-@$#', NULL, 'q', 'd', 'o');
+--------------------------------+
|mask(AbCD123-@$#, NULL, q, d, o)|
+--------------------------------+
| AqCDdddoooo|
+--------------------------------+
SELECT mask('AbCD123-@$#', NULL, NULL, 'd', 'o');
+-----------------------------------+
|mask(AbCD123-@$#, NULL, NULL, d, o)|
+-----------------------------------+
| AbCDdddoooo|
+-----------------------------------+
SELECT mask('AbCD123-@$#', NULL, NULL, NULL, 'o');
+--------------------------------------+
|mask(AbCD123-@$#, NULL, NULL, NULL, o)|
+--------------------------------------+
| AbCD123oooo|
+--------------------------------------+
SELECT mask(NULL, NULL, NULL, NULL, 'o');
+-------------------------------+
|mask(NULL, NULL, NULL, NULL, o)|
+-------------------------------+
| NULL|
+-------------------------------+
SELECT mask(NULL);
+-------------------------+
|mask(NULL, X, x, n, NULL)|
+-------------------------+
| NULL|
+-------------------------+
SELECT mask('AbCD123-@$#', NULL, NULL, NULL, NULL);
+-----------------------------------------+
|mask(AbCD123-@$#, NULL, NULL, NULL, NULL)|
+-----------------------------------------+
| AbCD123-@$#|
+-----------------------------------------+
-- octet_length
SELECT octet_length('Spark SQL');
+-----------------------+
|octet_length(Spark SQL)|
+-----------------------+
| 9|
+-----------------------+
SELECT octet_length(x'537061726b2053514c');
+-----------------------------------+
|octet_length(X'537061726B2053514C')|
+-----------------------------------+
| 9|
+-----------------------------------+
-- overlay
SELECT overlay('Spark SQL' PLACING '_' FROM 6);
+----------------------------+
|overlay(Spark SQL, _, 6, -1)|
+----------------------------+
| Spark_SQL|
+----------------------------+
SELECT overlay('Spark SQL' PLACING 'CORE' FROM 7);
+-------------------------------+
|overlay(Spark SQL, CORE, 7, -1)|
+-------------------------------+
| Spark CORE|
+-------------------------------+
SELECT overlay('Spark SQL' PLACING 'ANSI ' FROM 7 FOR 0);
+-------------------------------+
|overlay(Spark SQL, ANSI , 7, 0)|
+-------------------------------+
| Spark ANSI SQL|
+-------------------------------+
SELECT overlay('Spark SQL' PLACING 'tructured' FROM 2 FOR 4);
+-----------------------------------+
|overlay(Spark SQL, tructured, 2, 4)|
+-----------------------------------+
| Structured SQL|
+-----------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('_', 'utf-8') FROM 6);
+----------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(_, utf-8), 6, -1)|
+----------------------------------------------------------+
| [53 70 61 72 6B 5...|
+----------------------------------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('CORE', 'utf-8') FROM 7);
+-------------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(CORE, utf-8), 7, -1)|
+-------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+-------------------------------------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('ANSI ', 'utf-8') FROM 7 FOR 0);
+-------------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(ANSI , utf-8), 7, 0)|
+-------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+-------------------------------------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('tructured', 'utf-8') FROM 2 FOR 4);
+-----------------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(tructured, utf-8), 2, 4)|
+-----------------------------------------------------------------+
| [53 74 72 75 63 7...|
+-----------------------------------------------------------------+
-- position
SELECT position('bar', 'foobarbar');
+---------------------------+
|position(bar, foobarbar, 1)|
+---------------------------+
| 4|
+---------------------------+
SELECT position('bar', 'foobarbar', 5);
+---------------------------+
|position(bar, foobarbar, 5)|
+---------------------------+
| 7|
+---------------------------+
SELECT POSITION('bar' IN 'foobarbar');
+-------------------------+
|locate(bar, foobarbar, 1)|
+-------------------------+
| 4|
+-------------------------+
-- printf
SELECT printf("Hello World %d %s", 100, "days");
+------------------------------------+
|printf(Hello World %d %s, 100, days)|
+------------------------------------+
| Hello World 100 days|
+------------------------------------+
-- regexp_count
SELECT regexp_count('Steven Jones and Stephen Smith are the best players', 'Ste(v|ph)en');
+------------------------------------------------------------------------------+
|regexp_count(Steven Jones and Stephen Smith are the best players, Ste(v|ph)en)|
+------------------------------------------------------------------------------+
| 2|
+------------------------------------------------------------------------------+
SELECT regexp_count('abcdefghijklmnopqrstuvwxyz', '[a-z]{3}');
+--------------------------------------------------+
|regexp_count(abcdefghijklmnopqrstuvwxyz, [a-z]{3})|
+--------------------------------------------------+
| 8|
+--------------------------------------------------+
-- regexp_extract
SELECT regexp_extract('100-200', '(\\d+)-(\\d+)', 1);
+---------------------------------------+
|regexp_extract(100-200, (\d+)-(\d+), 1)|
+---------------------------------------+
| 100|
+---------------------------------------+
-- regexp_extract_all
SELECT regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)', 1);
+----------------------------------------------------+
|regexp_extract_all(100-200, 300-400, (\d+)-(\d+), 1)|
+----------------------------------------------------+
| [100, 300]|
+----------------------------------------------------+
-- regexp_instr
SELECT regexp_instr('[email protected]', '@[^.]*');
+----------------------------------------------+
|regexp_instr(user@spark.apache.org, @[^.]*, 0)|
+----------------------------------------------+
| 5|
+----------------------------------------------+
-- regexp_replace
SELECT regexp_replace('100-200', '(\\d+)', 'num');
+--------------------------------------+
|regexp_replace(100-200, (\d+), num, 1)|
+--------------------------------------+
| num-num|
+--------------------------------------+
-- regexp_substr
SELECT regexp_substr('Steven Jones and Stephen Smith are the best players', 'Ste(v|ph)en');
+-------------------------------------------------------------------------------+
|regexp_substr(Steven Jones and Stephen Smith are the best players, Ste(v|ph)en)|
+-------------------------------------------------------------------------------+
| Steven|
+-------------------------------------------------------------------------------+
SELECT regexp_substr('Steven Jones and Stephen Smith are the best players', 'Jeck');
+------------------------------------------------------------------------+
|regexp_substr(Steven Jones and Stephen Smith are the best players, Jeck)|
+------------------------------------------------------------------------+
| NULL|
+------------------------------------------------------------------------+
-- repeat
SELECT repeat('123', 2);
+--------------+
|repeat(123, 2)|
+--------------+
| 123123|
+--------------+
-- replace
SELECT replace('ABCabc', 'abc', 'DEF');
+-------------------------+
|replace(ABCabc, abc, DEF)|
+-------------------------+
| ABCDEF|
+-------------------------+
-- right
SELECT right('Spark SQL', 3);
+-------------------+
|right(Spark SQL, 3)|
+-------------------+
| SQL|
+-------------------+
-- rpad
SELECT rpad('hi', 5, '??');
+---------------+
|rpad(hi, 5, ??)|
+---------------+
| hi???|
+---------------+
SELECT rpad('hi', 1, '??');
+---------------+
|rpad(hi, 1, ??)|
+---------------+
| h|
+---------------+
SELECT rpad('hi', 5);
+--------------+
|rpad(hi, 5, )|
+--------------+
| hi |
+--------------+
SELECT hex(rpad(unhex('aabb'), 5));
+--------------------------------+
|hex(rpad(unhex(aabb), 5, X'00'))|
+--------------------------------+
| AABB000000|
+--------------------------------+
SELECT hex(rpad(unhex('aabb'), 5, unhex('1122')));
+--------------------------------------+
|hex(rpad(unhex(aabb), 5, unhex(1122)))|
+--------------------------------------+
| AABB112211|
+--------------------------------------+
-- rtrim
SELECT rtrim(' SparkSQL ');
+----------------------+
|rtrim( SparkSQL )|
+----------------------+
| SparkSQL|
+----------------------+
-- sentences
SELECT sentences('Hi there! Good morning.');
+--------------------------------------+
|sentences(Hi there! Good morning., , )|
+--------------------------------------+
| [[Hi, there], [Go...|
+--------------------------------------+
-- soundex
SELECT soundex('Miller');
+---------------+
|soundex(Miller)|
+---------------+
| M460|
+---------------+
-- space
SELECT concat(space(2), '1');
+-------------------+
|concat(space(2), 1)|
+-------------------+
| 1|
+-------------------+
-- split
SELECT split('oneAtwoBthreeC', '[ABC]');
+--------------------------------+
|split(oneAtwoBthreeC, [ABC], -1)|
+--------------------------------+
| [one, two, three, ]|
+--------------------------------+
SELECT split('oneAtwoBthreeC', '[ABC]', -1);
+--------------------------------+
|split(oneAtwoBthreeC, [ABC], -1)|
+--------------------------------+
| [one, two, three, ]|
+--------------------------------+
SELECT split('oneAtwoBthreeC', '[ABC]', 2);
+-------------------------------+
|split(oneAtwoBthreeC, [ABC], 2)|
+-------------------------------+
| [one, twoBthreeC]|
+-------------------------------+
-- split_part
SELECT split_part('11.12.13', '.', 3);
+--------------------------+
|split_part(11.12.13, ., 3)|
+--------------------------+
| 13|
+--------------------------+
-- startswith
SELECT startswith('Spark SQL', 'Spark');
+----------------------------+
|startswith(Spark SQL, Spark)|
+----------------------------+
| true|
+----------------------------+
SELECT startswith('Spark SQL', 'SQL');
+--------------------------+
|startswith(Spark SQL, SQL)|
+--------------------------+
| false|
+--------------------------+
SELECT startswith('Spark SQL', null);
+---------------------------+
|startswith(Spark SQL, NULL)|
+---------------------------+
| NULL|
+---------------------------+
SELECT startswith(x'537061726b2053514c', x'537061726b');
+------------------------------------------------+
|startswith(X'537061726B2053514C', X'537061726B')|
+------------------------------------------------+
| true|
+------------------------------------------------+
SELECT startswith(x'537061726b2053514c', x'53514c');
+--------------------------------------------+
|startswith(X'537061726B2053514C', X'53514C')|
+--------------------------------------------+
| false|
+--------------------------------------------+
-- substr
SELECT substr('Spark SQL', 5);
+--------------------------------+
|substr(Spark SQL, 5, 2147483647)|
+--------------------------------+
| k SQL|
+--------------------------------+
SELECT substr('Spark SQL', -3);
+---------------------------------+
|substr(Spark SQL, -3, 2147483647)|
+---------------------------------+
| SQL|
+---------------------------------+
SELECT substr('Spark SQL', 5, 1);
+-----------------------+
|substr(Spark SQL, 5, 1)|
+-----------------------+
| k|
+-----------------------+
SELECT substr('Spark SQL' FROM 5);
+-----------------------------------+
|substring(Spark SQL, 5, 2147483647)|
+-----------------------------------+
| k SQL|
+-----------------------------------+
SELECT substr('Spark SQL' FROM -3);
+------------------------------------+
|substring(Spark SQL, -3, 2147483647)|
+------------------------------------+
| SQL|
+------------------------------------+
SELECT substr('Spark SQL' FROM 5 FOR 1);
+--------------------------+
|substring(Spark SQL, 5, 1)|
+--------------------------+
| k|
+--------------------------+
SELECT substr(encode('Spark SQL', 'utf-8'), 5);
+-----------------------------------------------+
|substr(encode(Spark SQL, utf-8), 5, 2147483647)|
+-----------------------------------------------+
| [6B 20 53 51 4C]|
+-----------------------------------------------+
-- substring
SELECT substring('Spark SQL', 5);
+-----------------------------------+
|substring(Spark SQL, 5, 2147483647)|
+-----------------------------------+
| k SQL|
+-----------------------------------+
SELECT substring('Spark SQL', -3);
+------------------------------------+
|substring(Spark SQL, -3, 2147483647)|
+------------------------------------+
| SQL|
+------------------------------------+
SELECT substring('Spark SQL', 5, 1);
+--------------------------+
|substring(Spark SQL, 5, 1)|
+--------------------------+
| k|
+--------------------------+
SELECT substring('Spark SQL' FROM 5);
+-----------------------------------+
|substring(Spark SQL, 5, 2147483647)|
+-----------------------------------+
| k SQL|
+-----------------------------------+
SELECT substring('Spark SQL' FROM -3);
+------------------------------------+
|substring(Spark SQL, -3, 2147483647)|
+------------------------------------+
| SQL|
+------------------------------------+
SELECT substring('Spark SQL' FROM 5 FOR 1);
+--------------------------+
|substring(Spark SQL, 5, 1)|
+--------------------------+
| k|
+--------------------------+
SELECT substring(encode('Spark SQL', 'utf-8'), 5);
+--------------------------------------------------+
|substring(encode(Spark SQL, utf-8), 5, 2147483647)|
+--------------------------------------------------+
| [6B 20 53 51 4C]|
+--------------------------------------------------+
-- substring_index
SELECT substring_index('www.apache.org', '.', 2);
+-------------------------------------+
|substring_index(www.apache.org, ., 2)|
+-------------------------------------+
| www.apache|
+-------------------------------------+
-- to_binary
SELECT to_binary('abc', 'utf-8');
+---------------------+
|to_binary(abc, utf-8)|
+---------------------+
| [61 62 63]|
+---------------------+
-- to_char
SELECT to_char(454, '999');
+-----------------+
|to_char(454, 999)|
+-----------------+
| 454|
+-----------------+
SELECT to_char(454.00, '000D00');
+-----------------------+
|to_char(454.00, 000D00)|
+-----------------------+
| 454.00|
+-----------------------+
SELECT to_char(12454, '99G999');
+----------------------+
|to_char(12454, 99G999)|
+----------------------+
| 12,454|
+----------------------+
SELECT to_char(78.12, '$99.99');
+----------------------+
|to_char(78.12, $99.99)|
+----------------------+
| $78.12|
+----------------------+
SELECT to_char(-12454.8, '99G999D9S');
+----------------------------+
|to_char(-12454.8, 99G999D9S)|
+----------------------------+
| 12,454.8-|
+----------------------------+
-- to_number
SELECT to_number('454', '999');
+-------------------+
|to_number(454, 999)|
+-------------------+
| 454|
+-------------------+
SELECT to_number('454.00', '000.00');
+-------------------------+
|to_number(454.00, 000.00)|
+-------------------------+
| 454.00|
+-------------------------+
SELECT to_number('12,454', '99,999');
+-------------------------+
|to_number(12,454, 99,999)|
+-------------------------+
| 12454|
+-------------------------+
SELECT to_number('$78.12', '$99.99');
+-------------------------+
|to_number($78.12, $99.99)|
+-------------------------+
| 78.12|
+-------------------------+
SELECT to_number('12,454.8-', '99,999.9S');
+-------------------------------+
|to_number(12,454.8-, 99,999.9S)|
+-------------------------------+
| -12454.8|
+-------------------------------+
-- to_varchar
SELECT to_varchar(454, '999');
+-----------------+
|to_char(454, 999)|
+-----------------+
| 454|
+-----------------+
SELECT to_varchar(454.00, '000D00');
+-----------------------+
|to_char(454.00, 000D00)|
+-----------------------+
| 454.00|
+-----------------------+
SELECT to_varchar(12454, '99G999');
+----------------------+
|to_char(12454, 99G999)|
+----------------------+
| 12,454|
+----------------------+
SELECT to_varchar(78.12, '$99.99');
+----------------------+
|to_char(78.12, $99.99)|
+----------------------+
| $78.12|
+----------------------+
SELECT to_varchar(-12454.8, '99G999D9S');
+----------------------------+
|to_char(-12454.8, 99G999D9S)|
+----------------------------+
| 12,454.8-|
+----------------------------+
-- translate
SELECT translate('AaBbCc', 'abc', '123');
+---------------------------+
|translate(AaBbCc, abc, 123)|
+---------------------------+
| A1B2C3|
+---------------------------+
-- trim
SELECT trim(' SparkSQL ');
+---------------------+
|trim( SparkSQL )|
+---------------------+
| SparkSQL|
+---------------------+
SELECT trim(BOTH FROM ' SparkSQL ');
+---------------------+
|trim( SparkSQL )|
+---------------------+
| SparkSQL|
+---------------------+
SELECT trim(LEADING FROM ' SparkSQL ');
+----------------------+
|ltrim( SparkSQL )|
+----------------------+
| SparkSQL |
+----------------------+
SELECT trim(TRAILING FROM ' SparkSQL ');
+----------------------+
|rtrim( SparkSQL )|
+----------------------+
| SparkSQL|
+----------------------+
SELECT trim('SL' FROM 'SSparkSQLS');
+-----------------------------+
|TRIM(BOTH SL FROM SSparkSQLS)|
+-----------------------------+
| parkSQ|
+-----------------------------+
SELECT trim(BOTH 'SL' FROM 'SSparkSQLS');
+-----------------------------+
|TRIM(BOTH SL FROM SSparkSQLS)|
+-----------------------------+
| parkSQ|
+-----------------------------+
SELECT trim(LEADING 'SL' FROM 'SSparkSQLS');
+--------------------------------+
|TRIM(LEADING SL FROM SSparkSQLS)|
+--------------------------------+
| parkSQLS|
+--------------------------------+
SELECT trim(TRAILING 'SL' FROM 'SSparkSQLS');
+---------------------------------+
|TRIM(TRAILING SL FROM SSparkSQLS)|
+---------------------------------+
| SSparkSQ|
+---------------------------------+
-- try_to_binary
SELECT try_to_binary('abc', 'utf-8');
+-------------------------+
|try_to_binary(abc, utf-8)|
+-------------------------+
| [61 62 63]|
+-------------------------+
select try_to_binary('a!', 'base64');
+-------------------------+
|try_to_binary(a!, base64)|
+-------------------------+
| NULL|
+-------------------------+
select try_to_binary('abc', 'invalidFormat');
+---------------------------------+
|try_to_binary(abc, invalidFormat)|
+---------------------------------+
| NULL|
+---------------------------------+
-- try_to_number
SELECT try_to_number('454', '999');
+-----------------------+
|try_to_number(454, 999)|
+-----------------------+
| 454|
+-----------------------+
SELECT try_to_number('454.00', '000.00');
+-----------------------------+
|try_to_number(454.00, 000.00)|
+-----------------------------+
| 454.00|
+-----------------------------+
SELECT try_to_number('12,454', '99,999');
+-----------------------------+
|try_to_number(12,454, 99,999)|
+-----------------------------+
| 12454|
+-----------------------------+
SELECT try_to_number('$78.12', '$99.99');
+-----------------------------+
|try_to_number($78.12, $99.99)|
+-----------------------------+
| 78.12|
+-----------------------------+
SELECT try_to_number('12,454.8-', '99,999.9S');
+-----------------------------------+
|try_to_number(12,454.8-, 99,999.9S)|
+-----------------------------------+
| -12454.8|
+-----------------------------------+
-- ucase
SELECT ucase('SparkSql');
+---------------+
|ucase(SparkSql)|
+---------------+
| SPARKSQL|
+---------------+
-- unbase64
SELECT unbase64('U3BhcmsgU1FM');
+----------------------+
|unbase64(U3BhcmsgU1FM)|
+----------------------+
| [53 70 61 72 6B 2...|
+----------------------+
-- upper
SELECT upper('SparkSql');
+---------------+
|upper(SparkSql)|
+---------------+
| SPARKSQL|
+---------------+
条件函数
| 函数 | 描述 |
|---|---|
| coalesce(expr1, expr2, ...) | 如果存在，则返回第一个非空参数。否则，返回 null。 |
| if(expr1, expr2, expr3) | 如果 `expr1` 的计算结果为 true，则返回 `expr2`；否则返回 `expr3`。 |
| ifnull(expr1, expr2) | 如果 `expr1` 为 null，则返回 `expr2`，否则返回 `expr1`。 |
| nanvl(expr1, expr2) | 如果 `expr1` 不是 NaN，则返回 `expr1`，否则返回 `expr2`。 |
| nullif(expr1, expr2) | 如果 `expr1` 等于 `expr2`，则返回 null，否则返回 `expr1`。 |
| nvl(expr1, expr2) | 如果 `expr1` 为 null，则返回 `expr2`，否则返回 `expr1`。 |
| nvl2(expr1, expr2, expr3) | 如果 `expr1` 不为 null，则返回 `expr2`，否则返回 `expr3`。 |
| CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4]* [ELSE expr5] END | 当 `expr1` = true 时，返回 `expr2`；否则当 `expr3` = true 时，返回 `expr4`；否则返回 `expr5`。 |
示例
-- coalesce
SELECT coalesce(NULL, 1, NULL);
+-----------------------+
|coalesce(NULL, 1, NULL)|
+-----------------------+
| 1|
+-----------------------+
-- if
SELECT if(1 < 2, 'a', 'b');
+-------------------+
|(IF((1 < 2), a, b))|
+-------------------+
| a|
+-------------------+
-- ifnull
SELECT ifnull(NULL, array('2'));
+----------------------+
|ifnull(NULL, array(2))|
+----------------------+
| [2]|
+----------------------+
-- nanvl
SELECT nanvl(cast('NaN' as double), 123);
+-------------------------------+
|nanvl(CAST(NaN AS DOUBLE), 123)|
+-------------------------------+
| 123.0|
+-------------------------------+
-- nullif
SELECT nullif(2, 2);
+------------+
|nullif(2, 2)|
+------------+
| NULL|
+------------+
-- nvl
SELECT nvl(NULL, array('2'));
+-------------------+
|nvl(NULL, array(2))|
+-------------------+
| [2]|
+-------------------+
-- nvl2
SELECT nvl2(NULL, 2, 1);
+----------------+
|nvl2(NULL, 2, 1)|
+----------------+
| 1|
+----------------+
-- when
SELECT CASE WHEN 1 > 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
+-----------------------------------------------------------+
|CASE WHEN (1 > 0) THEN 1 WHEN (2 > 0) THEN 2.0 ELSE 1.2 END|
+-----------------------------------------------------------+
| 1.0|
+-----------------------------------------------------------+
SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
+-----------------------------------------------------------+
|CASE WHEN (1 < 0) THEN 1 WHEN (2 > 0) THEN 2.0 ELSE 1.2 END|
+-----------------------------------------------------------+
| 2.0|
+-----------------------------------------------------------+
SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 < 0 THEN 2.0 END;
+--------------------------------------------------+
|CASE WHEN (1 < 0) THEN 1 WHEN (2 < 0) THEN 2.0 END|
+--------------------------------------------------+
| NULL|
+--------------------------------------------------+
位函数
| 函数 | 描述 |
|---|---|
| expr1 & expr2 | 返回 `expr1` 和 `expr2` 按位 AND 的结果。 |
| expr1 ^ expr2 | 返回 `expr1` 和 `expr2` 按位异或的结果。 |
| bit_count(expr) | 返回参数 expr 中设置的位数作为无符号 64 位整数，如果参数为 NULL，则返回 NULL。 |
| bit_get(expr, pos) | 返回指定位置的位值（0 或 1）。位置从右到左编号，从零开始。位置参数不能为负数。 |
| getbit(expr, pos) | 返回指定位置的位值（0 或 1）。位置从右到左编号，从零开始。位置参数不能为负数。 |
| shiftright(base, expr) | 按位（有符号）右移。 |
| shiftrightunsigned(base, expr) | 按位无符号右移。 |
| expr1 | expr2 | 返回 `expr1` 和 `expr2` 按位 OR 的结果。 |
| ~ expr | 返回 `expr` 按位 NOT 的结果。 |
示例
-- &
SELECT 3 & 5;
+-------+
|(3 & 5)|
+-------+
| 1|
+-------+
-- ^
SELECT 3 ^ 5;
+-------+
|(3 ^ 5)|
+-------+
| 6|
+-------+
-- bit_count
SELECT bit_count(0);
+------------+
|bit_count(0)|
+------------+
| 0|
+------------+
-- bit_get
SELECT bit_get(11, 0);
+--------------+
|bit_get(11, 0)|
+--------------+
| 1|
+--------------+
SELECT bit_get(11, 2);
+--------------+
|bit_get(11, 2)|
+--------------+
| 0|
+--------------+
-- getbit
SELECT getbit(11, 0);
+-------------+
|getbit(11, 0)|
+-------------+
| 1|
+-------------+
SELECT getbit(11, 2);
+-------------+
|getbit(11, 2)|
+-------------+
| 0|
+-------------+
-- shiftright
SELECT shiftright(4, 1);
+----------------+
|shiftright(4, 1)|
+----------------+
| 2|
+----------------+
-- shiftrightunsigned
SELECT shiftrightunsigned(4, 1);
+------------------------+
|shiftrightunsigned(4, 1)|
+------------------------+
| 2|
+------------------------+
-- |
SELECT 3 | 5;
+-------+
|(3 | 5)|
+-------+
| 7|
+-------+
-- ~
SELECT ~ 0;
+---+
| ~0|
+---+
| -1|
+---+
转换函数
| 函数 | 描述 |
|---|---|
| bigint(expr) | 将值 `expr` 强制转换为目标数据类型 `bigint`。 |
| binary(expr) | 将值 `expr` 强制转换为目标数据类型 `binary`。 |
| boolean(expr) | 将值 `expr` 强制转换为目标数据类型 `boolean`。 |
| cast(expr AS type) | 将值 `expr` 强制转换为目标数据类型 `type`。 |
| date(expr) | 将值 `expr` 强制转换为目标数据类型 `date`。 |
| decimal(expr) | 将值 `expr` 强制转换为目标数据类型 `decimal`。 |
| double(expr) | 将值 `expr` 强制转换为目标数据类型 `double`。 |
| float(expr) | 将值 `expr` 强制转换为目标数据类型 `float`。 |
| int(expr) | 将值 `expr` 强制转换为目标数据类型 `int`。 |
| smallint(expr) | 将值 `expr` 强制转换为目标数据类型 `smallint`。 |
| string(expr) | 将值 `expr` 强制转换为目标数据类型 `string`。 |
| timestamp(expr) | 将值 `expr` 强制转换为目标数据类型 `timestamp`。 |
| tinyint(expr) | 将值 `expr` 强制转换为目标数据类型 `tinyint`。 |
示例
-- cast
SELECT cast('10' as int);
+---------------+
|CAST(10 AS INT)|
+---------------+
| 10|
+---------------+
谓词函数
| 函数 | 描述 |
|---|---|
| ! expr | 逻辑非。 |
| expr1 < expr2 | 如果 `expr1` 小于 `expr2`，则返回 true。 |
| expr1 <= expr2 | 如果 `expr1` 小于或等于 `expr2`，则返回 true。 |
| expr1 <=> expr2 | 对于非空操作数，返回与 EQUAL(=) 运算符相同的结果，但如果两者都为 null，则返回 true，如果其中一个为 null，则返回 false。 |
| expr1 = expr2 | 如果 `expr1` 等于 `expr2`，则返回 true，否则返回 false。 |
| expr1 == expr2 | 如果 `expr1` 等于 `expr2`，则返回 true，否则返回 false。 |
| expr1 > expr2 | 如果 `expr1` 大于 `expr2`，则返回 true。 |
| expr1 >= expr2 | 如果 `expr1` 大于或等于 `expr2`，则返回 true。 |
| expr1 and expr2 | 逻辑 AND。 |
| str ilike pattern[ ESCAPE escape] | 如果 str 不区分大小写地与 `pattern` 匹配（使用 `escape`），则返回 true；如果任何参数为 null，则返回 null；否则返回 false。 |
| expr1 in(expr2, expr3, ...) | 如果 `expr` 等于任何 valN，则返回 true。 |
| isnan(expr) | 如果 `expr` 为 NaN，则返回 true，否则返回 false。 |
| isnotnull(expr) | 如果 `expr` 不为 null，则返回 true，否则返回 false。 |
| isnull(expr) | 如果 `expr` 为 null，则返回 true，否则返回 false。 |
| str like pattern[ ESCAPE escape] | 如果 str 与 `pattern` 匹配（使用 `escape`），则返回 true；如果任何参数为 null，则返回 null；否则返回 false。 |
| not expr | 逻辑非。 |
| expr1 or expr2 | 逻辑 OR。 |
| regexp(str, regexp) | 如果 `str` 与 `regexp` 匹配，则返回 true，否则返回 false。 |
| regexp_like(str, regexp) | 如果 `str` 与 `regexp` 匹配，则返回 true，否则返回 false。 |
| rlike(str, regexp) | 如果 `str` 与 `regexp` 匹配，则返回 true，否则返回 false。 |
示例
-- !
SELECT ! true;
+----------+
|(NOT true)|
+----------+
| false|
+----------+
SELECT ! false;
+-----------+
|(NOT false)|
+-----------+
| true|
+-----------+
SELECT ! NULL;
+----------+
|(NOT NULL)|
+----------+
| NULL|
+----------+
-- <
SELECT 1 < 2;
+-------+
|(1 < 2)|
+-------+
| true|
+-------+
SELECT 1.1 < '1';
+---------+
|(1.1 < 1)|
+---------+
| false|
+---------+
SELECT to_date('2009-07-30 04:17:52') < to_date('2009-07-30 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) < to_date(2009-07-30 04:17:52))|
+-------------------------------------------------------------+
| false|
+-------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') < to_date('2009-08-01 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) < to_date(2009-08-01 04:17:52))|
+-------------------------------------------------------------+
| true|
+-------------------------------------------------------------+
SELECT 1 < NULL;
+----------+
|(1 < NULL)|
+----------+
| NULL|
+----------+
-- <=
SELECT 2 <= 2;
+--------+
|(2 <= 2)|
+--------+
| true|
+--------+
SELECT 1.0 <= '1';
+----------+
|(1.0 <= 1)|
+----------+
| true|
+----------+
SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-07-30 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) <= to_date(2009-07-30 04:17:52))|
+--------------------------------------------------------------+
| true|
+--------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-08-01 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) <= to_date(2009-08-01 04:17:52))|
+--------------------------------------------------------------+
| true|
+--------------------------------------------------------------+
SELECT 1 <= NULL;
+-----------+
|(1 <= NULL)|
+-----------+
| NULL|
+-----------+
-- <=>
SELECT 2 <=> 2;
+---------+
|(2 <=> 2)|
+---------+
| true|
+---------+
SELECT 1 <=> '1';
+---------+
|(1 <=> 1)|
+---------+
| true|
+---------+
SELECT true <=> NULL;
+---------------+
|(true <=> NULL)|
+---------------+
| false|
+---------------+
SELECT NULL <=> NULL;
+---------------+
|(NULL <=> NULL)|
+---------------+
| true|
+---------------+
-- =
SELECT 2 = 2;
+-------+
|(2 = 2)|
+-------+
| true|
+-------+
SELECT 1 = '1';
+-------+
|(1 = 1)|
+-------+
| true|
+-------+
SELECT true = NULL;
+-------------+
|(true = NULL)|
+-------------+
| NULL|
+-------------+
SELECT NULL = NULL;
+-------------+
|(NULL = NULL)|
+-------------+
| NULL|
+-------------+
-- ==
SELECT 2 == 2;
+-------+
|(2 = 2)|
+-------+
| true|
+-------+
SELECT 1 == '1';
+-------+
|(1 = 1)|
+-------+
| true|
+-------+
SELECT true == NULL;
+-------------+
|(true = NULL)|
+-------------+
| NULL|
+-------------+
SELECT NULL == NULL;
+-------------+
|(NULL = NULL)|
+-------------+
| NULL|
+-------------+
-- >
SELECT 2 > 1;
+-------+
|(2 > 1)|
+-------+
| true|
+-------+
SELECT 2 > 1.1;
+-------+
|(2 > 1)|
+-------+
| true|
+-------+
SELECT to_date('2009-07-30 04:17:52') > to_date('2009-07-30 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) > to_date(2009-07-30 04:17:52))|
+-------------------------------------------------------------+
| false|
+-------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') > to_date('2009-08-01 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) > to_date(2009-08-01 04:17:52))|
+-------------------------------------------------------------+
| false|
+-------------------------------------------------------------+
SELECT 1 > NULL;
+----------+
|(1 > NULL)|
+----------+
| NULL|
+----------+
-- >=
SELECT 2 >= 1;
+--------+
|(2 >= 1)|
+--------+
| true|
+--------+
SELECT 2.0 >= '2.1';
+------------+
|(2.0 >= 2.1)|
+------------+
| false|
+------------+
SELECT to_date('2009-07-30 04:17:52') >= to_date('2009-07-30 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) >= to_date(2009-07-30 04:17:52))|
+--------------------------------------------------------------+
| true|
+--------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') >= to_date('2009-08-01 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) >= to_date(2009-08-01 04:17:52))|
+--------------------------------------------------------------+
| false|
+--------------------------------------------------------------+
SELECT 1 >= NULL;
+-----------+
|(1 >= NULL)|
+-----------+
| NULL|
+-----------+
-- and
SELECT true and true;
+---------------+
|(true AND true)|
+---------------+
| true|
+---------------+
SELECT true and false;
+----------------+
|(true AND false)|
+----------------+
| false|
+----------------+
SELECT true and NULL;
+---------------+
|(true AND NULL)|
+---------------+
| NULL|
+---------------+
SELECT false and NULL;
+----------------+
|(false AND NULL)|
+----------------+
| false|
+----------------+
-- ilike
SELECT ilike('Spark', '_Park');
+-------------------+
|ilike(Spark, _Park)|
+-------------------+
| true|
+-------------------+
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT '%SystemDrive%\Users\John' ilike '\%SystemDrive\%\\users%';
+--------------------------------------------------------+
|ilike(%SystemDrive%\Users\John, \%SystemDrive\%\\users%)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT '%SystemDrive%\\USERS\\John' ilike '\%SystemDrive\%\\\\Users%';
+--------------------------------------------------------+
|ilike(%SystemDrive%\USERS\John, \%SystemDrive\%\\Users%)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
SELECT '%SystemDrive%/Users/John' ilike '/%SYSTEMDrive/%//Users%' ESCAPE '/';
+--------------------------------------------------------+
|ilike(%SystemDrive%/Users/John, /%SYSTEMDrive/%//Users%)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
-- in
SELECT 1 in(1, 2, 3);
+----------------+
|(1 IN (1, 2, 3))|
+----------------+
| true|
+----------------+
SELECT 1 in(2, 3, 4);
+----------------+
|(1 IN (2, 3, 4))|
+----------------+
| false|
+----------------+
SELECT named_struct('a', 1, 'b', 2) in(named_struct('a', 1, 'b', 1), named_struct('a', 1, 'b', 3));
+----------------------------------------------------------------------------------+
|(named_struct(a, 1, b, 2) IN (named_struct(a, 1, b, 1), named_struct(a, 1, b, 3)))|
+----------------------------------------------------------------------------------+
| false|
+----------------------------------------------------------------------------------+
SELECT named_struct('a', 1, 'b', 2) in(named_struct('a', 1, 'b', 2), named_struct('a', 1, 'b', 3));
+----------------------------------------------------------------------------------+
|(named_struct(a, 1, b, 2) IN (named_struct(a, 1, b, 2), named_struct(a, 1, b, 3)))|
+----------------------------------------------------------------------------------+
| true|
+----------------------------------------------------------------------------------+
-- isnan
SELECT isnan(cast('NaN' as double));
+--------------------------+
|isnan(CAST(NaN AS DOUBLE))|
+--------------------------+
| true|
+--------------------------+
-- isnotnull
SELECT isnotnull(1);
+---------------+
|(1 IS NOT NULL)|
+---------------+
| true|
+---------------+
-- isnull
SELECT isnull(1);
+-----------+
|(1 IS NULL)|
+-----------+
| false|
+-----------+
-- like
SELECT like('Spark', '_park');
+----------------+
|Spark LIKE _park|
+----------------+
| true|
+----------------+
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT '%SystemDrive%\Users\John' like '\%SystemDrive\%\\Users%';
+-----------------------------------------------------+
|%SystemDrive%\Users\John LIKE \%SystemDrive\%\\Users%|
+-----------------------------------------------------+
| true|
+-----------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT '%SystemDrive%\\Users\\John' like '\%SystemDrive\%\\\\Users%';
+-----------------------------------------------------+
|%SystemDrive%\Users\John LIKE \%SystemDrive\%\\Users%|
+-----------------------------------------------------+
| true|
+-----------------------------------------------------+
SELECT '%SystemDrive%/Users/John' like '/%SystemDrive/%//Users%' ESCAPE '/';
+-----------------------------------------------------+
|%SystemDrive%/Users/John LIKE /%SystemDrive/%//Users%|
+-----------------------------------------------------+
| true|
+-----------------------------------------------------+
-- not
SELECT not true;
+----------+
|(NOT true)|
+----------+
| false|
+----------+
SELECT not false;
+-----------+
|(NOT false)|
+-----------+
| true|
+-----------+
SELECT not NULL;
+----------+
|(NOT NULL)|
+----------+
| NULL|
+----------+
-- or
SELECT true or false;
+---------------+
|(true OR false)|
+---------------+
| true|
+---------------+
SELECT false or false;
+----------------+
|(false OR false)|
+----------------+
| false|
+----------------+
SELECT true or NULL;
+--------------+
|(true OR NULL)|
+--------------+
| true|
+--------------+
SELECT false or NULL;
+---------------+
|(false OR NULL)|
+---------------+
| NULL|
+---------------+
-- regexp
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT regexp('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
+--------------------------------------------------------+
|REGEXP(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT regexp('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
+--------------------------------------------------------+
|REGEXP(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
-- regexp_like
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT regexp_like('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
+-------------------------------------------------------------+
|REGEXP_LIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------------+
| true|
+-------------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT regexp_like('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
+-------------------------------------------------------------+
|REGEXP_LIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------------+
| true|
+-------------------------------------------------------------+
-- rlike
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT rlike('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
+-------------------------------------------------------+
|RLIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------+
| true|
+-------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT rlike('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
+-------------------------------------------------------+
|RLIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------+
| true|
+-------------------------------------------------------+
CSV 函数
| 函数 | 描述 |
|---|---|
| from_csv(csvStr, schema[, options]) | 返回具有给定 `csvStr` 和 `schema` 的结构值。 |
| schema_of_csv(csv[, options]) | 以 CSV 字符串的 DDL 格式返回架构。 |
| to_csv(expr[, options]) | 返回具有给定结构值的 CSV 字符串 |
示例
-- from_csv
SELECT from_csv('1, 0.8', 'a INT, b DOUBLE');
+----------------+
|from_csv(1, 0.8)|
+----------------+
| {1, 0.8}|
+----------------+
SELECT from_csv('26/08/2015', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
+--------------------+
|from_csv(26/08/2015)|
+--------------------+
|{2015-08-26 00:00...|
+--------------------+
-- schema_of_csv
SELECT schema_of_csv('1,abc');
+--------------------+
|schema_of_csv(1,abc)|
+--------------------+
|STRUCT<_c0: INT, ...|
+--------------------+
-- to_csv
SELECT to_csv(named_struct('a', 1, 'b', 2));
+--------------------------------+
|to_csv(named_struct(a, 1, b, 2))|
+--------------------------------+
| 1,2|
+--------------------------------+
SELECT to_csv(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
+----------------------------------------------------------------+
|to_csv(named_struct(time, to_timestamp(2015-08-26, yyyy-MM-dd)))|
+----------------------------------------------------------------+
| 26/08/2015|
+----------------------------------------------------------------+
其他函数
| 函数 | 描述 |
|---|---|
| aes_decrypt(expr, key[, mode[, padding[, aad]]]) | 使用 `mode` 中的 AES 和 `padding` 返回 `expr` 的解密值。支持 16、24 和 32 位的密钥长度。支持的 (`mode`, `padding`) 组合为 ('ECB', 'PKCS')、('GCM', 'NONE') 和 ('CBC', 'PKCS')。仅 GCM 支持可选的附加身份验证数据 (AAD)。如果为加密提供了 AAD，则必须为解密提供相同的 AAD 值。默认模式为 GCM。 |
| aes_encrypt(expr, key[, mode[, padding[, iv[, aad]]]]) | 使用给定 `mode` 中的 AES 和指定的 `padding` 返回 `expr` 的加密值。支持 16、24 和 32 位的密钥长度。支持的 (`mode`, `padding`) 组合为 ('ECB', 'PKCS')、('GCM', 'NONE') 和 ('CBC', 'PKCS')。仅 CBC 和 GCM 模式支持可选的初始化向量 (IV)。对于 CBC，这些向量必须为 16 字节，对于 GCM，则必须为 12 字节。如果未提供，则会生成一个随机向量并将其添加到输出的开头。仅 GCM 支持可选的附加身份验证数据 (AAD)。如果为加密提供了 AAD，则必须为解密提供相同的 AAD 值。默认模式为 GCM。 |
| assert_true(expr) | 如果 `expr` 不为 true，则抛出异常。 |
| bitmap_bit_position(child) | 返回给定输入子表达式对应的位位置。 |
| bitmap_bucket_number(child) | 返回给定输入子表达式对应的桶号。 |
| bitmap_count(child) | 返回子位图中设置的位数。 |
| current_catalog() | 返回当前目录。 |
| current_database() | 返回当前数据库。 |
| current_schema() | 返回当前数据库。 |
| current_user() | 当前执行上下文的用户名。 |
| equal_null(expr1, expr2) | 对于非空操作数，返回与 EQUAL(=) 运算符相同的结果，但如果两者都为 null，则返回 true，如果其中一个为 null，则返回 false。 |
| hll_sketch_estimate(expr) | 返回给定 Datasketches HllSketch 的二进制表示形式估计的唯一值数量。 |
| hll_union(first, second, allowDifferentLgConfigK) | 使用 Datasketches Union 对象合并 Datasketches HllSketch 对象的两个二进制表示形式。将 allowDifferentLgConfigK 设置为 true 以允许合并具有不同 lgConfigK 值的草图（默认为 false）。 |
| input_file_block_length() | 返回正在读取的块的长度，如果不可用，则返回 -1。 |
| input_file_block_start() | 返回正在读取的块的起始偏移量，如果不可用，则返回 -1。 |
| input_file_name() | 返回正在读取的文件的名称，如果不可用，则返回空字符串。 |
| java_method(class, method[, arg1[, arg2 ..]]) | 使用反射调用方法。 |
| monotonically_increasing_id() | 返回单调递增的 64 位整数。生成的 ID 保证是单调递增且唯一的，但不一定是连续的。当前实现将分区 ID 放在高 31 位中，低 33 位表示每个分区中的记录号。假设数据帧少于 10 亿个分区，并且每个分区少于 80 亿条记录。该函数是不确定的，因为其结果取决于分区 ID。 |
| reflect(class, method[, arg1[, arg2 ..]]) | 使用反射调用方法。 |
| spark_partition_id() | 返回当前分区 ID。 |
| try_aes_decrypt(expr, key[, mode[, padding[, aad]]]) | 这是 `aes_decrypt` 的一个特殊版本，它执行相同的操作，但如果无法执行解密，则返回 NULL 值而不是引发错误。 |
| typeof(expr) | 返回输入数据类型的 DDL 格式类型字符串。 |
| user() | 当前执行上下文的用户名。 |
| uuid() | 返回一个通用唯一标识符 (UUID) 字符串。该值以规范的 36 个字符的 UUID 字符串形式返回。 |
| version() | 返回 Spark 版本。该字符串包含 2 个字段，第一个是发行版本，第二个是 git 修订版本。 |
示例
-- aes_decrypt
SELECT aes_decrypt(unhex('83F16B2AA704794132802D248E6BFD4E380078182D1544813898AC97E709B28A94'), '0000111122223333');
+------------------------------------------------------------------------------------------------------------------------+
|aes_decrypt(unhex(83F16B2AA704794132802D248E6BFD4E380078182D1544813898AC97E709B28A94), 0000111122223333, GCM, DEFAULT, )|
+------------------------------------------------------------------------------------------------------------------------+
| [53 70 61 72 6B]|
+------------------------------------------------------------------------------------------------------------------------+
SELECT aes_decrypt(unhex('6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
+--------------------------------------------------------------------------------------------------------------------------------+
|aes_decrypt(unhex(6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210), 0000111122223333, GCM, DEFAULT, )|
+--------------------------------------------------------------------------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+--------------------------------------------------------------------------------------------------------------------------------+
SELECT aes_decrypt(unbase64('3lmwu+Mw0H3fi5NDvcu9lg=='), '1234567890abcdef', 'ECB', 'PKCS');
+------------------------------------------------------------------------------+
|aes_decrypt(unbase64(3lmwu+Mw0H3fi5NDvcu9lg==), 1234567890abcdef, ECB, PKCS, )|
+------------------------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+------------------------------------------------------------------------------+
SELECT aes_decrypt(unbase64('2NYmDCjgXTbbxGA3/SnJEfFC/JQ7olk2VQWReIAAFKo='), '1234567890abcdef', 'CBC');
+-----------------------------------------------------------------------------------------------------+
|aes_decrypt(unbase64(2NYmDCjgXTbbxGA3/SnJEfFC/JQ7olk2VQWReIAAFKo=), 1234567890abcdef, CBC, DEFAULT, )|
+-----------------------------------------------------------------------------------------------------+
| [41 70 61 63 68 6...|
+-----------------------------------------------------------------------------------------------------+
SELECT aes_decrypt(unbase64('AAAAAAAAAAAAAAAAAAAAAPSd4mWyMZ5mhvjiAPQJnfg='), 'abcdefghijklmnop12345678ABCDEFGH', 'CBC', 'DEFAULT');
+---------------------------------------------------------------------------------------------------------------------+
|aes_decrypt(unbase64(AAAAAAAAAAAAAAAAAAAAAPSd4mWyMZ5mhvjiAPQJnfg=), abcdefghijklmnop12345678ABCDEFGH, CBC, DEFAULT, )|
+---------------------------------------------------------------------------------------------------------------------+
| [53 70 61 72 6B]|
+---------------------------------------------------------------------------------------------------------------------+
SELECT aes_decrypt(unbase64('AAAAAAAAAAAAAAAAQiYi+sTLm7KD9UcZ2nlRdYDe/PX4'), 'abcdefghijklmnop12345678ABCDEFGH', 'GCM', 'DEFAULT', 'This is an AAD mixed into the input');
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|aes_decrypt(unbase64(AAAAAAAAAAAAAAAAQiYi+sTLm7KD9UcZ2nlRdYDe/PX4), abcdefghijklmnop12345678ABCDEFGH, GCM, DEFAULT, This is an AAD mixed into the input)|
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| [53 70 61 72 6B]|
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
-- aes_encrypt
SELECT hex(aes_encrypt('Spark', '0000111122223333'));
+-----------------------------------------------------------+
|hex(aes_encrypt(Spark, 0000111122223333, GCM, DEFAULT, , ))|
+-----------------------------------------------------------+
| F30133168D792EAF3...|
+-----------------------------------------------------------+
SELECT hex(aes_encrypt('Spark SQL', '0000111122223333', 'GCM'));
+---------------------------------------------------------------+
|hex(aes_encrypt(Spark SQL, 0000111122223333, GCM, DEFAULT, , ))|
+---------------------------------------------------------------+
| 71E5323818BDA5893...|
+---------------------------------------------------------------+
SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'ECB', 'PKCS'));
+---------------------------------------------------------------+
|base64(aes_encrypt(Spark SQL, 1234567890abcdef, ECB, PKCS, , ))|
+---------------------------------------------------------------+
| 3lmwu+Mw0H3fi5NDv...|
+---------------------------------------------------------------+
SELECT base64(aes_encrypt('Apache Spark', '1234567890abcdef', 'CBC', 'DEFAULT'));
+---------------------------------------------------------------------+
|base64(aes_encrypt(Apache Spark, 1234567890abcdef, CBC, DEFAULT, , ))|
+---------------------------------------------------------------------+
| XPholDIX2Fq0MIEf4...|
+---------------------------------------------------------------------+
SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'CBC', 'DEFAULT', unhex('00000000000000000000000000000000')));
+---------------------------------------------------------------------------------------------------------------------+
|base64(aes_encrypt(Spark, abcdefghijklmnop12345678ABCDEFGH, CBC, DEFAULT, unhex(00000000000000000000000000000000), ))|
+---------------------------------------------------------------------------------------------------------------------+
| AAAAAAAAAAAAAAAAA...|
+---------------------------------------------------------------------------------------------------------------------+
SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'GCM', 'DEFAULT', unhex('000000000000000000000000'), 'This is an AAD mixed into the input'));
+------------------------------------------------------------------------------------------------------------------------------------------------+
|base64(aes_encrypt(Spark, abcdefghijklmnop12345678ABCDEFGH, GCM, DEFAULT, unhex(000000000000000000000000), This is an AAD mixed into the input))|
+------------------------------------------------------------------------------------------------------------------------------------------------+
| AAAAAAAAAAAAAAAAQ...|
+------------------------------------------------------------------------------------------------------------------------------------------------+
-- assert_true
SELECT assert_true(0 < 1);
+--------------------------------------------+
|assert_true((0 < 1), '(0 < 1)' is not true!)|
+--------------------------------------------+
| NULL|
+--------------------------------------------+
-- bitmap_bit_position
SELECT bitmap_bit_position(1);
+----------------------+
|bitmap_bit_position(1)|
+----------------------+
| 0|
+----------------------+
SELECT bitmap_bit_position(123);
+------------------------+
|bitmap_bit_position(123)|
+------------------------+
| 122|
+------------------------+
-- bitmap_bucket_number
SELECT bitmap_bucket_number(123);
+-------------------------+
|bitmap_bucket_number(123)|
+-------------------------+
| 1|
+-------------------------+
SELECT bitmap_bucket_number(0);
+-----------------------+
|bitmap_bucket_number(0)|
+-----------------------+
| 0|
+-----------------------+
-- bitmap_count
SELECT bitmap_count(X '1010');
+---------------------+
|bitmap_count(X'1010')|
+---------------------+
| 2|
+---------------------+
SELECT bitmap_count(X 'FFFF');
+---------------------+
|bitmap_count(X'FFFF')|
+---------------------+
| 16|
+---------------------+
SELECT bitmap_count(X '0');
+-------------------+
|bitmap_count(X'00')|
+-------------------+
| 0|
+-------------------+
-- current_catalog
SELECT current_catalog();
+-----------------+
|current_catalog()|
+-----------------+
| spark_catalog|
+-----------------+
-- current_database
SELECT current_database();
+------------------+
|current_database()|
+------------------+
| default|
+------------------+
-- current_schema
SELECT current_schema();
+------------------+
|current_database()|
+------------------+
| default|
+------------------+
-- current_user
SELECT current_user();
+--------------+
|current_user()|
+--------------+
| jungtaek.lim|
+--------------+
-- equal_null
SELECT equal_null(3, 3);
+----------------+
|equal_null(3, 3)|
+----------------+
| true|
+----------------+
SELECT equal_null(1, '11');
+-----------------+
|equal_null(1, 11)|
+-----------------+
| false|
+-----------------+
SELECT equal_null(true, NULL);
+----------------------+
|equal_null(true, NULL)|
+----------------------+
| false|
+----------------------+
SELECT equal_null(NULL, 'abc');
+---------------------+
|equal_null(NULL, abc)|
+---------------------+
| false|
+---------------------+
SELECT equal_null(NULL, NULL);
+----------------------+
|equal_null(NULL, NULL)|
+----------------------+
| true|
+----------------------+
-- hll_sketch_estimate
SELECT hll_sketch_estimate(hll_sketch_agg(col)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
+--------------------------------------------+
|hll_sketch_estimate(hll_sketch_agg(col, 12))|
+--------------------------------------------+
| 3|
+--------------------------------------------+
-- hll_union
SELECT hll_sketch_estimate(hll_union(hll_sketch_agg(col1), hll_sketch_agg(col2))) FROM VALUES (1, 4), (1, 4), (2, 5), (2, 5), (3, 6) tab(col1, col2);
+-----------------------------------------------------------------------------------------+
|hll_sketch_estimate(hll_union(hll_sketch_agg(col1, 12), hll_sketch_agg(col2, 12), false))|
+-----------------------------------------------------------------------------------------+
| 6|
+-----------------------------------------------------------------------------------------+
-- input_file_block_length
SELECT input_file_block_length();
+-------------------------+
|input_file_block_length()|
+-------------------------+
| -1|
+-------------------------+
-- input_file_block_start
SELECT input_file_block_start();
+------------------------+
|input_file_block_start()|
+------------------------+
| -1|
+------------------------+
-- input_file_name
SELECT input_file_name();
+-----------------+
|input_file_name()|
+-----------------+
| |
+-----------------+
-- java_method
SELECT java_method('java.util.UUID', 'randomUUID');
+---------------------------------------+
|java_method(java.util.UUID, randomUUID)|
+---------------------------------------+
| fe37ceed-2d82-464...|
+---------------------------------------+
SELECT java_method('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
+-----------------------------------------------------------------------------+
|java_method(java.util.UUID, fromString, a5cf6c42-0c85-418f-af6c-3e4e5b1328f2)|
+-----------------------------------------------------------------------------+
| a5cf6c42-0c85-418...|
+-----------------------------------------------------------------------------+
-- monotonically_increasing_id
SELECT monotonically_increasing_id();
+-----------------------------+
|monotonically_increasing_id()|
+-----------------------------+
| 0|
+-----------------------------+
-- reflect
SELECT reflect('java.util.UUID', 'randomUUID');
+-----------------------------------+
|reflect(java.util.UUID, randomUUID)|
+-----------------------------------+
| 7fec8bae-b80b-448...|
+-----------------------------------+
SELECT reflect('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
+-------------------------------------------------------------------------+
|reflect(java.util.UUID, fromString, a5cf6c42-0c85-418f-af6c-3e4e5b1328f2)|
+-------------------------------------------------------------------------+
| a5cf6c42-0c85-418...|
+-------------------------------------------------------------------------+
-- spark_partition_id
SELECT spark_partition_id();
+--------------------+
|SPARK_PARTITION_ID()|
+--------------------+
| 0|
+--------------------+
-- try_aes_decrypt
SELECT try_aes_decrypt(unhex('6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
+------------------------------------------------------------------------------------------------------------------------------------+
|try_aes_decrypt(unhex(6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210), 0000111122223333, GCM, DEFAULT, )|
+------------------------------------------------------------------------------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+------------------------------------------------------------------------------------------------------------------------------------+
SELECT try_aes_decrypt(unhex('----------468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
+------------------------------------------------------------------------------------------------------------------------------------+
|try_aes_decrypt(unhex(----------468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210), 0000111122223333, GCM, DEFAULT, )|
+------------------------------------------------------------------------------------------------------------------------------------+
| NULL|
+------------------------------------------------------------------------------------------------------------------------------------+
-- typeof
SELECT typeof(1);
+---------+
|typeof(1)|
+---------+
| int|
+---------+
SELECT typeof(array(1));
+----------------+
|typeof(array(1))|
+----------------+
| array<int>|
+----------------+
-- user
SELECT user();
+--------------+
|current_user()|
+--------------+
| jungtaek.lim|
+--------------+
-- uuid
SELECT uuid();
+--------------------+
| uuid()|
+--------------------+
|55eab4e0-7a6a-444...|
+--------------------+
-- version
SELECT version();
+--------------------+
| version()|
+--------------------+
|3.5.1 fd86f85e181...|
+--------------------+
生成器函数
| 函数 | 描述 |
|---|---|
| explode(expr) | 将数组 `expr` 的元素分成多行，或将映射 `expr` 的元素分成多行和多列。除非另有指定，否则使用默认列名 `col` 表示数组元素，使用 `key` 和 `value` 表示映射元素。 |
| explode_outer(expr) | 将数组 `expr` 的元素分成多行，或将映射 `expr` 的元素分成多行和多列。除非另有指定，否则使用默认列名 `col` 表示数组元素，使用 `key` 和 `value` 表示映射元素。 |
| inline(expr) | 将结构体数组展开为一个表。默认情况下使用列名 col1、col2 等，除非另有指定。 |
| inline_outer(expr) | 将结构体数组展开为一个表。默认情况下使用列名 col1、col2 等，除非另有指定。 |
| posexplode(expr) | 将数组 `expr` 的元素分成带位置的多行，或将映射 `expr` 的元素分成带位置的多行和多列。除非另有指定，否则使用列名 `pos` 表示位置，`col` 表示数组元素，`key` 和 `value` 表示映射元素。 |
| posexplode_outer(expr) | 将数组 `expr` 的元素分成带位置的多行，或将映射 `expr` 的元素分成带位置的多行和多列。除非另有指定，否则使用列名 `pos` 表示位置，`col` 表示数组元素，`key` 和 `value` 表示映射元素。 |
| stack(n, expr1, ..., exprk) | 将 `expr1`、...、`exprk` 分成 `n` 行。默认情况下使用列名 col0、col1 等，除非另有指定。 |
示例
-- explode
SELECT explode(array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT explode(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT * FROM explode(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
-- explode_outer
SELECT explode_outer(array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT explode_outer(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT * FROM explode_outer(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
-- inline
SELECT inline(array(struct(1, 'a'), struct(2, 'b')));
+----+----+
|col1|col2|
+----+----+
| 1| a|
| 2| b|
+----+----+
-- inline_outer
SELECT inline_outer(array(struct(1, 'a'), struct(2, 'b')));
+----+----+
|col1|col2|
+----+----+
| 1| a|
| 2| b|
+----+----+
-- posexplode
SELECT posexplode(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
SELECT * FROM posexplode(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
-- posexplode_outer
SELECT posexplode_outer(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
SELECT * FROM posexplode_outer(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
-- stack
SELECT stack(2, 1, 2, 3);
+----+----+
|col0|col1|
+----+----+
| 1| 2|
| 3|NULL|
+----+----+