| 函数 | 描述 |
|---|
| any(expr) | 如果 expr 中至少有一个值为真，则返回 true。 |
| any_value(expr[, isIgnoreNull]) | 返回一组行的某个 expr 值。如果 isIgnoreNull 为 true，则只返回非空值。 |
| approx_count_distinct(expr[, relativeSD]) | 返回通过 HyperLogLog++ 估算的中心性。relativeSD 定义了允许的最大相对标准偏差。 |
| approx_percentile(col, percentage [, accuracy]) | Returns the approximate percentile of the numeric or ansi interval column col which is the smallest value in the ordered col values (sorted from least to greatest) such that no more than percentage of col values is less than the value or equal to that value. The value of percentage must be between 0.0 and 1.0. The accuracy parameter (default: 10000) is a positive numeric literal which controls approximation accuracy at the cost of memory. Higher value of accuracy yields better accuracy, 1.0/accuracy is the relative error of the approximation. When percentage is an array, each value of the percentage array must be between 0.0 and 1.0. In this case, returns the approximate percentile array of column col at the given percentage array. |
| array_agg(expr) | 收集并返回非唯一元素列表。 |
| avg(expr) | 返回由一组数值计算得出的平均值。 |
| bit_and(expr) | 返回所有非空输入值的按位 AND 位运算值，如果没有，则返回空值。 |
| bit_or(expr) | 返回所有非空输入值的按位 OR 位运算值，如果没有，则返回空值。 |
| bit_xor(expr) | 返回所有非空输入值的按位 XOR 位运算值，如果没有，则返回空值。 |
| 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(*) | 返回检索到的记录总数，包括包含空值的记录。 |
| count(expr[,expr...]) | 返回所提供表达式全部为非空的记录数。 |
| count(DISTINCT expr[,expr...]) | 返回所提供表达式为唯一且非空的记录数。 |
| count_if(expr) | 返回表达式的 TRUE 值的条数。 |
| count_min_sketch(col, eps, confidence, seed) | 根据给定的 esp、confidence 和 seed 返回列的最小计数草图。结果是一个字节数组，可以在使用前反序列化为 CountMinSketch。Count-min 草图是一种概率数据结构，用于使用亚线性空间进行卡方估计。 |
| covar_pop(expr1, expr2) | 返回一组数对的群体协方差。 |
| covar_samp(expr1, expr2) | 返回一组数对的样本协方差。 |
| every(expr) | 如果 expr 的所有值都为真，则返回 true。 |
| first(expr[, isIgnoreNull]) | 返回一组行中 expr 的第一个值。如果 isIgnoreNull 为 true，则只返回非空值。 |
| first_value(expr[, isIgnoreNull]) | 返回一组记录中 expr 的第一个值。如果 isIgnoreNull 为 true，则只返回非空值。 |
| grouping(col) | 指示 GROUP BY 中的指定列是否聚合，结果集中聚合的返回 1，未聚合的返回 0"、 |
| grouping_id([col1[, col2 ...]]) | 返回分组级别，等于 (grouping(c1) << (n-1)) + (grouping(c2) << (n-2)) + ... + grouping(cn)。 |
| histogram_numeric(expr,nb) | 使用 nb 个桶对数值 expr 计算直方图。返回值是一个 (x,y) 对数组，代表直方图各桶的中心。随着 nb 值的增加，直方图的近似粒度会越来越细，但可能会在异常值周围产生假象。在实际应用中，20-40 个直方图分段的效果不错，对于偏斜或较小的数据集，则需要更多的分段。请注意，该函数创建的直方图具有不均匀的分区宽度。它无法保证直方图的均方误差，但实际上与 R/S-Plus 统计计算软件包生成的直方图相当。注意：返回值中 x 字段的输出类型由聚合函数中消耗的输入值传入。 |
| hll_sketch_agg(expr,lgConfigK) | 返回 HllSketch 的可更新二进制表示。lgConfigK（可选）是 K 的 log2 值，K 是 HllSketch 的桶数或槽数。 |
| hll_union_agg(expr,allowDifferentLgConfigK) | 返回唯一值的估计数量。allowDifferentLgConfigK（可选）允许将具有不同 lgConfigK 值的草图联合起来（默认为 false）。 |
| kurtosis(expr) | 返回根据一组数值计算得出的峰度值。 |
| last(expr[, isIgnoreNull]) | 返回一组行的最后一个 expr 值。如果 isIgnoreNull 为 true，则只返回非空值 |
| last_value(expr[, isIgnoreNull]) | 返回一组记录中 expr 的最后值。如果 isIgnoreNull 为 true，则只返回非空值 |
| max(expr) | 返回 expr 的最大值。 |
| max_by(x,y) | 返回与 y 的最大值相关联的 x 值。 |
| mean(expr) | 返回根据一组数值计算得出的平均值。 |
| median(col) | 返回数字或 ANSI 区间列 col 的中值。 |
| min(expr) | 返回 expr 的最小值。 |
| min_by(x,y) | 返回与y的最小值相关联的x值。 |
| mode(col) | 返回 col 中最常出现的值。空值将被忽略。如果所有值都是 NULL 或只有 0 行，则返回 NULL。 |
| percentile(col, percentage1[, 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 的近似百分位数组。 |
| regr_avgx(y, x) | 返回组中非空对自变量的平均值，其中 y 为因变量，x 为自变量。 |
| regr_avgy(y, x) | 返回一组非空数据对中因变量的平均值，其中 y 是因变量，x 是自变量。 |
| regr_count(y, x) | 返回一组中非空数对的数量，其中 y 是因变量，x 是自变量。 |
| regr_intercept(y, x) | 返回一组非空数对的单变量线性回归线的截距，其中 y 为因变量，x 为自变量。 |
| regr_r2(y, x) | 返回某组非空对子的决定系数，其中 y 为因变量，x 为自变量。 |
| regr_slope(y, x) | 返回一组非空项的线性回归线的斜率，其中 y 为因变量，x 为自变量。 |
| regr_sxx(y, x) | 返回某组中非空配对的 REGR_COUNT(y, x) * VAR_POP(x)，其中 y 是因变量，x 是自变量。 |
| regr_sxy(y, x) | 返回某组非空数据对的 REGR_COUNT(y, x) * COVAR_POP(y,x)，其中 y 是因变量，x 是自变量。 |
| regr_syy(y, x) | 返回某组非空数据对的 REGR_COUNT(y, x) * VAR_POP(y)，其中 y 是因变量，x 是自变量。 |
| skewness(expr) | 返回根据一组数值计算的偏度值。 |
| some(expr) | 如果 expr 中至少有一个值为真，则返回 true。 |
| std(expr) | 返回根据一组数值计算的样本标准差。 |
| stddev(expr) | 返回根据一组数值计算的样本标准差。 |
| stddev_pop(expr) | 返回根据一组数值计算得出的总体标准差。 |
| stddev_samp(expr) | 返回根据一组数值计算得出的样本标准差。 |
| sum(expr) | 返回根据一组数值计算得出的总和。 |
| try_avg(expr) | 返回根据一组数值计算得出的平均值，溢出时结果为空。 |
| try_sum(expr) | 返回根据一组数值计算得出的总和，溢出时结果为空。 |
| var_pop(expr) | 返回根据组值计算的总体方差。 |
| var_samp(expr) | 返回根据一组数值计算得出的样本方差。 |
| variance(expr) | 返回根据一组数值计算得出的样本方差。 |
-- any
SELECT any(col) FROM VALUES (true), (false), (false) AS tab(col);
+--------+
|any(col)|
+--------+
| true|
+--------+
SELECT any(col) FROM VALUES (NULL), (true), (false) AS tab(col);
+--------+
|any(col)|
+--------+
| true|
+--------+
SELECT any(col) FROM VALUES (false), (false), (NULL) AS tab(col);
+--------+
|any(col)|
+--------+
| false|
+--------+
-- any_value
SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);
+--------------+
|any_value(col)|
+--------------+
| 10|
+--------------+
SELECT any_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
+--------------+
|any_value(col)|
+--------------+
| NULL|
+--------------+
SELECT any_value(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
+--------------+
|any_value(col)|
+--------------+
| 5|
+--------------+
-- approx_count_distinct
SELECT approx_count_distinct(col1) FROM VALUES (1), (1), (2), (2), (3) tab(col1);
+---------------------------+
|approx_count_distinct(col1)|
+---------------------------+
| 3|
+---------------------------+
-- approx_percentile
SELECT approx_percentile(col, array(0.5, 0.4, 0.1), 100) FROM VALUES (0), (1), (2), (10) AS tab(col);
+-------------------------------------------------+
|approx_percentile(col, array(0.5, 0.4, 0.1), 100)|
+-------------------------------------------------+
| [1, 1, 0]|
+-------------------------------------------------+
SELECT approx_percentile(col, 0.5, 100) FROM VALUES (0), (6), (7), (9), (10) AS tab(col);
+--------------------------------+
|approx_percentile(col, 0.5, 100)|
+--------------------------------+
| 7|
+--------------------------------+
SELECT approx_percentile(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+--------------------------------+
|approx_percentile(col, 0.5, 100)|
+--------------------------------+
| INTERVAL '1' MONTH|
+--------------------------------+
SELECT approx_percentile(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
+--------------------------------------------+
|approx_percentile(col, array(0.5, 0.7), 100)|
+--------------------------------------------+
| [INTERVAL '01' SE...|
+--------------------------------------------+
-- array_agg
SELECT array_agg(col) FROM VALUES (1), (2), (1) AS tab(col);
+-----------------+
|collect_list(col)|
+-----------------+
| [1, 2, 1]|
+-----------------+
-- avg
SELECT avg(col) FROM VALUES (1), (2), (3) AS tab(col);
+--------+
|avg(col)|
+--------+
| 2.0|
+--------+
SELECT avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
+--------+
|avg(col)|
+--------+
| 1.5|
+--------+
-- bit_and
SELECT bit_and(col) FROM VALUES (3), (5) AS tab(col);
+------------+
|bit_and(col)|
+------------+
| 1|
+------------+
-- bit_or
SELECT bit_or(col) FROM VALUES (3), (5) AS tab(col);
+-----------+
|bit_or(col)|
+-----------+
| 7|
+-----------+
-- bit_xor
SELECT bit_xor(col) FROM VALUES (3), (5) AS tab(col);
+------------+
|bit_xor(col)|
+------------+
| 6|
+------------+
-- bitmap_construct_agg
SELECT substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6) FROM VALUES (1), (2), (3) AS tab(col);
+--------------------------------------------------------------------+
|substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6)|
+--------------------------------------------------------------------+
| 070000|
+--------------------------------------------------------------------+
SELECT substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6) FROM VALUES (1), (1), (1) AS tab(col);
+--------------------------------------------------------------------+
|substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6)|
+--------------------------------------------------------------------+
| 010000|
+--------------------------------------------------------------------+
-- bitmap_or_agg
SELECT substring(hex(bitmap_or_agg(col)), 0, 6) FROM VALUES (X '10'), (X '20'), (X '40') AS tab(col);
+----------------------------------------+
|substring(hex(bitmap_or_agg(col)), 0, 6)|
+----------------------------------------+
| 700000|
+----------------------------------------+
SELECT substring(hex(bitmap_or_agg(col)), 0, 6) FROM VALUES (X '10'), (X '10'), (X '10') AS tab(col);
+----------------------------------------+
|substring(hex(bitmap_or_agg(col)), 0, 6)|
+----------------------------------------+
| 100000|
+----------------------------------------+
-- bool_and
SELECT bool_and(col) FROM VALUES (true), (true), (true) AS tab(col);
+-------------+
|bool_and(col)|
+-------------+
| true|
+-------------+
SELECT bool_and(col) FROM VALUES (NULL), (true), (true) AS tab(col);
+-------------+
|bool_and(col)|
+-------------+
| true|
+-------------+
SELECT bool_and(col) FROM VALUES (true), (false), (true) AS tab(col);
+-------------+
|bool_and(col)|
+-------------+
| false|
+-------------+
-- bool_or
SELECT bool_or(col) FROM VALUES (true), (false), (false) AS tab(col);
+------------+
|bool_or(col)|
+------------+
| true|
+------------+
SELECT bool_or(col) FROM VALUES (NULL), (true), (false) AS tab(col);
+------------+
|bool_or(col)|
+------------+
| true|
+------------+
SELECT bool_or(col) FROM VALUES (false), (false), (NULL) AS tab(col);
+------------+
|bool_or(col)|
+------------+
| false|
+------------+
-- collect_list
SELECT collect_list(col) FROM VALUES (1), (2), (1) AS tab(col);
+-----------------+
|collect_list(col)|
+-----------------+
| [1, 2, 1]|
+-----------------+
-- collect_set
SELECT collect_set(col) FROM VALUES (1), (2), (1) AS tab(col);
+----------------+
|collect_set(col)|
+----------------+
| [1, 2]|
+----------------+
-- corr
SELECT corr(c1, c2) FROM VALUES (3, 2), (3, 3), (6, 4) as tab(c1, c2);
+------------------+
| corr(c1, c2)|
+------------------+
|0.8660254037844387|
+------------------+
-- count
SELECT count(*) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
+--------+
|count(1)|
+--------+
| 4|
+--------+
SELECT count(col) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
+----------+
|count(col)|
+----------+
| 3|
+----------+
SELECT count(DISTINCT col) FROM VALUES (NULL), (5), (5), (10) AS tab(col);
+-------------------+
|count(DISTINCT col)|
+-------------------+
| 2|
+-------------------+
-- count_if
SELECT count_if(col % 2 = 0) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
+-------------------------+
|count_if(((col % 2) = 0))|
+-------------------------+
| 2|
+-------------------------+
SELECT count_if(col IS NULL) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
+-----------------------+
|count_if((col IS NULL))|
+-----------------------+
| 1|
+-----------------------+
-- count_min_sketch
SELECT hex(count_min_sketch(col, 0.5d, 0.5d, 1)) FROM VALUES (1), (2), (1) AS tab(col);
+---------------------------------------+
|hex(count_min_sketch(col, 0.5, 0.5, 1))|
+---------------------------------------+
| 00000001000000000...|
+---------------------------------------+
-- covar_pop
SELECT covar_pop(c1, c2) FROM VALUES (1,1), (2,2), (3,3) AS tab(c1, c2);
+------------------+
| covar_pop(c1, c2)|
+------------------+
|0.6666666666666666|
+------------------+
-- covar_samp
SELECT covar_samp(c1, c2) FROM VALUES (1,1), (2,2), (3,3) AS tab(c1, c2);
+------------------+
|covar_samp(c1, c2)|
+------------------+
| 1.0|
+------------------+
-- every
SELECT every(col) FROM VALUES (true), (true), (true) AS tab(col);
+----------+
|every(col)|
+----------+
| true|
+----------+
SELECT every(col) FROM VALUES (NULL), (true), (true) AS tab(col);
+----------+
|every(col)|
+----------+
| true|
+----------+
SELECT every(col) FROM VALUES (true), (false), (true) AS tab(col);
+----------+
|every(col)|
+----------+
| false|
+----------+
-- first
SELECT first(col) FROM VALUES (10), (5), (20) AS tab(col);
+----------+
|first(col)|
+----------+
| 10|
+----------+
SELECT first(col) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------+
|first(col)|
+----------+
| NULL|
+----------+
SELECT first(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------+
|first(col)|
+----------+
| 5|
+----------+
-- first_value
SELECT first_value(col) FROM VALUES (10), (5), (20) AS tab(col);
+----------------+
|first_value(col)|
+----------------+
| 10|
+----------------+
SELECT first_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------------+
|first_value(col)|
+----------------+
| NULL|
+----------------+
SELECT first_value(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------------+
|first_value(col)|
+----------------+
| 5|
+----------------+
-- grouping
SELECT name, grouping(name), sum(age) FROM VALUES (2, 'Alice'), (5, 'Bob') people(age, name) GROUP BY cube(name);
+-----+--------------+--------+
| name|grouping(name)|sum(age)|
+-----+--------------+--------+
| NULL| 1| 7|
|Alice| 0| 2|
| Bob| 0| 5|
+-----+--------------+--------+
-- grouping_id
SELECT name, grouping_id(), sum(age), avg(height) FROM VALUES (2, 'Alice', 165), (5, 'Bob', 180) people(age, name, height) GROUP BY cube(name, height);
+-----+-------------+--------+-----------+
| name|grouping_id()|sum(age)|avg(height)|
+-----+-------------+--------+-----------+
| NULL| 2| 2| 165.0|
|Alice| 0| 2| 165.0|
|Alice| 1| 2| 165.0|
| NULL| 3| 7| 172.5|
| Bob| 1| 5| 180.0|
| Bob| 0| 5| 180.0|
| NULL| 2| 5| 180.0|
+-----+-------------+--------+-----------+
-- histogram_numeric
SELECT histogram_numeric(col, 5) FROM VALUES (0), (1), (2), (10) AS tab(col);
+-------------------------+
|histogram_numeric(col, 5)|
+-------------------------+
| [{0, 1.0}, {1, 1....|
+-------------------------+
-- hll_sketch_agg
SELECT hll_sketch_estimate(hll_sketch_agg(col, 12)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
+--------------------------------------------+
|hll_sketch_estimate(hll_sketch_agg(col, 12))|
+--------------------------------------------+
| 3|
+--------------------------------------------+
-- hll_union_agg
SELECT hll_sketch_estimate(hll_union_agg(sketch, true)) FROM (SELECT hll_sketch_agg(col) as sketch FROM VALUES (1) tab(col) UNION ALL SELECT hll_sketch_agg(col, 20) as sketch FROM VALUES (1) tab(col));
+------------------------------------------------+
|hll_sketch_estimate(hll_union_agg(sketch, true))|
+------------------------------------------------+
| 1|
+------------------------------------------------+
-- kurtosis
SELECT kurtosis(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
+-------------------+
| kurtosis(col)|
+-------------------+
|-0.7014368047529618|
+-------------------+
SELECT kurtosis(col) FROM VALUES (1), (10), (100), (10), (1) as tab(col);
+-------------------+
| kurtosis(col)|
+-------------------+
|0.19432323191698986|
+-------------------+
-- last
SELECT last(col) FROM VALUES (10), (5), (20) AS tab(col);
+---------+
|last(col)|
+---------+
| 20|
+---------+
SELECT last(col) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------+
|last(col)|
+---------+
| NULL|
+---------+
SELECT last(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------+
|last(col)|
+---------+
| 5|
+---------+
-- last_value
SELECT last_value(col) FROM VALUES (10), (5), (20) AS tab(col);
+---------------+
|last_value(col)|
+---------------+
| 20|
+---------------+
SELECT last_value(col) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------------+
|last_value(col)|
+---------------+
| NULL|
+---------------+
SELECT last_value(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------------+
|last_value(col)|
+---------------+
| 5|
+---------------+
-- max
SELECT max(col) FROM VALUES (10), (50), (20) AS tab(col);
+--------+
|max(col)|
+--------+
| 50|
+--------+
-- max_by
SELECT max_by(x, y) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
+------------+
|max_by(x, y)|
+------------+
| b|
+------------+
-- mean
SELECT mean(col) FROM VALUES (1), (2), (3) AS tab(col);
+---------+
|mean(col)|
+---------+
| 2.0|
+---------+
SELECT mean(col) FROM VALUES (1), (2), (NULL) AS tab(col);
+---------+
|mean(col)|
+---------+
| 1.5|
+---------+
-- median
SELECT median(col) FROM VALUES (0), (10) AS tab(col);
+-----------+
|median(col)|
+-----------+
| 5.0|
+-----------+
SELECT median(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+--------------------+
| median(col)|
+--------------------+
|INTERVAL '0-5' YE...|
+--------------------+
-- min
SELECT min(col) FROM VALUES (10), (-1), (20) AS tab(col);
+--------+
|min(col)|
+--------+
| -1|
+--------+
-- min_by
SELECT min_by(x, y) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
+------------+
|min_by(x, y)|
+------------+
| a|
+------------+
-- mode
SELECT mode(col) FROM VALUES (0), (10), (10) AS tab(col);
+---------+
|mode(col)|
+---------+
| 10|
+---------+
SELECT mode(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+-------------------+
| mode(col)|
+-------------------+
|INTERVAL '10' MONTH|
+-------------------+
SELECT mode(col) FROM VALUES (0), (10), (10), (null), (null), (null) AS tab(col);
+---------+
|mode(col)|
+---------+
| 10|
+---------+
-- percentile
SELECT percentile(col, 0.3) FROM VALUES (0), (10) AS tab(col);
+-----------------------+
|percentile(col, 0.3, 1)|
+-----------------------+
| 3.0|
+-----------------------+
SELECT percentile(col, array(0.25, 0.75)) FROM VALUES (0), (10) AS tab(col);
+-------------------------------------+
|percentile(col, array(0.25, 0.75), 1)|
+-------------------------------------+
| [2.5, 7.5]|
+-------------------------------------+
SELECT percentile(col, 0.5) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+-----------------------+
|percentile(col, 0.5, 1)|
+-----------------------+
| INTERVAL '0-5' YE...|
+-----------------------+
SELECT percentile(col, array(0.2, 0.5)) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '10' SECOND) AS tab(col);
+-----------------------------------+
|percentile(col, array(0.2, 0.5), 1)|
+-----------------------------------+
| [INTERVAL '0 00:0...|
+-----------------------------------+
-- percentile_approx
SELECT percentile_approx(col, array(0.5, 0.4, 0.1), 100) FROM VALUES (0), (1), (2), (10) AS tab(col);
+-------------------------------------------------+
|percentile_approx(col, array(0.5, 0.4, 0.1), 100)|
+-------------------------------------------------+
| [1, 1, 0]|
+-------------------------------------------------+
SELECT percentile_approx(col, 0.5, 100) FROM VALUES (0), (6), (7), (9), (10) AS tab(col);
+--------------------------------+
|percentile_approx(col, 0.5, 100)|
+--------------------------------+
| 7|
+--------------------------------+
SELECT percentile_approx(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+--------------------------------+
|percentile_approx(col, 0.5, 100)|
+--------------------------------+
| INTERVAL '1' MONTH|
+--------------------------------+
SELECT percentile_approx(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
+--------------------------------------------+
|percentile_approx(col, array(0.5, 0.7), 100)|
+--------------------------------------------+
| [INTERVAL '01' SE...|
+--------------------------------------------+
-- regr_avgx
SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| 2.75|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (1, null) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (null, 1) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| 3.0|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| 3.0|
+---------------+
-- regr_avgy
SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| 1.75|
+---------------+
SELECT regr_avgy(y, x) FROM VALUES (1, null) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgy(y, x) FROM VALUES (null, 1) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_avgy(y, x)|
+------------------+
|1.6666666666666667|
+------------------+
SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| 1.5|
+---------------+
-- regr_count
SELECT regr_count(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 4|
+----------------+
SELECT regr_count(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 3|
+----------------+
SELECT regr_count(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 2|
+----------------+
-- regr_intercept
SELECT regr_intercept(y, x) FROM VALUES (1,1), (2,2), (3,3) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| 0.0|
+--------------------+
SELECT regr_intercept(y, x) FROM VALUES (1, null) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| NULL|
+--------------------+
SELECT regr_intercept(y, x) FROM VALUES (null, 1) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| NULL|
+--------------------+
-- regr_r2
SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_r2(y, x)|
+------------------+
|0.2727272727272726|
+------------------+
SELECT regr_r2(y, x) FROM VALUES (1, null) AS tab(y, x);
+-------------+
|regr_r2(y, x)|
+-------------+
| NULL|
+-------------+
SELECT regr_r2(y, x) FROM VALUES (null, 1) AS tab(y, x);
+-------------+
|regr_r2(y, x)|
+-------------+
| NULL|
+-------------+
SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_r2(y, x)|
+------------------+
|0.7500000000000001|
+------------------+
SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+-------------+
|regr_r2(y, x)|
+-------------+
| 1.0|
+-------------+
-- regr_slope
SELECT regr_slope(y, x) FROM VALUES (1,1), (2,2), (3,3) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| 1.0|
+----------------+
SELECT regr_slope(y, x) FROM VALUES (1, null) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| NULL|
+----------------+
SELECT regr_slope(y, x) FROM VALUES (null, 1) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| NULL|
+----------------+
-- regr_sxx
SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_sxx(y, x)|
+------------------+
|2.7499999999999996|
+------------------+
SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxx(y, x)|
+--------------+
| 2.0|
+--------------+
SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxx(y, x)|
+--------------+
| 2.0|
+--------------+
-- regr_sxy
SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_sxy(y, x)|
+------------------+
|0.7499999999999998|
+------------------+
SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxy(y, x)|
+--------------+
| 1.0|
+--------------+
SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxy(y, x)|
+--------------+
| 1.0|
+--------------+
-- regr_syy
SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_syy(y, x)|
+------------------+
|0.7499999999999999|
+------------------+
SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_syy(y, x)|
+------------------+
|0.6666666666666666|
+------------------+
SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_syy(y, x)|
+--------------+
| 0.5|
+--------------+
-- skewness
SELECT skewness(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
+------------------+
| skewness(col)|
+------------------+
|1.1135657469022013|
+------------------+
SELECT skewness(col) FROM VALUES (-1000), (-100), (10), (20) AS tab(col);
+-------------------+
| skewness(col)|
+-------------------+
|-1.1135657469022011|
+-------------------+
-- some
SELECT some(col) FROM VALUES (true), (false), (false) AS tab(col);
+---------+
|some(col)|
+---------+
| true|
+---------+
SELECT some(col) FROM VALUES (NULL), (true), (false) AS tab(col);
+---------+
|some(col)|
+---------+
| true|
+---------+
SELECT some(col) FROM VALUES (false), (false), (NULL) AS tab(col);
+---------+
|some(col)|
+---------+
| false|
+---------+
-- std
SELECT std(col) FROM VALUES (1), (2), (3) AS tab(col);
+--------+
|std(col)|
+--------+
| 1.0|
+--------+
-- stddev
SELECT stddev(col) FROM VALUES (1), (2), (3) AS tab(col);
+-----------+
|stddev(col)|
+-----------+
| 1.0|
+-----------+
-- stddev_pop
SELECT stddev_pop(col) FROM VALUES (1), (2), (3) AS tab(col);
+-----------------+
| stddev_pop(col)|
+-----------------+
|0.816496580927726|
+-----------------+
-- stddev_samp
SELECT stddev_samp(col) FROM VALUES (1), (2), (3) AS tab(col);
+----------------+
|stddev_samp(col)|
+----------------+
| 1.0|
+----------------+
-- sum
SELECT sum(col) FROM VALUES (5), (10), (15) AS tab(col);
+--------+
|sum(col)|
+--------+
| 30|
+--------+
SELECT sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
+--------+
|sum(col)|
+--------+
| 25|
+--------+
SELECT sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
+--------+
|sum(col)|
+--------+
| NULL|
+--------+
-- try_avg
SELECT try_avg(col) FROM VALUES (1), (2), (3) AS tab(col);
+------------+
|try_avg(col)|
+------------+
| 2.0|
+------------+
SELECT try_avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
+------------+
|try_avg(col)|
+------------+
| 1.5|
+------------+
SELECT try_avg(col) FROM VALUES (interval '2147483647 months'), (interval '1 months') AS tab(col);
+------------+
|try_avg(col)|
+------------+
| NULL|
+------------+
-- try_sum
SELECT try_sum(col) FROM VALUES (5), (10), (15) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| 30|
+------------+
SELECT try_sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| 25|
+------------+
SELECT try_sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| NULL|
+------------+
SELECT try_sum(col) FROM VALUES (9223372036854775807L), (1L) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| NULL|
+------------+
-- var_pop
SELECT var_pop(col) FROM VALUES (1), (2), (3) AS tab(col);
+------------------+
| var_pop(col)|
+------------------+
|0.6666666666666666|
+------------------+
-- var_samp
SELECT var_samp(col) FROM VALUES (1), (2), (3) AS tab(col);
+-------------+
|var_samp(col)|
+-------------+
| 1.0|
+-------------+
-- variance
SELECT variance(col) FROM VALUES (1), (2), (3) AS tab(col);
+-------------+
|variance(col)|
+-------------+
| 1.0|
+-------------+
| 函数 | 描述 |
|---|
| cume_dist() | Computes the position of a value relative to all values in the partition. |
| dense_rank() | Computes the rank of a value in a group of values. The result is one plus the previously assigned rank value. Unlike the function rank, dense_rank will not produce gaps in the ranking sequence. |
| lag(input[, offset[, default]]) | Returns the value of input at the offsetth row before the current row in the window. The default value of offset is 1 and the default value of default is null. If the value of input at the offsetth row is null, null is returned. If there is no such offset row (e.g., when the offset is 1, the first row of the window does not have any previous row), default is returned. |
| lead(input[, offset[, default]]) | Returns the value of input at the offsetth row after the current row in the window. The default value of offset is 1 and the default value of default is null. If the value of input at the offsetth row is null, null is returned. If there is no such an offset row (e.g., when the offset is 1, the last row of the window does not have any subsequent row), default is returned. |
| nth_value(input[, offset]) | Returns the value of input at the row that is the offsetth row from beginning of the window frame. Offset starts at 1. If ignoreNulls=true, we will skip nulls when finding the offsetth row. Otherwise, every row counts for the offset. If there is no such an offsetth row (e.g., when the offset is 10, size of the window frame is less than 10), null is returned. |
| ntile(n) | Divides the rows for each window partition into n buckets ranging from 1 to at most n. |
| percent_rank() | Computes the percentage ranking of a value in a group of values. |
| rank() | Computes the rank of a value in a group of values. The result is one plus the number of rows preceding or equal to the current row in the ordering of the partition. The values will produce gaps in the sequence. |
| row_number() | Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition. |
-- 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 |
| array_compact(array) | 删除数组中的空值。 |
| array_contains(array, value) | 如果数组包含值，则返回 true。 |
| array_distinct(array) | 删除数组中的重复值。 |
| array_except(array1, array2) | 返回一个包含 array1 中元素但不包含 array2 中元素的数组，不包含重复值。 |
| array_insert(x, pos, val) | 将 val 放入数组 x 的索引 pos 中。数组索引从 1 开始，最大负索引为-1，函数会在当前最后一个元素后插入新元素。索引大于数组大小时，将追加数组，如果索引为负数，则用 NULL元素预填充数组。 |
| array_intersect(array1, array2) | 返回一个数组，包含 array1 和 array2 交集的元素，不含重复元素。 |
| array_join(array, delimiter[, nullReplacement]) | 使用分隔符和可选字符串替换空值，合并数组中的元素。如果没有为 nullReplacement 设置值，任何空值都会被过滤。 |
| array_max(array) | 返回数组中的最大值。对于 double/float 类型，NaN 大于任何非 NaN 元素。NULL 元素将被跳过。 |
| array_min(array) | 返回数组中的最小值。对于 double/float 类型，NaN 大于任何非 NaN 元素。跳过 NULL 元素。 |
| array_position(array, element) | 返回数组中第一个匹配元素的（基于 1 的）long 类型索引，如果没有找到匹配元素，则返回 0。 |
| array_prepend(array, element) | 在作为第一个参数传递的数组开头添加元素。元素类型应与数组元素类型相同。空元素也会被添加到数组中。但如果传递的数组为空，则输出为NULL。 |
| array_remove(array, element) | 从数组中删除所有与元素相同的元素。 |
| array_repeat(element, count) | 返回包含元素 count 次的数组。 |
| array_union(array1, array2) | 返回一个包含 array1 和 array2 的联合数组，不含重复元素。 |
| arrays_overlap(a1, a2) | 如果数组 a1 中至少包含一个在数组 a2 中也存在的非空元素，则返回 true。如果两个数组没有共同元素且都是非空数组，并且其中任何一个数组包含一个空元素，则返回 null，否则返回 false。 |
| arrays_zip(array1, array2, ...) | 返回合并后的结构数组，其中第 N 个结构包含输入数组的所有第 N 个值。 |
| flatten(arrayOfArrays) | 将数组的数组转换为单个数组。 |
| get(array, index) | 返回给定（基于 0 的）索引处的数组元素。如果索引指向数组边界之外，则此函数返回 NULL。 |
| sequence(start, stop, step) | 生成一个从 start 到 stop（含）的元素数组，以 step 为单位递增。返回元素的类型与参数表达式的类型相同。支持的类型有：byte、short、integer、long、date、timestamp。开始和停止表达式必须解析为相同的类型。如果 start 和 stop 表达式解析为 date 或 timestamp 类型，那么 step 表达式必须解析为 interval 或 year-month interval 或 day-time interval 类型，否则解析为与 start 和 stop 表达式相同的类型。 |
| shuffle(array) | 返回给定数组的随机排列。 |
| slice(x, start, length) | 将数组 x 从索引起点（数组索引从 1 开始，如果起点为负数，则从终点开始）以指定的长度分集。 |
| sort_array(array[, ascendingOrder]) | 根据数组元素的自然排序对输入数组进行升序或降序排序。对于 double/float 类型，NaN 大于任何非 NaN 元素。空元素将按升序放在返回数组的开头，或按降序放在返回数组的结尾。 |
-- array
SELECT array(1, 2, 3);
+--------------+
|array(1, 2, 3)|
+--------------+
| [1, 2, 3]|
+--------------+
-- array_append
SELECT array_append(array('b', 'd', 'c', 'a'), 'd');
+----------------------------------+
|array_append(array(b, d, c, a), d)|
+----------------------------------+
| [b, d, c, a, d]|
+----------------------------------+
SELECT array_append(array(1, 2, 3, null), null);
+----------------------------------------+
|array_append(array(1, 2, 3, NULL), NULL)|
+----------------------------------------+
| [1, 2, 3, NULL, N...|
+----------------------------------------+
SELECT array_append(CAST(null as Array<Int>), 2);
+---------------------+
|array_append(NULL, 2)|
+---------------------+
| NULL|
+---------------------+
-- array_compact
SELECT array_compact(array(1, 2, 3, null));
+-----------------------------------+
|array_compact(array(1, 2, 3, NULL))|
+-----------------------------------+
| [1, 2, 3]|
+-----------------------------------+
SELECT array_compact(array("a", "b", "c"));
+-----------------------------+
|array_compact(array(a, b, c))|
+-----------------------------+
| [a, b, c]|
+-----------------------------+
-- array_contains
SELECT array_contains(array(1, 2, 3), 2);
+---------------------------------+
|array_contains(array(1, 2, 3), 2)|
+---------------------------------+
| true|
+---------------------------------+
-- array_distinct
SELECT array_distinct(array(1, 2, 3, null, 3));
+---------------------------------------+
|array_distinct(array(1, 2, 3, NULL, 3))|
+---------------------------------------+
| [1, 2, 3, NULL]|
+---------------------------------------+
-- array_except
SELECT array_except(array(1, 2, 3), array(1, 3, 5));
+--------------------------------------------+
|array_except(array(1, 2, 3), array(1, 3, 5))|
+--------------------------------------------+
| [2]|
+--------------------------------------------+
-- array_insert
SELECT array_insert(array(1, 2, 3, 4), 5, 5);
+-------------------------------------+
|array_insert(array(1, 2, 3, 4), 5, 5)|
+-------------------------------------+
| [1, 2, 3, 4, 5]|
+-------------------------------------+
SELECT array_insert(array(5, 4, 3, 2), -1, 1);
+--------------------------------------+
|array_insert(array(5, 4, 3, 2), -1, 1)|
+--------------------------------------+
| [5, 4, 3, 2, 1]|
+--------------------------------------+
SELECT array_insert(array(5, 3, 2, 1), -4, 4);
+--------------------------------------+
|array_insert(array(5, 3, 2, 1), -4, 4)|
+--------------------------------------+
| [5, 4, 3, 2, 1]|
+--------------------------------------+
-- array_intersect
SELECT array_intersect(array(1, 2, 3), array(1, 3, 5));
+-----------------------------------------------+
|array_intersect(array(1, 2, 3), array(1, 3, 5))|
+-----------------------------------------------+
| [1, 3]|
+-----------------------------------------------+
-- array_join
SELECT array_join(array('hello', 'world'), ' ');
+----------------------------------+
|array_join(array(hello, world), )|
+----------------------------------+
| hello world|
+----------------------------------+
SELECT array_join(array('hello', null ,'world'), ' ');
+----------------------------------------+
|array_join(array(hello, NULL, world), )|
+----------------------------------------+
| hello world|
+----------------------------------------+
SELECT array_join(array('hello', null ,'world'), ' ', ',');
+-------------------------------------------+
|array_join(array(hello, NULL, world), , ,)|
+-------------------------------------------+
| hello , world|
+-------------------------------------------+
-- array_max
SELECT array_max(array(1, 20, null, 3));
+--------------------------------+
|array_max(array(1, 20, NULL, 3))|
+--------------------------------+
| 20|
+--------------------------------+
-- array_min
SELECT array_min(array(1, 20, null, 3));
+--------------------------------+
|array_min(array(1, 20, NULL, 3))|
+--------------------------------+
| 1|
+--------------------------------+
-- array_position
SELECT array_position(array(312, 773, 708, 708), 708);
+----------------------------------------------+
|array_position(array(312, 773, 708, 708), 708)|
+----------------------------------------------+
| 3|
+----------------------------------------------+
SELECT array_position(array(312, 773, 708, 708), 414);
+----------------------------------------------+
|array_position(array(312, 773, 708, 708), 414)|
+----------------------------------------------+
| 0|
+----------------------------------------------+
-- array_prepend
SELECT array_prepend(array('b', 'd', 'c', 'a'), 'd');
+-----------------------------------+
|array_prepend(array(b, d, c, a), d)|
+-----------------------------------+
| [d, b, d, c, a]|
+-----------------------------------+
SELECT array_prepend(array(1, 2, 3, null), null);
+-----------------------------------------+
|array_prepend(array(1, 2, 3, NULL), NULL)|
+-----------------------------------------+
| [NULL, 1, 2, 3, N...|
+-----------------------------------------+
SELECT array_prepend(CAST(null as Array<Int>), 2);
+----------------------+
|array_prepend(NULL, 2)|
+----------------------+
| NULL|
+----------------------+
-- array_remove
SELECT array_remove(array(1, 2, 3, null, 3), 3);
+----------------------------------------+
|array_remove(array(1, 2, 3, NULL, 3), 3)|
+----------------------------------------+
| [1, 2, NULL]|
+----------------------------------------+
-- array_repeat
SELECT array_repeat('123', 2);
+--------------------+
|array_repeat(123, 2)|
+--------------------+
| [123, 123]|
+--------------------+
-- array_union
SELECT array_union(array(1, 2, 3), array(1, 3, 5));
+-------------------------------------------+
|array_union(array(1, 2, 3), array(1, 3, 5))|
+-------------------------------------------+
| [1, 2, 3, 5]|
+-------------------------------------------+
-- arrays_overlap
SELECT arrays_overlap(array(1, 2, 3), array(3, 4, 5));
+----------------------------------------------+
|arrays_overlap(array(1, 2, 3), array(3, 4, 5))|
+----------------------------------------------+
| true|
+----------------------------------------------+
-- arrays_zip
SELECT arrays_zip(array(1, 2, 3), array(2, 3, 4));
+------------------------------------------+
|arrays_zip(array(1, 2, 3), array(2, 3, 4))|
+------------------------------------------+
| [{1, 2}, {2, 3}, ...|
+------------------------------------------+
SELECT arrays_zip(array(1, 2), array(2, 3), array(3, 4));
+-------------------------------------------------+
|arrays_zip(array(1, 2), array(2, 3), array(3, 4))|
+-------------------------------------------------+
| [{1, 2, 3}, {2, 3...|
+-------------------------------------------------+
-- flatten
SELECT flatten(array(array(1, 2), array(3, 4)));
+----------------------------------------+
|flatten(array(array(1, 2), array(3, 4)))|
+----------------------------------------+
| [1, 2, 3, 4]|
+----------------------------------------+
-- get
SELECT get(array(1, 2, 3), 0);
+----------------------+
|get(array(1, 2, 3), 0)|
+----------------------+
| 1|
+----------------------+
SELECT get(array(1, 2, 3), 3);
+----------------------+
|get(array(1, 2, 3), 3)|
+----------------------+
| NULL|
+----------------------+
SELECT get(array(1, 2, 3), -1);
+-----------------------+
|get(array(1, 2, 3), -1)|
+-----------------------+
| NULL|
+-----------------------+
-- sequence
SELECT sequence(1, 5);
+---------------+
| sequence(1, 5)|
+---------------+
|[1, 2, 3, 4, 5]|
+---------------+
SELECT sequence(5, 1);
+---------------+
| sequence(5, 1)|
+---------------+
|[5, 4, 3, 2, 1]|
+---------------+
SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month);
+----------------------------------------------------------------------+
|sequence(to_date(2018-01-01), to_date(2018-03-01), INTERVAL '1' MONTH)|
+----------------------------------------------------------------------+
| [2018-01-01, 2018...|
+----------------------------------------------------------------------+
SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval '0-1' year to month);
+--------------------------------------------------------------------------------+
|sequence(to_date(2018-01-01), to_date(2018-03-01), INTERVAL '0-1' YEAR TO MONTH)|
+--------------------------------------------------------------------------------+
| [2018-01-01, 2018...|
+--------------------------------------------------------------------------------+
-- shuffle
SELECT shuffle(array(1, 20, 3, 5));
+---------------------------+
|shuffle(array(1, 20, 3, 5))|
+---------------------------+
| [3, 20, 1, 5]|
+---------------------------+
SELECT shuffle(array(1, 20, null, 3));
+------------------------------+
|shuffle(array(1, 20, NULL, 3))|
+------------------------------+
| [3, 1, NULL, 20]|
+------------------------------+
-- slice
SELECT slice(array(1, 2, 3, 4), 2, 2);
+------------------------------+
|slice(array(1, 2, 3, 4), 2, 2)|
+------------------------------+
| [2, 3]|
+------------------------------+
SELECT slice(array(1, 2, 3, 4), -2, 2);
+-------------------------------+
|slice(array(1, 2, 3, 4), -2, 2)|
+-------------------------------+
| [3, 4]|
+-------------------------------+
-- sort_array
SELECT sort_array(array('b', 'd', null, 'c', 'a'), true);
+-----------------------------------------+
|sort_array(array(b, d, NULL, c, a), true)|
+-----------------------------------------+
| [NULL, a, b, c, d]|
+-----------------------------------------+
| 函数 | 描述 |
|---|
| element_at(array, index) | 返回给定（基于 1 的）索引处的数组元素。如果 Index 为 0，RainLake 会报错。如果 index < 0，则从最后一个元素访问到第一个元素。如果索引超过数组长度，且 spark.sql.ansi.enabled 设置为 false，则函数返回 NULL。如果 spark.sql.ansi.enabled 设置为 true，则会对无效索引抛出 ArrayIndexOutOfBoundsException。 |
| element_at(map, key) | 返回给定键的值。如果键不包含在 map 中，函数返回 NULL。 |
| map(key0, value0, key1, value1, ...) | 用给定的键/值对创建一个 map。 |
| map_concat(map, ...) | 返回所有给定映射的联合值 |
| map_contains_key(map, key) | 如果 map 包含键值，则返回 true。 |
| map_entries(map) | 返回给定 map 中所有条目的无序数组。 |
| map_from_arrays(keys, values) | 用给定的键/值数组创建一个映射。keys 中的所有元素都不能为空 |
| map_from_entries(arrayOfEntries) | 返回从给定条目数组创建的映射。 |
| map_keys(map) | 返回包含映射键的无序数组。 |
| map_values(map) | 返回包含映射值的无序数组。 |
| str_to_map(text[, pairDelim[, keyValueDelim]]) | 使用分隔符将文本分割成键/值对后创建映射。默认分隔符为 pairDelim 的 , 和 keyValueDelim 的 :。pairDelim 和 keyValueDelim 都被视为正则表达式。 |
| try_element_at(array, index) | 返回给定（基于 1 的）索引处的数组元素。如果 Index 为 0，RainLake 将抛出错误。如果 index < 0，则从最后一个元素到第一个元素进行访问。如果索引超过数组长度，函数总是返回 NULL。 |
| try_element_at(map, key) | 返回给定键的值。如果 key 不在 map 中，函数总是返回 NULL。 |
-- element_at
SELECT element_at(array(1, 2, 3), 2);
+-----------------------------+
|element_at(array(1, 2, 3), 2)|
+-----------------------------+
| 2|
+-----------------------------+
SELECT element_at(map(1, 'a', 2, 'b'), 2);
+------------------------------+
|element_at(map(1, a, 2, b), 2)|
+------------------------------+
| b|
+------------------------------+
-- map
SELECT map(1.0, '2', 3.0, '4');
+--------------------+
| map(1.0, 2, 3.0, 4)|
+--------------------+
|{1.0 -> 2, 3.0 -> 4}|
+--------------------+
-- map_concat
SELECT map_concat(map(1, 'a', 2, 'b'), map(3, 'c'));
+--------------------------------------+
|map_concat(map(1, a, 2, b), map(3, c))|
+--------------------------------------+
| {1 -> a, 2 -> b, ...|
+--------------------------------------+
-- map_contains_key
SELECT map_contains_key(map(1, 'a', 2, 'b'), 1);
+------------------------------------+
|map_contains_key(map(1, a, 2, b), 1)|
+------------------------------------+
| true|
+------------------------------------+
SELECT map_contains_key(map(1, 'a', 2, 'b'), 3);
+------------------------------------+
|map_contains_key(map(1, a, 2, b), 3)|
+------------------------------------+
| false|
+------------------------------------+
-- map_entries
SELECT map_entries(map(1, 'a', 2, 'b'));
+----------------------------+
|map_entries(map(1, a, 2, b))|
+----------------------------+
| [{1, a}, {2, b}]|
+----------------------------+
-- map_from_arrays
SELECT map_from_arrays(array(1.0, 3.0), array('2', '4'));
+---------------------------------------------+
|map_from_arrays(array(1.0, 3.0), array(2, 4))|
+---------------------------------------------+
| {1.0 -> 2, 3.0 -> 4}|
+---------------------------------------------+
-- map_from_entries
SELECT map_from_entries(array(struct(1, 'a'), struct(2, 'b')));
+---------------------------------------------------+
|map_from_entries(array(struct(1, a), struct(2, b)))|
+---------------------------------------------------+
| {1 -> a, 2 -> b}|
+---------------------------------------------------+
-- map_keys
SELECT map_keys(map(1, 'a', 2, 'b'));
+-------------------------+
|map_keys(map(1, a, 2, b))|
+-------------------------+
| [1, 2]|
+-------------------------+
-- map_values
SELECT map_values(map(1, 'a', 2, 'b'));
+---------------------------+
|map_values(map(1, a, 2, b))|
+---------------------------+
| [a, b]|
+---------------------------+
-- str_to_map
SELECT str_to_map('a:1,b:2,c:3', ',', ':');
+-----------------------------+
|str_to_map(a:1,b:2,c:3, ,, :)|
+-----------------------------+
| {a -> 1, b -> 2, ...|
+-----------------------------+
SELECT str_to_map('a');
+-------------------+
|str_to_map(a, ,, :)|
+-------------------+
| {a -> NULL}|
+-------------------+
-- try_element_at
SELECT try_element_at(array(1, 2, 3), 2);
+---------------------------------+
|try_element_at(array(1, 2, 3), 2)|
+---------------------------------+
| 2|
+---------------------------------+
SELECT try_element_at(map(1, 'a', 2, 'b'), 2);
+----------------------------------+
|try_element_at(map(1, a, 2, b), 2)|
+----------------------------------+
| b|
+----------------------------------+
| 函数 | 描述 |
|---|
| add_months(start_date,num_months) | 返回 start_date 往后 num_months 个月的日期。 |
| convert_timezone([sourceTz, ]targetTz,sourceTs) | 将不含时区 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,ftmt) | 将 timestamp 转换为日期格式 fmt 指定格式的字符串值。 |
| date_from_unix_date(days) | 根据自 1970-01-01 起的天数创建日期。 |
| date_part(field,source) | 提取日期/时间戳或时间间隔的一部分。 |
| date_sub(start_date,num_days) | 返回 start_date 往前 num_days 天的日期。 |
| date_trunc(fmt,ts) | 返回截断为格式模型 fmt 指定单位的时间戳 ts。 |
| dateadd(start_date,num_days) | 返回 start_date后 num_days的日期。 |
| dateiff(endDate,startDate) | 返回从 startDate 到 endDate 的天数。 |
| datepart(field,source) | 提取日期/时间戳或时间间隔源的一部分。 |
| day(date) | 返回日期/时间戳在月中的日数。 |
| dayofmonth(date) | 返回日期/时间戳在月中的日数。 |
| dayofweek(date) | 返回日期/时间戳的星期（1 = 星期日，2 = 星期一，...，7 = 星期六）。 |
| dayofyear(date) | 返回日期/时间戳在年中的日数。 |
| extract(field FROM source) | 提取日期/时间戳或时间间隔的一部分。 |
| from_unixtime(unix_time[, fmt]) | 以指定的 fmt 返回unix_time。 |
| from_utc_timestamp(timestamp,时区) | 给定一个时间戳，如 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为假，函数会在无效输入时返回 NULL。否则，函数将引发错误。 |
| make_ym_interval([years[, months]]) | 从年和月中提取年月间隔。 |
| minute(timestamp) | 返回字符串/时间戳的分钟分量。 |
| month(date) | 返回日期/时间戳的月份分量。 |
| months_between(timestamp1, timestamp2[, roundOff]) | 如果 timestamp1晚于 timestamp2，则结果为正。如果 timestamp1 和 timestamp2 在每月的同一天，或都是每月的最后一天，则忽略当天的时间。否则，差值将按每月 31 天计算，除非 roundOff=false 否则将四舍五入到 8 位数。 |
| next_day(start_date, day_of_week) | 返回第一个日期，该日期晚于 start_date，并按所示命名。如果至少一个输入参数为 NULL，函数返回 NULL。当两个输入参数都不是 NULL 且 day_of_week 是无效输入时，如果 spark.sql.ansi.enabled 设置为 true，函数将抛出 IllegalArgumentException，否则返回 NULL。 |
| now() | 返回查询评估开始时的当前时间戳。 |
| quarter(date) | 返回日期所在年份的季度，范围为 1 至 4。 |
| second(timestamp) | 返回字符串/时间戳的第二个分量。 |
| session_window(time_column,gap_duration) | Generates session window given a timestamp specifying column and gap duration. See 'Types of time windows' in Structured Streaming guide doc for detailed explanation and examples. |
| timestamp_micros(microseconds) | 根据自 UTC 时间起的微秒数创建时间戳。 |
| timestamp_millis(milliseconds) | 根据自 UTC 时间起的毫秒数创建时间戳。 |
| timestamp_seconds(seconds) | 用UTC 时间以来的秒数（可以是小数）创建时间戳。 |
| to_date(date_str[, fmt]) | 将 date_str 表达式和 fmt 表达式解析为日期。如果输入无效，则返回空值。默认情况下，如果省略了 fmt，则会按照日期的转换规则转换成日期。 |
| to_timestamp(timestamp_str[, fmt]) | 使用 fmt 表达式将 timestamp_str 表达式解析为时间戳。如果输入无效，则返回空值。默认情况下，如果省略了 fmt，则按照时间戳的转换规则进行转换。结果数据类型与配置 spark.sql.timestampType 的值一致。 |
| to_timestamp_ltz(timestamp_str[, fmt]) | 将包含 fmt 表达式的 timestamp_str 表达式解析为本地时区的时间戳。如果输入无效，则返回空值。默认情况下，如果省略了 fmt，则会按照转换规则转换为时间戳。 |
| to_timestamp_ntz(timestamp_str[, fmt]) | 使用 fmt 表达式将 timestamp_str 表达式解析为不含时区的时间戳。如果输入无效，则返回空值。默认情况下，如果省略了 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) | 返回将日期的时间部分截断为格式模型 fmt 指定单位的 date。 |
| try_to_timestamp(timestamp_str[, fmt]) | 将timestamp_str表达式和fmt表达式解析为时间戳。无论是否启用 ANSI SQL 模式，如果输入无效，函数总是返回空值。默认情况下，如果省略了 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 08:00:00|
+------------------------------------------------------------------------------------------+
-- curdate
SELECT curdate();
+--------------+
|current_date()|
+--------------+
| 2024-09-09|
+--------------+
-- current_date
SELECT current_date();
+--------------+
|current_date()|
+--------------+
| 2024-09-09|
+--------------+
SELECT current_date;
+--------------+
|current_date()|
+--------------+
| 2024-09-09|
+--------------+
-- current_timestamp
SELECT current_timestamp();
+--------------------+
| current_timestamp()|
+--------------------+
|2024-09-09 06:59:...|
+--------------------+
SELECT current_timestamp;
+--------------------+
| current_timestamp()|
+--------------------+
|2024-09-09 06:59:...|
+--------------------+
-- current_timezone
SELECT current_timezone();
+------------------+
|current_timezone()|
+------------------+
| Asia/Shanghai|
+------------------+
-- date_add
SELECT date_add('2016-07-30', 1);
+-----------------------+
|date_add(2016-07-30, 1)|
+-----------------------+
| 2016-07-31|
+-----------------------+
-- date_diff
SELECT date_diff('2009-07-31', '2009-07-30');
+---------------------------------+
|date_diff(2009-07-31, 2009-07-30)|
+---------------------------------+
| 1|
+---------------------------------+
SELECT date_diff('2009-07-30', '2009-07-31');
+---------------------------------+
|date_diff(2009-07-30, 2009-07-31)|
+---------------------------------+
| -1|
+---------------------------------+
-- date_format
SELECT date_format('2016-04-08', 'y');
+--------------------------+
|date_format(2016-04-08, y)|
+--------------------------+
| 2016|
+--------------------------+
-- date_from_unix_date
SELECT date_from_unix_date(1);
+----------------------+
|date_from_unix_date(1)|
+----------------------+
| 1970-01-02|
+----------------------+
-- date_part
SELECT date_part('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456');
+-------------------------------------------------------+
|date_part(YEAR, TIMESTAMP '2019-08-12 01:00:00.123456')|
+-------------------------------------------------------+
| 2019|
+-------------------------------------------------------+
SELECT date_part('week', timestamp'2019-08-12 01:00:00.123456');
+-------------------------------------------------------+
|date_part(week, TIMESTAMP '2019-08-12 01:00:00.123456')|
+-------------------------------------------------------+
| 33|
+-------------------------------------------------------+
SELECT date_part('doy', DATE'2019-08-12');
+---------------------------------+
|date_part(doy, DATE '2019-08-12')|
+---------------------------------+
| 224|
+---------------------------------+
SELECT date_part('SECONDS', timestamp'2019-10-01 00:00:01.000001');
+----------------------------------------------------------+
|date_part(SECONDS, TIMESTAMP '2019-10-01 00:00:01.000001')|
+----------------------------------------------------------+
| 1.000001|
+----------------------------------------------------------+
SELECT date_part('days', interval 5 days 3 hours 7 minutes);
+-------------------------------------------------+
|date_part(days, INTERVAL '5 03:07' DAY TO MINUTE)|
+-------------------------------------------------+
| 5|
+-------------------------------------------------+
SELECT date_part('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
+-------------------------------------------------------------+
|date_part(seconds, INTERVAL '05:00:30.001001' HOUR TO SECOND)|
+-------------------------------------------------------------+
| 30.001001|
+-------------------------------------------------------------+
SELECT date_part('MONTH', INTERVAL '2021-11' YEAR TO MONTH);
+--------------------------------------------------+
|date_part(MONTH, INTERVAL '2021-11' YEAR TO MONTH)|
+--------------------------------------------------+
| 11|
+--------------------------------------------------+
SELECT date_part('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND);
+---------------------------------------------------------------+
|date_part(MINUTE, INTERVAL '123 23:55:59.002001' DAY TO SECOND)|
+---------------------------------------------------------------+
| 55|
+---------------------------------------------------------------+
-- date_sub
SELECT date_sub('2016-07-30', 1);
+-----------------------+
|date_sub(2016-07-30, 1)|
+-----------------------+
| 2016-07-29|
+-----------------------+
-- date_trunc
SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');
+-----------------------------------------+
|date_trunc(YEAR, 2015-03-05T09:32:05.359)|
+-----------------------------------------+
| 2015-01-01 00:00:00|
+-----------------------------------------+
SELECT date_trunc('MM', '2015-03-05T09:32:05.359');
+---------------------------------------+
|date_trunc(MM, 2015-03-05T09:32:05.359)|
+---------------------------------------+
| 2015-03-01 00:00:00|
+---------------------------------------+
SELECT date_trunc('DD', '2015-03-05T09:32:05.359');
+---------------------------------------+
|date_trunc(DD, 2015-03-05T09:32:05.359)|
+---------------------------------------+
| 2015-03-05 00:00:00|
+---------------------------------------+
SELECT date_trunc('HOUR', '2015-03-05T09:32:05.359');
+-----------------------------------------+
|date_trunc(HOUR, 2015-03-05T09:32:05.359)|
+-----------------------------------------+
| 2015-03-05 09:00:00|
+-----------------------------------------+
SELECT date_trunc('MILLISECOND', '2015-03-05T09:32:05.123456');
+---------------------------------------------------+
|date_trunc(MILLISECOND, 2015-03-05T09:32:05.123456)|
+---------------------------------------------------+
| 2015-03-05 09:32:...|
+---------------------------------------------------+
-- dateadd
SELECT dateadd('2016-07-30', 1);
+-----------------------+
|date_add(2016-07-30, 1)|
+-----------------------+
| 2016-07-31|
+-----------------------+
-- datediff
SELECT datediff('2009-07-31', '2009-07-30');
+--------------------------------+
|datediff(2009-07-31, 2009-07-30)|
+--------------------------------+
| 1|
+--------------------------------+
SELECT datediff('2009-07-30', '2009-07-31');
+--------------------------------+
|datediff(2009-07-30, 2009-07-31)|
+--------------------------------+
| -1|
+--------------------------------+
-- datepart
SELECT datepart('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456');
+----------------------------------------------------------+
|datepart(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+----------------------------------------------------------+
| 2019|
+----------------------------------------------------------+
SELECT datepart('week', timestamp'2019-08-12 01:00:00.123456');
+----------------------------------------------------------+
|datepart(week FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+----------------------------------------------------------+
| 33|
+----------------------------------------------------------+
SELECT datepart('doy', DATE'2019-08-12');
+------------------------------------+
|datepart(doy FROM DATE '2019-08-12')|
+------------------------------------+
| 224|
+------------------------------------+
SELECT datepart('SECONDS', timestamp'2019-10-01 00:00:01.000001');
+-------------------------------------------------------------+
|datepart(SECONDS FROM TIMESTAMP '2019-10-01 00:00:01.000001')|
+-------------------------------------------------------------+
| 1.000001|
+-------------------------------------------------------------+
SELECT datepart('days', interval 5 days 3 hours 7 minutes);
+----------------------------------------------------+
|datepart(days FROM INTERVAL '5 03:07' DAY TO MINUTE)|
+----------------------------------------------------+
| 5|
+----------------------------------------------------+
SELECT datepart('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
+----------------------------------------------------------------+
|datepart(seconds FROM INTERVAL '05:00:30.001001' HOUR TO SECOND)|
+----------------------------------------------------------------+
| 30.001001|
+----------------------------------------------------------------+
SELECT datepart('MONTH', INTERVAL '2021-11' YEAR TO MONTH);
+-----------------------------------------------------+
|datepart(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH)|
+-----------------------------------------------------+
| 11|
+-----------------------------------------------------+
SELECT datepart('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND);
+------------------------------------------------------------------+
|datepart(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND)|
+------------------------------------------------------------------+
| 55|
+------------------------------------------------------------------+
-- day
SELECT day('2009-07-30');
+---------------+
|day(2009-07-30)|
+---------------+
| 30|
+---------------+
-- dayofmonth
SELECT dayofmonth('2009-07-30');
+----------------------+
|dayofmonth(2009-07-30)|
+----------------------+
| 30|
+----------------------+
-- dayofweek
SELECT dayofweek('2009-07-30');
+---------------------+
|dayofweek(2009-07-30)|
+---------------------+
| 5|
+---------------------+
-- dayofyear
SELECT dayofyear('2016-04-09');
+---------------------+
|dayofyear(2016-04-09)|
+---------------------+
| 100|
+---------------------+
-- extract
SELECT extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456');
+---------------------------------------------------------+
|extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+---------------------------------------------------------+
| 2019|
+---------------------------------------------------------+
SELECT extract(week FROM timestamp'2019-08-12 01:00:00.123456');
+---------------------------------------------------------+
|extract(week FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+---------------------------------------------------------+
| 33|
+---------------------------------------------------------+
SELECT extract(doy FROM DATE'2019-08-12');
+-----------------------------------+
|extract(doy FROM DATE '2019-08-12')|
+-----------------------------------+
| 224|
+-----------------------------------+
SELECT extract(SECONDS FROM timestamp'2019-10-01 00:00:01.000001');
+------------------------------------------------------------+
|extract(SECONDS FROM TIMESTAMP '2019-10-01 00:00:01.000001')|
+------------------------------------------------------------+
| 1.000001|
+------------------------------------------------------------+
SELECT extract(days FROM interval 5 days 3 hours 7 minutes);
+---------------------------------------------------+
|extract(days FROM INTERVAL '5 03:07' DAY TO MINUTE)|
+---------------------------------------------------+
| 5|
+---------------------------------------------------+
SELECT extract(seconds FROM interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
+---------------------------------------------------------------+
|extract(seconds FROM INTERVAL '05:00:30.001001' HOUR TO SECOND)|
+---------------------------------------------------------------+
| 30.001001|
+---------------------------------------------------------------+
SELECT extract(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH);
+----------------------------------------------------+
|extract(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH)|
+----------------------------------------------------+
| 11|
+----------------------------------------------------+
SELECT extract(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND);
+-----------------------------------------------------------------+
|extract(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND)|
+-----------------------------------------------------------------+
| 55|
+-----------------------------------------------------------------+
-- from_unixtime
SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
+-------------------------------------+
|from_unixtime(0, yyyy-MM-dd HH:mm:ss)|
+-------------------------------------+
| 1970-01-01 08:00:00|
+-------------------------------------+
SELECT from_unixtime(0);
+-------------------------------------+
|from_unixtime(0, yyyy-MM-dd HH:mm:ss)|
+-------------------------------------+
| 1970-01-01 08:00:00|
+-------------------------------------+
-- from_utc_timestamp
SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul');
+------------------------------------------+
|from_utc_timestamp(2016-08-31, Asia/Seoul)|
+------------------------------------------+
| 2016-08-31 09:00:00|
+------------------------------------------+
-- hour
SELECT hour('2009-07-30 12:58:59');
+-------------------------+
|hour(2009-07-30 12:58:59)|
+-------------------------+
| 12|
+-------------------------+
-- last_day
SELECT last_day('2009-01-12');
+--------------------+
|last_day(2009-01-12)|
+--------------------+
| 2009-01-31|
+--------------------+
-- localtimestamp
SELECT localtimestamp();
+--------------------+
| localtimestamp()|
+--------------------+
|2024-09-09 06:59:...|
+--------------------+
-- 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|
+-----------------+
-- months_between
SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
+-----------------------------------------------------+
|months_between(1997-02-28 10:30:00, 1996-10-30, true)|
+-----------------------------------------------------+
| 3.94959677|
+-----------------------------------------------------+
SELECT months_between('1997-02-28 10:30:00', '1996-10-30', false);
+------------------------------------------------------+
|months_between(1997-02-28 10:30:00, 1996-10-30, false)|
+------------------------------------------------------+
| 3.9495967741935485|
+------------------------------------------------------+
-- next_day
SELECT next_day('2015-01-14', 'TU');
+------------------------+
|next_day(2015-01-14, TU)|
+------------------------+
| 2015-01-20|
+------------------------+
-- now
SELECT now();
+--------------------+
| now()|
+--------------------+
|2024-09-09 06:59:...|
+--------------------+
-- 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 23:30:...|
+----------------------------------+
-- timestamp_millis
SELECT timestamp_millis(1230219000123);
+-------------------------------+
|timestamp_millis(1230219000123)|
+-------------------------------+
| 2008-12-25 23:30:...|
+-------------------------------+
-- timestamp_seconds
SELECT timestamp_seconds(1230219000);
+-----------------------------+
|timestamp_seconds(1230219000)|
+-----------------------------+
| 2008-12-25 23: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)|
+-----------------------------------------+
| 1460044800|
+-----------------------------------------+
-- to_utc_timestamp
SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul');
+----------------------------------------+
|to_utc_timestamp(2016-08-31, Asia/Seoul)|
+----------------------------------------+
| 2016-08-30 15:00:00|
+----------------------------------------+
-- trunc
SELECT trunc('2019-08-04', 'week');
+-----------------------+
|trunc(2019-08-04, week)|
+-----------------------+
| 2019-07-29|
+-----------------------+
SELECT trunc('2019-08-04', 'quarter');
+--------------------------+
|trunc(2019-08-04, quarter)|
+--------------------------+
| 2019-07-01|
+--------------------------+
SELECT trunc('2009-02-12', 'MM');
+---------------------+
|trunc(2009-02-12, MM)|
+---------------------+
| 2009-02-01|
+---------------------+
SELECT trunc('2015-10-27', 'YEAR');
+-----------------------+
|trunc(2015-10-27, YEAR)|
+-----------------------+
| 2015-01-01|
+-----------------------+
-- try_to_timestamp
SELECT try_to_timestamp('2016-12-31 00:12:00');
+-------------------------------------+
|try_to_timestamp(2016-12-31 00:12:00)|
+-------------------------------------+
| 2016-12-31 00:12:00|
+-------------------------------------+
SELECT try_to_timestamp('2016-12-31', 'yyyy-MM-dd');
+----------------------------------------+
|try_to_timestamp(2016-12-31, yyyy-MM-dd)|
+----------------------------------------+
| 2016-12-31 00:00:00|
+----------------------------------------+
SELECT try_to_timestamp('foo', 'yyyy-MM-dd');
+---------------------------------+
|try_to_timestamp(foo, yyyy-MM-dd)|
+---------------------------------+
| NULL|
+---------------------------------+
-- unix_date
SELECT unix_date(DATE("1970-01-02"));
+---------------------+
|unix_date(1970-01-02)|
+---------------------+
| 1|
+---------------------+
-- unix_micros
SELECT unix_micros(TIMESTAMP('1970-01-01 00:00:01Z'));
+---------------------------------+
|unix_micros(1970-01-01 00:00:01Z)|
+---------------------------------+
| 1000000|
+---------------------------------+
-- unix_millis
SELECT unix_millis(TIMESTAMP('1970-01-01 00:00:01Z'));
+---------------------------------+
|unix_millis(1970-01-01 00:00:01Z)|
+---------------------------------+
| 1000|
+---------------------------------+
-- unix_seconds
SELECT unix_seconds(TIMESTAMP('1970-01-01 00:00:01Z'));
+----------------------------------+
|unix_seconds(1970-01-01 00:00:01Z)|
+----------------------------------+
| 1|
+----------------------------------+
-- unix_timestamp
SELECT unix_timestamp();
+--------------------------------------------------------+
|unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss)|
+--------------------------------------------------------+
| 1725865169|
+--------------------------------------------------------+
SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
+--------------------------------------+
|unix_timestamp(2016-04-08, yyyy-MM-dd)|
+--------------------------------------+
| 1460044800|
+--------------------------------------+
-- 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|
+----------------+
| 函数 | 描述 |
|---|
| 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]) | 返回 JSON 字符串 DDL 格式的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(struct(1, 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(struct(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(struct(1, 2)))|
+----------------------------+
| [{"a":1,"b":2}]|
+----------------------------+
SELECT to_json(map('a', named_struct(b = 1)));
+--------------------------+
|to_json(map(a, struct(1)))|
+--------------------------+
| {"a":{"b":1}}|
+--------------------------+
SELECT to_json(map(named_struct(a = 1),named_struct(b = 2)));
+----------------------------------+
|to_json(map(struct(1), struct(2)))|
+----------------------------------+
| {"[1]":{"b":2}}|
+----------------------------------+
SELECT to_json(map('a', 1));
+------------------+
|to_json(map(a, 1))|
+------------------+
| {"a":1}|
+------------------+
SELECT to_json(array(map('a', 1)));
+-------------------------+
|to_json(array(map(a, 1)))|
+-------------------------+
| [{"a":1}]|
+-------------------------+
| 函数 | 描述 |
|---|
| expr1 % expr2 | 返回 expr1/expr2 的余数。 |
| expr1 * expr2 | 返回 expr1*expr2。 |
| expr1 + expr2 | 返回 expr1+expr2。 |
| expr1 - expr2 | 返回 expr1-expr2。 |
| expr1 / expr2 | 返回 expr1/expr2。总是执行浮点除法。 |
| abs(expr) | 返回数值或区间值的绝对值。 |
| acos(expr) | 返回 expr 的反余弦值，如同由 java.lang.Math.acos 计算。 |
| acosh(expr) | 返回 expr 的反双曲余弦值。 |
| asin(expr) | 返回 expr 的反正弦值，如同由 java.lang.Math.asin计算。 |
| asinh(expr) | 返回 expr 的反双曲正弦值。 |
| atan(expr) | 返回 expr 的反正切值，如同使用 java.lang.Math.atan 所计算的结果。 |
| atan2(exprY, exprX) | 返回平面的正 x 轴与坐标 (exprX,exprY)所给点之间的夹角（弧度），如同使用 java.lang.Math.atan2 所计算的结果。 |
| atanh(expr) | 返回 expr 的反双曲正切值。 |
| bin(expr) | 返回以二进制表示的长整型数值 expr 的字符串。 |
| bround(expr,d) | 返回使用 HALF_EVEN 舍入模式舍入到小数点后 d 位的 expr 值。 |
| cbrt(expr) | 返回 expr 的立方根。 |
| ceil(expr[, scale]) | 返回四舍五入后不小于 expr 的最小数字。可以指定可选的 scale 参数来控制四舍五入的行为。 |
| ceiling(expr[, scale]) | 返回四舍五入后不小于 expr 的最小数值。可以指定可选的 scale 参数来控制四舍五入的行为。 |
| conv(num, from_base, to_base) | 将 num 从 from_base 进制转换为 to_base 进制。 |
| cos(expr) | 返回 expr 的余弦值，如同用 java.lang.Math.cos 计算。 |
| cosh(expr) | 返回 expr 的双曲余弦值，如同用 java.lang.Math.cosh 计算。 |
| cot(expr) | 返回 expr 的余切值，如同用 1/java.lang.Math.tan计算。 |
| csc(expr) | 返回 expr 的余割值，如同用 1/java.lang.Math.sin计算。 |
| degrees(expr) | 将弧度转换为角度。 |
| expr1 div expr2 | 用 expr1 除以 expr2。如果操作数为 NULL 或 expr2 为 0，则返回 NULL。结果会被转换为长整型。 |
| e() | 返回欧拉常数 e。 |
| exp(expr) | 返回 e 的 expr 次方。 |
| expm1(expr) | 返回 exp(expr) - 1。 |
| factorial(expr) | 返回 expr 的阶乘。expr 为 [0 .. 20]。否则为空。 |
| floor(expr[, scale]) | 返回四舍五入后不大于 expr 的最大数字。可以指定一个可选的 scale 参数来控制舍入行为。 |
| greatest(expr,...) | 返回所有参数的最大值，跳过空值。 |
| hex(expr) | 将 expr 转换为十六进制。 |
| hypot(expr1, expr2) | 返回 sqrt(expr1 ** 2 + expr2 ** 2). |
| least(expr, ...) | 返回所有参数的最小值，跳过空值。 |
| ln(expr) | 返回 expr 的自然对数（以 e 为底）。 |
| log(base, expr) | 返回以 base 为底 expr 的对数。 |
| log10(expr) | 返回以 10 为底 expr 的对数。 |
| log1p(expr) | 返回 log(1 + expr)。 |
| log2(expr) | 返回以 2 为底的 expr 的对数。 |
| expr1 mod expr2 | 返回 expr1/expr2 后的余数。 |
| negative(expr) | 返回 expr 的负值。 |
| pi() | 返回圆周率 pi。 |
| pmod(expr1, expr2) | 返回 expr1 mod expr2 的正值。 |
| positive(expr) | 返回 expr 的值。 |
| pow(expr1, expr2) | 返回 expr1 的 expr2 次幂。 |
| power(expr1, expr2) | 返回 expr1 的 expr2 次幂。 |
| radians(expr) | 将角度转换为弧度。 |
| rand([seed]) | 返回在 [0, 1) 范围内具有独立且同分布（i.i.d.）均匀分布值的随机值。 |
| randn([seed]) | 返回标准正态分布中独立且同分布（i.i.d.）的随机值。 |
| random([seed]) | 返回独立同分布（i.i.d.）的随机值，取值范围为 [0, 1]。 |
| rint(expr) | 返回与参数值最接近且等于数学整数的 double 值。 |
| round(expr, d) | 使用 HALF_UP 四舍五入模式，将 expr 四舍五入到小数点后 d 位。 |
| sec(expr) | 返回expr的正割，就像用 1/java.lang.Math.cos计算一样。 |
| shiftleft(base, expr) | 返回 base 按位左移 expr 位。 |
| sign(expr) | 当 expr 为负数、0 或正数时，返回 -1.0、0.0 或 1.0。 |
| signum(expr) | 当 expr 为负数、0 或正数时，返回 -1.0、0.0 或 1.0。 |
| sin(expr) | 返回 expr 的正弦值，如同用 java.lang.Math.sin计算。 |
| sinh(expr) | 返回 expr 的双曲正弦值，如同使用 java.lang.Math.sinh计算。 |
| sqrt(expr) | 返回 expr 的平方根。 |
| tan(expr) | 返回expr的正切值，如同用 java.lang.Math.tan计算。 |
| tanh(expr) | 返回expr的双曲正切值，如同用 java.lang.Math.tanh计算。 |
| try_add(expr1, expr2) | 返回 expr1 和 expr2 的和，溢出时结果为空。可接受的输入类型与 + 运算符相同。 |
| try_divide(dividend, divisor) | 返回dividend/divisor。它总是执行浮点除法。如果 expr2 为 0，其结果始终为空。divisor 必须是数值。 |
| try_multiply(expr1, expr2) | 返回 expr1*expr2 且结果在溢出时为空。可接受的输入类型与 * 运算符相同。 |
| try_subtract(expr1, expr2) | 返回expr1-expr2，溢出时结果为空。可接受的输入类型与 - 运算符相同。 |
| unhex(expr) | 将十六进制的 expr 转换为二进制。 |
| width_bucket(value, min_value, max_value, num_bucket) | 返回在等宽直方图中，在 min_value 至 max_value 的范围内，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.9298053371596178|
+------------------+
SELECT rand(0);
+------------------+
| rand(0)|
+------------------+
|0.7604953758285915|
+------------------+
SELECT rand(null);
+------------------+
| rand(NULL)|
+------------------+
|0.7604953758285915|
+------------------+
-- randn
SELECT randn();
+-------------------+
| randn()|
+-------------------+
|-0.8390096312651348|
+-------------------+
SELECT randn(0);
+------------------+
| randn(0)|
+------------------+
|1.6034991609278433|
+------------------+
SELECT randn(null);
+------------------+
| randn(NULL)|
+------------------+
|1.6034991609278433|
+------------------+
-- random
SELECT random();
+-------------------+
| rand()|
+-------------------+
|0.45289119642897513|
+-------------------+
SELECT random(0);
+------------------+
| rand(0)|
+------------------+
|0.7604953758285915|
+------------------+
SELECT random(null);
+------------------+
| rand(NULL)|
+------------------+
|0.7604953758285915|
+------------------+
-- rint
SELECT rint(12.3456);
+-------------+
|rint(12.3456)|
+-------------+
| 12.0|
+-------------+
-- round
SELECT round(2.5, 0);
+-------------+
|round(2.5, 0)|
+-------------+
| 3|
+-------------+
-- sec
SELECT sec(0);
+------+
|SEC(0)|
+------+
| 1.0|
+------+
-- shiftleft
SELECT shiftleft(2, 1);
+---------------+
|shiftleft(2, 1)|
+---------------+
| 4|
+---------------+
-- sign
SELECT sign(40);
+--------+
|sign(40)|
+--------+
| 1.0|
+--------+
SELECT sign(INTERVAL -'100' YEAR);
+--------------------------+
|sign(INTERVAL '-100' YEAR)|
+--------------------------+
| -1.0|
+--------------------------+
-- signum
SELECT signum(40);
+----------+
|SIGNUM(40)|
+----------+
| 1.0|
+----------+
SELECT signum(INTERVAL -'100' YEAR);
+----------------------------+
|SIGNUM(INTERVAL '-100' YEAR)|
+----------------------------+
| -1.0|
+----------------------------+
-- sin
SELECT sin(0);
+------+
|SIN(0)|
+------+
| 0.0|
+------+
-- sinh
SELECT sinh(0);
+-------+
|SINH(0)|
+-------+
| 0.0|
+-------+
-- sqrt
SELECT sqrt(4);
+-------+
|SQRT(4)|
+-------+
| 2.0|
+-------+
-- tan
SELECT tan(0);
+------+
|TAN(0)|
+------+
| 0.0|
+------+
-- tanh
SELECT tanh(0);
+-------+
|TANH(0)|
+-------+
| 0.0|
+-------+
-- try_add
SELECT try_add(1, 2);
+-------------+
|try_add(1, 2)|
+-------------+
| 3|
+-------------+
SELECT try_add(2147483647, 1);
+----------------------+
|try_add(2147483647, 1)|
+----------------------+
| NULL|
+----------------------+
SELECT try_add(date'2021-01-01', 1);
+-----------------------------+
|try_add(DATE '2021-01-01', 1)|
+-----------------------------+
| 2021-01-02|
+-----------------------------+
SELECT try_add(date'2021-01-01', interval 1 year);
+---------------------------------------------+
|try_add(DATE '2021-01-01', INTERVAL '1' YEAR)|
+---------------------------------------------+
| 2022-01-01|
+---------------------------------------------+
SELECT try_add(timestamp'2021-01-01 00:00:00', interval 1 day);
+----------------------------------------------------------+
|try_add(TIMESTAMP '2021-01-01 00:00:00', INTERVAL '1' DAY)|
+----------------------------------------------------------+
| 2021-01-02 00:00:00|
+----------------------------------------------------------+
SELECT try_add(interval 1 year, interval 2 year);
+---------------------------------------------+
|try_add(INTERVAL '1' YEAR, INTERVAL '2' YEAR)|
+---------------------------------------------+
| INTERVAL '3' YEAR|
+---------------------------------------------+
-- try_divide
SELECT try_divide(3, 2);
+----------------+
|try_divide(3, 2)|
+----------------+
| 1.5|
+----------------+
SELECT try_divide(2L, 2L);
+----------------+
|try_divide(2, 2)|
+----------------+
| 1.0|
+----------------+
SELECT try_divide(1, 0);
+----------------+
|try_divide(1, 0)|
+----------------+
| NULL|
+----------------+
SELECT try_divide(interval 2 month, 2);
+---------------------------------+
|try_divide(INTERVAL '2' MONTH, 2)|
+---------------------------------+
| INTERVAL '0-1' YE...|
+---------------------------------+
SELECT try_divide(interval 2 month, 0);
+---------------------------------+
|try_divide(INTERVAL '2' MONTH, 0)|
+---------------------------------+
| NULL|
+---------------------------------+
-- try_multiply
SELECT try_multiply(2, 3);
+------------------+
|try_multiply(2, 3)|
+------------------+
| 6|
+------------------+
SELECT try_multiply(-2147483648, 10);
+-----------------------------+
|try_multiply(-2147483648, 10)|
+-----------------------------+
| NULL|
+-----------------------------+
SELECT try_multiply(interval 2 year, 3);
+----------------------------------+
|try_multiply(INTERVAL '2' YEAR, 3)|
+----------------------------------+
| INTERVAL '6-0' YE...|
+----------------------------------+
-- try_subtract
SELECT try_subtract(2, 1);
+------------------+
|try_subtract(2, 1)|
+------------------+
| 1|
+------------------+
SELECT try_subtract(-2147483648, 1);
+----------------------------+
|try_subtract(-2147483648, 1)|
+----------------------------+
| NULL|
+----------------------------+
SELECT try_subtract(date'2021-01-02', 1);
+----------------------------------+
|try_subtract(DATE '2021-01-02', 1)|
+----------------------------------+
| 2021-01-01|
+----------------------------------+
SELECT try_subtract(date'2021-01-01', interval 1 year);
+--------------------------------------------------+
|try_subtract(DATE '2021-01-01', INTERVAL '1' YEAR)|
+--------------------------------------------------+
| 2020-01-01|
+--------------------------------------------------+
SELECT try_subtract(timestamp'2021-01-02 00:00:00', interval 1 day);
+---------------------------------------------------------------+
|try_subtract(TIMESTAMP '2021-01-02 00:00:00', INTERVAL '1' DAY)|
+---------------------------------------------------------------+
| 2021-01-01 00:00:00|
+---------------------------------------------------------------+
SELECT try_subtract(interval 2 year, interval 1 year);
+--------------------------------------------------+
|try_subtract(INTERVAL '2' YEAR, INTERVAL '1' YEAR)|
+--------------------------------------------------+
| INTERVAL '1' YEAR|
+--------------------------------------------------+
-- unhex
SELECT decode(unhex('537061726B2053514C'), 'UTF-8');
+----------------------------------------+
|decode(unhex(537061726B2053514C), UTF-8)|
+----------------------------------------+
| Spark SQL|
+----------------------------------------+
-- width_bucket
SELECT width_bucket(5.3, 0.2, 10.6, 5);
+-------------------------------+
|width_bucket(5.3, 0.2, 10.6, 5)|
+-------------------------------+
| 3|
+-------------------------------+
SELECT width_bucket(-2.1, 1.3, 3.4, 3);
+-------------------------------+
|width_bucket(-2.1, 1.3, 3.4, 3)|
+-------------------------------+
| 0|
+-------------------------------+
SELECT width_bucket(8.1, 0.0, 5.7, 4);
+------------------------------+
|width_bucket(8.1, 0.0, 5.7, 4)|
+------------------------------+
| 5|
+------------------------------+
SELECT width_bucket(-0.9, 5.2, 0.5, 2);
+-------------------------------+
|width_bucket(-0.9, 5.2, 0.5, 2)|
+-------------------------------+
| 3|
+-------------------------------+
SELECT width_bucket(INTERVAL '0' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
+--------------------------------------------------------------------------+
|width_bucket(INTERVAL '0' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10)|
+--------------------------------------------------------------------------+
| 1|
+--------------------------------------------------------------------------+
SELECT width_bucket(INTERVAL '1' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
+--------------------------------------------------------------------------+
|width_bucket(INTERVAL '1' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10)|
+--------------------------------------------------------------------------+
| 2|
+--------------------------------------------------------------------------+
SELECT width_bucket(INTERVAL '0' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
+-----------------------------------------------------------------------+
|width_bucket(INTERVAL '0' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10)|
+-----------------------------------------------------------------------+
| 1|
+-----------------------------------------------------------------------+
SELECT width_bucket(INTERVAL '1' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
+-----------------------------------------------------------------------+
|width_bucket(INTERVAL '1' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10)|
+-----------------------------------------------------------------------+
| 2|
+-----------------------------------------------------------------------+
| 函数 | 描述 |
|---|
| ascii(str) | 返回 str 第一个字符的ASCII数值。 |
| 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) |
| conconcat_ws(sep[, str | array(str)]+) | 返回以sep分隔的字符串的连接结果，跳过空值。 |
| contains(left, right) | 返回布尔值。如果在 left 中找到 right，则值为 True。如果输入表达式为空，则返回 NULL。否则，返回 False。左或右都必须是字符串或二进制类型。 |
| decode(bin,charset) | 使用第二个参数的字符集解码第一个参数。 |
| decode(expr, search, result[, search, result ] ... [, default]) | 依次比较 expr 和每个搜索值。如果 expr 等于搜索值，decode 会返回相应的结果。如果没有找到匹配值，则返回 default。如果省略 default，则返回空值。 |
| elt(n, input1, input2, ...) | 返回第 n 个输入值，例如，当 n 为 2 时返回 input2。如果索引超过数组长度，且 spark.sql.ansi.enabled 设置为 false，则函数返回 NULL。如果 spark.sql.ansi.enabled 设置为 true，则会对无效索引抛出 ArrayIndexOutOfBoundsException。 |
| encode(str, charset) | 使用第二个参数的字符集编码第一个参数。 |
| endswith(left, right) | 返回一个布尔值。如果 left 的结尾是 right，则值为 True。如果任一输入表达式为 NULL，则返回 NULL。否则返回 False。left 或 right 都必须是 STRING 或 BINARY 类型。 |
| find_in_set(str, str_array) | 返回给定字符串 (str)在逗号分隔列表 (str_array)中的索引（以 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 为基准）。 |
| lcase(str) | 返回将所有字符改为小写的 str。 |
| left(str,len) | 返回字符串 str 最左边的 len（len 可以是字符串类型）字符，如果 len 小于或等于 0，结果为空字符串。 |
| len(expr) | 返回字符串数据的字符长度或二进制数据的字节数。字符串数据的长度包括尾部空格。二进制数据的长度包括二进制零。 |
| length(expr) | 返回字符串数据的字符长度或二进制数据的字节数。字符串数据的长度包括尾部空格。二进制数据的长度包括二进制零。 |
| levenshtein(str1, str2[, threshold]) | 返回两个给定字符串之间的 Levenshtein 编辑距离。如果设置了阈值，且距离大于阈值，则返回-1。 |
| locate(substr, str[, pos]) | 返回在位置 pos 之后，substr在str中第一次出现的位置。给定的 pos 和返回值以 1 为单位。 |
| lower(str) | 返回所有字符小写的 str。 |
| lpad(str,len[,pad]) | 返回用 pad 左填充的长度为 len 的 str。如果 str 长度大于 len，返回值将缩短为 len 字符或字节。如果未指定 pad，那么如果 str 是字符串，将向左填充空格字符；如果是字节序列，将向左填充零。 |
| ltrim(str) | 删除 str 前面的空格字符。 |
| luhn_check(str) | 根据 Luhn 算法检查数字字符串是否有效。此校验和函数广泛应用于信用卡号码和政府身份证号码，以区分有效号码和输入错误的号码。 |
| mask(input[, upperChar, lowerChar, digitChar, otherChar]) | 屏蔽给定的字符串值。该函数用 'X' 或 'x' 替换字符，用 'n' 替换数字。这对于创建已删除敏感信息的表格副本非常有用。 |
| octet_length(expr) | 返回字符串数据的字节长度或二进制数据的字节数。 |
| overlay(input, replace, pos[, len]) | 用从 pos 开始、长度为 len 的 replace 替换 input 。 |
| position(substr, str[, pos]) | 返回substr在str中第一次出现的位置，位于pos之后。给定的 pos 和返回值以 1 为单位。 |
| printf(strfmt, obj, ...) | 从 printf 样式的格式字符串返回格式字符串。 |
| regexp_count(str, regexp) | 返回字符串 str 中匹配正则表达式模式 regexp 的次数。 |
| regexp_extract(str, regexp[, idx]) | 提取字符串str中与regexp表达式匹配且与 regex 组索引对应的第一个字符串。 |
| regexp_extract_all(str, regexp[, idx]) | 提取str中与regexp表达式匹配且与 regex 组索引对应的所有字符串。 |
| regexp_instr(str, regexp) | 为正则表达式搜索字符串，并返回一个整数，表示匹配子串的起始位置。位置以 1 为基础，而不是以 0 为基础。如果未找到匹配字符串，则返回 0。 |
| regexp_replace(str, regexp, rep[, position]) | 用 rep 替换 str 中所有匹配 regexp 的子串。 |
| regexp_substr(str, regexp) | 返回字符串 str 中与正则表达式 regexp 匹配的子字符串。如果未找到正则表达式，结果为空。 |
| repeat(str, n) | 返回重复给定字符串值 n 次的字符串。 |
| replace(str, search[, replace]) | 用 replace 替换所有出现的 search。 |
| right(str, len) | 返回字符串 str 最右边的 len（len 可以是字符串类型）字符，如果 len 小于或等于 0，结果为空字符串。 |
| rpad(str, len[, pad]) | 返回用pad右填充到len长度的str。如果 str 长度大于 len，返回值将缩短为 len 字符。如果未指定 pad，如果 str 是字符串，将用空格填充，如果是二进制字符串，将用零填充。 |
| rtrim(str) | 删除 str 的尾部空格字符。 |
| sentences(str[, lang, country]) | 将 str 拆分为一个词组数组。 |
| soundex(str) | 返回字符串的 Soundex 编码。 |
| space(n) | 返回由 n 个空格组成的字符串。 |
| split(str, regex, limit) | 将字符串中与 regex 匹配的部分拆分，并返回一个长度不超过 limit 的数组。 |
| split_part(str, delimiter, partNum) | 按分隔符分割 str 并返回请求分割的部分（以 1 为基准）。如果输入为空，则返回空字符串。如果 partNum 超出了分割部分的范围，则返回空字符串。如果 partNum 为 0，则抛出错误。如果 partNum 为负数，则从字符串末尾开始倒数。如果 delimiter 为空字符串，则不分割 str。 |
| startswith(left,right) | 返回一个布尔值。如果 left 从 right 开始，则值为 True。如果输入表达式为空，则返回 NULL。否则返回 False。左或右都必须是字符串或二进制类型。 |
| substr(str,pos[,len]) | 返回从 pos 开始长度为 len 的 str 子串，或从 pos 开始长度为 len 的字节数组片段。 |
| substr(str FROM pos[ FOR len]]) | 返回从 pos 开始、长度为 len 的 str 子串，或从 pos 开始、长度为 len 的字节数组片段。 |
| substring(str,pos[,len]) | 返回从 pos 开始长度为 len 的 str 子串，或从 pos 开始长度为 len 的字节数组片段。 |
| substring(str FROM pos[ FOR len]]) | 返回从 pos 开始、长度为 len 的 str 子串，或从 pos 开始、长度为 len 的字节数组片段。 |
| substring_index(str, delim, count) | 返回分隔符 delim 出现次数 count 之前 str 的子串。如果 count 为正数，则返回最后定界符左边的所有字符串（从左边开始计算）。如果 count 为负数，则返回最终分隔符右边的所有内容（从右开始计算）。搜索 delim 时，函数 substring_index 执行大小写敏感匹配。 |
| to_binary(str[,fmt]) | 根据提供的 fmt 将输入的 str 转换为二进制值。fmt 可以是不区分大小写的 hex、utf-8、utf8或 base64字符串。如果省略 fmt，默认转换的二进制格式为 hex。如果至少一个输入参数为 NULL，函数将返回 NULL。 |
| to_char(numberExpr, formatExpr) | 根据 formatExpr 将 numberExpr 转换为字符串。如果转换失败，将抛出异常。格式可以由以下字符组成，不区分大小写：0 或 9： 指定介于 0 和 9 之间的预期数字。格式字符串中的 0 或 9 序列与输入值中的数字序列相匹配，生成的结果字符串长度与格式字符串中的相应序列相同。如果 0/9 序列的个位数多于十进制数值的匹配部分，且以 0 开头并位于小数点之前，则结果字符串左侧填充 0。否则，用空格填充。'.' 或 'D'： 指定小数点的位置（可选，只允许使用一次）。','或'G'： 指定分组（千位）分隔符（,）的位置。每个分组分隔符的左右两边必须有 0 或 9。'$': 指定 $ 货币符号的位置。该字符只能指定一次。'S' 或 'MI'： 指定 - 或 + 符号的位置（可选，只允许在格式字符串的开头或结尾指定一次）。注意，'S'打印正值的'+'，而'MI'打印空格。'PR'： 仅允许出现在格式字符串的末尾；如果输入值为负数，则指定用角括弧将结果字符串包起来('<1>')。 |
| to_number(expr, ftmt) | 根据字符串格式 ftmt 将字符串 expr 转换为数字。如果转换失败，将抛出异常。格式可以由以下字符组成，不区分大小写：0 或 9： 指定介于 0 和 9 之间的预期数字。格式字符串中的 0 或 9 序列与输入字符串中的数字序列相匹配。如果 0/9 序列以 0 开头并位于小数点之前，则只能匹配相同大小的数字序列。否则，如果序列以 9 开头或在小数点后，则可以匹配大小相同或更小的数字序列。'.' 或 'D'： 指定小数点的位置（可选，只允许一次）。',' 或 'G'： 指定分组（千位）分隔符（,）的位置。每个分组分隔符的左右两边必须有 0 或 9。expr 必须与数字大小相关的分组分隔符相匹配。'$': 指定货币符号 $ 的位置。该字符只能指定一次。'S' 或 'MI'： 指定 - 或 + 符号的位置（可选，只允许在格式字符串的开头或结尾指定一次）。注意 'S' 允许使用 '-'，而 'MI' 不允许。'PR'： 仅允许出现在格式字符串的末尾；指定'expr'表示带包角括号的负数（'<1>'）。 |
| to_varchar(numberExpr, formatExpr) | 根据 formatExpr 将 numberExpr 转换为字符串。如果转换失败，将抛出异常。格式可以由以下字符组成，不区分大小写：0 或 9： 指定介于 0 和 9 之间的预期数字。格式字符串中的 0 或 9 序列与输入值中的数字序列相匹配，生成的结果字符串长度与格式字符串中的相应序列相同。如果 0/9 序列的个位数多于十进制数值的匹配部分，且以 0 开头并位于小数点之前，则结果字符串左侧填充 0。否则，用空格填充。'.' 或 'D'： 指定小数点的位置（可选，只允许使用一次）。',' 或 'G'： 指定分组（千位）分隔符（,）的位置。每个分组分隔符的左右两边必须有 0 或 9。'$': 指定 $ 货币符号的位置。该字符只能指定一次。'S' 或 'MI'： 指定 - 或 + 符号的位置（可选，只允许在格式字符串的开头或结尾指定一次）。注意，'S'打印正值的'+'，而 'MI' 打印空格。'PR'： 仅允许出现在格式字符串的末尾；如果输入值为负数，则指定用角括弧将结果字符串包起来('<1>')。 |
| translate(input, from, to) | 通过将 from 字符串中的字符替换为 to 字符串中的相应字符来翻译 input字符串。 |
| trim(str) | 从 str 中移除前导和尾随空格字符。 |
| trim(BOTH FROM str) | 从 str中移除前导和尾随空格字符。 |
| trim(LEADING FROM str) | 从 str 中移除前导空格字符。 |
| trim(TRAILING FROM str) | 从 str 中移除尾随空格字符。 |
| trim(trimStr FROM str) | 从 str 中删除前后的 trimStr 字符。 |
| trim(BOTH trimStr FROM str) | 从 str 中删除开头和结尾的 trimStr 字符。 |
| trim(LEADING trimStr FROM str) | 从 str 中移除前导 trimStr 字符。 |
| trim(TRAILING trimStr FROM str) | 从 str 中移除尾随的 trimStr 字符。 |
| try_to_binary(str[, fmt]) | 这是 to_binary的一个特殊版本，它执行相同的操作，但返回 NULL 值，而不是在无法执行转换时引发错误。 |
| try_to_number(expr, fmt) | 将字符串 expr 转换为基于字符串格式 fmt 的数字。如果字符串 expr 与预期格式不匹配，则返回NULL。格式遵循与 to_number 函数相同的语义。 |
| ucase(str) | 返回 str 并将所有字符更改为大写。 |
| unbase64(str) | 将参数从base 64字符串 str 转换为二进制。 |
| upper(str) | 返回 str 并将所有字符更改为大写。 |
-- ascii
SELECT ascii('222');
+----------+
|ascii(222)|
+----------+
| 50|
+----------+
SELECT ascii(2);
+--------+
|ascii(2)|
+--------+
| 50|
+--------+
-- base64
SELECT base64('Spark SQL');
+-----------------+
|base64(Spark SQL)|
+-----------------+
| U3BhcmsgU1FM|
+-----------------+
SELECT base64(x'537061726b2053514c');
+-----------------------------+
|base64(X'537061726B2053514C')|
+-----------------------------+
| U3BhcmsgU1FM|
+-----------------------------+
-- bit_length
SELECT bit_length('Spark SQL');
+---------------------+
|bit_length(Spark SQL)|
+---------------------+
| 72|
+---------------------+
SELECT bit_length(x'537061726b2053514c');
+---------------------------------+
|bit_length(X'537061726B2053514C')|
+---------------------------------+
| 72|
+---------------------------------+
-- btrim
SELECT btrim(' SparkSQL ');
+----------------------+
|btrim( SparkSQL )|
+----------------------+
| SparkSQL|
+----------------------+
SELECT btrim(encode(' SparkSQL ', 'utf-8'));
+-------------------------------------+
|btrim(encode( SparkSQL , utf-8))|
+-------------------------------------+
| SparkSQL|
+-------------------------------------+
SELECT btrim('SSparkSQLS', 'SL');
+---------------------+
|btrim(SSparkSQLS, SL)|
+---------------------+
| parkSQ|
+---------------------+
SELECT btrim(encode('SSparkSQLS', 'utf-8'), encode('SL', 'utf-8'));
+---------------------------------------------------+
|btrim(encode(SSparkSQLS, utf-8), encode(SL, utf-8))|
+---------------------------------------------------+
| parkSQ|
+---------------------------------------------------+
-- char
SELECT char(65);
+--------+
|char(65)|
+--------+
| A|
+--------+
-- char_length
SELECT char_length('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT char_length(x'537061726b2053514c');
+----------------------------------+
|char_length(X'537061726B2053514C')|
+----------------------------------+
| 9|
+----------------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- character_length
SELECT character_length('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
SELECT character_length(x'537061726b2053514c');
+---------------------------------------+
|character_length(X'537061726B2053514C')|
+---------------------------------------+
| 9|
+---------------------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- chr
SELECT chr(65);
+-------+
|chr(65)|
+-------+
| A|
+-------+
-- concat_ws
SELECT concat_ws(' ', 'Spark', 'SQL');
+------------------------+
|concat_ws( , Spark, SQL)|
+------------------------+
| Spark SQL|
+------------------------+
SELECT concat_ws('s');
+------------+
|concat_ws(s)|
+------------+
| |
+------------+
SELECT concat_ws('/', 'foo', null, 'bar');
+----------------------------+
|concat_ws(/, foo, NULL, bar)|
+----------------------------+
| foo/bar|
+----------------------------+
SELECT concat_ws(null, 'Spark', 'SQL');
+---------------------------+
|concat_ws(NULL, Spark, SQL)|
+---------------------------+
| NULL|
+---------------------------+
-- contains
SELECT contains('Spark SQL', 'Spark');
+--------------------------+
|contains(Spark SQL, Spark)|
+--------------------------+
| true|
+--------------------------+
SELECT contains('Spark SQL', 'SPARK');
+--------------------------+
|contains(Spark SQL, SPARK)|
+--------------------------+
| false|
+--------------------------+
SELECT contains('Spark SQL', null);
+-------------------------+
|contains(Spark SQL, NULL)|
+-------------------------+
| NULL|
+-------------------------+
SELECT contains(x'537061726b2053514c', x'537061726b');
+----------------------------------------------+
|contains(X'537061726B2053514C', X'537061726B')|
+----------------------------------------------+
| true|
+----------------------------------------------+
-- decode
SELECT decode(encode('abc', 'utf-8'), 'utf-8');
+---------------------------------+
|decode(encode(abc, utf-8), utf-8)|
+---------------------------------+
| abc|
+---------------------------------+
SELECT decode(2, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic');
+----------------------------------------------------------------------------------+
|decode(2, 1, Southlake, 2, San Francisco, 3, New Jersey, 4, Seattle, Non domestic)|
+----------------------------------------------------------------------------------+
| San Francisco|
+----------------------------------------------------------------------------------+
SELECT decode(6, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic');
+----------------------------------------------------------------------------------+
|decode(6, 1, Southlake, 2, San Francisco, 3, New Jersey, 4, Seattle, Non domestic)|
+----------------------------------------------------------------------------------+
| Non domestic|
+----------------------------------------------------------------------------------+
SELECT decode(6, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle');
+--------------------------------------------------------------------+
|decode(6, 1, Southlake, 2, San Francisco, 3, New Jersey, 4, Seattle)|
+--------------------------------------------------------------------+
| NULL|
+--------------------------------------------------------------------+
SELECT decode(null, 6, 'Spark', NULL, 'SQL', 4, 'rocks');
+-------------------------------------------+
|decode(NULL, 6, Spark, NULL, SQL, 4, rocks)|
+-------------------------------------------+
| SQL|
+-------------------------------------------+
-- elt
SELECT elt(1, 'scala', 'java');
+-------------------+
|elt(1, scala, java)|
+-------------------+
| scala|
+-------------------+
SELECT elt(2, 'a', 1);
+------------+
|elt(2, a, 1)|
+------------+
| 1|
+------------+
-- encode
SELECT encode('abc', 'utf-8');
+------------------+
|encode(abc, utf-8)|
+------------------+
| [61 62 63]|
+------------------+
-- endswith
SELECT endswith('Spark SQL', 'SQL');
+------------------------+
|endswith(Spark SQL, SQL)|
+------------------------+
| true|
+------------------------+
SELECT endswith('Spark SQL', 'Spark');
+--------------------------+
|endswith(Spark SQL, Spark)|
+--------------------------+
| false|
+--------------------------+
SELECT endswith('Spark SQL', null);
+-------------------------+
|endswith(Spark SQL, NULL)|
+-------------------------+
| NULL|
+-------------------------+
SELECT endswith(x'537061726b2053514c', x'537061726b');
+----------------------------------------------+
|endswith(X'537061726B2053514C', X'537061726B')|
+----------------------------------------------+
| false|
+----------------------------------------------+
SELECT endswith(x'537061726b2053514c', x'53514c');
+------------------------------------------+
|endswith(X'537061726B2053514C', X'53514C')|
+------------------------------------------+
| true|
+------------------------------------------+
-- find_in_set
SELECT find_in_set('ab','abc,b,ab,c,def');
+-------------------------------+
|find_in_set(ab, abc,b,ab,c,def)|
+-------------------------------+
| 3|
+-------------------------------+
-- format_number
SELECT format_number(12332.123456, 4);
+------------------------------+
|format_number(12332.123456, 4)|
+------------------------------+
| 12,332.1235|
+------------------------------+
SELECT format_number(12332.123456, '##################.###');
+---------------------------------------------------+
|format_number(12332.123456, ##################.###)|
+---------------------------------------------------+
| 12332.123|
+---------------------------------------------------+
-- format_string
SELECT format_string("Hello World %d %s", 100, "days");
+-------------------------------------------+
|format_string(Hello World %d %s, 100, days)|
+-------------------------------------------+
| Hello World 100 days|
+-------------------------------------------+
-- initcap
SELECT initcap('sPark sql');
+------------------+
|initcap(sPark sql)|
+------------------+
| Spark Sql|
+------------------+
-- instr
SELECT instr('SparkSQL', 'SQL');
+--------------------+
|instr(SparkSQL, SQL)|
+--------------------+
| 6|
+--------------------+
-- lcase
SELECT lcase('SparkSql');
+---------------+
|lcase(SparkSql)|
+---------------+
| sparksql|
+---------------+
-- left
SELECT left('Spark SQL', 3);
+------------------+
|left(Spark SQL, 3)|
+------------------+
| Spa|
+------------------+
SELECT left(encode('Spark SQL', 'utf-8'), 3);
+---------------------------------+
|left(encode(Spark SQL, utf-8), 3)|
+---------------------------------+
| [53 70 61]|
+---------------------------------+
-- len
SELECT len('Spark SQL ');
+---------------+
|len(Spark SQL )|
+---------------+
| 10|
+---------------+
SELECT len(x'537061726b2053514c');
+--------------------------+
|len(X'537061726B2053514C')|
+--------------------------+
| 9|
+--------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- length
SELECT length('Spark SQL ');
+------------------+
|length(Spark SQL )|
+------------------+
| 10|
+------------------+
SELECT length(x'537061726b2053514c');
+-----------------------------+
|length(X'537061726B2053514C')|
+-----------------------------+
| 9|
+-----------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- levenshtein
SELECT levenshtein('kitten', 'sitting');
+----------------------------+
|levenshtein(kitten, sitting)|
+----------------------------+
| 3|
+----------------------------+
SELECT levenshtein('kitten', 'sitting', 2);
+-------------------------------+
|levenshtein(kitten, sitting, 2)|
+-------------------------------+
| -1|
+-------------------------------+
-- locate
SELECT locate('bar', 'foobarbar');
+-------------------------+
|locate(bar, foobarbar, 1)|
+-------------------------+
| 4|
+-------------------------+
SELECT locate('bar', 'foobarbar', 5);
+-------------------------+
|locate(bar, foobarbar, 5)|
+-------------------------+
| 7|
+-------------------------+
SELECT POSITION('bar' IN 'foobarbar');
+-------------------------+
|locate(bar, foobarbar, 1)|
+-------------------------+
| 4|
+-------------------------+
-- lower
SELECT lower('SparkSql');
+---------------+
|lower(SparkSql)|
+---------------+
| sparksql|
+---------------+
-- lpad
SELECT lpad('hi', 5, '??');
+---------------+
|lpad(hi, 5, ??)|
+---------------+
| ???hi|
+---------------+
SELECT lpad('hi', 1, '??');
+---------------+
|lpad(hi, 1, ??)|
+---------------+
| h|
+---------------+
SELECT lpad('hi', 5);
+--------------+
|lpad(hi, 5, )|
+--------------+
| hi|
+--------------+
SELECT hex(lpad(unhex('aabb'), 5));
+--------------------------------+
|hex(lpad(unhex(aabb), 5, X'00'))|
+--------------------------------+
| 000000AABB|
+--------------------------------+
SELECT hex(lpad(unhex('aabb'), 5, unhex('1122')));
+--------------------------------------+
|hex(lpad(unhex(aabb), 5, unhex(1122)))|
+--------------------------------------+
| 112211AABB|
+--------------------------------------+
-- ltrim
SELECT ltrim(' SparkSQL ');
+----------------------+
|ltrim( SparkSQL )|
+----------------------+
| SparkSQL |
+----------------------+
-- luhn_check
SELECT luhn_check('8112189876');
+----------------------+
|luhn_check(8112189876)|
+----------------------+
| true|
+----------------------+
SELECT luhn_check('79927398713');
+-----------------------+
|luhn_check(79927398713)|
+-----------------------+
| true|
+-----------------------+
SELECT luhn_check('79927398714');
+-----------------------+
|luhn_check(79927398714)|
+-----------------------+
| false|
+-----------------------+
-- mask
SELECT mask('abcd-EFGH-8765-4321');
+----------------------------------------+
|mask(abcd-EFGH-8765-4321, X, x, n, NULL)|
+----------------------------------------+
| xxxx-XXXX-nnnn-nnnn|
+----------------------------------------+
SELECT mask('abcd-EFGH-8765-4321', 'Q');
+----------------------------------------+
|mask(abcd-EFGH-8765-4321, Q, x, n, NULL)|
+----------------------------------------+
| xxxx-QQQQ-nnnn-nnnn|
+----------------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q');
+--------------------------------+
|mask(AbCD123-@$#, Q, q, n, NULL)|
+--------------------------------+
| QqQQnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#');
+--------------------------------+
|mask(AbCD123-@$#, X, x, n, NULL)|
+--------------------------------+
| XxXXnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q');
+--------------------------------+
|mask(AbCD123-@$#, Q, x, n, NULL)|
+--------------------------------+
| QxQQnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q');
+--------------------------------+
|mask(AbCD123-@$#, Q, q, n, NULL)|
+--------------------------------+
| QqQQnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q', 'd');
+--------------------------------+
|mask(AbCD123-@$#, Q, q, d, NULL)|
+--------------------------------+
| QqQQddd-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q', 'd', 'o');
+-----------------------------+
|mask(AbCD123-@$#, Q, q, d, o)|
+-----------------------------+
| QqQQdddoooo|
+-----------------------------+
SELECT mask('AbCD123-@$#', NULL, 'q', 'd', 'o');
+--------------------------------+
|mask(AbCD123-@$#, NULL, q, d, o)|
+--------------------------------+
| AqCDdddoooo|
+--------------------------------+
SELECT mask('AbCD123-@$#', NULL, NULL, 'd', 'o');
+-----------------------------------+
|mask(AbCD123-@$#, NULL, NULL, d, o)|
+-----------------------------------+
| AbCDdddoooo|
+-----------------------------------+
SELECT mask('AbCD123-@$#', NULL, NULL, NULL, 'o');
+--------------------------------------+
|mask(AbCD123-@$#, NULL, NULL, NULL, o)|
+--------------------------------------+
| AbCD123oooo|
+--------------------------------------+
SELECT mask(NULL, NULL, NULL, NULL, 'o');
+-------------------------------+
|mask(NULL, NULL, NULL, NULL, o)|
+-------------------------------+
| NULL|
+-------------------------------+
SELECT mask(NULL);
+-------------------------+
|mask(NULL, X, x, n, NULL)|
+-------------------------+
| NULL|
+-------------------------+
SELECT mask('AbCD123-@$#', NULL, NULL, NULL, NULL);
+-----------------------------------------+
|mask(AbCD123-@$#, NULL, NULL, NULL, NULL)|
+-----------------------------------------+
| AbCD123-@$#|
+-----------------------------------------+
-- octet_length
SELECT octet_length('Spark SQL');
+-----------------------+
|octet_length(Spark SQL)|
+-----------------------+
| 9|
+-----------------------+
SELECT octet_length(x'537061726b2053514c');
+-----------------------------------+
|octet_length(X'537061726B2053514C')|
+-----------------------------------+
| 9|
+-----------------------------------+
-- overlay
SELECT overlay('Spark SQL' PLACING '_' FROM 6);
+----------------------------+
|overlay(Spark SQL, _, 6, -1)|
+----------------------------+
| Spark_SQL|
+----------------------------+
SELECT overlay('Spark SQL' PLACING 'CORE' FROM 7);
+-------------------------------+
|overlay(Spark SQL, CORE, 7, -1)|
+-------------------------------+
| Spark CORE|
+-------------------------------+
SELECT overlay('Spark SQL' PLACING 'ANSI ' FROM 7 FOR 0);
+-------------------------------+
|overlay(Spark SQL, ANSI , 7, 0)|
+-------------------------------+
| Spark ANSI SQL|
+-------------------------------+
SELECT overlay('Spark SQL' PLACING 'tructured' FROM 2 FOR 4);
+-----------------------------------+
|overlay(Spark SQL, tructured, 2, 4)|
+-----------------------------------+
| Structured SQL|
+-----------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('_', 'utf-8') FROM 6);
+----------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(_, utf-8), 6, -1)|
+----------------------------------------------------------+
| [53 70 61 72 6B 5...|
+----------------------------------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('CORE', 'utf-8') FROM 7);
+-------------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(CORE, utf-8), 7, -1)|
+-------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+-------------------------------------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('ANSI ', 'utf-8') FROM 7 FOR 0);
+-------------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(ANSI , utf-8), 7, 0)|
+-------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+-------------------------------------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('tructured', 'utf-8') FROM 2 FOR 4);
+-----------------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(tructured, utf-8), 2, 4)|
+-----------------------------------------------------------------+
| [53 74 72 75 63 7...|
+-----------------------------------------------------------------+
-- position
SELECT position('bar', 'foobarbar');
+---------------------------+
|position(bar, foobarbar, 1)|
+---------------------------+
| 4|
+---------------------------+
SELECT position('bar', 'foobarbar', 5);
+---------------------------+
|position(bar, foobarbar, 5)|
+---------------------------+
| 7|
+---------------------------+
SELECT POSITION('bar' IN 'foobarbar');
+-------------------------+
|locate(bar, foobarbar, 1)|
+-------------------------+
| 4|
+-------------------------+
-- printf
SELECT printf("Hello World %d %s", 100, "days");
+------------------------------------+
|printf(Hello World %d %s, 100, days)|
+------------------------------------+
| Hello World 100 days|
+------------------------------------+
-- regexp_count
SELECT regexp_count('Steven Jones and Stephen Smith are the best players', 'Ste(v|ph)en');
+------------------------------------------------------------------------------+
|regexp_count(Steven Jones and Stephen Smith are the best players, Ste(v|ph)en)|
+------------------------------------------------------------------------------+
| 2|
+------------------------------------------------------------------------------+
SELECT regexp_count('abcdefghijklmnopqrstuvwxyz', '[a-z]{3}');
+--------------------------------------------------+
|regexp_count(abcdefghijklmnopqrstuvwxyz, [a-z]{3})|
+--------------------------------------------------+
| 8|
+--------------------------------------------------+
-- regexp_extract
SELECT regexp_extract('100-200', '(\\d+)-(\\d+)', 1);
+---------------------------------------+
|regexp_extract(100-200, (\d+)-(\d+), 1)|
+---------------------------------------+
| 100|
+---------------------------------------+
-- regexp_extract_all
SELECT regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)', 1);
+----------------------------------------------------+
|regexp_extract_all(100-200, 300-400, (\d+)-(\d+), 1)|
+----------------------------------------------------+
| [100, 300]|
+----------------------------------------------------+
-- regexp_instr
SELECT regexp_instr('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|
+--------------------------------------+
-- regexp_substr
SELECT regexp_substr('Steven Jones and Stephen Smith are the best players', 'Ste(v|ph)en');
+-------------------------------------------------------------------------------+
|regexp_substr(Steven Jones and Stephen Smith are the best players, Ste(v|ph)en)|
+-------------------------------------------------------------------------------+
| Steven|
+-------------------------------------------------------------------------------+
SELECT regexp_substr('Steven Jones and Stephen Smith are the best players', 'Jeck');
+------------------------------------------------------------------------+
|regexp_substr(Steven Jones and Stephen Smith are the best players, Jeck)|
+------------------------------------------------------------------------+
| NULL|
+------------------------------------------------------------------------+
-- repeat
SELECT repeat('123', 2);
+--------------+
|repeat(123, 2)|
+--------------+
| 123123|
+--------------+
-- replace
SELECT replace('ABCabc', 'abc', 'DEF');
+-------------------------+
|replace(ABCabc, abc, DEF)|
+-------------------------+
| ABCDEF|
+-------------------------+
-- right
SELECT right('Spark SQL', 3);
+-------------------+
|right(Spark SQL, 3)|
+-------------------+
| SQL|
+-------------------+
-- rpad
SELECT rpad('hi', 5, '??');
+---------------+
|rpad(hi, 5, ??)|
+---------------+
| hi???|
+---------------+
SELECT rpad('hi', 1, '??');
+---------------+
|rpad(hi, 1, ??)|
+---------------+
| h|
+---------------+
SELECT rpad('hi', 5);
+--------------+
|rpad(hi, 5, )|
+--------------+
| hi |
+--------------+
SELECT hex(rpad(unhex('aabb'), 5));
+--------------------------------+
|hex(rpad(unhex(aabb), 5, X'00'))|
+--------------------------------+
| AABB000000|
+--------------------------------+
SELECT hex(rpad(unhex('aabb'), 5, unhex('1122')));
+--------------------------------------+
|hex(rpad(unhex(aabb), 5, unhex(1122)))|
+--------------------------------------+
| AABB112211|
+--------------------------------------+
-- rtrim
SELECT rtrim(' SparkSQL ');
+----------------------+
|rtrim( SparkSQL )|
+----------------------+
| SparkSQL|
+----------------------+
-- sentences
SELECT sentences('Hi there! Good morning.');
+--------------------------------------+
|sentences(Hi there! Good morning., , )|
+--------------------------------------+
| [[Hi, there], [Go...|
+--------------------------------------+
-- soundex
SELECT soundex('Miller');
+---------------+
|soundex(Miller)|
+---------------+
| M460|
+---------------+
-- space
SELECT concat(space(2), '1');
+-------------------+
|concat(space(2), 1)|
+-------------------+
| 1|
+-------------------+
-- split
SELECT split('oneAtwoBthreeC', '[ABC]');
+--------------------------------+
|split(oneAtwoBthreeC, [ABC], -1)|
+--------------------------------+
| [one, two, three, ]|
+--------------------------------+
SELECT split('oneAtwoBthreeC', '[ABC]', -1);
+--------------------------------+
|split(oneAtwoBthreeC, [ABC], -1)|
+--------------------------------+
| [one, two, three, ]|
+--------------------------------+
SELECT split('oneAtwoBthreeC', '[ABC]', 2);
+-------------------------------+
|split(oneAtwoBthreeC, [ABC], 2)|
+-------------------------------+
| [one, twoBthreeC]|
+-------------------------------+
-- split_part
SELECT split_part('11.12.13', '.', 3);
+--------------------------+
|split_part(11.12.13, ., 3)|
+--------------------------+
| 13|
+--------------------------+
-- startswith
SELECT startswith('Spark SQL', 'Spark');
+----------------------------+
|startswith(Spark SQL, Spark)|
+----------------------------+
| true|
+----------------------------+
SELECT startswith('Spark SQL', 'SQL');
+--------------------------+
|startswith(Spark SQL, SQL)|
+--------------------------+
| false|
+--------------------------+
SELECT startswith('Spark SQL', null);
+---------------------------+
|startswith(Spark SQL, NULL)|
+---------------------------+
| NULL|
+---------------------------+
SELECT startswith(x'537061726b2053514c', x'537061726b');
+------------------------------------------------+
|startswith(X'537061726B2053514C', X'537061726B')|
+------------------------------------------------+
| true|
+------------------------------------------------+
SELECT startswith(x'537061726b2053514c', x'53514c');
+--------------------------------------------+
|startswith(X'537061726B2053514C', X'53514C')|
+--------------------------------------------+
| false|
+--------------------------------------------+
-- substr
SELECT substr('Spark SQL', 5);
+--------------------------------+
|substr(Spark SQL, 5, 2147483647)|
+--------------------------------+
| k SQL|
+--------------------------------+
SELECT substr('Spark SQL', -3);
+---------------------------------+
|substr(Spark SQL, -3, 2147483647)|
+---------------------------------+
| SQL|
+---------------------------------+
SELECT substr('Spark SQL', 5, 1);
+-----------------------+
|substr(Spark SQL, 5, 1)|
+-----------------------+
| k|
+-----------------------+
SELECT substr('Spark SQL' FROM 5);
+-----------------------------------+
|substring(Spark SQL, 5, 2147483647)|
+-----------------------------------+
| k SQL|
+-----------------------------------+
SELECT substr('Spark SQL' FROM -3);
+------------------------------------+
|substring(Spark SQL, -3, 2147483647)|
+------------------------------------+
| SQL|
+------------------------------------+
SELECT substr('Spark SQL' FROM 5 FOR 1);
+--------------------------+
|substring(Spark SQL, 5, 1)|
+--------------------------+
| k|
+--------------------------+
SELECT substr(encode('Spark SQL', 'utf-8'), 5);
+-----------------------------------------------+
|substr(encode(Spark SQL, utf-8), 5, 2147483647)|
+-----------------------------------------------+
| [6B 20 53 51 4C]|
+-----------------------------------------------+
-- substring
SELECT substring('Spark SQL', 5);
+-----------------------------------+
|substring(Spark SQL, 5, 2147483647)|
+-----------------------------------+
| k SQL|
+-----------------------------------+
SELECT substring('Spark SQL', -3);
+------------------------------------+
|substring(Spark SQL, -3, 2147483647)|
+------------------------------------+
| SQL|
+------------------------------------+
SELECT substring('Spark SQL', 5, 1);
+--------------------------+
|substring(Spark SQL, 5, 1)|
+--------------------------+
| k|
+--------------------------+
SELECT substring('Spark SQL' FROM 5);
+-----------------------------------+
|substring(Spark SQL, 5, 2147483647)|
+-----------------------------------+
| k SQL|
+-----------------------------------+
SELECT substring('Spark SQL' FROM -3);
+------------------------------------+
|substring(Spark SQL, -3, 2147483647)|
+------------------------------------+
| SQL|
+------------------------------------+
SELECT substring('Spark SQL' FROM 5 FOR 1);
+--------------------------+
|substring(Spark SQL, 5, 1)|
+--------------------------+
| k|
+--------------------------+
SELECT substring(encode('Spark SQL', 'utf-8'), 5);
+--------------------------------------------------+
|substring(encode(Spark SQL, utf-8), 5, 2147483647)|
+--------------------------------------------------+
| [6B 20 53 51 4C]|
+--------------------------------------------------+
-- substring_index
SELECT substring_index('www.suan-chang.com', '.', 2);
+-----------------------------------------+
|substring_index(www.suan-chang.com, ., 2)|
+-----------------------------------------+
| www.suan-chang|
+-----------------------------------------+
-- to_binary
SELECT to_binary('abc', 'utf-8');
+---------------------+
|to_binary(abc, utf-8)|
+---------------------+
| [61 62 63]|
+---------------------+
-- to_char
SELECT to_char(454, '999');
+-----------------+
|to_char(454, 999)|
+-----------------+
| 454|
+-----------------+
SELECT to_char(454.00, '000D00');
+-----------------------+
|to_char(454.00, 000D00)|
+-----------------------+
| 454.00|
+-----------------------+
SELECT to_char(12454, '99G999');
+----------------------+
|to_char(12454, 99G999)|
+----------------------+
| 12,454|
+----------------------+
SELECT to_char(78.12, '$99.99');
+----------------------+
|to_char(78.12, $99.99)|
+----------------------+
| $78.12|
+----------------------+
SELECT to_char(-12454.8, '99G999D9S');
+----------------------------+
|to_char(-12454.8, 99G999D9S)|
+----------------------------+
| 12,454.8-|
+----------------------------+
-- to_number
SELECT to_number('454', '999');
+-------------------+
|to_number(454, 999)|
+-------------------+
| 454|
+-------------------+
SELECT to_number('454.00', '000.00');
+-------------------------+
|to_number(454.00, 000.00)|
+-------------------------+
| 454.00|
+-------------------------+
SELECT to_number('12,454', '99,999');
+-------------------------+
|to_number(12,454, 99,999)|
+-------------------------+
| 12454|
+-------------------------+
SELECT to_number('$78.12', '$99.99');
+-------------------------+
|to_number($78.12, $99.99)|
+-------------------------+
| 78.12|
+-------------------------+
SELECT to_number('12,454.8-', '99,999.9S');
+-------------------------------+
|to_number(12,454.8-, 99,999.9S)|
+-------------------------------+
| -12454.8|
+-------------------------------+
-- to_varchar
SELECT to_varchar(454, '999');
+-----------------+
|to_char(454, 999)|
+-----------------+
| 454|
+-----------------+
SELECT to_varchar(454.00, '000D00');
+-----------------------+
|to_char(454.00, 000D00)|
+-----------------------+
| 454.00|
+-----------------------+
SELECT to_varchar(12454, '99G999');
+----------------------+
|to_char(12454, 99G999)|
+----------------------+
| 12,454|
+----------------------+
SELECT to_varchar(78.12, '$99.99');
+----------------------+
|to_char(78.12, $99.99)|
+----------------------+
| $78.12|
+----------------------+
SELECT to_varchar(-12454.8, '99G999D9S');
+----------------------------+
|to_char(-12454.8, 99G999D9S)|
+----------------------------+
| 12,454.8-|
+----------------------------+
-- translate
SELECT translate('AaBbCc', 'abc', '123');
+---------------------------+
|translate(AaBbCc, abc, 123)|
+---------------------------+
| A1B2C3|
+---------------------------+
-- trim
SELECT trim(' SparkSQL ');
+---------------------+
|trim( SparkSQL )|
+---------------------+
| SparkSQL|
+---------------------+
SELECT trim(BOTH FROM ' SparkSQL ');
+---------------------+
|trim( SparkSQL )|
+---------------------+
| SparkSQL|
+---------------------+
SELECT trim(LEADING FROM ' SparkSQL ');
+----------------------+
|ltrim( SparkSQL )|
+----------------------+
| SparkSQL |
+----------------------+
SELECT trim(TRAILING FROM ' SparkSQL ');
+----------------------+
|rtrim( SparkSQL )|
+----------------------+
| SparkSQL|
+----------------------+
SELECT trim('SL' FROM 'SSparkSQLS');
+-----------------------------+
|TRIM(BOTH SL FROM SSparkSQLS)|
+-----------------------------+
| parkSQ|
+-----------------------------+
SELECT trim(BOTH 'SL' FROM 'SSparkSQLS');
+-----------------------------+
|TRIM(BOTH SL FROM SSparkSQLS)|
+-----------------------------+
| parkSQ|
+-----------------------------+
SELECT trim(LEADING 'SL' FROM 'SSparkSQLS');
+--------------------------------+
|TRIM(LEADING SL FROM SSparkSQLS)|
+--------------------------------+
| parkSQLS|
+--------------------------------+
SELECT trim(TRAILING 'SL' FROM 'SSparkSQLS');
+---------------------------------+
|TRIM(TRAILING SL FROM SSparkSQLS)|
+---------------------------------+
| SSparkSQ|
+---------------------------------+
-- try_to_binary
SELECT try_to_binary('abc', 'utf-8');
+-------------------------+
|try_to_binary(abc, utf-8)|
+-------------------------+
| [61 62 63]|
+-------------------------+
select try_to_binary('a!', 'base64');
+-------------------------+
|try_to_binary(a!, base64)|
+-------------------------+
| NULL|
+-------------------------+
select try_to_binary('abc', 'invalidFormat');
+---------------------------------+
|try_to_binary(abc, invalidFormat)|
+---------------------------------+
| NULL|
+---------------------------------+
-- try_to_number
SELECT try_to_number('454', '999');
+-----------------------+
|try_to_number(454, 999)|
+-----------------------+
| 454|
+-----------------------+
SELECT try_to_number('454.00', '000.00');
+-----------------------------+
|try_to_number(454.00, 000.00)|
+-----------------------------+
| 454.00|
+-----------------------------+
SELECT try_to_number('12,454', '99,999');
+-----------------------------+
|try_to_number(12,454, 99,999)|
+-----------------------------+
| 12454|
+-----------------------------+
SELECT try_to_number('$78.12', '$99.99');
+-----------------------------+
|try_to_number($78.12, $99.99)|
+-----------------------------+
| 78.12|
+-----------------------------+
SELECT try_to_number('12,454.8-', '99,999.9S');
+-----------------------------------+
|try_to_number(12,454.8-, 99,999.9S)|
+-----------------------------------+
| -12454.8|
+-----------------------------------+
-- ucase
SELECT ucase('SparkSql');
+---------------+
|ucase(SparkSql)|
+---------------+
| SPARKSQL|
+---------------+
-- unbase64
SELECT unbase64('U3BhcmsgU1FM');
+----------------------+
|unbase64(U3BhcmsgU1FM)|
+----------------------+
| [53 70 61 72 6B 2...|
+----------------------+
-- upper
SELECT upper('SparkSql');
+---------------+
|upper(SparkSql)|
+---------------+
| SPARKSQL|
+---------------+
| 函数 | 描述 |
|---|
| coalesce(expr1, expr2, ...) | 如果存在，则返回第一个非空参数。否则返回空值。 |
| if(expr1, expr2, expr3) | 如果 expr1 计算结果为真，则返回 expr2；否则返回 expr3。 |
| ifnull(expr1, expr2) | 如果 expr1 为空，则返回 expr2，否则返回 expr1。 |
| nanvl(expr1, expr2) | 如果 expr1 不是 NaN，则返回 expr1，否则返回 expr2。 |
| nullif(expr1, expr2) | 如果 expr1 等于 expr2，则返回空值，否则返回 expr1。 |
| nvl(expr1, expr2) | 如果 expr1 为空，则返回 expr2，否则返回 expr1。 |
| nvl2(expr1, expr2, expr3) | 如果 expr1 不为空，则返回 expr2，否则返回 expr3。 |
| CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4]* [ELSE expr5] END | 当 expr1 = true 时，返回 expr2；否则当 expr3 = true 时，返回 expr4；否则返回 expr5。 |
-- coalesce
SELECT coalesce(NULL, 1, NULL);
+-----------------------+
|coalesce(NULL, 1, NULL)|
+-----------------------+
| 1|
+-----------------------+
-- if
SELECT if(1 < 2, 'a', 'b');
+-------------------+
|(IF((1 < 2), a, b))|
+-------------------+
| a|
+-------------------+
-- ifnull
SELECT ifnull(NULL, array('2'));
+----------------------+
|ifnull(NULL, array(2))|
+----------------------+
| [2]|
+----------------------+
-- nanvl
SELECT nanvl(cast('NaN' as double), 123);
+-------------------------------+
|nanvl(CAST(NaN AS DOUBLE), 123)|
+-------------------------------+
| 123.0|
+-------------------------------+
-- nullif
SELECT nullif(2, 2);
+------------+
|nullif(2, 2)|
+------------+
| NULL|
+------------+
-- nvl
SELECT nvl(NULL, array('2'));
+-------------------+
|nvl(NULL, array(2))|
+-------------------+
| [2]|
+-------------------+
-- nvl2
SELECT nvl2(NULL, 2, 1);
+----------------+
|nvl2(NULL, 2, 1)|
+----------------+
| 1|
+----------------+
-- when
SELECT CASE WHEN 1 > 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
+-----------------------------------------------------------+
|CASE WHEN (1 > 0) THEN 1 WHEN (2 > 0) THEN 2.0 ELSE 1.2 END|
+-----------------------------------------------------------+
| 1.0|
+-----------------------------------------------------------+
SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
+-----------------------------------------------------------+
|CASE WHEN (1 < 0) THEN 1 WHEN (2 > 0) THEN 2.0 ELSE 1.2 END|
+-----------------------------------------------------------+
| 2.0|
+-----------------------------------------------------------+
SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 < 0 THEN 2.0 END;
+--------------------------------------------------+
|CASE WHEN (1 < 0) THEN 1 WHEN (2 < 0) THEN 2.0 END|
+--------------------------------------------------+
| NULL|
+--------------------------------------------------+
| 函数 | 描述 |
|---|
| expr1 & expr2 | 返回 expr1 和 expr2 按位与的结果。 |
| expr1 ^ expr2 | 返回 expr1 和 expr2 按位异或的结果。 |
| bit_count(expr) | 返回参数 expr 当作无符号 64 位整数中二进制为 1 的位数，如果参数为 NULL，则返回 NULL。 |
| bit_get(expr, pos) | 返回指定位置的位（0 或 1）的值。位置从右到左编号，从零开始。位置参数不能为负数。 |
| getbit(expr, pos) | 返回指定位置的位（0 或 1）的值。位置从右到左编号，从零开始。位置参数不能为负数。 |
| shiftright(base, expr) | 按位（有符号）右移。 |
| shiftrightunsigned(base, expr) | 按位无符号右移。 |
| expr1 | expr2 | 返回 expr1 和 expr2 按位或的结果。 |
| ~ expr | 返回 expr 按位非的结果。 |
-- &
SELECT 3 & 5;
+-------+
|(3 & 5)|
+-------+
| 1|
+-------+
-- ^
SELECT 3 ^ 5;
+-------+
|(3 ^ 5)|
+-------+
| 6|
+-------+
-- bit_count
SELECT bit_count(0);
+------------+
|bit_count(0)|
+------------+
| 0|
+------------+
-- bit_get
SELECT bit_get(11, 0);
+--------------+
|bit_get(11, 0)|
+--------------+
| 1|
+--------------+
SELECT bit_get(11, 2);
+--------------+
|bit_get(11, 2)|
+--------------+
| 0|
+--------------+
-- getbit
SELECT getbit(11, 0);
+-------------+
|getbit(11, 0)|
+-------------+
| 1|
+-------------+
SELECT getbit(11, 2);
+-------------+
|getbit(11, 2)|
+-------------+
| 0|
+-------------+
-- shiftright
SELECT shiftright(4, 1);
+----------------+
|shiftright(4, 1)|
+----------------+
| 2|
+----------------+
-- shiftrightunsigned
SELECT shiftrightunsigned(4, 1);
+------------------------+
|shiftrightunsigned(4, 1)|
+------------------------+
| 2|
+------------------------+
-- |
SELECT 3 | 5;
+-------+
|(3 | 5)|
+-------+
| 7|
+-------+
-- ~
SELECT ~ 0;
+---+
| ~0|
+---+
| -1|
+---+
| 函数 | 描述 |
|---|
| bigint(expr) | 将值 expr 转换为目标数据类型 bigint。 |
| binary(expr) | 将值 expr 转换为目标数据类型 binary。 |
| boolean(expr) | 将值 expr 转换为目标数据类型 boolean。 |
| cast(expr AS type) | 将值 expr 转换为目标数据类型 type。 |
| date(expr) | 将值 expr 转换为目标数据类型 date。 |
| decimal(expr) | 将值 expr 转换为目标数据类型 decimal。 |
| double(expr) | 将值 expr 转换为目标数据类型 double。 |
| float(expr) | 将值 expr 转换为目标数据类型 float。 |
| int(expr) | 将值 expr 转换为目标数据类型 int。 |
| smallint(expr) | 将值 expr 转换为目标数据类型 smallint。 |
| string(expr) | 将值 expr 转换为目标数据类型 string。 |
| timestamp(expr) | 将值 expr 转换为目标数据类型 timestamp。 |
| tinyint(expr) | 将值 expr 转换为目标数据类型 tinyint。 |
-- cast
SELECT cast('10' as int);
+---------------+
|CAST(10 AS INT)|
+---------------+
| 10|
+---------------+
| 函数 | 描述 |
|---|
| ! expr | 逻辑非。 |
| expr1 < expr2 | 如果 expr1 小于 expr2，则返回 true。 |
| expr1 <= expr2 | 如果 expr1 小于或等于 expr2，则返回 true。 |
| expr1 <=> expr2 | 对于非空操作数，返回与 EQUAL(=) 运算符相同的结果，但如果两者都为空，则返回 true，如果其中一个为空，则返回 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 | 逻辑与。 |
| str ilike pattern[ ESCAPE escape] | 如果 str 匹配 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 匹配 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|
+----------------+
-- ilike
SELECT ilike('Spark', '_Park');
+-------------------+
|ilike(Spark, _Park)|
+-------------------+
| true|
+-------------------+
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT '%SystemDrive%\Users\John' ilike '\%SystemDrive\%\\users%';
+--------------------------------------------------------+
|ilike(%SystemDrive%\Users\John, \%SystemDrive\%\\users%)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT '%SystemDrive%\\USERS\\John' ilike '\%SystemDrive\%\\\\Users%';
+--------------------------------------------------------+
|ilike(%SystemDrive%\USERS\John, \%SystemDrive\%\\Users%)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
SELECT '%SystemDrive%/Users/John' ilike '/%SYSTEMDrive/%//Users%' ESCAPE '/';
+--------------------------------------------------------+
|ilike(%SystemDrive%/Users/John, /%SYSTEMDrive/%//Users%)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
-- in
SELECT 1 in(1, 2, 3);
+----------------+
|(1 IN (1, 2, 3))|
+----------------+
| true|
+----------------+
SELECT 1 in(2, 3, 4);
+----------------+
|(1 IN (2, 3, 4))|
+----------------+
| false|
+----------------+
SELECT named_struct(a = 1, b = 2) in (named_struct(a = 1, b = 1), named_struct(a = 1, b = 3));
+----------------------------------------------+
|(struct(1, 2) IN (struct(1, 1), struct(1, 3)))|
+----------------------------------------------+
| false|
+----------------------------------------------+
SELECT named_struct(a = 1, b = 2) in (named_struct(a = 1, b = 2), named_struct(a = 1, b = 3));
+----------------------------------------------+
|(struct(1, 2) IN (struct(1, 2), struct(1, 3)))|
+----------------------------------------------+
| true|
+----------------------------------------------+
-- isnan
SELECT isnan(cast('NaN' as double));
+--------------------------+
|isnan(CAST(NaN AS DOUBLE))|
+--------------------------+
| true|
+--------------------------+
-- isnotnull
SELECT isnotnull(1);
+---------------+
|(1 IS NOT NULL)|
+---------------+
| true|
+---------------+
-- isnull
SELECT isnull(1);
+-----------+
|(1 IS NULL)|
+-----------+
| false|
+-----------+
-- like
SELECT like('Spark', '_park');
+----------------+
|Spark LIKE _park|
+----------------+
| true|
+----------------+
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT '%SystemDrive%\Users\John' like '\%SystemDrive\%\\Users%';
+-----------------------------------------------------+
|%SystemDrive%\Users\John LIKE \%SystemDrive\%\\Users%|
+-----------------------------------------------------+
| true|
+-----------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT '%SystemDrive%\\Users\\John' like '\%SystemDrive\%\\\\Users%';
+-----------------------------------------------------+
|%SystemDrive%\Users\John LIKE \%SystemDrive\%\\Users%|
+-----------------------------------------------------+
| true|
+-----------------------------------------------------+
SELECT '%SystemDrive%/Users/John' like '/%SystemDrive/%//Users%' ESCAPE '/';
+-----------------------------------------------------+
|%SystemDrive%/Users/John LIKE /%SystemDrive/%//Users%|
+-----------------------------------------------------+
| true|
+-----------------------------------------------------+
-- not
SELECT not true;
+----------+
|(NOT true)|
+----------+
| false|
+----------+
SELECT not false;
+-----------+
|(NOT false)|
+-----------+
| true|
+-----------+
SELECT not NULL;
+----------+
|(NOT NULL)|
+----------+
| NULL|
+----------+
-- or
SELECT true or false;
+---------------+
|(true OR false)|
+---------------+
| true|
+---------------+
SELECT false or false;
+----------------+
|(false OR false)|
+----------------+
| false|
+----------------+
SELECT true or NULL;
+--------------+
|(true OR NULL)|
+--------------+
| true|
+--------------+
SELECT false or NULL;
+---------------+
|(false OR NULL)|
+---------------+
| NULL|
+---------------+
-- regexp
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT regexp('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
+--------------------------------------------------------+
|REGEXP(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT regexp('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
+--------------------------------------------------------+
|REGEXP(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
-- regexp_like
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT regexp_like('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
+-------------------------------------------------------------+
|REGEXP_LIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------------+
| true|
+-------------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT regexp_like('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
+-------------------------------------------------------------+
|REGEXP_LIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------------+
| true|
+-------------------------------------------------------------+
-- rlike
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT rlike('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
+-------------------------------------------------------+
|RLIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------+
| true|
+-------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT rlike('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
+-------------------------------------------------------+
|RLIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------+
| true|
+-------------------------------------------------------+
| 函数 | 描述 |
|---|
| from_csv(csvStr, schema[, options]) | 返回具有给定 csvStr 和 schema 的结构值。 |
| schema_of_csv(csv[, options]) | 返回 CSV 字符串的 DDL 格式的架构。 |
| to_csv(expr[, options]) | 返回具有给定结构值的 CSV 字符串 |
-- from_csv
SELECT from_csv('1, 0.8', 'a INT, b DOUBLE');
+----------------+
|from_csv(1, 0.8)|
+----------------+
| {1, 0.8}|
+----------------+
SELECT from_csv('26/08/2015', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
+--------------------+
|from_csv(26/08/2015)|
+--------------------+
|{2015-08-26 00:00...|
+--------------------+
-- schema_of_csv
SELECT schema_of_csv('1,abc');
+--------------------+
|schema_of_csv(1,abc)|
+--------------------+
|STRUCT<_c0: INT, ...|
+--------------------+
-- to_csv
SELECT to_csv(named_struct(a = 1, b = 2));
+--------------------------+
|to_csv(named_struct(1, 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(to_timestamp(2015-08-26, yyyy-MM-dd)))|
+----------------------------------------------------------+
| 26/08/2015|
+----------------------------------------------------------+
| 函数 | 描述 |
|---|
| 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')。可选初始化向量 (IV) 仅支持 CBC 和 GCM 模式。对于 CBC，这些向量必须是 16 个字节，对于 GCM，这些向量必须是 12 个字节。如果未提供，将生成一个随机向量并将其添加到输出的前面。可选的附加认证数据 (AAD) 仅支持 GCM。如果为加密提供，则必须提供相同的 AAD 值才能解密。默认模式为 GCM。 |
| assert_true(expr) | 如果 expr 不为真，则抛出异常。 |
| bitmap_bit_position(child) | 返回给定输入子表达式的位位置。 |
| bitmap_bucket_number(child) | 返回给定输入子表达式的存储桶编号。 |
| bitmap_count(child) | 返回子位图中设置位数。 |
| current_catalog() | 返回当前目录。 |
| current_database() | 返回当前数据库。 |
| current_schema() | 返回当前数据库。 |
| current_user() | 当前执行上下文的用户名。 |
| equal_null(expr1, expr2) | 对于非空操作数，返回与 EQUAL(=) 运算符相同的结果，但如果两个操作数都为空，则返回 true，如果其中一个为空，则返回 false。 |
| hll_sketch_estimate(expr) | 根据 Datasketches HllSketch 的二进制表示形式，返回估计的唯一值数量。 |
| hll_union(first, second, allowDifferentLgConfigK) | 使用 Datasketches Union 对象合并 Datasketches HllSketch 对象的两个二进制表示形式。将 allowDifferentLgConfigK 设置为 true，以允许具有不同 lgConfigK 值的草图合并（默认为 false）。 |
| input_file_block_length() | 返回正在读取的块的长度，如果不可用则返回 -1。 |
| input_file_block_start() | 返回正在读取的块的起始偏移量，如果不可用则返回 -1。 |
| input_file_name() | 返回正在读取的文件的名称，如果不可用则返回空字符串。 |
| java_method(class, method[, arg1[, arg2 ..]]) | 使用反射调用方法。 |
| monotonically_increasing_id() | 返回单调递增的 64 位整数。生成的 ID 保证单调递增且唯一，但不连续。当前实现将分区 ID 放在高 31 位，低 33 位表示每个分区内的记录号。假设数据框的分区数少于 10 亿，每个分区的记录数少于 80 亿。该函数是非确定性的，因为其结果取决于分区 ID。 |
| reflect(class, method[, arg1[, arg2 ..]]) | 使用反射调用方法。 |
| spark_partition_id() | 返回当前分区 ID。 |
| try_aes_decrypt(expr, key[, mode[, padding[, aad]]]) | 这是 aes_decrypt 的特殊版本，执行相同的操作，但如果无法执行解密，则返回 NULL 值而不是引发错误。 |
| typeof(expr) | 返回输入数据类型的 DDL 格式的类型字符串。 |
| user() | 当前执行上下文的用户名。 |
| uuid() | 返回通用唯一标识符 (UUID) 字符串。该值以规范的 UUID 36 个字符的字符串形式返回。 |
| 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, , ))|
+-----------------------------------------------------------+
| 9614413752042C234...|
+-----------------------------------------------------------+
SELECT hex(aes_encrypt('Spark SQL', '0000111122223333', 'GCM'));
+---------------------------------------------------------------+
|hex(aes_encrypt(Spark SQL, 0000111122223333, GCM, DEFAULT, , ))|
+---------------------------------------------------------------+
| AA9594C51312A7113...|
+---------------------------------------------------------------+
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, , ))|
+---------------------------------------------------------------------+
| xeNc/BK93l2gtRIEH...|
+---------------------------------------------------------------------+
SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'CBC', 'DEFAULT', unhex('00000000000000000000000000000000')));
+---------------------------------------------------------------------------------------------------------------------+
|base64(aes_encrypt(Spark, abcdefghijklmnop12345678ABCDEFGH, CBC, DEFAULT, unhex(00000000000000000000000000000000), ))|
+---------------------------------------------------------------------------------------------------------------------+
| AAAAAAAAAAAAAAAAA...|
+---------------------------------------------------------------------------------------------------------------------+
SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'GCM', 'DEFAULT', unhex('000000000000000000000000'), 'This is an AAD mixed into the input'));
+------------------------------------------------------------------------------------------------------------------------------------------------+
|base64(aes_encrypt(Spark, abcdefghijklmnop12345678ABCDEFGH, GCM, DEFAULT, unhex(000000000000000000000000), This is an AAD mixed into the input))|
+------------------------------------------------------------------------------------------------------------------------------------------------+
| AAAAAAAAAAAAAAAAQ...|
+------------------------------------------------------------------------------------------------------------------------------------------------+
-- assert_true
SELECT assert_true(0 < 1);
+--------------------------------------------+
|assert_true((0 < 1), '(0 < 1)' is not true!)|
+--------------------------------------------+
| NULL|
+--------------------------------------------+
-- bitmap_bit_position
SELECT bitmap_bit_position(1);
+----------------------+
|bitmap_bit_position(1)|
+----------------------+
| 0|
+----------------------+
SELECT bitmap_bit_position(123);
+------------------------+
|bitmap_bit_position(123)|
+------------------------+
| 122|
+------------------------+
-- bitmap_bucket_number
SELECT bitmap_bucket_number(123);
+-------------------------+
|bitmap_bucket_number(123)|
+-------------------------+
| 1|
+-------------------------+
SELECT bitmap_bucket_number(0);
+-----------------------+
|bitmap_bucket_number(0)|
+-----------------------+
| 0|
+-----------------------+
-- bitmap_count
SELECT bitmap_count(X '1010');
+---------------------+
|bitmap_count(X'1010')|
+---------------------+
| 2|
+---------------------+
SELECT bitmap_count(X 'FFFF');
+---------------------+
|bitmap_count(X'FFFF')|
+---------------------+
| 16|
+---------------------+
SELECT bitmap_count(X '0');
+-------------------+
|bitmap_count(X'00')|
+-------------------+
| 0|
+-------------------+
-- current_catalog
SELECT current_catalog();
+-----------------+
|current_catalog()|
+-----------------+
| spark_catalog|
+-----------------+
-- current_database
SELECT current_database();
+------------------+
|current_database()|
+------------------+
| default|
+------------------+
-- current_schema
SELECT current_schema();
+------------------+
|current_database()|
+------------------+
| default|
+------------------+
-- current_user
SELECT current_user();
+--------------+
|current_user()|
+--------------+
| rainlake|
+--------------+
-- equal_null
SELECT equal_null(3, 3);
+----------------+
|equal_null(3, 3)|
+----------------+
| true|
+----------------+
SELECT equal_null(1, '11');
+-----------------+
|equal_null(1, 11)|
+-----------------+
| false|
+-----------------+
SELECT equal_null(true, NULL);
+----------------------+
|equal_null(true, NULL)|
+----------------------+
| false|
+----------------------+
SELECT equal_null(NULL, 'abc');
+---------------------+
|equal_null(NULL, abc)|
+---------------------+
| false|
+---------------------+
SELECT equal_null(NULL, NULL);
+----------------------+
|equal_null(NULL, NULL)|
+----------------------+
| true|
+----------------------+
-- hll_sketch_estimate
SELECT hll_sketch_estimate(hll_sketch_agg(col)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
+--------------------------------------------+
|hll_sketch_estimate(hll_sketch_agg(col, 12))|
+--------------------------------------------+
| 3|
+--------------------------------------------+
-- hll_union
SELECT hll_sketch_estimate(hll_union(hll_sketch_agg(col1), hll_sketch_agg(col2))) FROM VALUES (1, 4), (1, 4), (2, 5), (2, 5), (3, 6) tab(col1, col2);
+-----------------------------------------------------------------------------------------+
|hll_sketch_estimate(hll_union(hll_sketch_agg(col1, 12), hll_sketch_agg(col2, 12), false))|
+-----------------------------------------------------------------------------------------+
| 6|
+-----------------------------------------------------------------------------------------+
-- input_file_block_length
SELECT input_file_block_length();
+-------------------------+
|input_file_block_length()|
+-------------------------+
| -1|
+-------------------------+
-- input_file_block_start
SELECT input_file_block_start();
+------------------------+
|input_file_block_start()|
+------------------------+
| -1|
+------------------------+
-- input_file_name
SELECT input_file_name();
+-----------------+
|input_file_name()|
+-----------------+
| |
+-----------------+
-- java_method
SELECT java_method('java.util.UUID', 'randomUUID');
+---------------------------------------+
|java_method(java.util.UUID, randomUUID)|
+---------------------------------------+
| 343994f0-f2e9-4f6...|
+---------------------------------------+
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)|
+-----------------------------------+
| 51efe616-c51d-42e...|
+-----------------------------------+
SELECT reflect('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
+-------------------------------------------------------------------------+
|reflect(java.util.UUID, fromString, a5cf6c42-0c85-418f-af6c-3e4e5b1328f2)|
+-------------------------------------------------------------------------+
| a5cf6c42-0c85-418...|
+-------------------------------------------------------------------------+
-- spark_partition_id
SELECT spark_partition_id();
+--------------------+
|SPARK_PARTITION_ID()|
+--------------------+
| 0|
+--------------------+
-- try_aes_decrypt
SELECT try_aes_decrypt(unhex('6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
+------------------------------------------------------------------------------------------------------------------------------------+
|try_aes_decrypt(unhex(6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210), 0000111122223333, GCM, DEFAULT, )|
+------------------------------------------------------------------------------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+------------------------------------------------------------------------------------------------------------------------------------+
SELECT try_aes_decrypt(unhex('----------468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
+------------------------------------------------------------------------------------------------------------------------------------+
|try_aes_decrypt(unhex(----------468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210), 0000111122223333, GCM, DEFAULT, )|
+------------------------------------------------------------------------------------------------------------------------------------+
| NULL|
+------------------------------------------------------------------------------------------------------------------------------------+
-- typeof
SELECT typeof(1);
+---------+
|typeof(1)|
+---------+
| int|
+---------+
SELECT typeof(array(1));
+----------------+
|typeof(array(1))|
+----------------+
| array<int>|
+----------------+
-- user
SELECT user();
+--------------+
|current_user()|
+--------------+
| rainlake|
+--------------+
-- uuid
SELECT uuid();
+--------------------+
| uuid()|
+--------------------+
|3dfba2c3-7a5e-4c2...|
+--------------------+
-- version
SELECT version();
+--------------------+
| version()|
+--------------------+
|3.5.0 ce5ddad9903...|
+--------------------+
| 函数 | 描述 |
|---|
| explode(expr) | 将数组expr的元素分成多行，或将映射expr的元素分成多行和多列。除非另有指定，否则使用默认的数组元素列名col或映射元素的key和value。 |
| explode_outer(expr) | 将数组expr的元素分成多行，或将映射expr的元素分成多行和多列。除非另有指定，否则使用默认的数组元素列名col或映射元素的key和value。 |
| inline(expr) | 将结构体数组分解为表。除非另有指定，否则默认使用列名 col1、col2 等。 |
| inline_outer(expr) | 将结构体数组分解为表。除非另有指定，否则默认使用列名 col1、col2 等。 |
| posexplode(expr) | 将数组expr的元素按位置分成多行，或将映射expr的元素按位置分成多行多列。除非另有说明，否则使用列名pos表示位置、使用列名col表示数组元素或使用列名key和value表示映射元素。 |
| posexplode_outer(expr) | 将数组expr的元素按位置分成多行，或将映射expr的元素按位置分成多行多列。除非另有说明，否则使用列名pos表示位置、使用列名col表示数组元素或使用列名key和value表示映射元素。 |
| stack(n, expr1, ..., exprk) | 将expr1, ..., exprk分成n行。除非另有说明，否则默认使用列名 col0、col1 等。 |
-- explode
SELECT explode(array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT explode(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT * FROM explode(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
-- explode_outer
SELECT explode_outer(array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT explode_outer(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT * FROM explode_outer(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
-- inline
SELECT inline(array(struct(1, 'a'), struct(2, 'b')));
+----+----+
|col1|col2|
+----+----+
| 1| a|
| 2| b|
+----+----+
-- inline_outer
SELECT inline_outer(array(struct(1, 'a'), struct(2, 'b')));
+----+----+
|col1|col2|
+----+----+
| 1| a|
| 2| b|
+----+----+
-- posexplode
SELECT posexplode(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
SELECT * FROM posexplode(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
-- posexplode_outer
SELECT posexplode_outer(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
SELECT * FROM posexplode_outer(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
-- stack
SELECT stack(2, 1, 2, 3);
+----+----+
|col0|col1|
+----+----+
| 1| 2|
| 3|NULL|
+----+----+
