内置函数
聚合函数
| 函数 | 描述 |
|---|---|
| any(expr) | 如果 `expr` 的至少一个值为 true，则返回 true。 |
| any_value(expr[, isIgnoreNull]) | 返回一组行中 `expr` 的某个值。如果 `isIgnoreNull` 为 true，则只返回非 null 值。 |
| approx_count_distinct(expr[, relativeSD]) | 使用 HyperLogLog++ 返回估计的基数。`relativeSD` 定义允许的最大相对标准差。 |
| approx_percentile(col, percentage [, accuracy]) | 返回数值或 ANSI 区间列 `col` 的近似 `百分位数`，它是 `col` 有序值（从小到大排序）中最小的值，使得 `col` 值中不超过 `percentage` 的值小于或等于该值。百分位数的值必须介于 0.0 和 1.0 之间。`accuracy` 参数（默认值：10000）是一个正数值字面量，它以内存为代价控制近似精度。`accuracy` 的值越高，精度越好，`1.0/accuracy` 是近似的相对误差。当 `percentage` 是一个数组时，百分位数数组中的每个值必须介于 0.0 和 1.0 之间。在这种情况下，返回给定百分位数数组中列 `col` 的近似百分位数数组。 |
| array_agg(expr) | 收集并返回一个非唯一元素的列表。 |
| avg(expr) | 返回从一组值计算的平均值。 |
| bit_and(expr) | 返回所有非空输入值的按位 AND，如果没有则返回 null。 |
| bit_or(expr) | 返回所有非空输入值的按位 OR，如果没有则返回 null。 |
| bit_xor(expr) | 返回所有非空输入值的按位 XOR，如果没有则返回 null。 |
| bitmap_construct_agg(child) | 返回一个位图，其中包含从子表达式的所有值设置的位的位置。子表达式很可能是 bitmap_bit_position()。 |
| bitmap_or_agg(child) | 返回一个位图，它是子表达式中所有位图的按位 OR。输入应该是从 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...]) | 返回所提供的表达式全部非 null 的行数。 |
| count(DISTINCT expr[, expr...]) | 返回所提供的表达式唯一且非 null 的行数。 |
| count_if(expr) | 返回表达式的 `TRUE` 值数量。 |
| count_min_sketch(col, eps, confidence, seed) | 返回给定 esp、置信度和 seed 的列的 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，则只返回非 null 值。 |
| first_value(expr[, isIgnoreNull]) | 返回一组行中 `expr` 的第一个值。如果 `isIgnoreNull` 为 true，则只返回非 null 值。 |
| 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 个桶计算数值 'expr' 的直方图。返回值是一个 (x,y) 对数组，表示直方图桶的中心。随着 'nb' 值的增加，直方图近似变得更细粒度，但可能会在异常值周围产生伪影。实践中，20-40 个直方图桶效果良好，对于偏斜或较小的数据集需要更多桶。请注意，此函数创建的直方图具有非均匀的桶宽度。它不保证直方图的均方误差，但在实践中与 R/S-Plus 统计计算包生成的直方图相当。注意：返回值中 'x' 字段的输出类型是从聚合函数中消耗的输入值传播的。 |
| hll_sketch_agg(expr, lgConfigK) | 返回 HllSketch 的可更新二进制表示。`lgConfigK`（可选）是 K 的 log-base-2，其中 K 是 HllSketch 的桶或槽的数量。 |
| hll_union_agg(expr, allowDifferentLgConfigK) | 返回唯一值的估计数量。`allowDifferentLgConfigK`（可选）允许合并具有不同 lgConfigK 值的草图（默认为 false）。 |
| kurtosis(expr) | 返回从一组值计算的峰度值。 |
| last(expr[, isIgnoreNull]) | 返回一组行中 `expr` 的最后一个值。如果 `isIgnoreNull` 为 true，则只返回非 null 值。 |
| last_value(expr[, isIgnoreNull]) | 返回一组行中 `expr` 的最后一个值。如果 `isIgnoreNull` 为 true，则只返回非 null 值。 |
| listagg(expr[, delimiter])[ WITHIN GROUP (ORDER BY key [ASC | DESC] [,...])] | 返回非 NULL 输入值的连接，由分隔符按键排序。如果所有值都为 NULL，则返回 NULL。 |
| 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[, deterministic]) | 返回 `col` 中值出现频率最高的值。NULL 值将被忽略。如果所有值均为 NULL 或行数为 0，则返回 NULL。当多个值具有相同的最高频率时，如果 `deterministic` 为 false 或未定义，则返回其中任意一个值；如果 `deterministic` 为 true，则返回最低值。 |
| mode() WITHIN GROUP (ORDER BY col) | 返回 `col` 中出现频率最高的值（在 ORDER BY 子句中指定）。NULL 值将被忽略。如果所有值均为 NULL 或行数为 0，则返回 NULL。当多个值具有相同的最高频率时，只返回一个值。该值将根据排序方向选择。如果排序方向为 asc，则返回具有相同频率的多个值中的最小值；如果排序方向为 desc，则返回最大值。 |
| 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` 的近似 `百分位数`，它是 `col` 有序值（从小到大排序）中最小的值，使得 `col` 值中不超过 `percentage` 的值小于或等于该值。百分位数的值必须介于 0.0 和 1.0 之间。`accuracy` 参数（默认值：10000）是一个正数值字面量，它以内存为代价控制近似精度。`accuracy` 的值越高，精度越好，`1.0/accuracy` 是近似的相对误差。当 `percentage` 是一个数组时，百分位数数组中的每个值必须介于 0.0 和 1.0 之间。在这种情况下，返回给定百分位数数组中列 `col` 的近似百分位数数组。 |
| percentile_cont(percentage) WITHIN GROUP (ORDER BY col) | 根据数值或 ANSI 区间列 `col` 的连续分布，返回给定 `percentage`（在 ORDER BY 子句中指定）处的百分位数。 |
| percentile_disc(percentage) WITHIN GROUP (ORDER BY col) | 根据数值或 ANSI 区间列 `col` 的离散分布，返回给定 `percentage`（在 ORDER BY 子句中指定）处的百分位数。 |
| regr_avgx(y, x) | 返回组中非 null 对的自变量平均值，其中 `y` 是因变量，`x` 是自变量。 |
| regr_avgy(y, x) | 返回组中非 null 对的因变量平均值，其中 `y` 是因变量，`x` 是自变量。 |
| regr_count(y, x) | 返回组中非 null 数值对的数量，其中 `y` 是因变量，`x` 是自变量。 |
| regr_intercept(y, x) | 返回组中非 null 对的一元线性回归线的截距，其中 `y` 是因变量，`x` 是自变量。 |
| regr_r2(y, x) | 返回组中非 null 对的决定系数，其中 `y` 是因变量，`x` 是自变量。 |
| regr_slope(y, x) | 返回组中非 null 对的线性回归线的斜率，其中 `y` 是因变量，`x` 是自变量。 |
| regr_sxx(y, x) | 返回组中非 null 对的 REGR_COUNT(y, x) * VAR_POP(x)，其中 `y` 是因变量，`x` 是自变量。 |
| regr_sxy(y, x) | 返回组中非 null 对的 REGR_COUNT(y, x) * COVAR_POP(y, x)，其中 `y` 是因变量，`x` 是自变量。 |
| regr_syy(y, x) | 返回组中非 null 对的 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) | 返回从一组值计算的样本标准差。 |
| string_agg(expr[, delimiter])[ WITHIN GROUP (ORDER BY key [ASC | DESC] [,...])] | 返回非 NULL 输入值的连接，由分隔符按键排序。如果所有值都为 NULL，则返回 NULL。 |
| 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|
+---------------+
-- listagg
SELECT listagg(col) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
+------------------+
|listagg(col, NULL)|
+------------------+
| abc|
+------------------+
SELECT listagg(col) WITHIN GROUP (ORDER BY col DESC) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
+--------------------------------------------------------------+
|listagg(col, NULL) WITHIN GROUP (ORDER BY col DESC NULLS LAST)|
+--------------------------------------------------------------+
| cba|
+--------------------------------------------------------------+
SELECT listagg(col) FROM VALUES ('a'), (NULL), ('b') AS tab(col);
+------------------+
|listagg(col, NULL)|
+------------------+
| ab|
+------------------+
SELECT listagg(col) FROM VALUES ('a'), ('a') AS tab(col);
+------------------+
|listagg(col, NULL)|
+------------------+
| aa|
+------------------+
SELECT listagg(DISTINCT col) FROM VALUES ('a'), ('a'), ('b') AS tab(col);
+---------------------------+
|listagg(DISTINCT col, NULL)|
+---------------------------+
| ab|
+---------------------------+
SELECT listagg(col, ', ') FROM VALUES ('a'), ('b'), ('c') AS tab(col);
+----------------+
|listagg(col, , )|
+----------------+
| a, b, c|
+----------------+
SELECT listagg(col) FROM VALUES (NULL), (NULL) AS tab(col);
+------------------+
|listagg(col, NULL)|
+------------------+
| NULL|
+------------------+
-- 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|
+---------+
SELECT mode(col, false) FROM VALUES (-10), (0), (10) AS tab(col);
+---------+
|mode(col)|
+---------+
| 0|
+---------+
SELECT mode(col, true) FROM VALUES (-10), (0), (10) AS tab(col);
+---------------------------------------+
|mode() WITHIN GROUP (ORDER BY col DESC)|
+---------------------------------------+
| -10|
+---------------------------------------+
SELECT mode() WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10), (10) AS tab(col);
+---------------------------------------+
|mode() WITHIN GROUP (ORDER BY col DESC)|
+---------------------------------------+
| 10|
+---------------------------------------+
SELECT mode() WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10), (10), (20), (20) AS tab(col);
+---------------------------------------+
|mode() WITHIN GROUP (ORDER BY col DESC)|
+---------------------------------------+
| 10|
+---------------------------------------+
SELECT mode() WITHIN GROUP (ORDER BY col DESC) FROM VALUES (0), (10), (10), (20), (20) AS tab(col);
+----------------------------------+
|mode() WITHIN GROUP (ORDER BY col)|
+----------------------------------+
| 20|
+----------------------------------+
-- 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...|
+--------------------------------------------+
-- percentile_cont
SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10) AS tab(col);
+-------------------------------------------------+
|percentile_cont(0.25) WITHIN GROUP (ORDER BY col)|
+-------------------------------------------------+
| 2.5|
+-------------------------------------------------+
SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+-------------------------------------------------+
|percentile_cont(0.25) WITHIN GROUP (ORDER BY col)|
+-------------------------------------------------+
| INTERVAL '0-2' YE...|
+-------------------------------------------------+
-- percentile_disc
SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10) AS tab(col);
+-------------------------------------------------+
|percentile_disc(0.25) WITHIN GROUP (ORDER BY col)|
+-------------------------------------------------+
| 0.0|
+-------------------------------------------------+
SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+-------------------------------------------------+
|percentile_disc(0.25) WITHIN GROUP (ORDER BY col)|
+-------------------------------------------------+
| INTERVAL '0-0' YE...|
+-------------------------------------------------+
-- 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, null) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 0|
+----------------+
SELECT regr_count(y, x) FROM VALUES (null, 1) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 0|
+----------------+
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), (4, 4) 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|
+--------------------+
SELECT regr_intercept(y, x) FROM VALUES (1, 1), (2, null), (3, 3), (4, 4) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| 0.0|
+--------------------+
SELECT regr_intercept(y, x) FROM VALUES (1, 1), (2, null), (null, 3), (4, 4) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| 0.0|
+--------------------+
-- 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), (4, 4) 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|
+----------------+
SELECT regr_slope(y, x) FROM VALUES (1, 1), (2, null), (3, 3), (4, 4) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| 1.0|
+----------------+
SELECT regr_slope(y, x) FROM VALUES (1, 1), (2, null), (null, 3), (4, 4) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| 1.0|
+----------------+
-- 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, null) AS tab(y, x);
+--------------+
|regr_sxx(y, x)|
+--------------+
| NULL|
+--------------+
SELECT regr_sxx(y, x) FROM VALUES (null, 1) AS tab(y, x);
+--------------+
|regr_sxx(y, x)|
+--------------+
| NULL|
+--------------+
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, null) AS tab(y, x);
+--------------+
|regr_sxy(y, x)|
+--------------+
| NULL|
+--------------+
SELECT regr_sxy(y, x) FROM VALUES (null, 1) AS tab(y, x);
+--------------+
|regr_sxy(y, x)|
+--------------+
| NULL|
+--------------+
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, null) AS tab(y, x);
+--------------+
|regr_syy(y, x)|
+--------------+
| NULL|
+--------------+
SELECT regr_syy(y, x) FROM VALUES (null, 1) AS tab(y, x);
+--------------+
|regr_syy(y, x)|
+--------------+
| NULL|
+--------------+
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|
+----------------+
-- string_agg
SELECT string_agg(col) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
+---------------------+
|string_agg(col, NULL)|
+---------------------+
| abc|
+---------------------+
SELECT string_agg(col) WITHIN GROUP (ORDER BY col DESC) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
+--------------------------------------------------------------+
|listagg(col, NULL) WITHIN GROUP (ORDER BY col DESC NULLS LAST)|
+--------------------------------------------------------------+
| cba|
+--------------------------------------------------------------+
SELECT string_agg(col) FROM VALUES ('a'), (NULL), ('b') AS tab(col);
+---------------------+
|string_agg(col, NULL)|
+---------------------+
| ab|
+---------------------+
SELECT string_agg(col) FROM VALUES ('a'), ('a') AS tab(col);
+---------------------+
|string_agg(col, NULL)|
+---------------------+
| aa|
+---------------------+
SELECT string_agg(DISTINCT col) FROM VALUES ('a'), ('a'), ('b') AS tab(col);
+------------------------------+
|string_agg(DISTINCT col, NULL)|
+------------------------------+
| ab|
+------------------------------+
SELECT string_agg(col, ', ') FROM VALUES ('a'), ('b'), ('c') AS tab(col);
+-------------------+
|string_agg(col, , )|
+-------------------+
| a, b, c|
+-------------------+
SELECT string_agg(col) FROM VALUES (NULL), (NULL) AS tab(col);
+---------------------+
|string_agg(col, NULL)|
+---------------------+
| NULL|
+---------------------+
-- 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() | 计算一组值中值的排名。结果是先前分配的排名值加一。与函数 rank 不同，dense_rank 不会在排名序列中产生间隙。 |
| lag(input[, offset[, default]]) | 返回窗口中当前行之前第 `offset` 行的 `input` 值。`offset` 的默认值为 1，`default` 的默认值为 null。如果第 `offset` 行的 `input` 值为 null，则返回 null。如果没有这样的偏移行（例如，当 offset 为 1 时，窗口的第一行没有前一行），则返回 `default`。 |
| lead(input[, offset[, default]]) | 返回窗口中当前行之后第 `offset` 行的 `input` 值。`offset` 的默认值为 1，`default` 的默认值为 null。如果第 `offset` 行的 `input` 值为 null，则返回 null。如果没有这样的偏移行（例如，当 offset 为 1 时，窗口的最后一行没有后续行），则返回 `default`。 |
| nth_value(input[, offset]) | 返回窗口帧开头起第 `offset` 行的 `input` 值。偏移量从 1 开始。如果 ignoreNulls=true，则在查找第 `offset` 行时我们将跳过 null 值。否则，每行都计入 `offset`。如果不存在这样的第 `offset` 行（例如，当 offset 为 10 时，窗口帧的大小小于 10），则返回 null。 |
| ntile(n) | 将每个窗口分区的行划分为 `n` 个桶，范围从 1 到最多 `n`。 |
| percent_rank() | 计算一组值中值的百分比排名。 |
| rank() | 计算一组值中值的排名。结果是分区排序中当前行之前或等于当前行的行数加一。这些值将在序列中产生间隙。 |
| row_number() | 根据窗口分区中行的顺序，为每行分配一个从一开始的唯一序列号。 |
示例
-- 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，则输出为 NULL。 |
| array_compact(array) | 从数组中移除 null 值。 |
| 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' 元素，如果索引为负数则在数组开头预置 'null' 元素。 |
| array_intersect(array1, array2) | 返回一个数组，其中包含 array1 和 array2 交集中的元素，且不重复。 |
| array_join(array, delimiter[, nullReplacement]) | 使用分隔符和可选的字符串替换 null 值来连接给定数组的元素。如果没有为 nullReplacement 设置值，则过滤掉任何 null 值。 |
| 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，则输出为 NULL。 |
| array_remove(array, element) | 从数组中移除所有等于 element 的元素。 |
| array_repeat(element, count) | 返回包含 element 出现 count 次的数组。 |
| array_size(expr) | 返回数组的大小。对于 null 输入，函数返回 null。 |
| array_union(array1, array2) | 返回一个数组，其中包含 array1 和 array2 并集中的元素，且不重复。 |
| arrays_overlap(a1, a2) | 如果 a1 包含至少一个非 null 且也在 a2 中存在的元素，则返回 true。如果数组没有共同元素，并且它们都非空，并且其中任何一个包含 null 元素，则返回 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 元素。Null 元素在升序排列时放置在返回数组的开头，在降序排列时放置在返回数组的末尾。 |
示例
-- 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_size
SELECT array_size(array('b', 'd', 'c', 'a'));
+-----------------------------+
|array_size(array(b, d, c, a))|
+-----------------------------+
| 4|
+-----------------------------+
-- 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, 3, 20, 1]|
+---------------------------+
SELECT shuffle(array(1, 20, null, 3));
+------------------------------+
|shuffle(array(1, 20, NULL, 3))|
+------------------------------+
| [3, 20, 1, NULL]|
+------------------------------+
-- 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]|
+-----------------------------------------+
SELECT sort_array(array('b', 'd', null, 'c', 'a'), false);
+------------------------------------------+
|sort_array(array(b, d, NULL, c, a), false)|
+------------------------------------------+
| [d, c, b, a, NULL]|
+------------------------------------------+
集合函数
| 函数 | 描述 |
|---|---|
| aggregate(expr, start, merge, finish) | 将二元运算符应用于初始状态和数组中的所有元素，并将其归约为单一状态。通过应用完成函数将最终状态转换为最终结果。 |
| array_sort(expr, func) | 对输入数组进行排序。如果省略 func，则按升序排序。输入数组的元素必须是可排序的。对于 double/float 类型，NaN 大于任何非 NaN 元素。Null 元素将被放置在返回数组的末尾。自 3.0.0 版本以来，此函数还根据给定的比较器函数对数组进行排序并返回。比较器将接受两个参数，表示数组的两个元素。如果第一个元素小于、等于或大于第二个元素，则返回负整数、0 或正整数。如果比较器函数返回 null，则函数将失败并引发错误。 |
| cardinality(expr) | 返回数组或映射的大小。仅当 spark.sql.ansi.enabled 为 false 且 spark.sql.legacy.sizeOfNull 为 true 时，此函数对 null 输入返回 -1。否则，对 null 输入返回 null。在默认设置下，函数对 null 输入返回 null。 |
| concat(col1, col2, ..., colN) | 返回 col1, col2, ..., colN 的连接。 |
| element_at(array, index) | 返回给定（基于 1 的）索引处的数组元素。如果索引为 0，Spark 将抛出错误。如果索引 < 0，则从后往前访问元素。如果索引超出数组长度且 `spark.sql.ansi.enabled` 设置为 false，则函数返回 NULL。如果 `spark.sql.ansi.enabled` 设置为 true，则对于无效索引，它会抛出 ArrayIndexOutOfBoundsException。 |
| element_at(map, key) | 返回给定键的值。如果映射中不包含该键，则函数返回 NULL。 |
| exists(expr, pred) | 测试谓词是否对数组中的一个或多个元素成立。 |
| filter(expr, func) | 使用给定谓词过滤输入数组。 |
| forall(expr, pred) | 测试谓词是否对数组中的所有元素成立。 |
| map_filter(expr, func) | 使用函数过滤映射中的条目。 |
| map_zip_with(map1, map2, function) | 通过将函数应用于具有相同键的值对，将两个给定映射合并为一个映射。对于只在一个映射中存在的键，NULL 将作为缺失键的值传递。如果输入映射包含重复键，则只有重复键的第一个条目会传递给 lambda 函数。 |
| reduce(expr, start, merge, finish) | 将二元运算符应用于初始状态和数组中的所有元素，并将其归约为单一状态。通过应用完成函数将最终状态转换为最终结果。 |
| reverse(array) | 返回一个反转的字符串或元素顺序反转的数组。 |
| size(expr) | 返回数组或映射的大小。仅当 spark.sql.ansi.enabled 为 false 且 spark.sql.legacy.sizeOfNull 为 true 时，此函数对 null 输入返回 -1。否则，对 null 输入返回 null。在默认设置下，函数对 null 输入返回 null。 |
| transform(expr, func) | 使用函数转换数组中的元素。 |
| transform_keys(expr, func) | 使用函数转换映射中的元素。 |
| transform_values(expr, func) | 使用函数转换映射中的值。 |
| try_element_at(array, index) | 返回给定（基于 1 的）索引处的数组元素。如果索引为 0，Spark 将抛出错误。如果索引 < 0，则从后往前访问元素。如果索引超出数组长度，则函数始终返回 NULL。 |
| try_element_at(map, key) | 返回给定键的值。如果映射中不包含该键，则函数始终返回 NULL。 |
| zip_with(left, right, func) | 使用函数将两个给定数组逐元素合并为单个数组。如果一个数组较短，则在应用函数之前，会在末尾追加 null 以匹配较长数组的长度。 |
示例
-- aggregate
SELECT aggregate(array(1, 2, 3), 0, (acc, x) -> acc + x);
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|aggregate(array(1, 2, 3), 0, lambdafunction((namedlambdavariable() + namedlambdavariable()), namedlambdavariable(), namedlambdavariable()), lambdafunction(namedlambdavariable(), namedlambdavariable()))|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 6|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SELECT aggregate(array(1, 2, 3), 0, (acc, x) -> acc + x, acc -> acc * 10);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|aggregate(array(1, 2, 3), 0, lambdafunction((namedlambdavariable() + namedlambdavariable()), namedlambdavariable(), namedlambdavariable()), lambdafunction((namedlambdavariable() * 10), namedlambdavariable()))|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 60|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- array_sort
SELECT array_sort(array(5, 6, 1), (left, right) -> case when left < right then -1 when left > right then 1 else 0 end);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|array_sort(array(5, 6, 1), lambdafunction(CASE WHEN (namedlambdavariable() < namedlambdavariable()) THEN -1 WHEN (namedlambdavariable() > namedlambdavariable()) THEN 1 ELSE 0 END, namedlambdavariable(), namedlambdavariable()))|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [1, 5, 6]|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SELECT array_sort(array('bc', 'ab', 'dc'), (left, right) -> case when left is null and right is null then 0 when left is null then -1 when right is null then 1 when left < right then 1 when left > right then -1 else 0 end);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|array_sort(array(bc, ab, dc), lambdafunction(CASE WHEN ((namedlambdavariable() IS NULL) AND (namedlambdavariable() IS NULL)) THEN 0 WHEN (namedlambdavariable() IS NULL) THEN -1 WHEN (namedlambdavariable() IS NULL) THEN 1 WHEN (namedlambdavariable() < namedlambdavariable()) THEN 1 WHEN (namedlambdavariable() > namedlambdavariable()) THEN -1 ELSE 0 END, namedlambdavariable(), namedlambdavariable()))|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [dc, bc, ab]|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SELECT array_sort(array('b', 'd', null, 'c', 'a'));
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|array_sort(array(b, d, NULL, c, a), lambdafunction((IF(((namedlambdavariable() IS NULL) AND (namedlambdavariable() IS NULL)), 0, (IF((namedlambdavariable() IS NULL), 1, (IF((namedlambdavariable() IS NULL), -1, (IF((namedlambdavariable() < namedlambdavariable()), -1, (IF((namedlambdavariable() > namedlambdavariable()), 1, 0)))))))))), namedlambdavariable(), namedlambdavariable()))|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [a, b, c, d, NULL]|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- cardinality
SELECT cardinality(array('b', 'd', 'c', 'a'));
+------------------------------+
|cardinality(array(b, d, c, a))|
+------------------------------+
| 4|
+------------------------------+
SELECT cardinality(map('a', 1, 'b', 2));
+----------------------------+
|cardinality(map(a, 1, b, 2))|
+----------------------------+
| 2|
+----------------------------+
-- concat
SELECT concat('Spark', 'SQL');
+------------------+
|concat(Spark, SQL)|
+------------------+
| SparkSQL|
+------------------+
SELECT concat(array(1, 2, 3), array(4, 5), array(6));
+---------------------------------------------+
|concat(array(1, 2, 3), array(4, 5), array(6))|
+---------------------------------------------+
| [1, 2, 3, 4, 5, 6]|
+---------------------------------------------+
-- 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|
+------------------------------+
-- exists
SELECT exists(array(1, 2, 3), x -> x % 2 == 0);
+------------------------------------------------------------------------------------------------+
|exists(array(1, 2, 3), lambdafunction(((namedlambdavariable() % 2) = 0), namedlambdavariable()))|
+------------------------------------------------------------------------------------------------+
| true|
+------------------------------------------------------------------------------------------------+
SELECT exists(array(1, 2, 3), x -> x % 2 == 10);
+-------------------------------------------------------------------------------------------------+
|exists(array(1, 2, 3), lambdafunction(((namedlambdavariable() % 2) = 10), namedlambdavariable()))|
+-------------------------------------------------------------------------------------------------+
| false|
+-------------------------------------------------------------------------------------------------+
SELECT exists(array(1, null, 3), x -> x % 2 == 0);
+---------------------------------------------------------------------------------------------------+
|exists(array(1, NULL, 3), lambdafunction(((namedlambdavariable() % 2) = 0), namedlambdavariable()))|
+---------------------------------------------------------------------------------------------------+
| NULL|
+---------------------------------------------------------------------------------------------------+
SELECT exists(array(0, null, 2, 3, null), x -> x IS NULL);
+----------------------------------------------------------------------------------------------------------+
|exists(array(0, NULL, 2, 3, NULL), lambdafunction((namedlambdavariable() IS NULL), namedlambdavariable()))|
+----------------------------------------------------------------------------------------------------------+
| true|
+----------------------------------------------------------------------------------------------------------+
SELECT exists(array(1, 2, 3), x -> x IS NULL);
+----------------------------------------------------------------------------------------------+
|exists(array(1, 2, 3), lambdafunction((namedlambdavariable() IS NULL), namedlambdavariable()))|
+----------------------------------------------------------------------------------------------+
| false|
+----------------------------------------------------------------------------------------------+
-- filter
SELECT filter(array(1, 2, 3), x -> x % 2 == 1);
+------------------------------------------------------------------------------------------------+
|filter(array(1, 2, 3), lambdafunction(((namedlambdavariable() % 2) = 1), namedlambdavariable()))|
+------------------------------------------------------------------------------------------------+
| [1, 3]|
+------------------------------------------------------------------------------------------------+
SELECT filter(array(0, 2, 3), (x, i) -> x > i);
+-------------------------------------------------------------------------------------------------------------------------------------+
|filter(array(0, 2, 3), lambdafunction((namedlambdavariable() > namedlambdavariable()), namedlambdavariable(), namedlambdavariable()))|
+-------------------------------------------------------------------------------------------------------------------------------------+
| [2, 3]|
+-------------------------------------------------------------------------------------------------------------------------------------+
SELECT filter(array(0, null, 2, 3, null), x -> x IS NOT NULL);
+--------------------------------------------------------------------------------------------------------------+
|filter(array(0, NULL, 2, 3, NULL), lambdafunction((namedlambdavariable() IS NOT NULL), namedlambdavariable()))|
+--------------------------------------------------------------------------------------------------------------+
| [0, 2, 3]|
+--------------------------------------------------------------------------------------------------------------+
-- forall
SELECT forall(array(1, 2, 3), x -> x % 2 == 0);
+------------------------------------------------------------------------------------------------+
|forall(array(1, 2, 3), lambdafunction(((namedlambdavariable() % 2) = 0), namedlambdavariable()))|
+------------------------------------------------------------------------------------------------+
| false|
+------------------------------------------------------------------------------------------------+
SELECT forall(array(2, 4, 8), x -> x % 2 == 0);
+------------------------------------------------------------------------------------------------+
|forall(array(2, 4, 8), lambdafunction(((namedlambdavariable() % 2) = 0), namedlambdavariable()))|
+------------------------------------------------------------------------------------------------+
| true|
+------------------------------------------------------------------------------------------------+
SELECT forall(array(1, null, 3), x -> x % 2 == 0);
+---------------------------------------------------------------------------------------------------+
|forall(array(1, NULL, 3), lambdafunction(((namedlambdavariable() % 2) = 0), namedlambdavariable()))|
+---------------------------------------------------------------------------------------------------+
| false|
+---------------------------------------------------------------------------------------------------+
SELECT forall(array(2, null, 8), x -> x % 2 == 0);
+---------------------------------------------------------------------------------------------------+
|forall(array(2, NULL, 8), lambdafunction(((namedlambdavariable() % 2) = 0), namedlambdavariable()))|
+---------------------------------------------------------------------------------------------------+
| NULL|
+---------------------------------------------------------------------------------------------------+
-- map_filter
SELECT map_filter(map(1, 0, 2, 2, 3, -1), (k, v) -> k > v);
+-------------------------------------------------------------------------------------------------------------------------------------------------+
|map_filter(map(1, 0, 2, 2, 3, -1), lambdafunction((namedlambdavariable() > namedlambdavariable()), namedlambdavariable(), namedlambdavariable()))|
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| {1 -> 0, 3 -> -1}|
+-------------------------------------------------------------------------------------------------------------------------------------------------+
-- map_zip_with
SELECT map_zip_with(map(1, 'a', 2, 'b'), map(1, 'x', 2, 'y'), (k, v1, v2) -> concat(v1, v2));
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|map_zip_with(map(1, a, 2, b), map(1, x, 2, y), lambdafunction(concat(namedlambdavariable(), namedlambdavariable()), namedlambdavariable(), namedlambdavariable(), namedlambdavariable()))|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {1 -> ax, 2 -> by}|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SELECT map_zip_with(map('a', 1, 'b', 2), map('b', 3, 'c', 4), (k, v1, v2) -> coalesce(v1, 0) + coalesce(v2, 0));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|map_zip_with(map(a, 1, b, 2), map(b, 3, c, 4), lambdafunction((coalesce(namedlambdavariable(), 0) + coalesce(namedlambdavariable(), 0)), namedlambdavariable(), namedlambdavariable(), namedlambdavariable()))|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {a -> 1, b -> 5, ...|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- reduce
SELECT reduce(array(1, 2, 3), 0, (acc, x) -> acc + x);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|reduce(array(1, 2, 3), 0, lambdafunction((namedlambdavariable() + namedlambdavariable()), namedlambdavariable(), namedlambdavariable()), lambdafunction(namedlambdavariable(), namedlambdavariable()))|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 6|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SELECT reduce(array(1, 2, 3), 0, (acc, x) -> acc + x, acc -> acc * 10);
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|reduce(array(1, 2, 3), 0, lambdafunction((namedlambdavariable() + namedlambdavariable()), namedlambdavariable(), namedlambdavariable()), lambdafunction((namedlambdavariable() * 10), namedlambdavariable()))|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 60|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- reverse
SELECT reverse('Spark SQL');
+------------------+
|reverse(Spark SQL)|
+------------------+
| LQS krapS|
+------------------+
SELECT reverse(array(2, 1, 4, 3));
+--------------------------+
|reverse(array(2, 1, 4, 3))|
+--------------------------+
| [3, 4, 1, 2]|
+--------------------------+
-- size
SELECT size(array('b', 'd', 'c', 'a'));
+-----------------------+
|size(array(b, d, c, a))|
+-----------------------+
| 4|
+-----------------------+
SELECT size(map('a', 1, 'b', 2));
+---------------------+
|size(map(a, 1, b, 2))|
+---------------------+
| 2|
+---------------------+
-- transform
SELECT transform(array(1, 2, 3), x -> x + 1);
+---------------------------------------------------------------------------------------------+
|transform(array(1, 2, 3), lambdafunction((namedlambdavariable() + 1), namedlambdavariable()))|
+---------------------------------------------------------------------------------------------+
| [2, 3, 4]|
+---------------------------------------------------------------------------------------------+
SELECT transform(array(1, 2, 3), (x, i) -> x + i);
+----------------------------------------------------------------------------------------------------------------------------------------+
|transform(array(1, 2, 3), lambdafunction((namedlambdavariable() + namedlambdavariable()), namedlambdavariable(), namedlambdavariable()))|
+----------------------------------------------------------------------------------------------------------------------------------------+
| [1, 3, 5]|
+----------------------------------------------------------------------------------------------------------------------------------------+
-- transform_keys
SELECT transform_keys(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -> k + 1);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
|transform_keys(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), lambdafunction((namedlambdavariable() + 1), namedlambdavariable(), namedlambdavariable()))|
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| {2 -> 1, 3 -> 2, ...|
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
SELECT transform_keys(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -> k + v);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|transform_keys(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), lambdafunction((namedlambdavariable() + namedlambdavariable()), namedlambdavariable(), namedlambdavariable()))|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {2 -> 1, 4 -> 2, ...|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- transform_values
SELECT transform_values(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -> v + 1);
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|transform_values(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), lambdafunction((namedlambdavariable() + 1), namedlambdavariable(), namedlambdavariable()))|
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {1 -> 2, 2 -> 3, ...|
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
SELECT transform_values(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -> k + v);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|transform_values(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), lambdafunction((namedlambdavariable() + namedlambdavariable()), namedlambdavariable(), namedlambdavariable()))|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {1 -> 2, 2 -> 4, ...|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- 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|
+----------------------------------+
-- zip_with
SELECT zip_with(array(1, 2, 3), array('a', 'b', 'c'), (x, y) -> (y, x));
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|zip_with(array(1, 2, 3), array(a, b, c), lambdafunction(named_struct(y, namedlambdavariable(), x, namedlambdavariable()), namedlambdavariable(), namedlambdavariable()))|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{a, 1}, {b, 2}, ...|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SELECT zip_with(array(1, 2), array(3, 4), (x, y) -> x + y);
+-------------------------------------------------------------------------------------------------------------------------------------------------+
|zip_with(array(1, 2), array(3, 4), lambdafunction((namedlambdavariable() + namedlambdavariable()), namedlambdavariable(), namedlambdavariable()))|
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| [4, 6]|
+-------------------------------------------------------------------------------------------------------------------------------------------------+
SELECT zip_with(array('a', 'b', 'c'), array('d', 'e', 'f'), (x, y) -> concat(x, y));
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|zip_with(array(a, b, c), array(d, e, f), lambdafunction(concat(namedlambdavariable(), namedlambdavariable()), namedlambdavariable(), namedlambdavariable()))|
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [ad, be, cf]|
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
STRUCT 函数
| 函数 | 描述 |
|---|---|
| named_struct(name1, val1, name2, val2, ...) | 创建具有给定字段名和值的结构体。 |
| struct(col1, col2, col3, ...) | 创建具有给定字段值的结构体。 |
示例
-- named_struct
SELECT named_struct("a", 1, "b", 2, "c", 3);
+------------------------------+
|named_struct(a, 1, b, 2, c, 3)|
+------------------------------+
| {1, 2, 3}|
+------------------------------+
-- struct
SELECT struct(1, 2, 3);
+---------------+
|struct(1, 2, 3)|
+---------------+
| {1, 2, 3}|
+---------------+
Map 函数
| 函数 | 描述 |
|---|---|
| map(key0, value0, key1, value1, ...) | 创建具有给定键/值对的映射。 |
| map_concat(map, ...) | 返回所有给定映射的并集。 |
| map_contains_key(map, key) | 如果映射包含该键，则返回 true。 |
| map_entries(map) | 返回给定映射中所有条目的无序数组。 |
| map_from_arrays(keys, values) | 使用给定键/值数组对创建映射。键中的所有元素都不应为 null。 |
| map_from_entries(arrayOfEntries) | 返回从给定条目数组创建的映射。 |
| map_keys(map) | 返回包含映射键的无序数组。 |
| map_values(map) | 返回包含映射值的无序数组。 |
| str_to_map(text[, pairDelim[, keyValueDelim]]) | 使用分隔符将文本拆分为键/值对后创建映射。`pairDelim` 的默认分隔符是 ','，`keyValueDelim` 的默认分隔符是 ':'。`pairDelim` 和 `keyValueDelim` 都被视为正则表达式。 |
示例
-- 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}|
+-------------------+
日期和时间戳函数
| 函数 | 描述 |
|---|---|
| add_months(start_date, num_months) | 返回 `start_date` 之后 `num_months` 的日期。 |
| convert_timezone([sourceTz, ]targetTz, sourceTs) | 将不带时区的时间戳 `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) | 返回时间戳 `ts`，截断为格式模型 `fmt` 指定的单位。 |
| dateadd(start_date, num_days) | 返回 `start_date` 之后 `num_days` 的日期。 |
| datediff(endDate, startDate) | 返回从 `startDate` 到 `endDate` 的天数。 |
| datepart(field, source) | 从日期/时间戳或间隔源中提取一部分。 |
| day(date) | 返回日期/时间戳的月份中的日期。 |
| dayname(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) | 返回日期/时间戳的月份部分。 |
| monthname(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，则函数抛出 SparkIllegalArgumentException，否则返回 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_make_interval([years[, months[, weeks[, days[, hours[, mins[, secs]]]]]]]) | 这是 `make_interval` 的一个特殊版本，执行相同的操作，但在发生溢出时返回 NULL。 |
| try_make_timestamp(year, month, day, hour, min, sec[, timezone]) | 尝试从年、月、日、小时、分钟、秒和时区字段创建时间戳。结果数据类型与配置 `spark.sql.timestampType` 的值一致。函数在无效输入时返回 NULL。 |
| try_make_timestamp_ltz(year, month, day, hour, min, sec[, timezone]) | 尝试从年、月、日、小时、分钟、秒和时区字段创建带本地时区的当前时间戳。函数在无效输入时返回 NULL。 |
| try_make_timestamp_ntz(year, month, day, hour, min, sec) | 尝试从年、月、日、小时、分钟、秒字段创建本地日期时间。函数在无效输入时返回 NULL。 |
| 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 16:00:00|
+------------------------------------------------------------------------------------------+
-- curdate
SELECT curdate();
+--------------+
|current_date()|
+--------------+
| 2025-05-19|
+--------------+
-- current_date
SELECT current_date();
+--------------+
|current_date()|
+--------------+
| 2025-05-19|
+--------------+
SELECT current_date;
+--------------+
|current_date()|
+--------------+
| 2025-05-19|
+--------------+
-- current_timestamp
SELECT current_timestamp();
+--------------------+
| current_timestamp()|
+--------------------+
|2025-05-19 09:07:...|
+--------------------+
SELECT current_timestamp;
+--------------------+
| current_timestamp()|
+--------------------+
|2025-05-19 09:07:...|
+--------------------+
-- current_timezone
SELECT current_timezone();
+------------------+
|current_timezone()|
+------------------+
| Etc/UTC|
+------------------+
-- 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|
+---------------+
-- dayname
SELECT dayname(DATE('2008-02-20'));
+-------------------+
|dayname(2008-02-20)|
+-------------------+
| Wed|
+-------------------+
-- 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 00:00:00|
+-------------------------------------+
SELECT from_unixtime(0);
+-------------------------------------+
|from_unixtime(0, yyyy-MM-dd HH:mm:ss)|
+-------------------------------------+
| 1970-01-01 00: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()|
+--------------------+
|2025-05-19 09:07:...|
+--------------------+
-- 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 05: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 05: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|
+-----------------+
-- monthname
SELECT monthname('2008-02-20');
+---------------------+
|monthname(2008-02-20)|
+---------------------+
| Feb|
+---------------------+
-- 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()|
+--------------------+
|2025-05-19 09:07:...|
+--------------------+
-- 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-25 15:30:...|
+----------------------------------+
-- timestamp_millis
SELECT timestamp_millis(1230219000123);
+-------------------------------+
|timestamp_millis(1230219000123)|
+-------------------------------+
| 2008-12-25 15:30:...|
+-------------------------------+
-- timestamp_seconds
SELECT timestamp_seconds(1230219000);
+-----------------------------+
|timestamp_seconds(1230219000)|
+-----------------------------+
| 2008-12-25 15:30:00|
+-----------------------------+
SELECT timestamp_seconds(1230219000.123);
+---------------------------------+
|timestamp_seconds(1230219000.123)|
+---------------------------------+
| 2008-12-25 15: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)|
+-----------------------------------------+
| 1460073600|
+-----------------------------------------+
-- 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_make_interval
SELECT try_make_interval(100, 11, 1, 1, 12, 30, 01.001001);
+--------------------------------------------------+
|try_make_interval(100, 11, 1, 1, 12, 30, 1.001001)|
+--------------------------------------------------+
| 100 years 11 mont...|
+--------------------------------------------------+
SELECT try_make_interval(100, null, 3);
+--------------------------------------------------+
|try_make_interval(100, NULL, 3, 0, 0, 0, 0.000000)|
+--------------------------------------------------+
| NULL|
+--------------------------------------------------+
SELECT try_make_interval(0, 1, 0, 1, 0, 0, 100.000001);
+-----------------------------------------------+
|try_make_interval(0, 1, 0, 1, 0, 0, 100.000001)|
+-----------------------------------------------+
| 1 months 1 days 1...|
+-----------------------------------------------+
SELECT try_make_interval(2147483647);
+------------------------------------------------------+
|try_make_interval(2147483647, 0, 0, 0, 0, 0, 0.000000)|
+------------------------------------------------------+
| NULL|
+------------------------------------------------------+
-- try_make_timestamp
SELECT try_make_timestamp(2014, 12, 28, 6, 30, 45.887);
+-----------------------------------------------+
|try_make_timestamp(2014, 12, 28, 6, 30, 45.887)|
+-----------------------------------------------+
| 2014-12-28 06:30:...|
+-----------------------------------------------+
SELECT try_make_timestamp(2014, 12, 28, 6, 30, 45.887, 'CET');
+-----------------------------------------------+
|try_make_timestamp(2014, 12, 28, 6, 30, 45.887)|
+-----------------------------------------------+
| 2014-12-28 05:30:...|
+-----------------------------------------------+
SELECT try_make_timestamp(2019, 6, 30, 23, 59, 60);
+-------------------------------------------+
|try_make_timestamp(2019, 6, 30, 23, 59, 60)|
+-------------------------------------------+
| 2019-07-01 00:00:00|
+-------------------------------------------+
SELECT try_make_timestamp(2019, 6, 30, 23, 59, 1);
+------------------------------------------+
|try_make_timestamp(2019, 6, 30, 23, 59, 1)|
+------------------------------------------+
| 2019-06-30 23:59:01|
+------------------------------------------+
SELECT try_make_timestamp(null, 7, 22, 15, 30, 0);
+------------------------------------------+
|try_make_timestamp(NULL, 7, 22, 15, 30, 0)|
+------------------------------------------+
| NULL|
+------------------------------------------+
SELECT try_make_timestamp(2024, 13, 22, 15, 30, 0);
+-------------------------------------------+
|try_make_timestamp(2024, 13, 22, 15, 30, 0)|
+-------------------------------------------+
| NULL|
+-------------------------------------------+
-- try_make_timestamp_ltz
SELECT try_make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887);
+---------------------------------------------------+
|try_make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887)|
+---------------------------------------------------+
| 2014-12-28 06:30:...|
+---------------------------------------------------+
SELECT try_make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, 'CET');
+--------------------------------------------------------+
|try_make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, CET)|
+--------------------------------------------------------+
| 2014-12-28 05:30:...|
+--------------------------------------------------------+
SELECT try_make_timestamp_ltz(2019, 6, 30, 23, 59, 60);
+-----------------------------------------------+
|try_make_timestamp_ltz(2019, 6, 30, 23, 59, 60)|
+-----------------------------------------------+
| 2019-07-01 00:00:00|
+-----------------------------------------------+
SELECT try_make_timestamp_ltz(null, 7, 22, 15, 30, 0);
+----------------------------------------------+
|try_make_timestamp_ltz(NULL, 7, 22, 15, 30, 0)|
+----------------------------------------------+
| NULL|
+----------------------------------------------+
SELECT try_make_timestamp_ltz(2024, 13, 22, 15, 30, 0);
+-----------------------------------------------+
|try_make_timestamp_ltz(2024, 13, 22, 15, 30, 0)|
+-----------------------------------------------+
| NULL|
+-----------------------------------------------+
-- try_make_timestamp_ntz
SELECT try_make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887);
+---------------------------------------------------+
|try_make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887)|
+---------------------------------------------------+
| 2014-12-28 06:30:...|
+---------------------------------------------------+
SELECT try_make_timestamp_ntz(2019, 6, 30, 23, 59, 60);
+-----------------------------------------------+
|try_make_timestamp_ntz(2019, 6, 30, 23, 59, 60)|
+-----------------------------------------------+
| 2019-07-01 00:00:00|
+-----------------------------------------------+
SELECT try_make_timestamp_ntz(null, 7, 22, 15, 30, 0);
+----------------------------------------------+
|try_make_timestamp_ntz(NULL, 7, 22, 15, 30, 0)|
+----------------------------------------------+
| NULL|
+----------------------------------------------+
SELECT try_make_timestamp_ntz(2024, 13, 22, 15, 30, 0);
+-----------------------------------------------+
|try_make_timestamp_ntz(2024, 13, 22, 15, 30, 0)|
+-----------------------------------------------+
| NULL|
+-----------------------------------------------+
-- 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)|
+--------------------------------------------------------+
| 1747645659|
+--------------------------------------------------------+
SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
+--------------------------------------+
|unix_timestamp(2016-04-08, yyyy-MM-dd)|
+--------------------------------------+
| 1460073600|
+--------------------------------------+
-- 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|
+----------------+
数学函数
| 函数 | 描述 |
|---|---|
| expr1 % expr2, 或 mod(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 舍入模式将 `expr` 四舍五入到 `d` 位小数。 |
| 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。结果转换为 long。 |
| 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 % expr2, 或 mod(expr1, 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]) | 返回一个具有独立同分布 (i.i.d.) 且在 [0, 1) 范围内均匀分布的随机值。 |
| randn([seed]) | 返回一个具有独立同分布 (i.i.d.) 且从标准正态分布中抽取的随机值。 |
| random([seed]) | 返回一个具有独立同分布 (i.i.d.) 且在 [0, 1) 范围内均匀分布的随机值。 |
| rint(expr) | 返回与参数值最接近且等于数学整数的双精度浮点值。 |
| round(expr, d) | 使用 HALF_UP 舍入模式将 `expr` 四舍五入到 `d` 位小数。 |
| sec(expr) | 返回 `expr` 的正割，如同通过 `1/java.lang.Math.cos` 计算。 |
| 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_mod(dividend, divisor) | 返回 `expr1`/`expr2` 后的余数。`dividend` 必须是数值类型。`divisor` 必须是数值类型。 |
| try_multiply(expr1, expr2) | 返回 `expr1`*`expr2`，溢出时结果为 null。可接受的输入类型与 `*` 运算符相同。 |
| try_subtract(expr1, expr2) | 返回 `expr1`-`expr2`，溢出时结果为 null。可接受的输入类型与 `-` 运算符相同。 |
| unhex(expr) | 将十六进制 `expr` 转换为二进制。 |
| uniform(min, max[, seed]) | 返回一个具有独立同分布 (i.i.d.) 且在指定数值范围内的随机值。随机种子是可选的。指定范围最小值和最大值的提供数字必须是常量。如果这两个数字都是整数，则结果也将是整数。否则，如果其中一个或两个都是浮点数，则结果也将是浮点数。 |
| width_bucket(value, min_value, max_value, num_bucket) | 返回 `value` 在具有 `num_bucket` 个桶的等宽直方图（范围从 `min_value` 到 `max_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.41940687541824373|
+-------------------+
SELECT rand(0);
+------------------+
| rand(0)|
+------------------+
|0.7604953758285915|
+------------------+
SELECT rand(null);
+------------------+
| rand(NULL)|
+------------------+
|0.7604953758285915|
+------------------+
-- randn
SELECT randn();
+------------------+
| randn()|
+------------------+
|0.8646964843291269|
+------------------+
SELECT randn(0);
+------------------+
| randn(0)|
+------------------+
|1.6034991609278433|
+------------------+
SELECT randn(null);
+------------------+
| randn(NULL)|
+------------------+
|1.6034991609278433|
+------------------+
-- random
SELECT random();
+-----------------+
| rand()|
+-----------------+
|0.927036862897507|
+-----------------+
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|
+------+
-- 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_mod
SELECT try_mod(3, 2);
+-------------+
|try_mod(3, 2)|
+-------------+
| 1|
+-------------+
SELECT try_mod(2L, 2L);
+-------------+
|try_mod(2, 2)|
+-------------+
| 0|
+-------------+
SELECT try_mod(3.0, 2.0);
+-----------------+
|try_mod(3.0, 2.0)|
+-----------------+
| 1.0|
+-----------------+
SELECT try_mod(1, 0);
+-------------+
|try_mod(1, 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|
+----------------------------------------+
-- uniform
SELECT uniform(10, 20, 0) > 0 AS result;
+------+
|result|
+------+
| true|
+------+
-- 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` 参数转换为 Base64 字符串。 |
| 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)。 |
| collate(expr, collationName) | 使用指定排序规则标记给定表达式。 |
| collation(expr) | 返回给定表达式的排序规则名称。 |
| concat_ws(sep[, str | array(str)]+) | 返回由 `sep` 分隔的字符串连接，跳过 null 值。 |
| contains(left, right) | 返回布尔值。如果 right 在 left 内部找到，则值为 True。如果任一输入表达式为 NULL，则返回 NULL。否则返回 False。left 和 right 都必须是 STRING 或 BINARY 类型。 |
| decode(bin, charset) | 使用第二个参数字符集解码第一个参数。如果任一参数为 null，则结果也将为 null。 |
| decode(expr, search, result [, search, result ] ... [, default]) | 按顺序将 expr 与每个 search 值进行比较。如果 expr 等于某个 search 值，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) | 使用第二个参数字符集编码第一个参数。如果任一参数为 null，则结果也将为 null。 |
| 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) | 返回 `str` 中 `substr` 第一次出现的（基于 1 的）索引。 |
| is_valid_utf8(str) | 如果 `str` 是有效的 UTF-8 字符串，则返回 true，否则返回 false。 |
| lcase(str) | 返回 `str`，其中所有字符都转换为小写。 |
| left(str, len) | 返回字符串 `str` 最左边的 `len`（`len` 可以是字符串类型）个字符，如果 `len` 小于或等于 0，则结果为空字符串。 |
| len(expr) | 返回字符串数据的字符长度或二进制数据的字节数。字符串数据的长度包括尾随空格。二进制数据的长度包括二进制零。 |
| length(expr) | 返回字符串数据的字符长度或二进制数据的字节数。字符串数据的长度包括尾随空格。二进制数据的长度包括二进制零。 |
| levenshtein(str1, str2[, threshold]) | 返回两个给定字符串之间的 Levenshtein 距离。如果设置了 threshold 且距离超过它，则返回 -1。 |
| locate(substr, str[, pos]) | 返回 `str` 中 `substr` 在位置 `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 算法。此校验和函数广泛应用于信用卡号和政府身份证号，以区分有效号码和错误输入的号码。 |
| make_valid_utf8(str) | 如果 `str` 是有效的 UTF-8 字符串，则返回原始字符串，否则返回一个新字符串，其中无效的 UTF8 字节序列使用 UNICODE 替换字符 U+FFFD 替换。 |
| 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]) | 返回 `str` 中 `substr` 在位置 `pos` 之后第一次出现的索引。给定的 `pos` 和返回值都是基于 1 的。 |
| printf(strfmt, obj, ...) | 从 printf 样式格式字符串返回格式化字符串。 |
| randstr(length[, seed]) | 返回指定长度的字符串，其字符从以下字符池中均匀随机选择：0-9、a-z、A-Z。随机种子是可选的。字符串长度必须是常量两字节或四字节整数（分别为 SMALLINT 或 INT）。 |
| 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) | 将 `str` 围绕匹配 `regex` 的出现进行拆分，并返回一个长度最多为 `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` 中分隔符 `delim` 出现 `count` 次之前的子字符串。如果 `count` 为正，则返回最后一个分隔符（从左计数）左侧的所有内容。如果 `count` 为负，则返回最后一个分隔符（从右计数）右侧的所有内容。函数 substring_index 在搜索 `delim` 时执行区分大小写的匹配。 |
| to_binary(str[, fmt]) | 根据提供的 `fmt` 将输入 `str` 转换为二进制值。`fmt` 可以是大小写不敏感的字符串字面量 "hex"、"utf-8"、"utf8" 或 "base64"。默认情况下，如果省略 `fmt`，则转换的二进制格式为 "hex"。如果至少一个输入参数为 NULL，则函数返回 NULL。 |
| to_char(expr, format) | 根据 `format` 将 `expr` 转换为字符串。如果转换失败则抛出异常。格式可以包含以下字符（不区分大小写）：'0' 或 '9'：指定预期的 0 到 9 之间的数字。格式字符串中的 0 或 9 序列与输入值中的数字序列匹配，生成与格式字符串中相应序列长度相同的结果字符串。如果 0/9 序列包含的数字位数多于小数部分匹配的位数，以 0 开头，并且在小数点之前，则结果字符串左侧用零填充。否则，用空格填充。'.' 或 'D'：指定小数点的位置（可选，只允许一次）。',' 或 'G'：指定分组（千位）分隔符 (,) 的位置。每个分组分隔符的左侧和右侧必须有一个 0 或 9。'$'：指定 $ 货币符号的位置。此字符只能指定一次。'S' 或 'MI'：指定 '-' 或 '+' 号的位置（可选，只允许在格式字符串的开头或结尾出现一次）。请注意，'S' 对正值打印 '+'，但 'MI' 打印空格。'PR'：只允许在格式字符串的末尾；指定如果输入值为负数，结果字符串将用尖括号括起来（例如：'<1>'）。如果 `expr` 是日期时间，`format` 应为有效的日期时间模式，请参阅 日期时间模式。如果 `expr` 是二进制，则将其转换为以下格式之一的字符串：'base64'：Base64 字符串。'hex'：十六进制格式的字符串。'utf-8'：输入二进制数据解码为 UTF-8 字符串。 |
| 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(expr, format) | 根据 `format` 将 `expr` 转换为字符串。如果转换失败则抛出异常。格式可以包含以下字符（不区分大小写）：'0' 或 '9'：指定预期的 0 到 9 之间的数字。格式字符串中的 0 或 9 序列与输入值中的数字序列匹配，生成与格式字符串中相应序列长度相同的结果字符串。如果 0/9 序列包含的数字位数多于小数部分匹配的位数，以 0 开头，并且在小数点之前，则结果字符串左侧用零填充。否则，用空格填充。'.' 或 'D'：指定小数点的位置（可选，只允许一次）。',' 或 'G'：指定分组（千位）分隔符 (,) 的位置。每个分组分隔符的左侧和右侧必须有一个 0 或 9。'$'：指定 $ 货币符号的位置。此字符只能指定一次。'S' 或 'MI'：指定 '-' 或 '+' 号的位置（可选，只允许在格式字符串的开头或结尾出现一次）。请注意，'S' 对正值打印 '+'，但 'MI' 打印空格。'PR'：只允许在格式字符串的末尾；指定如果输入值为负数，结果字符串将用尖括号括起来（例如：'<1>'）。如果 `expr` 是日期时间，`format` 应为有效的日期时间模式，请参阅 日期时间模式。如果 `expr` 是二进制，则将其转换为以下格式之一的字符串：'base64'：Base64 字符串。'hex'：十六进制格式的字符串。'utf-8'：输入二进制数据解码为 UTF-8 字符串。 |
| 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 函数相同的语义。 |
| try_validate_utf8(str) | 如果 `str` 是有效的 UTF-8 字符串，则返回原始字符串，否则返回 NULL。 |
| ucase(str) | 返回 `str`，其中所有字符都转换为大写。 |
| unbase64(str) | 将 Base64 字符串 `str` 参数转换为二进制。 |
| upper(str) | 返回 `str`，其中所有字符都转换为大写。 |
| validate_utf8(str) | 如果 `str` 是有效的 UTF-8 字符串，则返回原始字符串，否则抛出异常。 |
示例
-- 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|
+-------+
-- collate
SELECT COLLATION('Spark SQL' collate UTF8_LCASE);
+-----------------------------------------+
|collation(collate(Spark SQL, UTF8_LCASE))|
+-----------------------------------------+
| SYSTEM.BUILTIN.UT...|
+-----------------------------------------+
-- collation
SELECT collation('Spark SQL');
+--------------------+
|collation(Spark SQL)|
+--------------------+
|SYSTEM.BUILTIN.UT...|
+--------------------+
-- 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|
+--------------------+
-- is_valid_utf8
SELECT is_valid_utf8('Spark');
+--------------------+
|is_valid_utf8(Spark)|
+--------------------+
| true|
+--------------------+
SELECT is_valid_utf8(x'61');
+--------------------+
|is_valid_utf8(X'61')|
+--------------------+
| true|
+--------------------+
SELECT is_valid_utf8(x'80');
+--------------------+
|is_valid_utf8(X'80')|
+--------------------+
| false|
+--------------------+
SELECT is_valid_utf8(x'61C262');
+------------------------+
|is_valid_utf8(X'61C262')|
+------------------------+
| false|
+------------------------+
-- 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|
+-----------------------+
-- make_valid_utf8
SELECT make_valid_utf8('Spark');
+----------------------+
|make_valid_utf8(Spark)|
+----------------------+
| Spark|
+----------------------+
SELECT make_valid_utf8(x'61');
+----------------------+
|make_valid_utf8(X'61')|
+----------------------+
| a|
+----------------------+
SELECT make_valid_utf8(x'80');
+----------------------+
|make_valid_utf8(X'80')|
+----------------------+
| �|
+----------------------+
SELECT make_valid_utf8(x'61C262');
+--------------------------+
|make_valid_utf8(X'61C262')|
+--------------------------+
| a�b|
+--------------------------+
-- 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|
+------------------------------------+
-- randstr
SELECT randstr(3, 0) AS result;
+------+
|result|
+------+
| ceV|
+------+
-- 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|
+---------------------------------------+
SELECT regexp_extract('100-200', r'(\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]|
+----------------------------------------------------+
SELECT regexp_extract_all('100-200, 300-400', r'(\d+)-(\d+)', 1);
+----------------------------------------------------+
|regexp_extract_all(100-200, 300-400, (\d+)-(\d+), 1)|
+----------------------------------------------------+
| [100, 300]|
+----------------------------------------------------+
-- regexp_instr
SELECT regexp_instr(r"\abc", r"^\\abc$");
+------------------------------+
|regexp_instr(\abc, ^\\abc$, 0)|
+------------------------------+
| 1|
+------------------------------+
SELECT regexp_instr('user@spark.apache.org', '@[^.]*');
+----------------------------------------------+
|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|
+--------------------------------------+
SELECT regexp_replace('100-200', r'(\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...|
+--------------------------------------+
SELECT sentences('Hi there! Good morning.', 'en');
+----------------------------------------+
|sentences(Hi there! Good morning., en, )|
+----------------------------------------+
| [[Hi, there], [Go...|
+----------------------------------------+
SELECT sentences('Hi there! Good morning.', 'en', 'US');
+------------------------------------------+
|sentences(Hi there! Good morning., en, US)|
+------------------------------------------+
| [[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-|
+----------------------------+
SELECT to_char(date'2016-04-08', 'y');
+---------------------------------+
|date_format(DATE '2016-04-08', y)|
+---------------------------------+
| 2016|
+---------------------------------+
SELECT to_char(x'537061726b2053514c', 'base64');
+-----------------------------+
|base64(X'537061726B2053514C')|
+-----------------------------+
| U3BhcmsgU1FM|
+-----------------------------+
SELECT to_char(x'537061726b2053514c', 'hex');
+--------------------------+
|hex(X'537061726B2053514C')|
+--------------------------+
| 537061726B2053514C|
+--------------------------+
SELECT to_char(encode('abc', 'utf-8'), 'utf-8');
+---------------------------------+
|decode(encode(abc, utf-8), utf-8)|
+---------------------------------+
| abc|
+---------------------------------+
-- 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-|
+----------------------------+
SELECT to_varchar(date'2016-04-08', 'y');
+---------------------------------+
|date_format(DATE '2016-04-08', y)|
+---------------------------------+
| 2016|
+---------------------------------+
SELECT to_varchar(x'537061726b2053514c', 'base64');
+---------------------------------+
|to_varchar(X'537061726B2053514C')|
+---------------------------------+
| U3BhcmsgU1FM|
+---------------------------------+
SELECT to_varchar(x'537061726b2053514c', 'hex');
+---------------------------------+
|to_varchar(X'537061726B2053514C')|
+---------------------------------+
| 537061726B2053514C|
+---------------------------------+
SELECT to_varchar(encode('abc', 'utf-8'), 'utf-8');
+-------------------------------------+
|to_varchar(encode(abc, utf-8), utf-8)|
+-------------------------------------+
| abc|
+-------------------------------------+
-- 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|
+-----------------------------------+
-- try_validate_utf8
SELECT try_validate_utf8('Spark');
+------------------------+
|try_validate_utf8(Spark)|
+------------------------+
| Spark|
+------------------------+
SELECT try_validate_utf8(x'61');
+------------------------+
|try_validate_utf8(X'61')|
+------------------------+
| a|
+------------------------+
SELECT try_validate_utf8(x'80');
+------------------------+
|try_validate_utf8(X'80')|
+------------------------+
| NULL|
+------------------------+
SELECT try_validate_utf8(x'61C262');
+----------------------------+
|try_validate_utf8(X'61C262')|
+----------------------------+
| NULL|
+----------------------------+
-- 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|
+---------------+
-- validate_utf8
SELECT validate_utf8('Spark');
+--------------------+
|validate_utf8(Spark)|
+--------------------+
| Spark|
+--------------------+
SELECT validate_utf8(x'61');
+--------------------+
|validate_utf8(X'61')|
+--------------------+
| a|
+--------------------+
条件函数
| 函数 | 描述 |
|---|---|
| input [NOT] between lower AND upper | 评估 `input` 是否在 `lower` 和 `upper` 之间 [或不在之间] |
| coalesce(expr1, expr2, ...) | 如果存在，则返回第一个非 null 参数。否则返回 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`。 |
| nullifzero(expr) | 如果 `expr` 等于零，则返回 null，否则返回 `expr`。 |
| 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`。 |
| zeroifnull(expr) | 如果 `expr` 等于 null，则返回零，否则返回 `expr`。 |
示例
-- between
SELECT 0.5 between 0.1 AND 1.0;
+----------------------+
|between(0.5, 0.1, 1.0)|
+----------------------+
| true|
+----------------------+
-- 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|
+------------+
-- nullifzero
SELECT nullifzero(0);
+-------------+
|nullifzero(0)|
+-------------+
| NULL|
+-------------+
SELECT nullifzero(2);
+-------------+
|nullifzero(2)|
+-------------+
| 2|
+-------------+
-- 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|
+--------------------------------------------------+
-- zeroifnull
SELECT zeroifnull(NULL);
+----------------+
|zeroifnull(NULL)|
+----------------+
| 0|
+----------------+
SELECT zeroifnull(2);
+-------------+
|zeroifnull(2)|
+-------------+
| 2|
+-------------+
哈希函数
| 函数 | 描述 |
|---|---|
| crc32(expr) | 返回 `expr` 的循环冗余校验值（bigint 类型）。 |
| hash(expr1, expr2, ...) | 返回参数的哈希值。 |
| md5(expr) | 返回 `expr` 的 MD5 128 位校验和（十六进制字符串）。 |
| sha(expr) | 返回 `expr` 的 SHA1 哈希值（十六进制字符串）。 |
| sha1(expr) | 返回 `expr` 的 SHA1 哈希值（十六进制字符串）。 |
| sha2(expr, bitLength) | 返回 `expr` 的 SHA-2 系列校验和（十六进制字符串）。支持 SHA-224、SHA-256、SHA-384 和 SHA-512。位长度为 0 等效于 256。 |
| xxhash64(expr1, expr2, ...) | 返回参数的 64 位哈希值。哈希种子为 42。 |
示例
-- crc32
SELECT crc32('Spark');
+------------+
|crc32(Spark)|
+------------+
| 1557323817|
+------------+
-- hash
SELECT hash('Spark', array(123), 2);
+--------------------------+
|hash(Spark, array(123), 2)|
+--------------------------+
| -1321691492|
+--------------------------+
-- md5
SELECT md5('Spark');
+--------------------+
| md5(Spark)|
+--------------------+
|8cde774d6f7333752...|
+--------------------+
-- sha
SELECT sha('Spark');
+--------------------+
| sha(Spark)|
+--------------------+
|85f5955f4b27a9a4c...|
+--------------------+
-- sha1
SELECT sha1('Spark');
+--------------------+
| sha1(Spark)|
+--------------------+
|85f5955f4b27a9a4c...|
+--------------------+
-- sha2
SELECT sha2('Spark', 256);
+--------------------+
| sha2(Spark, 256)|
+--------------------+
|529bc3b07127ecb7e...|
+--------------------+
-- xxhash64
SELECT xxhash64('Spark', array(123), 2);
+------------------------------+
|xxhash64(Spark, array(123), 2)|
+------------------------------+
| 5602566077635097486|
+------------------------------+
CSV 函数
| 函数 | 描述 |
|---|---|
| from_csv(csvStr, schema[, options]) | 返回一个结构体值，包含给定的 `csvStr` 和 `schema`。 |
| schema_of_csv(csv[, options]) | 以 DDL 格式返回 CSV 字符串的 schema。 |
| 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|
+----------------------------------------------------------------+
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 字符串的 schema。 |
| 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}]|
+-------------------------+
XML 函数
| 函数 | 描述 |
|---|---|
| from_xml(xmlStr, schema[, options]) | 返回一个结构体值，包含给定的 `xmlStr` 和 `schema`。 |
| schema_of_xml(xml[, options]) | 以 DDL 格式返回 XML 字符串的 schema。 |
| to_xml(expr[, options]) | 返回给定结构体值的 XML 字符串 |
| xpath(xml, xpath) | 返回一个字符串数组，其中包含 XML 节点中与 XPath 表达式匹配的值。 |
| xpath_boolean(xml, xpath) | 如果 XPath 表达式求值为 true，或找到匹配节点，则返回 true。 |
| xpath_double(xml, xpath) | 返回一个双精度浮点值，如果没有找到匹配项则返回零，如果找到匹配项但值为非数字则返回 NaN。 |
| xpath_float(xml, xpath) | 返回一个浮点值，如果没有找到匹配项则返回零，如果找到匹配项但值为非数字则返回 NaN。 |
| xpath_int(xml, xpath) | 返回一个整数值，如果没有找到匹配项则返回零，或者如果找到匹配项但值为非数字则返回零。 |
| xpath_long(xml, xpath) | 返回一个长整数值，如果没有找到匹配项则返回零，或者如果找到匹配项但值为非数字则返回零。 |
| xpath_number(xml, xpath) | 返回一个双精度浮点值，如果没有找到匹配项则返回零，如果找到匹配项但值为非数字则返回 NaN。 |
| xpath_short(xml, xpath) | 返回一个短整数值，如果没有找到匹配项则返回零，或者如果找到匹配项但值为非数字则返回零。 |
| xpath_string(xml, xpath) | 返回第一个与 XPath 表达式匹配的 XML 节点的文本内容。 |
示例
-- from_xml
SELECT from_xml('<p><a>1</a><b>0.8</b></p>', 'a INT, b DOUBLE');
+-----------------------------------+
|from_xml(<p><a>1</a><b>0.8</b></p>)|
+-----------------------------------+
| {1, 0.8}|
+-----------------------------------+
SELECT from_xml('<p><time>26/08/2015</time></p>', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
+----------------------------------------+
|from_xml(<p><time>26/08/2015</time></p>)|
+----------------------------------------+
| {2015-08-26 00:00...|
+----------------------------------------+
SELECT from_xml('<p><teacher>Alice</teacher><student><name>Bob</name><rank>1</rank></student><student><name>Charlie</name><rank>2</rank></student></p>', 'STRUCT<teacher: STRING, student: ARRAY<STRUCT<name: STRING, rank: INT>>>');
+-----------------------------------------------------------------------------------------------------------------------------------------------+
|from_xml(<p><teacher>Alice</teacher><student><name>Bob</name><rank>1</rank></student><student><name>Charlie</name><rank>2</rank></student></p>)|
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| {Alice, [{Bob, 1}...|
+-----------------------------------------------------------------------------------------------------------------------------------------------+
-- schema_of_xml
SELECT schema_of_xml('<p><a>1</a></p>');
+------------------------------+
|schema_of_xml(<p><a>1</a></p>)|
+------------------------------+
| STRUCT<a: BIGINT>|
+------------------------------+
SELECT schema_of_xml('<p><a attr="2">1</a><a>3</a></p>', map('excludeAttribute', 'true'));
+-----------------------------------------------+
|schema_of_xml(<p><a attr="2">1</a><a>3</a></p>)|
+-----------------------------------------------+
| STRUCT<a: ARRAY<B...|
+-----------------------------------------------+
-- to_xml
SELECT to_xml(named_struct('a', 1, 'b', 2));
+--------------------------------+
|to_xml(named_struct(a, 1, b, 2))|
+--------------------------------+
| <ROW>\n <a>1</...|
+--------------------------------+
SELECT to_xml(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
+----------------------------------------------------------------+
|to_xml(named_struct(time, to_timestamp(2015-08-26, yyyy-MM-dd)))|
+----------------------------------------------------------------+
| <ROW>\n <time>...|
+----------------------------------------------------------------+
-- xpath
SELECT xpath('<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>','a/b/text()');
+-----------------------------------------------------------------------+
|xpath(<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>, a/b/text())|
+-----------------------------------------------------------------------+
| [b1, b2, b3]|
+-----------------------------------------------------------------------+
SELECT xpath('<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>','a/b');
+----------------------------------------------------------------+
|xpath(<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>, a/b)|
+----------------------------------------------------------------+
| [NULL, NULL, NULL]|
+----------------------------------------------------------------+
-- xpath_boolean
SELECT xpath_boolean('<a><b>1</b></a>','a/b');
+-----------------------------------+
|xpath_boolean(<a><b>1</b></a>, a/b)|
+-----------------------------------+
| true|
+-----------------------------------+
-- xpath_double
SELECT xpath_double('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
+-----------------------------------------------+
|xpath_double(<a><b>1</b><b>2</b></a>, sum(a/b))|
+-----------------------------------------------+
| 3.0|
+-----------------------------------------------+
-- xpath_float
SELECT xpath_float('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
+----------------------------------------------+
|xpath_float(<a><b>1</b><b>2</b></a>, sum(a/b))|
+----------------------------------------------+
| 3.0|
+----------------------------------------------+
-- xpath_int
SELECT xpath_int('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
+--------------------------------------------+
|xpath_int(<a><b>1</b><b>2</b></a>, sum(a/b))|
+--------------------------------------------+
| 3|
+--------------------------------------------+
-- xpath_long
SELECT xpath_long('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
+---------------------------------------------+
|xpath_long(<a><b>1</b><b>2</b></a>, sum(a/b))|
+---------------------------------------------+
| 3|
+---------------------------------------------+
-- xpath_number
SELECT xpath_number('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
+-----------------------------------------------+
|xpath_number(<a><b>1</b><b>2</b></a>, sum(a/b))|
+-----------------------------------------------+
| 3.0|
+-----------------------------------------------+
-- xpath_short
SELECT xpath_short('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
+----------------------------------------------+
|xpath_short(<a><b>1</b><b>2</b></a>, sum(a/b))|
+----------------------------------------------+
| 3|
+----------------------------------------------+
-- xpath_string
SELECT xpath_string('<a><b>b</b><c>cc</c></a>','a/c');
+-------------------------------------------+
|xpath_string(<a><b>b</b><c>cc</c></a>, a/c)|
+-------------------------------------------+
| cc|
+-------------------------------------------+
URL 函数
| 函数 | 描述 |
|---|---|
| parse_url(url, partToExtract[, key]) | 从 URL 中提取一部分。 |
| try_parse_url(url, partToExtract[, key]) | 这是 `parse_url` 的特殊版本，执行相同的操作，但如果无法执行解析，则返回 NULL 值而不是引发错误。 |
| try_url_decode(str) | 这是 `url_decode` 的特殊版本，执行相同的操作，但如果无法执行解码，则返回 NULL 值而不是引发错误。 |
| url_decode(str) | 使用特定编码方案解码 'application/x-www-form-urlencoded' 格式的 `str`。 |
| url_encode(str) | 使用特定编码方案将字符串转换为 'application/x-www-form-urlencoded' 格式。 |
示例
-- parse_url
SELECT parse_url('https://spark.apache.org/path?query=1', 'HOST');
+-----------------------------------------------------+
|parse_url(http://spark.apache.org/path?query=1, HOST)|
+-----------------------------------------------------+
| spark.apache.org|
+-----------------------------------------------------+
SELECT parse_url('https://spark.apache.org/path?query=1', 'QUERY');
+------------------------------------------------------+
|parse_url(http://spark.apache.org/path?query=1, QUERY)|
+------------------------------------------------------+
| query=1|
+------------------------------------------------------+
SELECT parse_url('https://spark.apache.org/path?query=1', 'QUERY', 'query');
+-------------------------------------------------------------+
|parse_url(http://spark.apache.org/path?query=1, QUERY, query)|
+-------------------------------------------------------------+
| 1|
+-------------------------------------------------------------+
-- try_parse_url
SELECT try_parse_url('https://spark.apache.org/path?query=1', 'HOST');
+---------------------------------------------------------+
|try_parse_url(http://spark.apache.org/path?query=1, HOST)|
+---------------------------------------------------------+
| spark.apache.org|
+---------------------------------------------------------+
SELECT try_parse_url('https://spark.apache.org/path?query=1', 'QUERY');
+----------------------------------------------------------+
|try_parse_url(http://spark.apache.org/path?query=1, QUERY)|
+----------------------------------------------------------+
| query=1|
+----------------------------------------------------------+
SELECT try_parse_url('inva lid://spark.apache.org/path?query=1', 'QUERY');
+--------------------------------------------------------------+
|try_parse_url(inva lid://spark.apache.org/path?query=1, QUERY)|
+--------------------------------------------------------------+
| NULL|
+--------------------------------------------------------------+
SELECT try_parse_url('https://spark.apache.org/path?query=1', 'QUERY', 'query');
+-----------------------------------------------------------------+
|try_parse_url(http://spark.apache.org/path?query=1, QUERY, query)|
+-----------------------------------------------------------------+
| 1|
+-----------------------------------------------------------------+
-- try_url_decode
SELECT try_url_decode('https%3A%2F%2Fspark.apache.org');
+----------------------------------------------+
|try_url_decode(https%3A%2F%2Fspark.apache.org)|
+----------------------------------------------+
| https://spark.apa...|
+----------------------------------------------+
-- url_decode
SELECT url_decode('https%3A%2F%2Fspark.apache.org');
+------------------------------------------+
|url_decode(https%3A%2F%2Fspark.apache.org)|
+------------------------------------------+
| https://spark.apa...|
+------------------------------------------+
-- url_encode
SELECT url_encode('https://spark.apache.org');
+------------------------------------+
|url_encode(https://spark.apache.org)|
+------------------------------------+
| https%3A%2F%2Fspa...|
+------------------------------------+
位函数
| 函数 | 描述 |
|---|---|
| expr1 & expr2 | 返回 `expr1` 和 `expr2` 的按位与结果。 |
| base << exp | 按位左移。 |
| base >> expr | 按位（有符号）右移。 |
| base >>> expr | 按位无符号右移。 |
| expr1 ^ expr2 | 返回 `expr1` 和 `expr2` 的按位异或结果。 |
| bit_count(expr) | 返回参数 `expr` 中设置的位数（无符号 64 位整数），如果参数为 NULL 则返回 NULL。 |
| bit_get(expr, pos) | 返回指定位置的位值（0 或 1）。位置从右到左编号，从零开始。位置参数不能为负数。 |
| getbit(expr, pos) | 返回指定位置的位值（0 或 1）。位置从右到左编号，从零开始。位置参数不能为负数。 |
| base shiftleft exp | 按位左移。 |
| base shiftright expr | 按位（有符号）右移。 |
| base shiftrightunsigned expr | 按位无符号右移。 |
| expr1 | expr2 | 返回 `expr1` 和 `expr2` 的按位或结果。 |
| ~ expr | 返回 `expr` 的按位非结果。 |
示例
-- &
SELECT 3 & 5;
+-------+
|(3 & 5)|
+-------+
| 1|
+-------+
-- <<
SELECT shiftleft(2, 1);
+---------------+
|shiftleft(2, 1)|
+---------------+
| 4|
+---------------+
SELECT 2 << 1;
+--------+
|(2 << 1)|
+--------+
| 4|
+--------+
-- >>
SELECT shiftright(4, 1);
+----------------+
|shiftright(4, 1)|
+----------------+
| 2|
+----------------+
SELECT 4 >> 1;
+--------+
|(4 >> 1)|
+--------+
| 2|
+--------+
-- >>>
SELECT shiftrightunsigned(4, 1);
+------------------------+
|shiftrightunsigned(4, 1)|
+------------------------+
| 2|
+------------------------+
SELECT 4 >>> 1;
+---------+
|(4 >>> 1)|
+---------+
| 2|
+---------+
-- ^
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|
+-------------+
-- shiftleft
SELECT shiftleft(2, 1);
+---------------+
|shiftleft(2, 1)|
+---------------+
| 4|
+---------------+
SELECT 2 << 1;
+--------+
|(2 << 1)|
+--------+
| 4|
+--------+
-- shiftright
SELECT shiftright(4, 1);
+----------------+
|shiftright(4, 1)|
+----------------+
| 2|
+----------------+
SELECT 4 >> 1;
+--------+
|(4 >> 1)|
+--------+
| 2|
+--------+
-- shiftrightunsigned
SELECT shiftrightunsigned(4, 1);
+------------------------+
|shiftrightunsigned(4, 1)|
+------------------------+
| 2|
+------------------------+
SELECT 4 >>> 1;
+---------+
|(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`。也支持 `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|
+---------------+
SELECT '10' :: int;
+---------------+
|CAST(10 AS INT)|
+---------------+
| 10|
+---------------+
谓词函数
| 函数 | 描述 |
|---|---|
| ! expr | 逻辑非。 |
| expr1 < expr2 | 如果 `expr1` 小于 `expr2`，则返回 true。 |
| expr1 <= expr2 | 如果 `expr1` 小于或等于 `expr2`，则返回 true。 |
| expr1 <=> expr2 | 对于非 null 操作数，返回与 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 | 逻辑与。 |
| equal_null(expr1, expr2) | 对于非 null 操作数，返回与 EQUAL(=) 运算符相同的结果，但如果两者都为 null 则返回 true，如果其中一个为 null 则返回 false。 |
| str ilike pattern[ ESCAPE escape] | 如果 str 使用 `escape` 不区分大小写地匹配 `pattern`，则返回 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 使用 `escape` 匹配 `pattern`，则返回 true；如果任何参数为 null 则返回 null；否则返回 false。 |
| not expr | 逻辑非。 |
| expr1 or expr2 | 逻辑或。 |
| 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|
+----------------+
-- 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|
+----------------------+
-- ilike
SELECT ilike('Spark', '_Park');
+-------------------+
|ilike(Spark, _Park)|
+-------------------+
| true|
+-------------------+
SELECT '\\abc' AS S, S ilike r'\\abc', S ilike '\\\\abc';
+----+--------------------------------------+--------------------------------------+
| S|ilike(lateralAliasReference(S), \\abc)|ilike(lateralAliasReference(S), \\abc)|
+----+--------------------------------------+--------------------------------------+
|\abc| true| 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 r'%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|
+----------------+
SELECT '\\abc' AS S, S like r'\\abc', S like '\\\\abc';
+----+-----------------------------------+-----------------------------------+
| S|lateralAliasReference(S) LIKE \\abc|lateralAliasReference(S) LIKE \\abc|
+----+-----------------------------------+-----------------------------------+
|\abc| true| 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 r'%SystemDrive%\\Users%';
+---------------------------------------------------+
|%SystemDrive%\Users\John LIKE %SystemDrive%\\Users%|
+---------------------------------------------------+
| true|
+---------------------------------------------------+
SELECT '%SystemDrive%/Users/John' like '/%SystemDrive/%//Users%' ESCAPE '/';
+----------------------------------------------------------------+
|%SystemDrive%/Users/John LIKE /%SystemDrive/%//Users% ESCAPE '/'|
+----------------------------------------------------------------+
| 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|
+--------------------------------------------------------+
SELECT regexp('%SystemDrive%\\Users\\John', r'%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|
+-------------------------------------------------------------+
SELECT regexp_like('%SystemDrive%\\Users\\John', r'%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|
+-------------------------------------------------------+
SELECT rlike('%SystemDrive%\\Users\\John', r'%SystemDrive%\\Users.*');
+-------------------------------------------------------+
|RLIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------+
| true|
+-------------------------------------------------------+
其他函数
| 函数 | 描述 |
|---|---|
| aes_decrypt(expr, key[, mode[, padding[, aad]]]) | 使用 AES 在 `mode` 和 `padding` 下返回 `expr` 的解密值。支持 16、24 和 32 位密钥长度。支持的 (`mode`, `padding`) 组合有 ('ECB', 'PKCS')、('GCM', 'NONE') 和 ('CBC', 'PKCS')。可选的附加认证数据 (AAD) 仅支持 GCM 模式。如果用于加密，则必须为解密提供相同的 AAD 值。默认模式为 GCM。 |
| aes_encrypt(expr, key[, mode[, padding[, iv[, aad]]]]) | 使用 AES 在给定 `mode` 和指定 `padding` 下返回 `expr` 的加密值。支持 16、24 和 32 位密钥长度。支持的 (`mode`, `padding`) 组合有 ('ECB', 'PKCS')、('GCM', 'NONE') 和 ('CBC', 'PKCS')。可选的初始化向量 (IVs) 仅支持 CBC 和 GCM 模式。CBC 模式下必须为 16 字节，GCM 模式下必须为 12 字节。如果未提供，将生成一个随机向量并添加到输出前。可选的附加认证数据 (AAD) 仅支持 GCM 模式。如果用于加密，则必须为解密提供相同的 AAD 值。默认模式为 GCM。 |
| assert_true(expr [, message]) | 如果 `expr` 不为 true，则抛出异常。 |
| bitmap_bit_position(child) | 返回给定输入子表达式的位位置。 |
| bitmap_bucket_number(child) | 返回给定输入子表达式的桶号。 |
| bitmap_count(child) | 返回子位图中的设置位数。 |
| current_catalog() | 返回当前目录。 |
| current_database() | 返回当前数据库。 |
| current_schema() | 返回当前数据库。 |
| current_user() | 当前执行上下文的用户名称。 |
| from_avro(child, jsonFormatSchema, options) | 将二进制 Avro 值转换为 Catalyst 值。 |
| from_protobuf(data, messageName, descFilePath, options) | 将二进制 Protobuf 值转换为 Catalyst 值。 |
| 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 ..]]) | 使用反射调用方法。 |
| schema_of_avro(jsonFormatSchema, options) | 以 DDL 格式返回 JSON 字符串格式的 Avro schema。 |
| session_user() | 当前执行上下文的用户名称。 |
| spark_partition_id() | 返回当前分区 ID。 |
| to_avro(child[, jsonFormatSchema]) | 将 Catalyst 二进制输入值转换为相应的 Avro 格式结果。 |
| to_protobuf(child, messageName, descFilePath, options) | 将 Catalyst 二进制输入值转换为相应的 Protobuf 格式结果。 |
| try_aes_decrypt(expr, key[, mode[, padding[, aad]]]) | 这是 `aes_decrypt` 的特殊版本，执行相同的操作，但如果无法执行解密，则返回 NULL 值而不是引发错误。 |
| try_reflect(class, method[, arg1[, arg2 ..]]) | 这是 `reflect` 的特殊版本，执行相同的操作，但如果调用的方法抛出异常，则返回 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, , ))|
+-----------------------------------------------------------+
| FA4BE3E7B9E3DD07F...|
+-----------------------------------------------------------+
SELECT hex(aes_encrypt('Spark SQL', '0000111122223333', 'GCM'));
+---------------------------------------------------------------+
|hex(aes_encrypt(Spark SQL, 0000111122223333, GCM, DEFAULT, , ))|
+---------------------------------------------------------------+
| 4C9835D33A48E4660...|
+---------------------------------------------------------------+
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, , ))|
+---------------------------------------------------------------------+
| FD+zPF4ejYVMHmPGk...|
+---------------------------------------------------------------------+
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_schema()|
+----------------+
| default|
+----------------+
-- current_schema
SELECT current_schema();
+----------------+
|current_schema()|
+----------------+
| default|
+----------------+
-- current_user
SELECT current_user();
+--------------+
|current_user()|
+--------------+
| spark-rm|
+--------------+
-- 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)|
+---------------------------------------+
| f2c32f70-de25-4b7...|
+---------------------------------------+
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)|
+-----------------------------------+
| 4742f5b7-031a-4d7...|
+-----------------------------------+
SELECT reflect('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
+-------------------------------------------------------------------------+
|reflect(java.util.UUID, fromString, a5cf6c42-0c85-418f-af6c-3e4e5b1328f2)|
+-------------------------------------------------------------------------+
| a5cf6c42-0c85-418...|
+-------------------------------------------------------------------------+
-- schema_of_avro
SELECT schema_of_avro('{"type": "record", "name": "struct", "fields": [{"name": "u", "type": ["int", "string"]}]}', map());
+-----------------------------------------------------------------------------------------------------------------+
|schema_of_avro({"type": "record", "name": "struct", "fields": [{"name": "u", "type": ["int", "string"]}]}, map())|
+-----------------------------------------------------------------------------------------------------------------+
| STRUCT<u: STRUCT<...|
+-----------------------------------------------------------------------------------------------------------------+
-- session_user
SELECT session_user();
+--------------+
|session_user()|
+--------------+
| spark-rm|
+--------------+
-- 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|
+------------------------------------------------------------------------------------------------------------------------------------+
-- try_reflect
SELECT try_reflect('java.util.UUID', 'randomUUID');
+---------------------------------------+
|try_reflect(java.util.UUID, randomUUID)|
+---------------------------------------+
| d20850cd-f747-4a9...|
+---------------------------------------+
SELECT try_reflect('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
+-----------------------------------------------------------------------------+
|try_reflect(java.util.UUID, fromString, a5cf6c42-0c85-418f-af6c-3e4e5b1328f2)|
+-----------------------------------------------------------------------------+
| a5cf6c42-0c85-418...|
+-----------------------------------------------------------------------------+
SELECT try_reflect('java.net.URLDecoder', 'decode', '%');
+-------------------------------------------+
|try_reflect(java.net.URLDecoder, decode, %)|
+-------------------------------------------+
| NULL|
+-------------------------------------------+
-- typeof
SELECT typeof(1);
+---------+
|typeof(1)|
+---------+
| int|
+---------+
SELECT typeof(array(1));
+----------------+
|typeof(array(1))|
+----------------+
| array<int>|
+----------------+
-- user
SELECT user();
+--------+
| user()|
+--------+
|spark-rm|
+--------+
-- uuid
SELECT uuid();
+--------------------+
| uuid()|
+--------------------+
|46bf63f3-13cd-4c9...|
+--------------------+
-- version
SELECT version();
+--------------------+
| version()|
+--------------------+
|4.0.0 fa33ea000a0...|
+--------------------+
生成器函数
| 函数 | 描述 |
|---|---|
| collations() | 获取所有 Spark SQL 字符串排序规则。 |
| 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`。 |
| sql_keywords() | 获取 Spark SQL 关键字。 |
| stack(n, expr1, ..., exprk) | 将 `expr1`, ..., `exprk` 分离为 `n` 行。除非另有指定，否则默认使用列名 col0、col1 等。 |
示例
-- collations
SELECT * FROM collations() WHERE NAME = 'UTF8_BINARY';
+-------+-------+-----------+--------+-------+------------------+----------------+-------------+-----------+
|CATALOG| SCHEMA| NAME|LANGUAGE|COUNTRY|ACCENT_SENSITIVITY|CASE_SENSITIVITY|PAD_ATTRIBUTE|ICU_VERSION|
+-------+-------+-----------+--------+-------+------------------+----------------+-------------+-----------+
| SYSTEM|BUILTIN|UTF8_BINARY| NULL| NULL| ACCENT_SENSITIVE| CASE_SENSITIVE| NO_PAD| NULL|
+-------+-------+-----------+--------+-------+------------------+----------------+-------------+-----------+
-- explode
SELECT * FROM explode(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|
+---+
-- inline
SELECT * FROM inline(array(struct(1, 'a'), struct(2, 'b')));
+----+----+
|col1|col2|
+----+----+
| 1| a|
| 2| b|
+----+----+
SELECT * FROM inline(input => 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|
+----+----+
SELECT inline_outer(input => array(struct(1, 'a'), struct(2, 'b')));
+----+----+
|col1|col2|
+----+----+
| 1| a|
| 2| b|
+----+----+
-- posexplode
SELECT * FROM posexplode(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
SELECT * FROM posexplode(collection => array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
-- posexplode_outer
SELECT * FROM posexplode_outer(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
SELECT * FROM posexplode_outer(collection => array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
-- sql_keywords
SELECT * FROM sql_keywords() LIMIT 2;
+-------+--------+
|keyword|reserved|
+-------+--------+
| ADD| false|
| AFTER| false|
+-------+--------+
-- stack
SELECT stack(2, 1, 2, 3);
+----+----+
|col0|col1|
+----+----+
| 1| 2|
| 3|NULL|
+----+----+
表函数
| 函数 | 描述 |
|---|---|
| range(start[, end[, step[, numSlices]]]) / range(end) | 返回指定范围内的值表。 |
示例
-- range
SELECT * FROM range(1);
+---+
| id|
+---+
| 0|
+---+
SELECT * FROM range(0, 2);
+---+
| id|
+---+
| 0|
| 1|
+---+
SELECT * FROM range(0, 4, 2);
+---+
| id|
+---+
| 0|
| 2|
+---+
Variant 函数
| 函数 | 描述 |
|---|---|
| is_variant_null(expr) | 检查 variant 值是否为 variant null。当且仅当输入为 variant null 时返回 true，否则返回 false（包括 SQL NULL 的情况）。 |
| parse_json(jsonStr) | 将 JSON 字符串解析为 Variant 值。如果字符串不是有效的 JSON 值，则抛出异常。 |
| schema_of_variant(v) | 以 SQL 格式返回 variant 的 schema。 |
| schema_of_variant_agg(v) | 以 SQL 格式返回 variant 列的合并 schema。 |
| to_variant_object(expr) | 将嵌套输入（数组/映射/结构体）转换为 variant，其中映射和结构体被转换为 variant 对象，这些对象与 SQL 结构体不同，是无序的。输入映射只能有字符串键。 |
| try_parse_json(jsonStr) | 将 JSON 字符串解析为 Variant 值。如果字符串不是有效的 JSON 值，则返回 NULL。 |
| try_variant_get(v, path[, type]) | 根据 `path` 从 `v` 中提取一个子 variant，然后将该子 variant 转换为 `type`。如果省略 `type`，则默认为 `variant`。如果路径不存在或转换失败，则返回 null。 |
| variant_explode(expr) | 它将 variant 对象/数组分离为多行，其中包含其字段/元素。其结果 schema 为 `struct<pos int, key string, value variant>`。`pos` 是字段/元素在其父对象/数组中的位置，`value` 是字段/元素的值。当展开 variant 对象时，`key` 是字段名称；当展开 variant 数组时，`key` 为 NULL。它会忽略任何非 variant 数组/对象的输入，包括 SQL NULL、variant null 和任何其他 variant 值。 |
| variant_explode_outer(expr) | 它将 variant 对象/数组分离为多行，其中包含其字段/元素。其结果 schema 为 `struct<pos int, key string, value variant>`。`pos` 是字段/元素在其父对象/数组中的位置，`value` 是字段/元素的值。当展开 variant 对象时，`key` 是字段名称；当展开 variant 数组时，`key` 为 NULL。它会忽略任何非 variant 数组/对象的输入，包括 SQL NULL、variant null 和任何其他 variant 值。 |
| variant_get(v, path[, type]) | 根据 `path` 从 `v` 中提取一个子 variant，然后将该子 variant 转换为 `type`。如果省略 `type`，则默认为 `variant`。如果路径不存在，则返回 null。如果转换失败，则抛出异常。 |
示例
-- is_variant_null
SELECT is_variant_null(parse_json('null'));
+---------------------------------+
|is_variant_null(parse_json(null))|
+---------------------------------+
| true|
+---------------------------------+
SELECT is_variant_null(parse_json('"null"'));
+-----------------------------------+
|is_variant_null(parse_json("null"))|
+-----------------------------------+
| false|
+-----------------------------------+
SELECT is_variant_null(parse_json('13'));
+-------------------------------+
|is_variant_null(parse_json(13))|
+-------------------------------+
| false|
+-------------------------------+
SELECT is_variant_null(parse_json(null));
+---------------------------------+
|is_variant_null(parse_json(NULL))|
+---------------------------------+
| false|
+---------------------------------+
SELECT is_variant_null(variant_get(parse_json('{"a":null, "b":"spark"}'), "$.c"));
+----------------------------------------------------------------------+
|is_variant_null(variant_get(parse_json({"a":null, "b":"spark"}), $.c))|
+----------------------------------------------------------------------+
| false|
+----------------------------------------------------------------------+
SELECT is_variant_null(variant_get(parse_json('{"a":null, "b":"spark"}'), "$.a"));
+----------------------------------------------------------------------+
|is_variant_null(variant_get(parse_json({"a":null, "b":"spark"}), $.a))|
+----------------------------------------------------------------------+
| true|
+----------------------------------------------------------------------+
-- parse_json
SELECT parse_json('{"a":1,"b":0.8}');
+---------------------------+
|parse_json({"a":1,"b":0.8})|
+---------------------------+
| {"a":1,"b":0.8}|
+---------------------------+
-- schema_of_variant
SELECT schema_of_variant(parse_json('null'));
+-----------------------------------+
|schema_of_variant(parse_json(null))|
+-----------------------------------+
| VOID|
+-----------------------------------+
SELECT schema_of_variant(parse_json('[{"b":true,"a":0}]'));
+-------------------------------------------------+
|schema_of_variant(parse_json([{"b":true,"a":0}]))|
+-------------------------------------------------+
| ARRAY<OBJECT<a: B...|
+-------------------------------------------------+
-- schema_of_variant_agg
SELECT schema_of_variant_agg(parse_json(j)) FROM VALUES ('1'), ('2'), ('3') AS tab(j);
+------------------------------------+
|schema_of_variant_agg(parse_json(j))|
+------------------------------------+
| BIGINT|
+------------------------------------+
SELECT schema_of_variant_agg(parse_json(j)) FROM VALUES ('{"a": 1}'), ('{"b": true}'), ('{"c": 1.23}') AS tab(j);
+------------------------------------+
|schema_of_variant_agg(parse_json(j))|
+------------------------------------+
| OBJECT<a: BIGINT,...|
+------------------------------------+
-- to_variant_object
SELECT to_variant_object(named_struct('a', 1, 'b', 2));
+-------------------------------------------+
|to_variant_object(named_struct(a, 1, b, 2))|
+-------------------------------------------+
| {"a":1,"b":2}|
+-------------------------------------------+
SELECT to_variant_object(array(1, 2, 3));
+---------------------------------+
|to_variant_object(array(1, 2, 3))|
+---------------------------------+
| [1,2,3]|
+---------------------------------+
SELECT to_variant_object(array(named_struct('a', 1)));
+--------------------------------------------+
|to_variant_object(array(named_struct(a, 1)))|
+--------------------------------------------+
| [{"a":1}]|
+--------------------------------------------+
SELECT to_variant_object(array(map("a", 2)));
+-----------------------------------+
|to_variant_object(array(map(a, 2)))|
+-----------------------------------+
| [{"a":2}]|
+-----------------------------------+
-- try_parse_json
SELECT try_parse_json('{"a":1,"b":0.8}');
+-------------------------------+
|try_parse_json({"a":1,"b":0.8})|
+-------------------------------+
| {"a":1,"b":0.8}|
+-------------------------------+
SELECT try_parse_json('{"a":1,');
+-----------------------+
|try_parse_json({"a":1,)|
+-----------------------+
| NULL|
+-----------------------+
-- try_variant_get
SELECT try_variant_get(parse_json('{"a": 1}'), '$.a', 'int');
+------------------------------------------+
|try_variant_get(parse_json({"a": 1}), $.a)|
+------------------------------------------+
| 1|
+------------------------------------------+
SELECT try_variant_get(parse_json('{"a": 1}'), '$.b', 'int');
+------------------------------------------+
|try_variant_get(parse_json({"a": 1}), $.b)|
+------------------------------------------+
| NULL|
+------------------------------------------+
SELECT try_variant_get(parse_json('[1, "2"]'), '$[1]', 'string');
+-------------------------------------------+
|try_variant_get(parse_json([1, "2"]), $[1])|
+-------------------------------------------+
| 2|
+-------------------------------------------+
SELECT try_variant_get(parse_json('[1, "2"]'), '$[2]', 'string');
+-------------------------------------------+
|try_variant_get(parse_json([1, "2"]), $[2])|
+-------------------------------------------+
| NULL|
+-------------------------------------------+
SELECT try_variant_get(parse_json('[1, "hello"]'), '$[1]');
+-----------------------------------------------+
|try_variant_get(parse_json([1, "hello"]), $[1])|
+-----------------------------------------------+
| "hello"|
+-----------------------------------------------+
SELECT try_variant_get(parse_json('[1, "hello"]'), '$[1]', 'int');
+-----------------------------------------------+
|try_variant_get(parse_json([1, "hello"]), $[1])|
+-----------------------------------------------+
| NULL|
+-----------------------------------------------+
-- variant_explode
SELECT * from variant_explode(parse_json('["hello", "world"]'));
+---+----+-------+
|pos| key| value|
+---+----+-------+
| 0|NULL|"hello"|
| 1|NULL|"world"|
+---+----+-------+
SELECT * from variant_explode(input => parse_json('{"a": true, "b": 3.14}'));
+---+---+-----+
|pos|key|value|
+---+---+-----+
| 0| a| true|
| 1| b| 3.14|
+---+---+-----+
-- variant_explode_outer
SELECT * from variant_explode_outer(parse_json('["hello", "world"]'));
+---+----+-------+
|pos| key| value|
+---+----+-------+
| 0|NULL|"hello"|
| 1|NULL|"world"|
+---+----+-------+
SELECT * from variant_explode_outer(input => parse_json('{"a": true, "b": 3.14}'));
+---+---+-----+
|pos|key|value|
+---+---+-----+
| 0| a| true|
| 1| b| 3.14|
+---+---+-----+
-- variant_get
SELECT variant_get(parse_json('{"a": 1}'), '$.a', 'int');
+--------------------------------------+
|variant_get(parse_json({"a": 1}), $.a)|
+--------------------------------------+
| 1|
+--------------------------------------+
SELECT variant_get(parse_json('{"a": 1}'), '$.b', 'int');
+--------------------------------------+
|variant_get(parse_json({"a": 1}), $.b)|
+--------------------------------------+
| NULL|
+--------------------------------------+
SELECT variant_get(parse_json('[1, "2"]'), '$[1]', 'string');
+---------------------------------------+
|variant_get(parse_json([1, "2"]), $[1])|
+---------------------------------------+
| 2|
+---------------------------------------+
SELECT variant_get(parse_json('[1, "2"]'), '$[2]', 'string');
+---------------------------------------+
|variant_get(parse_json([1, "2"]), $[2])|
+---------------------------------------+
| NULL|
+---------------------------------------+
SELECT variant_get(parse_json('[1, "hello"]'), '$[1]');
+-------------------------------------------+
|variant_get(parse_json([1, "hello"]), $[1])|
+-------------------------------------------+
| "hello"|
+-------------------------------------------+