內建函數
聚合函數
| 函數 | 說明 |
|---|---|
| any(expr) | 如果 `expr` 至少有一個值為 true，則傳回 true。 |
| any_value(expr[, isIgnoreNull]) | 傳回一組列的 `expr` 的某個值。如果 `isIgnoreNull` 為 true，則只傳回非 Null 值。 |
| approx_count_distinct(expr[, relativeSD]) | 傳回 HyperLogLog++ 估計的基數。`relativeSD` 定義允許的最大相對標準差。 |
| approx_percentile(col, percentage [, accuracy]) | 傳回數值或 ansi 間隔欄位 `col` 的近似 `百分比`，這是已排序 `col` 值（從最小到最大排序）中最小的值，使得不超過 `percentage` 的 `col` 值小於該值或等於該值。percentage 的值必須介於 0.0 和 1.0 之間。`accuracy` 參數（預設值：10000）是一個正數文字，用以控制近似精確度，但會犧牲記憶體。`accuracy` 的值越高，精確度越好，`1.0/accuracy` 是近似的相對誤差。當 `percentage` 是陣列時，percentage 陣列的每個值都必須介於 0.0 和 1.0 之間。在這種情況下，傳回欄位 `col` 在給定百分比陣列中的近似百分比陣列。 |
| array_agg(expr) | 收集並傳回一個非唯一元素清單。 |
| avg(expr) | 傳回從一組值計算的平均值。 |
| bit_and(expr) | 傳回所有非 Null 輸入值的按位元 AND，如果沒有，則傳回 Null。 |
| bit_or(expr) | 傳回所有非 Null 輸入值的按位元 OR，如果沒有，則傳回 Null。 |
| bit_xor(expr) | 傳回所有非 Null 輸入值的按位元 XOR，如果沒有，則傳回 Null。 |
| bitmap_construct_agg(child) | 傳回一個位元圖，其中包含從子式所有值設定的位元位置。子式很可能是 bitmap_bit_position()。 |
| bitmap_or_agg(child) | 傳回一個位元圖，它是從子式所有位元圖的按位元 OR。輸入應該是從 bitmap_construct_agg() 建立的位元圖。 |
| bool_and(expr) | 如果 `expr` 的所有值都為 true，則傳回 true。 |
| bool_or(expr) | 如果 `expr` 至少有一個值為 true，則傳回 true。 |
| collect_list(expr) | 收集並傳回一個非唯一元素清單。 |
| collect_set(expr) | 收集並傳回一組唯一元素。 |
| corr(expr1, expr2) | 傳回一組數字對之間的 Pearson 相關係數。 |
| count(*) | 傳回檢索到的列總數，包括包含 null 的列。 |
| count(expr[, expr...]) | 傳回提供之表達式皆為非 null 的列數。 |
| count(DISTINCT expr[, expr...]) | 傳回提供之表達式為唯一且非 null 的列數。 |
| count_if(expr) | 傳回表達式的 `TRUE` 值數。 |
| count_min_sketch(col, eps, confidence, seed) | 傳回具有給定 esp、信心度和種子的欄位的計數最小草圖。結果為位元組陣列，可在使用前反序列化為 `CountMinSketch`。計數最小草圖是一種機率性資料結構，用於使用次線性空間進行基數估計。 |
| covar_pop(expr1, expr2) | 傳回一組數字對的總體共變異數。 |
| covar_samp(expr1, expr2) | 傳回一組數字對的樣本共變異數。 |
| every(expr) | 如果 `expr` 的所有值都為 true，則傳回 true。 |
| first(expr[, isIgnoreNull]) | 傳回一群列的 `expr` 第一個值。如果 `isIgnoreNull` 為 true，則僅傳回非 null 值。 |
| first_value(expr[, isIgnoreNull]) | 傳回一群列的 `expr` 第一個值。如果 `isIgnoreNull` 為 true，則僅傳回非 null 值。 |
| grouping(col) | 表示 GROUP BY 中指定的欄位是否聚合，結果集中聚合為 1，未聚合為 0。 |
| grouping_id([col1[, col2 ..]]) | 傳回群組層級，等於 `(grouping(c1) << (n-1)) + (grouping(c2) << (n-2)) + ... + grouping(cn)` |
| histogram_numeric(expr, nb) | 使用 nb 個區間對數值 `expr` 計算直方圖。傳回值是陣列，其中包含 (x,y) 對，代表直方圖區間的中心。隨著 `nb` 值增加，直方圖近似值會更精細，但可能會在異常值周圍產生人工製品。實際上，20-40 個直方圖區間似乎運作良好，而傾斜或較小的資料集需要更多區間。請注意，此函式會建立具有非均勻區間寬度的直方圖。它不保證直方圖的均方誤差，但在實際上與 R/S-Plus 統計運算套件產生的直方圖相當。注意：傳回值中 `x` 欄位的輸出類型從聚合函式中使用的輸入值傳播而來。 |
| hll_sketch_agg(expr, lgConfigK) | 傳回 HllSketch 可更新的二進制表示。`lgConfigK`（選用）K 的 log-base-2，其中 K 是 HllSketch 的儲存區或時段數。 |
| hll_union_agg(expr, allowDifferentLgConfigK) | 傳回估計的唯一值數目。`allowDifferentLgConfigK`（選用）允許具有不同 lgConfigK 值的草圖進行合併（預設為 false）。 |
| kurtosis(expr) | 傳回從群組值計算的峰度值。 |
| last(expr[, isIgnoreNull]) | 傳回群組列的 `expr` 最後一個值。如果 `isIgnoreNull` 為 true，只傳回非 Null 值 |
| last_value(expr[, isIgnoreNull]) | 傳回群組列的 `expr` 最後一個值。如果 `isIgnoreNull` 為 true，只傳回非 Null 值 |
| max(expr) | 傳回 `expr` 的最大值。 |
| max_by(x, y) | 傳回與 `y` 最大值關聯的 `x` 值。 |
| mean(expr) | 傳回從一組值計算的平均值。 |
| median(col) | 傳回數值或 ANSI 間隔欄 `col` 的中位數。 |
| min(expr) | 傳回 `expr` 的最小值。 |
| min_by(x, y) | 傳回與 `y` 最小值關聯的 `x` 值。 |
| mode(col) | 傳回 `col` 內值的出現頻率最高值。會忽略 NULL 值。如果所有值都是 NULL，或有 0 列，則傳回 NULL。 |
| percentile(col, percentage [, frequency]) | 傳回數值或 ANSI 間隔欄 `col` 在給定百分比下的精確百分位數值。percentage 的值必須介於 0.0 和 1.0 之間。frequency 的值應為正整數 |
| percentile(col, array(percentage1 [, percentage2]...) [, frequency]) | 傳回數值欄 `col` 在給定百分比下的精確百分位數值陣列。百分比陣列的每個值都必須介於 0.0 和 1.0 之間。frequency 的值應為正整數 |
| percentile_approx(col, percentage [, accuracy]) | 傳回數值或 ansi 間隔欄位 `col` 的近似 `百分比`，這是已排序 `col` 值（從最小到最大排序）中最小的值，使得不超過 `percentage` 的 `col` 值小於該值或等於該值。percentage 的值必須介於 0.0 和 1.0 之間。`accuracy` 參數（預設值：10000）是一個正數文字，用以控制近似精確度，但會犧牲記憶體。`accuracy` 的值越高，精確度越好，`1.0/accuracy` 是近似的相對誤差。當 `percentage` 是陣列時，percentage 陣列的每個值都必須介於 0.0 和 1.0 之間。在這種情況下，傳回欄位 `col` 在給定百分比陣列中的近似百分比陣列。 |
| regr_avgx(y, x) | 傳回群組中非 Null 成對的獨立變數平均值，其中 `y` 是依變數，`x` 是獨立變數。 |
| regr_avgy(y, x) | 傳回群組中非 Null 成對的依變數平均值，其中 `y` 是依變數，`x` 是獨立變數。 |
| regr_count(y, x) | 傳回群組中非 Null 數值對的數量，其中 `y` 是依變數，而 `x` 是自變數。 |
| regr_intercept(y, x) | 傳回群組中非 Null 對的單變量線性回歸線的截距，其中 `y` 是依變數，而 `x` 是自變數。 |
| regr_r2(y, x) | 傳回群組中非 Null 對的決定係數，其中 `y` 是依變數，而 `x` 是自變數。 |
| regr_slope(y, x) | 傳回群組中非 Null 對的線性回歸線的斜率，其中 `y` 是依變數，而 `x` 是自變數。 |
| regr_sxx(y, x) | 傳回群組中非 Null 對的 REGR_COUNT(y, x) * VAR_POP(x)，其中 `y` 是依變數，而 `x` 是自變數。 |
| regr_sxy(y, x) | 傳回群組中非 Null 對的 REGR_COUNT(y, x) * COVAR_POP(y, x)，其中 `y` 是依變數，而 `x` 是自變數。 |
| regr_syy(y, x) | 傳回群組中非 Null 對的 REGR_COUNT(y, x) * VAR_POP(y)，其中 `y` 是依變數，而 `x` 是自變數。 |
| skewness(expr) | 傳回根據群組值計算出的偏度值。 |
| some(expr) | 如果 `expr` 至少有一個值為 true，則傳回 true。 |
| std(expr) | 傳回根據群組值計算出的樣本標準差。 |
| stddev(expr) | 傳回根據群組值計算出的樣本標準差。 |
| stddev_pop(expr) | 傳回根據群組值計算出的母體標準差。 |
| stddev_samp(expr) | 傳回根據群組值計算出的樣本標準差。 |
| sum(expr) | 傳回根據群組值計算出的總和。 |
| try_avg(expr) | 傳回根據群組值計算出的平均值，且結果在溢位時為 Null。 |
| try_sum(expr) | 傳回根據群組值計算出的總和，且結果在溢位時為 Null。 |
| var_pop(expr) | 傳回根據群組值計算出的母體變異數。 |
| var_samp(expr) | 傳回根據群組值計算出的樣本變異數。 |
| variance(expr) | 傳回根據群組值計算出的樣本變異數。 |
範例
-- any
SELECT any(col) FROM VALUES (true), (false), (false) AS tab(col);
+--------+
|any(col)|
+--------+
| true|
+--------+
SELECT any(col) FROM VALUES (NULL), (true), (false) AS tab(col);
+--------+
|any(col)|
+--------+
| true|
+--------+
SELECT any(col) FROM VALUES (false), (false), (NULL) AS tab(col);
+--------+
|any(col)|
+--------+
| false|
+--------+
-- any_value
SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);
+--------------+
|any_value(col)|
+--------------+
| 10|
+--------------+
SELECT any_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
+--------------+
|any_value(col)|
+--------------+
| NULL|
+--------------+
SELECT any_value(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
+--------------+
|any_value(col)|
+--------------+
| 5|
+--------------+
-- approx_count_distinct
SELECT approx_count_distinct(col1) FROM VALUES (1), (1), (2), (2), (3) tab(col1);
+---------------------------+
|approx_count_distinct(col1)|
+---------------------------+
| 3|
+---------------------------+
-- approx_percentile
SELECT approx_percentile(col, array(0.5, 0.4, 0.1), 100) FROM VALUES (0), (1), (2), (10) AS tab(col);
+-------------------------------------------------+
|approx_percentile(col, array(0.5, 0.4, 0.1), 100)|
+-------------------------------------------------+
| [1, 1, 0]|
+-------------------------------------------------+
SELECT approx_percentile(col, 0.5, 100) FROM VALUES (0), (6), (7), (9), (10) AS tab(col);
+--------------------------------+
|approx_percentile(col, 0.5, 100)|
+--------------------------------+
| 7|
+--------------------------------+
SELECT approx_percentile(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+--------------------------------+
|approx_percentile(col, 0.5, 100)|
+--------------------------------+
| INTERVAL '1' MONTH|
+--------------------------------+
SELECT approx_percentile(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
+--------------------------------------------+
|approx_percentile(col, array(0.5, 0.7), 100)|
+--------------------------------------------+
| [INTERVAL '01' SE...|
+--------------------------------------------+
-- array_agg
SELECT array_agg(col) FROM VALUES (1), (2), (1) AS tab(col);
+-----------------+
|collect_list(col)|
+-----------------+
| [1, 2, 1]|
+-----------------+
-- avg
SELECT avg(col) FROM VALUES (1), (2), (3) AS tab(col);
+--------+
|avg(col)|
+--------+
| 2.0|
+--------+
SELECT avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
+--------+
|avg(col)|
+--------+
| 1.5|
+--------+
-- bit_and
SELECT bit_and(col) FROM VALUES (3), (5) AS tab(col);
+------------+
|bit_and(col)|
+------------+
| 1|
+------------+
-- bit_or
SELECT bit_or(col) FROM VALUES (3), (5) AS tab(col);
+-----------+
|bit_or(col)|
+-----------+
| 7|
+-----------+
-- bit_xor
SELECT bit_xor(col) FROM VALUES (3), (5) AS tab(col);
+------------+
|bit_xor(col)|
+------------+
| 6|
+------------+
-- bitmap_construct_agg
SELECT substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6) FROM VALUES (1), (2), (3) AS tab(col);
+--------------------------------------------------------------------+
|substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6)|
+--------------------------------------------------------------------+
| 070000|
+--------------------------------------------------------------------+
SELECT substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6) FROM VALUES (1), (1), (1) AS tab(col);
+--------------------------------------------------------------------+
|substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6)|
+--------------------------------------------------------------------+
| 010000|
+--------------------------------------------------------------------+
-- bitmap_or_agg
SELECT substring(hex(bitmap_or_agg(col)), 0, 6) FROM VALUES (X '10'), (X '20'), (X '40') AS tab(col);
+----------------------------------------+
|substring(hex(bitmap_or_agg(col)), 0, 6)|
+----------------------------------------+
| 700000|
+----------------------------------------+
SELECT substring(hex(bitmap_or_agg(col)), 0, 6) FROM VALUES (X '10'), (X '10'), (X '10') AS tab(col);
+----------------------------------------+
|substring(hex(bitmap_or_agg(col)), 0, 6)|
+----------------------------------------+
| 100000|
+----------------------------------------+
-- bool_and
SELECT bool_and(col) FROM VALUES (true), (true), (true) AS tab(col);
+-------------+
|bool_and(col)|
+-------------+
| true|
+-------------+
SELECT bool_and(col) FROM VALUES (NULL), (true), (true) AS tab(col);
+-------------+
|bool_and(col)|
+-------------+
| true|
+-------------+
SELECT bool_and(col) FROM VALUES (true), (false), (true) AS tab(col);
+-------------+
|bool_and(col)|
+-------------+
| false|
+-------------+
-- bool_or
SELECT bool_or(col) FROM VALUES (true), (false), (false) AS tab(col);
+------------+
|bool_or(col)|
+------------+
| true|
+------------+
SELECT bool_or(col) FROM VALUES (NULL), (true), (false) AS tab(col);
+------------+
|bool_or(col)|
+------------+
| true|
+------------+
SELECT bool_or(col) FROM VALUES (false), (false), (NULL) AS tab(col);
+------------+
|bool_or(col)|
+------------+
| false|
+------------+
-- collect_list
SELECT collect_list(col) FROM VALUES (1), (2), (1) AS tab(col);
+-----------------+
|collect_list(col)|
+-----------------+
| [1, 2, 1]|
+-----------------+
-- collect_set
SELECT collect_set(col) FROM VALUES (1), (2), (1) AS tab(col);
+----------------+
|collect_set(col)|
+----------------+
| [1, 2]|
+----------------+
-- corr
SELECT corr(c1, c2) FROM VALUES (3, 2), (3, 3), (6, 4) as tab(c1, c2);
+------------------+
| corr(c1, c2)|
+------------------+
|0.8660254037844387|
+------------------+
-- count
SELECT count(*) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
+--------+
|count(1)|
+--------+
| 4|
+--------+
SELECT count(col) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
+----------+
|count(col)|
+----------+
| 3|
+----------+
SELECT count(DISTINCT col) FROM VALUES (NULL), (5), (5), (10) AS tab(col);
+-------------------+
|count(DISTINCT col)|
+-------------------+
| 2|
+-------------------+
-- count_if
SELECT count_if(col % 2 = 0) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
+-------------------------+
|count_if(((col % 2) = 0))|
+-------------------------+
| 2|
+-------------------------+
SELECT count_if(col IS NULL) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
+-----------------------+
|count_if((col IS NULL))|
+-----------------------+
| 1|
+-----------------------+
-- count_min_sketch
SELECT hex(count_min_sketch(col, 0.5d, 0.5d, 1)) FROM VALUES (1), (2), (1) AS tab(col);
+---------------------------------------+
|hex(count_min_sketch(col, 0.5, 0.5, 1))|
+---------------------------------------+
| 00000001000000000...|
+---------------------------------------+
-- covar_pop
SELECT covar_pop(c1, c2) FROM VALUES (1,1), (2,2), (3,3) AS tab(c1, c2);
+------------------+
| covar_pop(c1, c2)|
+------------------+
|0.6666666666666666|
+------------------+
-- covar_samp
SELECT covar_samp(c1, c2) FROM VALUES (1,1), (2,2), (3,3) AS tab(c1, c2);
+------------------+
|covar_samp(c1, c2)|
+------------------+
| 1.0|
+------------------+
-- every
SELECT every(col) FROM VALUES (true), (true), (true) AS tab(col);
+----------+
|every(col)|
+----------+
| true|
+----------+
SELECT every(col) FROM VALUES (NULL), (true), (true) AS tab(col);
+----------+
|every(col)|
+----------+
| true|
+----------+
SELECT every(col) FROM VALUES (true), (false), (true) AS tab(col);
+----------+
|every(col)|
+----------+
| false|
+----------+
-- first
SELECT first(col) FROM VALUES (10), (5), (20) AS tab(col);
+----------+
|first(col)|
+----------+
| 10|
+----------+
SELECT first(col) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------+
|first(col)|
+----------+
| NULL|
+----------+
SELECT first(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------+
|first(col)|
+----------+
| 5|
+----------+
-- first_value
SELECT first_value(col) FROM VALUES (10), (5), (20) AS tab(col);
+----------------+
|first_value(col)|
+----------------+
| 10|
+----------------+
SELECT first_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------------+
|first_value(col)|
+----------------+
| NULL|
+----------------+
SELECT first_value(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
+----------------+
|first_value(col)|
+----------------+
| 5|
+----------------+
-- grouping
SELECT name, grouping(name), sum(age) FROM VALUES (2, 'Alice'), (5, 'Bob') people(age, name) GROUP BY cube(name);
+-----+--------------+--------+
| name|grouping(name)|sum(age)|
+-----+--------------+--------+
| NULL| 1| 7|
|Alice| 0| 2|
| Bob| 0| 5|
+-----+--------------+--------+
-- grouping_id
SELECT name, grouping_id(), sum(age), avg(height) FROM VALUES (2, 'Alice', 165), (5, 'Bob', 180) people(age, name, height) GROUP BY cube(name, height);
+-----+-------------+--------+-----------+
| name|grouping_id()|sum(age)|avg(height)|
+-----+-------------+--------+-----------+
| NULL| 2| 2| 165.0|
|Alice| 0| 2| 165.0|
|Alice| 1| 2| 165.0|
| NULL| 3| 7| 172.5|
| Bob| 1| 5| 180.0|
| Bob| 0| 5| 180.0|
| NULL| 2| 5| 180.0|
+-----+-------------+--------+-----------+
-- histogram_numeric
SELECT histogram_numeric(col, 5) FROM VALUES (0), (1), (2), (10) AS tab(col);
+-------------------------+
|histogram_numeric(col, 5)|
+-------------------------+
| [{0, 1.0}, {1, 1....|
+-------------------------+
-- hll_sketch_agg
SELECT hll_sketch_estimate(hll_sketch_agg(col, 12)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
+--------------------------------------------+
|hll_sketch_estimate(hll_sketch_agg(col, 12))|
+--------------------------------------------+
| 3|
+--------------------------------------------+
-- hll_union_agg
SELECT hll_sketch_estimate(hll_union_agg(sketch, true)) FROM (SELECT hll_sketch_agg(col) as sketch FROM VALUES (1) tab(col) UNION ALL SELECT hll_sketch_agg(col, 20) as sketch FROM VALUES (1) tab(col));
+------------------------------------------------+
|hll_sketch_estimate(hll_union_agg(sketch, true))|
+------------------------------------------------+
| 1|
+------------------------------------------------+
-- kurtosis
SELECT kurtosis(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
+-------------------+
| kurtosis(col)|
+-------------------+
|-0.7014368047529618|
+-------------------+
SELECT kurtosis(col) FROM VALUES (1), (10), (100), (10), (1) as tab(col);
+-------------------+
| kurtosis(col)|
+-------------------+
|0.19432323191698986|
+-------------------+
-- last
SELECT last(col) FROM VALUES (10), (5), (20) AS tab(col);
+---------+
|last(col)|
+---------+
| 20|
+---------+
SELECT last(col) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------+
|last(col)|
+---------+
| NULL|
+---------+
SELECT last(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------+
|last(col)|
+---------+
| 5|
+---------+
-- last_value
SELECT last_value(col) FROM VALUES (10), (5), (20) AS tab(col);
+---------------+
|last_value(col)|
+---------------+
| 20|
+---------------+
SELECT last_value(col) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------------+
|last_value(col)|
+---------------+
| NULL|
+---------------+
SELECT last_value(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
+---------------+
|last_value(col)|
+---------------+
| 5|
+---------------+
-- max
SELECT max(col) FROM VALUES (10), (50), (20) AS tab(col);
+--------+
|max(col)|
+--------+
| 50|
+--------+
-- max_by
SELECT max_by(x, y) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
+------------+
|max_by(x, y)|
+------------+
| b|
+------------+
-- mean
SELECT mean(col) FROM VALUES (1), (2), (3) AS tab(col);
+---------+
|mean(col)|
+---------+
| 2.0|
+---------+
SELECT mean(col) FROM VALUES (1), (2), (NULL) AS tab(col);
+---------+
|mean(col)|
+---------+
| 1.5|
+---------+
-- median
SELECT median(col) FROM VALUES (0), (10) AS tab(col);
+-----------+
|median(col)|
+-----------+
| 5.0|
+-----------+
SELECT median(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+--------------------+
| median(col)|
+--------------------+
|INTERVAL '0-5' YE...|
+--------------------+
-- min
SELECT min(col) FROM VALUES (10), (-1), (20) AS tab(col);
+--------+
|min(col)|
+--------+
| -1|
+--------+
-- min_by
SELECT min_by(x, y) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
+------------+
|min_by(x, y)|
+------------+
| a|
+------------+
-- mode
SELECT mode(col) FROM VALUES (0), (10), (10) AS tab(col);
+---------+
|mode(col)|
+---------+
| 10|
+---------+
SELECT mode(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+-------------------+
| mode(col)|
+-------------------+
|INTERVAL '10' MONTH|
+-------------------+
SELECT mode(col) FROM VALUES (0), (10), (10), (null), (null), (null) AS tab(col);
+---------+
|mode(col)|
+---------+
| 10|
+---------+
-- percentile
SELECT percentile(col, 0.3) FROM VALUES (0), (10) AS tab(col);
+-----------------------+
|percentile(col, 0.3, 1)|
+-----------------------+
| 3.0|
+-----------------------+
SELECT percentile(col, array(0.25, 0.75)) FROM VALUES (0), (10) AS tab(col);
+-------------------------------------+
|percentile(col, array(0.25, 0.75), 1)|
+-------------------------------------+
| [2.5, 7.5]|
+-------------------------------------+
SELECT percentile(col, 0.5) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+-----------------------+
|percentile(col, 0.5, 1)|
+-----------------------+
| INTERVAL '0-5' YE...|
+-----------------------+
SELECT percentile(col, array(0.2, 0.5)) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '10' SECOND) AS tab(col);
+-----------------------------------+
|percentile(col, array(0.2, 0.5), 1)|
+-----------------------------------+
| [INTERVAL '0 00:0...|
+-----------------------------------+
-- percentile_approx
SELECT percentile_approx(col, array(0.5, 0.4, 0.1), 100) FROM VALUES (0), (1), (2), (10) AS tab(col);
+-------------------------------------------------+
|percentile_approx(col, array(0.5, 0.4, 0.1), 100)|
+-------------------------------------------------+
| [1, 1, 0]|
+-------------------------------------------------+
SELECT percentile_approx(col, 0.5, 100) FROM VALUES (0), (6), (7), (9), (10) AS tab(col);
+--------------------------------+
|percentile_approx(col, 0.5, 100)|
+--------------------------------+
| 7|
+--------------------------------+
SELECT percentile_approx(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
+--------------------------------+
|percentile_approx(col, 0.5, 100)|
+--------------------------------+
| INTERVAL '1' MONTH|
+--------------------------------+
SELECT percentile_approx(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
+--------------------------------------------+
|percentile_approx(col, array(0.5, 0.7), 100)|
+--------------------------------------------+
| [INTERVAL '01' SE...|
+--------------------------------------------+
-- regr_avgx
SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| 2.75|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (1, null) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (null, 1) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| 3.0|
+---------------+
SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgx(y, x)|
+---------------+
| 3.0|
+---------------+
-- regr_avgy
SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| 1.75|
+---------------+
SELECT regr_avgy(y, x) FROM VALUES (1, null) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgy(y, x) FROM VALUES (null, 1) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| NULL|
+---------------+
SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_avgy(y, x)|
+------------------+
|1.6666666666666667|
+------------------+
SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+---------------+
|regr_avgy(y, x)|
+---------------+
| 1.5|
+---------------+
-- regr_count
SELECT regr_count(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 4|
+----------------+
SELECT regr_count(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 3|
+----------------+
SELECT regr_count(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+----------------+
|regr_count(y, x)|
+----------------+
| 2|
+----------------+
-- regr_intercept
SELECT regr_intercept(y, x) FROM VALUES (1,1), (2,2), (3,3) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| 0.0|
+--------------------+
SELECT regr_intercept(y, x) FROM VALUES (1, null) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| NULL|
+--------------------+
SELECT regr_intercept(y, x) FROM VALUES (null, 1) AS tab(y, x);
+--------------------+
|regr_intercept(y, x)|
+--------------------+
| NULL|
+--------------------+
-- regr_r2
SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_r2(y, x)|
+------------------+
|0.2727272727272726|
+------------------+
SELECT regr_r2(y, x) FROM VALUES (1, null) AS tab(y, x);
+-------------+
|regr_r2(y, x)|
+-------------+
| NULL|
+-------------+
SELECT regr_r2(y, x) FROM VALUES (null, 1) AS tab(y, x);
+-------------+
|regr_r2(y, x)|
+-------------+
| NULL|
+-------------+
SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_r2(y, x)|
+------------------+
|0.7500000000000001|
+------------------+
SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+-------------+
|regr_r2(y, x)|
+-------------+
| 1.0|
+-------------+
-- regr_slope
SELECT regr_slope(y, x) FROM VALUES (1,1), (2,2), (3,3) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| 1.0|
+----------------+
SELECT regr_slope(y, x) FROM VALUES (1, null) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| NULL|
+----------------+
SELECT regr_slope(y, x) FROM VALUES (null, 1) AS tab(y, x);
+----------------+
|regr_slope(y, x)|
+----------------+
| NULL|
+----------------+
-- regr_sxx
SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_sxx(y, x)|
+------------------+
|2.7499999999999996|
+------------------+
SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxx(y, x)|
+--------------+
| 2.0|
+--------------+
SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxx(y, x)|
+--------------+
| 2.0|
+--------------+
-- regr_sxy
SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_sxy(y, x)|
+------------------+
|0.7499999999999998|
+------------------+
SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxy(y, x)|
+--------------+
| 1.0|
+--------------+
SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_sxy(y, x)|
+--------------+
| 1.0|
+--------------+
-- regr_syy
SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_syy(y, x)|
+------------------+
|0.7499999999999999|
+------------------+
SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
+------------------+
| regr_syy(y, x)|
+------------------+
|0.6666666666666666|
+------------------+
SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
+--------------+
|regr_syy(y, x)|
+--------------+
| 0.5|
+--------------+
-- skewness
SELECT skewness(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
+------------------+
| skewness(col)|
+------------------+
|1.1135657469022013|
+------------------+
SELECT skewness(col) FROM VALUES (-1000), (-100), (10), (20) AS tab(col);
+-------------------+
| skewness(col)|
+-------------------+
|-1.1135657469022011|
+-------------------+
-- some
SELECT some(col) FROM VALUES (true), (false), (false) AS tab(col);
+---------+
|some(col)|
+---------+
| true|
+---------+
SELECT some(col) FROM VALUES (NULL), (true), (false) AS tab(col);
+---------+
|some(col)|
+---------+
| true|
+---------+
SELECT some(col) FROM VALUES (false), (false), (NULL) AS tab(col);
+---------+
|some(col)|
+---------+
| false|
+---------+
-- std
SELECT std(col) FROM VALUES (1), (2), (3) AS tab(col);
+--------+
|std(col)|
+--------+
| 1.0|
+--------+
-- stddev
SELECT stddev(col) FROM VALUES (1), (2), (3) AS tab(col);
+-----------+
|stddev(col)|
+-----------+
| 1.0|
+-----------+
-- stddev_pop
SELECT stddev_pop(col) FROM VALUES (1), (2), (3) AS tab(col);
+-----------------+
| stddev_pop(col)|
+-----------------+
|0.816496580927726|
+-----------------+
-- stddev_samp
SELECT stddev_samp(col) FROM VALUES (1), (2), (3) AS tab(col);
+----------------+
|stddev_samp(col)|
+----------------+
| 1.0|
+----------------+
-- sum
SELECT sum(col) FROM VALUES (5), (10), (15) AS tab(col);
+--------+
|sum(col)|
+--------+
| 30|
+--------+
SELECT sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
+--------+
|sum(col)|
+--------+
| 25|
+--------+
SELECT sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
+--------+
|sum(col)|
+--------+
| NULL|
+--------+
-- try_avg
SELECT try_avg(col) FROM VALUES (1), (2), (3) AS tab(col);
+------------+
|try_avg(col)|
+------------+
| 2.0|
+------------+
SELECT try_avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
+------------+
|try_avg(col)|
+------------+
| 1.5|
+------------+
SELECT try_avg(col) FROM VALUES (interval '2147483647 months'), (interval '1 months') AS tab(col);
+------------+
|try_avg(col)|
+------------+
| NULL|
+------------+
-- try_sum
SELECT try_sum(col) FROM VALUES (5), (10), (15) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| 30|
+------------+
SELECT try_sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| 25|
+------------+
SELECT try_sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| NULL|
+------------+
SELECT try_sum(col) FROM VALUES (9223372036854775807L), (1L) AS tab(col);
+------------+
|try_sum(col)|
+------------+
| NULL|
+------------+
-- var_pop
SELECT var_pop(col) FROM VALUES (1), (2), (3) AS tab(col);
+------------------+
| var_pop(col)|
+------------------+
|0.6666666666666666|
+------------------+
-- var_samp
SELECT var_samp(col) FROM VALUES (1), (2), (3) AS tab(col);
+-------------+
|var_samp(col)|
+-------------+
| 1.0|
+-------------+
-- variance
SELECT variance(col) FROM VALUES (1), (2), (3) AS tab(col);
+-------------+
|variance(col)|
+-------------+
| 1.0|
+-------------+
視窗函數
| 函數 | 說明 |
|---|---|
| cume_dist() | 計算值相對於分割中所有值的相對位置。 |
| dense_rank() | 計算值在值群組中的排名。結果為先前指定的排名值加一。與 rank 函數不同，dense_rank 在排名順序中不會產生間隙。 |
| lag(input[, offset[, default]]) | 傳回視窗中目前列之前第 `offset` 列的 `input` 值。`offset` 的預設值為 1，而 `default` 的預設值為 Null。如果第 `offset` 列的 `input` 值為 Null，則傳回 Null。如果沒有此類的偏移列（例如，當偏移量為 1 時，視窗的第一列沒有任何前一列），則傳回 `default`。 |
| lead(input[, offset[, default]]) | 傳回視窗中目前列之後第 `offset` 列的 `input` 值。`offset` 的預設值為 1，而 `default` 的預設值為 Null。如果第 `offset` 列的 `input` 值為 Null，則傳回 Null。如果沒有此類的偏移列（例如，當偏移量為 1 時，視窗的最後一列沒有任何後續列），則傳回 `default`。 |
| nth_value(input[, offset]) | 傳回視窗框架開頭起第 `offset` 列的 `input` 值。`offset` 從 1 開始。如果 ignoreNulls=true，在尋找第 `offset` 列時，我們會略過 null。否則，每一列都會計入 `offset`。如果沒有這樣的第 `offset` 列（例如，當 offset 為 10，視窗框架大小小於 10），則傳回 null。 |
| ntile(n) | 將每個視窗分割區的列分割成從 1 到最多 `n` 的 `n` 個區段。 |
| percent_rank() | 計算一組值中某個值的百分比排名。 |
| rank() | 計算一組值中某個值的排名。結果是分割區中與目前列相同或小於目前列的列數加一。這些值會在序列中產生間隙。 |
| row_number() | 根據視窗分割區中列的順序，為每一列指定一個唯一的順序號，從 1 開始。 |
範例
-- cume_dist
SELECT a, b, cume_dist() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+--------------------------------------------------------------------------------------------------------------+
| a| b|cume_dist() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+--------------------------------------------------------------------------------------------------------------+
| A1| 1| 0.6666666666666666|
| A1| 1| 0.6666666666666666|
| A1| 2| 1.0|
| A2| 3| 1.0|
+---+---+--------------------------------------------------------------------------------------------------------------+
-- dense_rank
SELECT a, b, dense_rank(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+--------------------------------------------------------------------------------------------------------------+
| a| b|DENSE_RANK() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+--------------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 1|
| A1| 2| 2|
| A2| 3| 1|
+---+---+--------------------------------------------------------------------------------------------------------------+
-- lag
SELECT a, b, lag(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+-----------------------------------------------------------------------------------------------------------+
| a| b|lag(b, 1, NULL) OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN -1 FOLLOWING AND -1 FOLLOWING)|
+---+---+-----------------------------------------------------------------------------------------------------------+
| A1| 1| NULL|
| A1| 1| 1|
| A1| 2| 1|
| A2| 3| NULL|
+---+---+-----------------------------------------------------------------------------------------------------------+
-- lead
SELECT a, b, lead(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+----------------------------------------------------------------------------------------------------------+
| a| b|lead(b, 1, NULL) OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)|
+---+---+----------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 2|
| A1| 2| NULL|
| A2| 3| NULL|
+---+---+----------------------------------------------------------------------------------------------------------+
-- nth_value
SELECT a, b, nth_value(b, 2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+------------------------------------------------------------------------------------------------------------------+
| a| b|nth_value(b, 2) OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+------------------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 1|
| A1| 2| 1|
| A2| 3| NULL|
+---+---+------------------------------------------------------------------------------------------------------------------+
-- ntile
SELECT a, b, ntile(2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+----------------------------------------------------------------------------------------------------------+
| a| b|ntile(2) OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+----------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 1|
| A1| 2| 2|
| A2| 3| 1|
+---+---+----------------------------------------------------------------------------------------------------------+
-- percent_rank
SELECT a, b, percent_rank(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+----------------------------------------------------------------------------------------------------------------+
| a| b|PERCENT_RANK() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+----------------------------------------------------------------------------------------------------------------+
| A1| 1| 0.0|
| A1| 1| 0.0|
| A1| 2| 1.0|
| A2| 3| 0.0|
+---+---+----------------------------------------------------------------------------------------------------------------+
-- rank
SELECT a, b, rank(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+--------------------------------------------------------------------------------------------------------+
| a| b|RANK() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+--------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 1|
| A1| 2| 3|
| A2| 3| 1|
+---+---+--------------------------------------------------------------------------------------------------------+
-- row_number
SELECT a, b, row_number() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
+---+---+--------------------------------------------------------------------------------------------------------------+
| a| b|row_number() OVER (PARTITION BY a ORDER BY b ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)|
+---+---+--------------------------------------------------------------------------------------------------------------+
| A1| 1| 1|
| A1| 1| 2|
| A1| 2| 3|
| A2| 3| 1|
+---+---+--------------------------------------------------------------------------------------------------------------+
陣列函數
| 函數 | 說明 |
|---|---|
| array(expr, ...) | 傳回包含給定元素的陣列。 |
| array_append(array, element) | 將元素新增到作為第一個引數傳遞的陣列的結尾。元素的類型應與陣列元素的類型類似。Null 元素也會附加到陣列中。但如果傳遞的陣列為 NULL，輸出則為 NULL |
| array_compact(array) | 從陣列中移除 null 值。 |
| array_contains(array, value) | 如果陣列包含該值，則傳回 true。 |
| array_distinct(array) | 從陣列中移除重複值。 |
| array_except(array1, array2) | 傳回 array1 中但不在 array2 中的元素陣列，且不含重複值。 |
| array_insert(x, pos, val) | 將 val 放入陣列 x 的索引位置 pos。陣列索引從 1 開始。最大的負索引為 -1，函數會將新元素插入目前最後一個元素之後。大於陣列大小的索引會附加到陣列，或如果索引為負數，則會將陣列置於陣列之前，且包含「null」元素。 |
| array_intersect(array1, array2) | 傳回陣列 1 和陣列 2 交集中的元素陣列，不含重複項目。 |
| array_join(array, delimiter[, nullReplacement]) | 使用分隔符號串接給定陣列的元素，並使用一個可選的字串取代 Null 值。如果未設定 nullReplacement 的值，則會濾除任何 Null 值。 |
| array_max(array) | 傳回陣列中的最大值。對於 double/float 類型，NaN 大於任何非 NaN 元素。會略過 NULL 元素。 |
| array_min(array) | 傳回陣列中的最小值。對於 double/float 類型，NaN 大於任何非 NaN 元素。會略過 NULL 元素。 |
| array_position(array, element) | 傳回陣列中第一個相符元素的 (從 1 開始) 索引，如果找不到相符項目，則傳回 0。 |
| array_prepend(array, element) | 將元素新增到作為第一個引數傳遞的陣列的開頭。元素的類型應與陣列元素的類型相同。Null 元素也會新增到陣列的開頭。但如果傳遞的陣列為 NULL，輸出為 NULL |
| array_remove(array, element) | 從陣列中移除所有等於 element 的元素。 |
| array_repeat(element, count) | 傳回包含 element count 次的陣列。 |
| array_union(array1, array2) | 傳回陣列 1 和陣列 2 聯集中的元素陣列，不含重複項目。 |
| arrays_overlap(a1, a2) | 如果 a1 包含至少一個在 a2 中也存在的非 Null 元素，則傳回 true。如果陣列沒有共同元素，且它們都是非空的，而且其中一個包含 Null 元素，則傳回 null，否則傳回 false。 |
| arrays_zip(a1, a2, ...) | 傳回一個合併的結構陣列，其中第 N 個結構包含輸入陣列的第 N 個值。 |
| flatten(arrayOfArrays) | 將陣列陣列轉換成單一陣列。 |
| get(array, index) | 傳回陣列中位於給定 (從 0 開始) 索引的元素。如果索引指向陣列邊界之外，則此函式傳回 NULL。 |
| sequence(start, stop, step) | 產生一個陣列，元素從開始到結束（包含），並以步長遞增。傳回元素的類型與參數表達式的類型相同。支援的類型有：位元組、短整數、整數、長整數、日期、時間戳記。開始和結束表達式必須解析為相同的類型。如果開始和結束表達式解析為「日期」或「時間戳記」類型，則步長表達式必須解析為「間隔」或「年-月間隔」或「日-時間隔」類型，否則與開始和結束表達式為相同的類型。 |
| shuffle(陣列) | 傳回給定陣列的隨機排列。 |
| slice(x, 開始, 長度) | 從索引開始（陣列索引從 1 開始，或如果開始為負數，則從最後開始）對陣列 x 進行子集化，並具有指定的長度。 |
| sort_array(陣列[, 升冪]) | 根據陣列元素的自然排序，以升冪或降冪對輸入陣列進行排序。對於 double/float 類型，NaN 大於任何非 NaN 元素。Null 元素將按升冪順序放置在傳回陣列的開頭，或按降冪順序放置在傳回陣列的結尾。 |
範例
-- array
SELECT array(1, 2, 3);
+--------------+
|array(1, 2, 3)|
+--------------+
| [1, 2, 3]|
+--------------+
-- array_append
SELECT array_append(array('b', 'd', 'c', 'a'), 'd');
+----------------------------------+
|array_append(array(b, d, c, a), d)|
+----------------------------------+
| [b, d, c, a, d]|
+----------------------------------+
SELECT array_append(array(1, 2, 3, null), null);
+----------------------------------------+
|array_append(array(1, 2, 3, NULL), NULL)|
+----------------------------------------+
| [1, 2, 3, NULL, N...|
+----------------------------------------+
SELECT array_append(CAST(null as Array<Int>), 2);
+---------------------+
|array_append(NULL, 2)|
+---------------------+
| NULL|
+---------------------+
-- array_compact
SELECT array_compact(array(1, 2, 3, null));
+-----------------------------------+
|array_compact(array(1, 2, 3, NULL))|
+-----------------------------------+
| [1, 2, 3]|
+-----------------------------------+
SELECT array_compact(array("a", "b", "c"));
+-----------------------------+
|array_compact(array(a, b, c))|
+-----------------------------+
| [a, b, c]|
+-----------------------------+
-- array_contains
SELECT array_contains(array(1, 2, 3), 2);
+---------------------------------+
|array_contains(array(1, 2, 3), 2)|
+---------------------------------+
| true|
+---------------------------------+
-- array_distinct
SELECT array_distinct(array(1, 2, 3, null, 3));
+---------------------------------------+
|array_distinct(array(1, 2, 3, NULL, 3))|
+---------------------------------------+
| [1, 2, 3, NULL]|
+---------------------------------------+
-- array_except
SELECT array_except(array(1, 2, 3), array(1, 3, 5));
+--------------------------------------------+
|array_except(array(1, 2, 3), array(1, 3, 5))|
+--------------------------------------------+
| [2]|
+--------------------------------------------+
-- array_insert
SELECT array_insert(array(1, 2, 3, 4), 5, 5);
+-------------------------------------+
|array_insert(array(1, 2, 3, 4), 5, 5)|
+-------------------------------------+
| [1, 2, 3, 4, 5]|
+-------------------------------------+
SELECT array_insert(array(5, 4, 3, 2), -1, 1);
+--------------------------------------+
|array_insert(array(5, 4, 3, 2), -1, 1)|
+--------------------------------------+
| [5, 4, 3, 2, 1]|
+--------------------------------------+
SELECT array_insert(array(5, 3, 2, 1), -4, 4);
+--------------------------------------+
|array_insert(array(5, 3, 2, 1), -4, 4)|
+--------------------------------------+
| [5, 4, 3, 2, 1]|
+--------------------------------------+
-- array_intersect
SELECT array_intersect(array(1, 2, 3), array(1, 3, 5));
+-----------------------------------------------+
|array_intersect(array(1, 2, 3), array(1, 3, 5))|
+-----------------------------------------------+
| [1, 3]|
+-----------------------------------------------+
-- array_join
SELECT array_join(array('hello', 'world'), ' ');
+----------------------------------+
|array_join(array(hello, world), )|
+----------------------------------+
| hello world|
+----------------------------------+
SELECT array_join(array('hello', null ,'world'), ' ');
+----------------------------------------+
|array_join(array(hello, NULL, world), )|
+----------------------------------------+
| hello world|
+----------------------------------------+
SELECT array_join(array('hello', null ,'world'), ' ', ',');
+-------------------------------------------+
|array_join(array(hello, NULL, world), , ,)|
+-------------------------------------------+
| hello , world|
+-------------------------------------------+
-- array_max
SELECT array_max(array(1, 20, null, 3));
+--------------------------------+
|array_max(array(1, 20, NULL, 3))|
+--------------------------------+
| 20|
+--------------------------------+
-- array_min
SELECT array_min(array(1, 20, null, 3));
+--------------------------------+
|array_min(array(1, 20, NULL, 3))|
+--------------------------------+
| 1|
+--------------------------------+
-- array_position
SELECT array_position(array(312, 773, 708, 708), 708);
+----------------------------------------------+
|array_position(array(312, 773, 708, 708), 708)|
+----------------------------------------------+
| 3|
+----------------------------------------------+
SELECT array_position(array(312, 773, 708, 708), 414);
+----------------------------------------------+
|array_position(array(312, 773, 708, 708), 414)|
+----------------------------------------------+
| 0|
+----------------------------------------------+
-- array_prepend
SELECT array_prepend(array('b', 'd', 'c', 'a'), 'd');
+-----------------------------------+
|array_prepend(array(b, d, c, a), d)|
+-----------------------------------+
| [d, b, d, c, a]|
+-----------------------------------+
SELECT array_prepend(array(1, 2, 3, null), null);
+-----------------------------------------+
|array_prepend(array(1, 2, 3, NULL), NULL)|
+-----------------------------------------+
| [NULL, 1, 2, 3, N...|
+-----------------------------------------+
SELECT array_prepend(CAST(null as Array<Int>), 2);
+----------------------+
|array_prepend(NULL, 2)|
+----------------------+
| NULL|
+----------------------+
-- array_remove
SELECT array_remove(array(1, 2, 3, null, 3), 3);
+----------------------------------------+
|array_remove(array(1, 2, 3, NULL, 3), 3)|
+----------------------------------------+
| [1, 2, NULL]|
+----------------------------------------+
-- array_repeat
SELECT array_repeat('123', 2);
+--------------------+
|array_repeat(123, 2)|
+--------------------+
| [123, 123]|
+--------------------+
-- array_union
SELECT array_union(array(1, 2, 3), array(1, 3, 5));
+-------------------------------------------+
|array_union(array(1, 2, 3), array(1, 3, 5))|
+-------------------------------------------+
| [1, 2, 3, 5]|
+-------------------------------------------+
-- arrays_overlap
SELECT arrays_overlap(array(1, 2, 3), array(3, 4, 5));
+----------------------------------------------+
|arrays_overlap(array(1, 2, 3), array(3, 4, 5))|
+----------------------------------------------+
| true|
+----------------------------------------------+
-- arrays_zip
SELECT arrays_zip(array(1, 2, 3), array(2, 3, 4));
+------------------------------------------+
|arrays_zip(array(1, 2, 3), array(2, 3, 4))|
+------------------------------------------+
| [{1, 2}, {2, 3}, ...|
+------------------------------------------+
SELECT arrays_zip(array(1, 2), array(2, 3), array(3, 4));
+-------------------------------------------------+
|arrays_zip(array(1, 2), array(2, 3), array(3, 4))|
+-------------------------------------------------+
| [{1, 2, 3}, {2, 3...|
+-------------------------------------------------+
-- flatten
SELECT flatten(array(array(1, 2), array(3, 4)));
+----------------------------------------+
|flatten(array(array(1, 2), array(3, 4)))|
+----------------------------------------+
| [1, 2, 3, 4]|
+----------------------------------------+
-- get
SELECT get(array(1, 2, 3), 0);
+----------------------+
|get(array(1, 2, 3), 0)|
+----------------------+
| 1|
+----------------------+
SELECT get(array(1, 2, 3), 3);
+----------------------+
|get(array(1, 2, 3), 3)|
+----------------------+
| NULL|
+----------------------+
SELECT get(array(1, 2, 3), -1);
+-----------------------+
|get(array(1, 2, 3), -1)|
+-----------------------+
| NULL|
+-----------------------+
-- sequence
SELECT sequence(1, 5);
+---------------+
| sequence(1, 5)|
+---------------+
|[1, 2, 3, 4, 5]|
+---------------+
SELECT sequence(5, 1);
+---------------+
| sequence(5, 1)|
+---------------+
|[5, 4, 3, 2, 1]|
+---------------+
SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month);
+----------------------------------------------------------------------+
|sequence(to_date(2018-01-01), to_date(2018-03-01), INTERVAL '1' MONTH)|
+----------------------------------------------------------------------+
| [2018-01-01, 2018...|
+----------------------------------------------------------------------+
SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval '0-1' year to month);
+--------------------------------------------------------------------------------+
|sequence(to_date(2018-01-01), to_date(2018-03-01), INTERVAL '0-1' YEAR TO MONTH)|
+--------------------------------------------------------------------------------+
| [2018-01-01, 2018...|
+--------------------------------------------------------------------------------+
-- shuffle
SELECT shuffle(array(1, 20, 3, 5));
+---------------------------+
|shuffle(array(1, 20, 3, 5))|
+---------------------------+
| [5, 1, 20, 3]|
+---------------------------+
SELECT shuffle(array(1, 20, null, 3));
+------------------------------+
|shuffle(array(1, 20, NULL, 3))|
+------------------------------+
| [1, NULL, 20, 3]|
+------------------------------+
-- slice
SELECT slice(array(1, 2, 3, 4), 2, 2);
+------------------------------+
|slice(array(1, 2, 3, 4), 2, 2)|
+------------------------------+
| [2, 3]|
+------------------------------+
SELECT slice(array(1, 2, 3, 4), -2, 2);
+-------------------------------+
|slice(array(1, 2, 3, 4), -2, 2)|
+-------------------------------+
| [3, 4]|
+-------------------------------+
-- sort_array
SELECT sort_array(array('b', 'd', null, 'c', 'a'), true);
+-----------------------------------------+
|sort_array(array(b, d, NULL, c, a), true)|
+-----------------------------------------+
| [NULL, a, b, c, d]|
+-----------------------------------------+
對應函數
| 函數 | 說明 |
|---|---|
| element_at(陣列, 索引) | 傳回陣列中給定 (以 1 為基底) 索引的元素。如果索引為 0，Spark 將擲回錯誤。如果索引 < 0，則從最後一個元素存取到第一個元素。如果索引超過陣列長度，且 `spark.sql.ansi.enabled` 設為 false，則函數傳回 NULL。如果 `spark.sql.ansi.enabled` 設為 true，則會對無效索引擲回 ArrayIndexOutOfBoundsException。 |
| element_at(對應, 鍵) | 傳回給定鍵的值。如果對應中不包含鍵，則函數傳回 NULL。 |
| map(鍵0, 值0, 鍵1, 值1, ...) | 使用給定的鍵/值對建立對應。 |
| map_concat(對應, ...) | 傳回所有給定對應的聯集 |
| map_contains_key(對應, 鍵) | 如果對應包含鍵，則傳回 true。 |
| map_entries(對應) | 傳回給定對應中所有條目的未排序陣列。 |
| map_from_arrays(鍵, 值) | 使用給定的鍵/值陣列對建立對應。鍵中的所有元素都不應為 null |
| map_from_entries(條目陣列) | 傳回從給定的條目陣列建立的對應。 |
| map_keys(對應) | 傳回包含映射鍵的無序陣列。 |
| map_values(map) | 傳回包含映射值的無序陣列。 |
| str_to_map(text[, pairDelim[, keyValueDelim]]) | 使用分隔符號將文字分割成鍵/值對後，建立映射。預設分隔符號為 `pairDelim` 的「,」和 `keyValueDelim` 的「:」。`pairDelim` 和 `keyValueDelim` 都視為正規表示式。 |
| try_element_at(array, index) | 傳回陣列中指定 (以 1 為基底) 索引的元素。如果索引為 0，Spark 會擲回錯誤。如果索引 < 0，則從最後一個元素存取到第一個元素。如果索引超過陣列長度，函數會永遠傳回 NULL。 |
| try_element_at(map, key) | 傳回指定鍵的值。如果映射中不包含鍵，函數會永遠傳回 NULL。 |
範例
-- element_at
SELECT element_at(array(1, 2, 3), 2);
+-----------------------------+
|element_at(array(1, 2, 3), 2)|
+-----------------------------+
| 2|
+-----------------------------+
SELECT element_at(map(1, 'a', 2, 'b'), 2);
+------------------------------+
|element_at(map(1, a, 2, b), 2)|
+------------------------------+
| b|
+------------------------------+
-- map
SELECT map(1.0, '2', 3.0, '4');
+--------------------+
| map(1.0, 2, 3.0, 4)|
+--------------------+
|{1.0 -> 2, 3.0 -> 4}|
+--------------------+
-- map_concat
SELECT map_concat(map(1, 'a', 2, 'b'), map(3, 'c'));
+--------------------------------------+
|map_concat(map(1, a, 2, b), map(3, c))|
+--------------------------------------+
| {1 -> a, 2 -> b, ...|
+--------------------------------------+
-- map_contains_key
SELECT map_contains_key(map(1, 'a', 2, 'b'), 1);
+------------------------------------+
|map_contains_key(map(1, a, 2, b), 1)|
+------------------------------------+
| true|
+------------------------------------+
SELECT map_contains_key(map(1, 'a', 2, 'b'), 3);
+------------------------------------+
|map_contains_key(map(1, a, 2, b), 3)|
+------------------------------------+
| false|
+------------------------------------+
-- map_entries
SELECT map_entries(map(1, 'a', 2, 'b'));
+----------------------------+
|map_entries(map(1, a, 2, b))|
+----------------------------+
| [{1, a}, {2, b}]|
+----------------------------+
-- map_from_arrays
SELECT map_from_arrays(array(1.0, 3.0), array('2', '4'));
+---------------------------------------------+
|map_from_arrays(array(1.0, 3.0), array(2, 4))|
+---------------------------------------------+
| {1.0 -> 2, 3.0 -> 4}|
+---------------------------------------------+
-- map_from_entries
SELECT map_from_entries(array(struct(1, 'a'), struct(2, 'b')));
+---------------------------------------------------+
|map_from_entries(array(struct(1, a), struct(2, b)))|
+---------------------------------------------------+
| {1 -> a, 2 -> b}|
+---------------------------------------------------+
-- map_keys
SELECT map_keys(map(1, 'a', 2, 'b'));
+-------------------------+
|map_keys(map(1, a, 2, b))|
+-------------------------+
| [1, 2]|
+-------------------------+
-- map_values
SELECT map_values(map(1, 'a', 2, 'b'));
+---------------------------+
|map_values(map(1, a, 2, b))|
+---------------------------+
| [a, b]|
+---------------------------+
-- str_to_map
SELECT str_to_map('a:1,b:2,c:3', ',', ':');
+-----------------------------+
|str_to_map(a:1,b:2,c:3, ,, :)|
+-----------------------------+
| {a -> 1, b -> 2, ...|
+-----------------------------+
SELECT str_to_map('a');
+-------------------+
|str_to_map(a, ,, :)|
+-------------------+
| {a -> NULL}|
+-------------------+
-- try_element_at
SELECT try_element_at(array(1, 2, 3), 2);
+---------------------------------+
|try_element_at(array(1, 2, 3), 2)|
+---------------------------------+
| 2|
+---------------------------------+
SELECT try_element_at(map(1, 'a', 2, 'b'), 2);
+----------------------------------+
|try_element_at(map(1, a, 2, b), 2)|
+----------------------------------+
| b|
+----------------------------------+
日期和時間戳記函數
| 函數 | 說明 |
|---|---|
| add_months(start_date, num_months) | 傳回在 `start_date` 之後 `num_months` 的日期。 |
| convert_timezone([sourceTz, ]targetTz, sourceTs) | 將沒有時區時間戳記 `sourceTs` 從 `sourceTz` 時區轉換成 `targetTz`。 |
| curdate() | 傳回查詢評估開始時的目前日期。在同一個查詢中呼叫 curdate，都會傳回相同的值。 |
| current_date() | 傳回查詢評估開始時的目前日期。在同一個查詢中呼叫 current_date，都會傳回相同的值。 |
| current_date | 傳回查詢評估開始時的目前日期。 |
| current_timestamp() | 傳回查詢評估開始時的目前時間戳記。在同一個查詢中呼叫 current_timestamp，都會傳回相同的值。 |
| current_timestamp | 傳回查詢評估開始時的目前時間戳記。 |
| current_timezone() | 傳回目前階段的當地時區。 |
| date_add(start_date, num_days) | 傳回在 `start_date` 之後 `num_days` 的日期。 |
| date_diff(endDate, startDate) | 傳回從 `startDate` 到 `endDate` 的天數。 |
| date_format(timestamp, fmt) | 將 `timestamp` 轉換成日期格式 `fmt` 指定的字串值。 |
| date_from_unix_date(days) | 從 1970-01-01 以來的日數建立日期。 |
| date_part(field, source) | 萃取日期/時間戳記或區間來源的一部分。 |
| date_sub(start_date, num_days) | 傳回在 `start_date` 之前 `num_days` 的日期。 |
| date_trunc(fmt, ts) | 傳回時間戳記 `ts`，並根據格式模型 `fmt` 指定的單位進行截斷。 |
| dateadd(start_date, num_days) | 傳回在 `start_date` 之後 `num_days` 的日期。 |
| datediff(endDate, startDate) | 傳回從 `startDate` 到 `endDate` 的天數。 |
| datepart(欄位, 來源) | 萃取日期/時間戳記或區間來源的一部分。 |
| day(日期) | 傳回日期/時間戳記的月份中的天數。 |
| dayofmonth(日期) | 傳回日期/時間戳記的月份中的天數。 |
| dayofweek(日期) | 傳回日期/時間戳記的星期幾 (1 = 星期日，2 = 星期一，...，7 = 星期六)。 |
| dayofyear(日期) | 傳回日期/時間戳記的年中的天數。 |
| extract(欄位 FROM 來源) | 萃取日期/時間戳記或區間來源的一部分。 |
| from_unixtime(unix_time[, fmt]) | 以指定的 `fmt` 傳回 `unix_time`。 |
| from_utc_timestamp(時間戳記, 時區) | 假設時間戳記為「2017-07-14 02:40:00.0」，將其解譯為 UTC 時間，並將該時間以時間戳記形式呈現於指定時區。例如，「GMT+1」會產生「2017-07-14 03:40:00.0」。 |
| hour(時間戳記) | 傳回字串/時間戳記的小時部分。 |
| last_day(日期) | 傳回日期所屬月份的最後一天。 |
| localtimestamp() | 傳回查詢評估開始時沒有時區的目前時間戳記。在同一個查詢中所有呼叫 localtimestamp 會傳回相同的值。 |
| localtimestamp | 傳回查詢評估開始時，在工作階段時區的目前當地日期時間。 |
| make_date(年, 月, 日) | 根據年、月和日欄位建立日期。如果組態 `spark.sql.ansi.enabled` 為 false，函數會在無效輸入時傳回 NULL。否則，它會擲回錯誤。 |
| make_dt_interval([天數[, 小時[, 分鐘[, 秒數]]]]) | 根據天數、小時、分鐘和秒數建立 DayTimeIntervalType 持續時間。 |
| make_interval([年數[, 月數[, 週數[, 天數[, 小時[, 分鐘[, 秒數]]]]]]]) | 根據年數、月數、週數、天數、小時、分鐘和秒數建立間隔。 |
| make_timestamp(年, 月, 日, 小時, 分鐘, 秒數[, 時區]) | 根據年、月、日、小時、分鐘、秒數和時區欄位建立時間戳記。結果資料類型與組態 `spark.sql.timestampType` 的值一致。如果組態 `spark.sql.ansi.enabled` 為 false，函數會在無效輸入時傳回 NULL。否則，它會擲回錯誤。 |
| make_timestamp_ltz(年, 月, 日, 小時, 分鐘, 秒數[, 時區]) | 根據年、月、日、小時、分鐘、秒數和時區欄位建立具有當地時區的目前時間戳記。如果組態 `spark.sql.ansi.enabled` 為 false，函數會在無效輸入時傳回 NULL。否則，它會擲回錯誤。 |
| make_timestamp_ntz(年, 月, 日, 小時, 分鐘, 秒數) | 根據年、月、日、小時、分鐘和秒數欄位建立當地日期時間。如果組態 `spark.sql.ansi.enabled` 為 false，函數會在無效輸入時傳回 NULL。否則，它會擲回錯誤。 |
| make_ym_interval([年數[, 月數]]) | 根據年數和月數建立年-月間隔。 |
| minute(時間戳記) | 傳回字串/時間戳記的分鐘部分。 |
| month(日期) | 傳回日期/時間戳記的月份部分。 |
| months_between(時間戳記1, 時間戳記2[, 四捨五入]) | 如果 `timestamp1` 晚於 `timestamp2`，則結果為正數。如果 `timestamp1` 和 `timestamp2` 在同一天，或兩者都是該月的最後一天，則會忽略時間。否則，差異會根據每月 31 天計算，並四捨五入到 8 位數，除非 roundOff=false。 |
| next_day(start_date, day_of_week) | 傳回晚於 `start_date` 且名稱為指定名稱的第一個日期。如果至少一個輸入參數為 NULL，則函數會傳回 NULL。當兩個輸入參數都不為 NULL，且 day_of_week 為無效輸入時，如果 `spark.sql.ansi.enabled` 設為 true，函數會擲出 IllegalArgumentException，否則會傳回 NULL。 |
| now() | 傳回查詢評估開始時的目前時間戳記。 |
| quarter(date) | 傳回日期的年份中的季度，範圍為 1 到 4。 |
| second(timestamp) | 傳回字串/時間戳記的秒元件。 |
| session_window(time_column, gap_duration) | 提供指定時間戳記指定欄位和間隔持續時間的會話視窗。請參閱結構化串流指南文件中的「時間視窗類型」以取得詳細說明和範例。 |
| timestamp_micros(microseconds) | 根據自 UTC 紀元以來的微秒數建立時間戳記。 |
| timestamp_millis(milliseconds) | 根據自 UTC 紀元以來的毫秒數建立時間戳記。 |
| timestamp_seconds(seconds) | 根據自 UTC 紀元以來的秒數（可以是分數）建立時間戳記。 |
| to_date(date_str[, fmt]) | 使用 `fmt` 表達式解析 `date_str` 表達式為日期。輸入無效時傳回 null。如果省略 `fmt`，預設會遵循轉換規則轉換為日期。 |
| to_timestamp(timestamp_str[, fmt]) | 使用 `fmt` 表達式解析 `timestamp_str` 表達式為時間戳記。輸入無效時傳回 null。如果省略 `fmt`，預設會遵循轉換規則轉換為時間戳記。結果資料類型與設定 `spark.sql.timestampType` 的值一致。 |
| to_timestamp_ltz(timestamp_str[, fmt]) | 使用 `fmt` 表達式解析 `timestamp_str` 表達式為具有當地時區的時間戳記。輸入無效時傳回 null。如果省略 `fmt`，預設會遵循轉換規則轉換為時間戳記。 |
| to_timestamp_ntz(timestamp_str[, fmt]) | 使用 `fmt` 表達式解析 `timestamp_str` 表達式為沒有時區的時間戳記。輸入無效時傳回 null。如果省略 `fmt`，預設會遵循轉換規則轉換為時間戳記。 |
| to_unix_timestamp(timeExp[, fmt]) | 傳回指定時間之 UNIX 時間戳記。 |
| to_utc_timestamp(timestamp, timezone) | 指定一個時間戳記，例如「2017-07-14 02:40:00.0」，並將其視為指定時區中的時間，然後將該時間呈現為 UTC 中的時間戳記。例如，「GMT+1」會產生「2017-07-14 01:40:00.0」。 |
| trunc(date, fmt) | 傳回 `date`，其中一天的時間部分已根據格式模型 `fmt` 指定的單位進行截斷。 |
| try_to_timestamp(timestamp_str[, fmt]) | 使用 `fmt` 表達式將 `timestamp_str` 表達式解析為時間戳記。此函數在啟用/未啟用 ANSI SQL 模式時，對於無效的輸入總是傳回 null。預設情況下，如果省略 `fmt`，則遵循轉換規則為時間戳記。結果資料類型與設定 `spark.sql.timestampType` 的值一致。 |
| unix_date(date) | 傳回自 1970-01-01 以來的日數。 |
| unix_micros(timestamp) | 傳回自 1970-01-01 00:00:00 UTC 以來的微秒數。 |
| unix_millis(timestamp) | 傳回自 1970-01-01 00:00:00 UTC 以來的毫秒數。截斷較高精度的層級。 |
| unix_seconds(timestamp) | 傳回自 1970-01-01 00:00:00 UTC 以來的秒數。截斷較高精度的層級。 |
| unix_timestamp([timeExp[, fmt]]) | 傳回目前或指定時間的 UNIX 時間戳記。 |
| weekday(date) | 傳回日期/時間戳記的星期幾（0 = 星期一，1 = 星期二，...，6 = 星期日）。 |
| weekofyear(date) | 傳回指定日期的星期幾。星期從星期一開始，而星期 1 是有 >3 天的第一個星期。 |
| window(time_column, window_duration[, slide_duration[, start_time]]) | 根據指定時間戳記欄位，將列分類到一個或多個時間視窗中。視窗開始時間包含在內，但視窗結束時間不包含在內，例如 12:05 會在視窗 [12:05,12:10) 中，但不在 [12:00,12:05) 中。視窗支援微秒精度。不支援月份順序的視窗。請參閱結構化串流指南文件中的「事件時間視窗作業」，以取得詳細說明和範例。 |
| window_time(window_column) | 從時間/工作階段視窗欄位中擷取時間值，可用於視窗的事件時間值。擷取的時間為 (window.end - 1)，這反映了聚合視窗具有獨佔的上限 - [start, end) 請參閱結構化串流指南文件中的「事件時間視窗作業」，以取得詳細說明和範例。 |
| year(date) | 傳回日期/時間戳記的年份部分。 |
範例
-- add_months
SELECT add_months('2016-08-31', 1);
+-------------------------+
|add_months(2016-08-31, 1)|
+-------------------------+
| 2016-09-30|
+-------------------------+
-- convert_timezone
SELECT convert_timezone('Europe/Brussels', 'America/Los_Angeles', timestamp_ntz'2021-12-06 00:00:00');
+-------------------------------------------------------------------------------------------+
|convert_timezone(Europe/Brussels, America/Los_Angeles, TIMESTAMP_NTZ '2021-12-06 00:00:00')|
+-------------------------------------------------------------------------------------------+
| 2021-12-05 15:00:00|
+-------------------------------------------------------------------------------------------+
SELECT convert_timezone('Europe/Brussels', timestamp_ntz'2021-12-05 15:00:00');
+------------------------------------------------------------------------------------------+
|convert_timezone(current_timezone(), Europe/Brussels, TIMESTAMP_NTZ '2021-12-05 15:00:00')|
+------------------------------------------------------------------------------------------+
| 2021-12-05 07:00:00|
+------------------------------------------------------------------------------------------+
-- curdate
SELECT curdate();
+--------------+
|current_date()|
+--------------+
| 2024-02-24|
+--------------+
-- current_date
SELECT current_date();
+--------------+
|current_date()|
+--------------+
| 2024-02-24|
+--------------+
SELECT current_date;
+--------------+
|current_date()|
+--------------+
| 2024-02-24|
+--------------+
-- current_timestamp
SELECT current_timestamp();
+--------------------+
| current_timestamp()|
+--------------------+
|2024-02-24 16:36:...|
+--------------------+
SELECT current_timestamp;
+--------------------+
| current_timestamp()|
+--------------------+
|2024-02-24 16:36:...|
+--------------------+
-- current_timezone
SELECT current_timezone();
+------------------+
|current_timezone()|
+------------------+
| Asia/Seoul|
+------------------+
-- date_add
SELECT date_add('2016-07-30', 1);
+-----------------------+
|date_add(2016-07-30, 1)|
+-----------------------+
| 2016-07-31|
+-----------------------+
-- date_diff
SELECT date_diff('2009-07-31', '2009-07-30');
+---------------------------------+
|date_diff(2009-07-31, 2009-07-30)|
+---------------------------------+
| 1|
+---------------------------------+
SELECT date_diff('2009-07-30', '2009-07-31');
+---------------------------------+
|date_diff(2009-07-30, 2009-07-31)|
+---------------------------------+
| -1|
+---------------------------------+
-- date_format
SELECT date_format('2016-04-08', 'y');
+--------------------------+
|date_format(2016-04-08, y)|
+--------------------------+
| 2016|
+--------------------------+
-- date_from_unix_date
SELECT date_from_unix_date(1);
+----------------------+
|date_from_unix_date(1)|
+----------------------+
| 1970-01-02|
+----------------------+
-- date_part
SELECT date_part('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456');
+-------------------------------------------------------+
|date_part(YEAR, TIMESTAMP '2019-08-12 01:00:00.123456')|
+-------------------------------------------------------+
| 2019|
+-------------------------------------------------------+
SELECT date_part('week', timestamp'2019-08-12 01:00:00.123456');
+-------------------------------------------------------+
|date_part(week, TIMESTAMP '2019-08-12 01:00:00.123456')|
+-------------------------------------------------------+
| 33|
+-------------------------------------------------------+
SELECT date_part('doy', DATE'2019-08-12');
+---------------------------------+
|date_part(doy, DATE '2019-08-12')|
+---------------------------------+
| 224|
+---------------------------------+
SELECT date_part('SECONDS', timestamp'2019-10-01 00:00:01.000001');
+----------------------------------------------------------+
|date_part(SECONDS, TIMESTAMP '2019-10-01 00:00:01.000001')|
+----------------------------------------------------------+
| 1.000001|
+----------------------------------------------------------+
SELECT date_part('days', interval 5 days 3 hours 7 minutes);
+-------------------------------------------------+
|date_part(days, INTERVAL '5 03:07' DAY TO MINUTE)|
+-------------------------------------------------+
| 5|
+-------------------------------------------------+
SELECT date_part('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
+-------------------------------------------------------------+
|date_part(seconds, INTERVAL '05:00:30.001001' HOUR TO SECOND)|
+-------------------------------------------------------------+
| 30.001001|
+-------------------------------------------------------------+
SELECT date_part('MONTH', INTERVAL '2021-11' YEAR TO MONTH);
+--------------------------------------------------+
|date_part(MONTH, INTERVAL '2021-11' YEAR TO MONTH)|
+--------------------------------------------------+
| 11|
+--------------------------------------------------+
SELECT date_part('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND);
+---------------------------------------------------------------+
|date_part(MINUTE, INTERVAL '123 23:55:59.002001' DAY TO SECOND)|
+---------------------------------------------------------------+
| 55|
+---------------------------------------------------------------+
-- date_sub
SELECT date_sub('2016-07-30', 1);
+-----------------------+
|date_sub(2016-07-30, 1)|
+-----------------------+
| 2016-07-29|
+-----------------------+
-- date_trunc
SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');
+-----------------------------------------+
|date_trunc(YEAR, 2015-03-05T09:32:05.359)|
+-----------------------------------------+
| 2015-01-01 00:00:00|
+-----------------------------------------+
SELECT date_trunc('MM', '2015-03-05T09:32:05.359');
+---------------------------------------+
|date_trunc(MM, 2015-03-05T09:32:05.359)|
+---------------------------------------+
| 2015-03-01 00:00:00|
+---------------------------------------+
SELECT date_trunc('DD', '2015-03-05T09:32:05.359');
+---------------------------------------+
|date_trunc(DD, 2015-03-05T09:32:05.359)|
+---------------------------------------+
| 2015-03-05 00:00:00|
+---------------------------------------+
SELECT date_trunc('HOUR', '2015-03-05T09:32:05.359');
+-----------------------------------------+
|date_trunc(HOUR, 2015-03-05T09:32:05.359)|
+-----------------------------------------+
| 2015-03-05 09:00:00|
+-----------------------------------------+
SELECT date_trunc('MILLISECOND', '2015-03-05T09:32:05.123456');
+---------------------------------------------------+
|date_trunc(MILLISECOND, 2015-03-05T09:32:05.123456)|
+---------------------------------------------------+
| 2015-03-05 09:32:...|
+---------------------------------------------------+
-- dateadd
SELECT dateadd('2016-07-30', 1);
+-----------------------+
|date_add(2016-07-30, 1)|
+-----------------------+
| 2016-07-31|
+-----------------------+
-- datediff
SELECT datediff('2009-07-31', '2009-07-30');
+--------------------------------+
|datediff(2009-07-31, 2009-07-30)|
+--------------------------------+
| 1|
+--------------------------------+
SELECT datediff('2009-07-30', '2009-07-31');
+--------------------------------+
|datediff(2009-07-30, 2009-07-31)|
+--------------------------------+
| -1|
+--------------------------------+
-- datepart
SELECT datepart('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456');
+----------------------------------------------------------+
|datepart(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+----------------------------------------------------------+
| 2019|
+----------------------------------------------------------+
SELECT datepart('week', timestamp'2019-08-12 01:00:00.123456');
+----------------------------------------------------------+
|datepart(week FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+----------------------------------------------------------+
| 33|
+----------------------------------------------------------+
SELECT datepart('doy', DATE'2019-08-12');
+------------------------------------+
|datepart(doy FROM DATE '2019-08-12')|
+------------------------------------+
| 224|
+------------------------------------+
SELECT datepart('SECONDS', timestamp'2019-10-01 00:00:01.000001');
+-------------------------------------------------------------+
|datepart(SECONDS FROM TIMESTAMP '2019-10-01 00:00:01.000001')|
+-------------------------------------------------------------+
| 1.000001|
+-------------------------------------------------------------+
SELECT datepart('days', interval 5 days 3 hours 7 minutes);
+----------------------------------------------------+
|datepart(days FROM INTERVAL '5 03:07' DAY TO MINUTE)|
+----------------------------------------------------+
| 5|
+----------------------------------------------------+
SELECT datepart('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
+----------------------------------------------------------------+
|datepart(seconds FROM INTERVAL '05:00:30.001001' HOUR TO SECOND)|
+----------------------------------------------------------------+
| 30.001001|
+----------------------------------------------------------------+
SELECT datepart('MONTH', INTERVAL '2021-11' YEAR TO MONTH);
+-----------------------------------------------------+
|datepart(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH)|
+-----------------------------------------------------+
| 11|
+-----------------------------------------------------+
SELECT datepart('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND);
+------------------------------------------------------------------+
|datepart(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND)|
+------------------------------------------------------------------+
| 55|
+------------------------------------------------------------------+
-- day
SELECT day('2009-07-30');
+---------------+
|day(2009-07-30)|
+---------------+
| 30|
+---------------+
-- dayofmonth
SELECT dayofmonth('2009-07-30');
+----------------------+
|dayofmonth(2009-07-30)|
+----------------------+
| 30|
+----------------------+
-- dayofweek
SELECT dayofweek('2009-07-30');
+---------------------+
|dayofweek(2009-07-30)|
+---------------------+
| 5|
+---------------------+
-- dayofyear
SELECT dayofyear('2016-04-09');
+---------------------+
|dayofyear(2016-04-09)|
+---------------------+
| 100|
+---------------------+
-- extract
SELECT extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456');
+---------------------------------------------------------+
|extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+---------------------------------------------------------+
| 2019|
+---------------------------------------------------------+
SELECT extract(week FROM timestamp'2019-08-12 01:00:00.123456');
+---------------------------------------------------------+
|extract(week FROM TIMESTAMP '2019-08-12 01:00:00.123456')|
+---------------------------------------------------------+
| 33|
+---------------------------------------------------------+
SELECT extract(doy FROM DATE'2019-08-12');
+-----------------------------------+
|extract(doy FROM DATE '2019-08-12')|
+-----------------------------------+
| 224|
+-----------------------------------+
SELECT extract(SECONDS FROM timestamp'2019-10-01 00:00:01.000001');
+------------------------------------------------------------+
|extract(SECONDS FROM TIMESTAMP '2019-10-01 00:00:01.000001')|
+------------------------------------------------------------+
| 1.000001|
+------------------------------------------------------------+
SELECT extract(days FROM interval 5 days 3 hours 7 minutes);
+---------------------------------------------------+
|extract(days FROM INTERVAL '5 03:07' DAY TO MINUTE)|
+---------------------------------------------------+
| 5|
+---------------------------------------------------+
SELECT extract(seconds FROM interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
+---------------------------------------------------------------+
|extract(seconds FROM INTERVAL '05:00:30.001001' HOUR TO SECOND)|
+---------------------------------------------------------------+
| 30.001001|
+---------------------------------------------------------------+
SELECT extract(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH);
+----------------------------------------------------+
|extract(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH)|
+----------------------------------------------------+
| 11|
+----------------------------------------------------+
SELECT extract(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND);
+-----------------------------------------------------------------+
|extract(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND)|
+-----------------------------------------------------------------+
| 55|
+-----------------------------------------------------------------+
-- from_unixtime
SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
+-------------------------------------+
|from_unixtime(0, yyyy-MM-dd HH:mm:ss)|
+-------------------------------------+
| 1970-01-01 09:00:00|
+-------------------------------------+
SELECT from_unixtime(0);
+-------------------------------------+
|from_unixtime(0, yyyy-MM-dd HH:mm:ss)|
+-------------------------------------+
| 1970-01-01 09:00:00|
+-------------------------------------+
-- from_utc_timestamp
SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul');
+------------------------------------------+
|from_utc_timestamp(2016-08-31, Asia/Seoul)|
+------------------------------------------+
| 2016-08-31 09:00:00|
+------------------------------------------+
-- hour
SELECT hour('2009-07-30 12:58:59');
+-------------------------+
|hour(2009-07-30 12:58:59)|
+-------------------------+
| 12|
+-------------------------+
-- last_day
SELECT last_day('2009-01-12');
+--------------------+
|last_day(2009-01-12)|
+--------------------+
| 2009-01-31|
+--------------------+
-- localtimestamp
SELECT localtimestamp();
+--------------------+
| localtimestamp()|
+--------------------+
|2024-02-24 16:36:...|
+--------------------+
-- make_date
SELECT make_date(2013, 7, 15);
+----------------------+
|make_date(2013, 7, 15)|
+----------------------+
| 2013-07-15|
+----------------------+
SELECT make_date(2019, 7, NULL);
+------------------------+
|make_date(2019, 7, NULL)|
+------------------------+
| NULL|
+------------------------+
-- make_dt_interval
SELECT make_dt_interval(1, 12, 30, 01.001001);
+-------------------------------------+
|make_dt_interval(1, 12, 30, 1.001001)|
+-------------------------------------+
| INTERVAL '1 12:30...|
+-------------------------------------+
SELECT make_dt_interval(2);
+-----------------------------------+
|make_dt_interval(2, 0, 0, 0.000000)|
+-----------------------------------+
| INTERVAL '2 00:00...|
+-----------------------------------+
SELECT make_dt_interval(100, null, 3);
+----------------------------------------+
|make_dt_interval(100, NULL, 3, 0.000000)|
+----------------------------------------+
| NULL|
+----------------------------------------+
-- make_interval
SELECT make_interval(100, 11, 1, 1, 12, 30, 01.001001);
+----------------------------------------------+
|make_interval(100, 11, 1, 1, 12, 30, 1.001001)|
+----------------------------------------------+
| 100 years 11 mont...|
+----------------------------------------------+
SELECT make_interval(100, null, 3);
+----------------------------------------------+
|make_interval(100, NULL, 3, 0, 0, 0, 0.000000)|
+----------------------------------------------+
| NULL|
+----------------------------------------------+
SELECT make_interval(0, 1, 0, 1, 0, 0, 100.000001);
+-------------------------------------------+
|make_interval(0, 1, 0, 1, 0, 0, 100.000001)|
+-------------------------------------------+
| 1 months 1 days 1...|
+-------------------------------------------+
-- make_timestamp
SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
+-------------------------------------------+
|make_timestamp(2014, 12, 28, 6, 30, 45.887)|
+-------------------------------------------+
| 2014-12-28 06:30:...|
+-------------------------------------------+
SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887, 'CET');
+------------------------------------------------+
|make_timestamp(2014, 12, 28, 6, 30, 45.887, CET)|
+------------------------------------------------+
| 2014-12-28 14:30:...|
+------------------------------------------------+
SELECT make_timestamp(2019, 6, 30, 23, 59, 60);
+---------------------------------------+
|make_timestamp(2019, 6, 30, 23, 59, 60)|
+---------------------------------------+
| 2019-07-01 00:00:00|
+---------------------------------------+
SELECT make_timestamp(2019, 6, 30, 23, 59, 1);
+--------------------------------------+
|make_timestamp(2019, 6, 30, 23, 59, 1)|
+--------------------------------------+
| 2019-06-30 23:59:01|
+--------------------------------------+
SELECT make_timestamp(null, 7, 22, 15, 30, 0);
+--------------------------------------+
|make_timestamp(NULL, 7, 22, 15, 30, 0)|
+--------------------------------------+
| NULL|
+--------------------------------------+
-- make_timestamp_ltz
SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887);
+-----------------------------------------------+
|make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887)|
+-----------------------------------------------+
| 2014-12-28 06:30:...|
+-----------------------------------------------+
SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, 'CET');
+----------------------------------------------------+
|make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, CET)|
+----------------------------------------------------+
| 2014-12-28 14:30:...|
+----------------------------------------------------+
SELECT make_timestamp_ltz(2019, 6, 30, 23, 59, 60);
+-------------------------------------------+
|make_timestamp_ltz(2019, 6, 30, 23, 59, 60)|
+-------------------------------------------+
| 2019-07-01 00:00:00|
+-------------------------------------------+
SELECT make_timestamp_ltz(null, 7, 22, 15, 30, 0);
+------------------------------------------+
|make_timestamp_ltz(NULL, 7, 22, 15, 30, 0)|
+------------------------------------------+
| NULL|
+------------------------------------------+
-- make_timestamp_ntz
SELECT make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887);
+-----------------------------------------------+
|make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887)|
+-----------------------------------------------+
| 2014-12-28 06:30:...|
+-----------------------------------------------+
SELECT make_timestamp_ntz(2019, 6, 30, 23, 59, 60);
+-------------------------------------------+
|make_timestamp_ntz(2019, 6, 30, 23, 59, 60)|
+-------------------------------------------+
| 2019-07-01 00:00:00|
+-------------------------------------------+
SELECT make_timestamp_ntz(null, 7, 22, 15, 30, 0);
+------------------------------------------+
|make_timestamp_ntz(NULL, 7, 22, 15, 30, 0)|
+------------------------------------------+
| NULL|
+------------------------------------------+
-- make_ym_interval
SELECT make_ym_interval(1, 2);
+----------------------+
|make_ym_interval(1, 2)|
+----------------------+
| INTERVAL '1-2' YE...|
+----------------------+
SELECT make_ym_interval(1, 0);
+----------------------+
|make_ym_interval(1, 0)|
+----------------------+
| INTERVAL '1-0' YE...|
+----------------------+
SELECT make_ym_interval(-1, 1);
+-----------------------+
|make_ym_interval(-1, 1)|
+-----------------------+
| INTERVAL '-0-11' ...|
+-----------------------+
SELECT make_ym_interval(2);
+----------------------+
|make_ym_interval(2, 0)|
+----------------------+
| INTERVAL '2-0' YE...|
+----------------------+
-- minute
SELECT minute('2009-07-30 12:58:59');
+---------------------------+
|minute(2009-07-30 12:58:59)|
+---------------------------+
| 58|
+---------------------------+
-- month
SELECT month('2016-07-30');
+-----------------+
|month(2016-07-30)|
+-----------------+
| 7|
+-----------------+
-- months_between
SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
+-----------------------------------------------------+
|months_between(1997-02-28 10:30:00, 1996-10-30, true)|
+-----------------------------------------------------+
| 3.94959677|
+-----------------------------------------------------+
SELECT months_between('1997-02-28 10:30:00', '1996-10-30', false);
+------------------------------------------------------+
|months_between(1997-02-28 10:30:00, 1996-10-30, false)|
+------------------------------------------------------+
| 3.9495967741935485|
+------------------------------------------------------+
-- next_day
SELECT next_day('2015-01-14', 'TU');
+------------------------+
|next_day(2015-01-14, TU)|
+------------------------+
| 2015-01-20|
+------------------------+
-- now
SELECT now();
+--------------------+
| now()|
+--------------------+
|2024-02-24 16:36:...|
+--------------------+
-- quarter
SELECT quarter('2016-08-31');
+-------------------+
|quarter(2016-08-31)|
+-------------------+
| 3|
+-------------------+
-- second
SELECT second('2009-07-30 12:58:59');
+---------------------------+
|second(2009-07-30 12:58:59)|
+---------------------------+
| 59|
+---------------------------+
-- session_window
SELECT a, session_window.start, session_window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:10:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, session_window(b, '5 minutes') ORDER BY a, start;
+---+-------------------+-------------------+---+
| a| start| end|cnt|
+---+-------------------+-------------------+---+
| A1|2021-01-01 00:00:00|2021-01-01 00:09:30| 2|
| A1|2021-01-01 00:10:00|2021-01-01 00:15:00| 1|
| A2|2021-01-01 00:01:00|2021-01-01 00:06:00| 1|
+---+-------------------+-------------------+---+
SELECT a, session_window.start, session_window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:10:00'), ('A2', '2021-01-01 00:01:00'), ('A2', '2021-01-01 00:04:30') AS tab(a, b) GROUP by a, session_window(b, CASE WHEN a = 'A1' THEN '5 minutes' WHEN a = 'A2' THEN '1 minute' ELSE '10 minutes' END) ORDER BY a, start;
+---+-------------------+-------------------+---+
| a| start| end|cnt|
+---+-------------------+-------------------+---+
| A1|2021-01-01 00:00:00|2021-01-01 00:09:30| 2|
| A1|2021-01-01 00:10:00|2021-01-01 00:15:00| 1|
| A2|2021-01-01 00:01:00|2021-01-01 00:02:00| 1|
| A2|2021-01-01 00:04:30|2021-01-01 00:05:30| 1|
+---+-------------------+-------------------+---+
-- timestamp_micros
SELECT timestamp_micros(1230219000123123);
+----------------------------------+
|timestamp_micros(1230219000123123)|
+----------------------------------+
| 2008-12-26 00:30:...|
+----------------------------------+
-- timestamp_millis
SELECT timestamp_millis(1230219000123);
+-------------------------------+
|timestamp_millis(1230219000123)|
+-------------------------------+
| 2008-12-26 00:30:...|
+-------------------------------+
-- timestamp_seconds
SELECT timestamp_seconds(1230219000);
+-----------------------------+
|timestamp_seconds(1230219000)|
+-----------------------------+
| 2008-12-26 00:30:00|
+-----------------------------+
SELECT timestamp_seconds(1230219000.123);
+---------------------------------+
|timestamp_seconds(1230219000.123)|
+---------------------------------+
| 2008-12-26 00:30:...|
+---------------------------------+
-- to_date
SELECT to_date('2009-07-30 04:17:52');
+----------------------------+
|to_date(2009-07-30 04:17:52)|
+----------------------------+
| 2009-07-30|
+----------------------------+
SELECT to_date('2016-12-31', 'yyyy-MM-dd');
+-------------------------------+
|to_date(2016-12-31, yyyy-MM-dd)|
+-------------------------------+
| 2016-12-31|
+-------------------------------+
-- to_timestamp
SELECT to_timestamp('2016-12-31 00:12:00');
+---------------------------------+
|to_timestamp(2016-12-31 00:12:00)|
+---------------------------------+
| 2016-12-31 00:12:00|
+---------------------------------+
SELECT to_timestamp('2016-12-31', 'yyyy-MM-dd');
+------------------------------------+
|to_timestamp(2016-12-31, yyyy-MM-dd)|
+------------------------------------+
| 2016-12-31 00:00:00|
+------------------------------------+
-- to_timestamp_ltz
SELECT to_timestamp_ltz('2016-12-31 00:12:00');
+-------------------------------------+
|to_timestamp_ltz(2016-12-31 00:12:00)|
+-------------------------------------+
| 2016-12-31 00:12:00|
+-------------------------------------+
SELECT to_timestamp_ltz('2016-12-31', 'yyyy-MM-dd');
+----------------------------------------+
|to_timestamp_ltz(2016-12-31, yyyy-MM-dd)|
+----------------------------------------+
| 2016-12-31 00:00:00|
+----------------------------------------+
-- to_timestamp_ntz
SELECT to_timestamp_ntz('2016-12-31 00:12:00');
+-------------------------------------+
|to_timestamp_ntz(2016-12-31 00:12:00)|
+-------------------------------------+
| 2016-12-31 00:12:00|
+-------------------------------------+
SELECT to_timestamp_ntz('2016-12-31', 'yyyy-MM-dd');
+----------------------------------------+
|to_timestamp_ntz(2016-12-31, yyyy-MM-dd)|
+----------------------------------------+
| 2016-12-31 00:00:00|
+----------------------------------------+
-- to_unix_timestamp
SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd');
+-----------------------------------------+
|to_unix_timestamp(2016-04-08, yyyy-MM-dd)|
+-----------------------------------------+
| 1460041200|
+-----------------------------------------+
-- to_utc_timestamp
SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul');
+----------------------------------------+
|to_utc_timestamp(2016-08-31, Asia/Seoul)|
+----------------------------------------+
| 2016-08-30 15:00:00|
+----------------------------------------+
-- trunc
SELECT trunc('2019-08-04', 'week');
+-----------------------+
|trunc(2019-08-04, week)|
+-----------------------+
| 2019-07-29|
+-----------------------+
SELECT trunc('2019-08-04', 'quarter');
+--------------------------+
|trunc(2019-08-04, quarter)|
+--------------------------+
| 2019-07-01|
+--------------------------+
SELECT trunc('2009-02-12', 'MM');
+---------------------+
|trunc(2009-02-12, MM)|
+---------------------+
| 2009-02-01|
+---------------------+
SELECT trunc('2015-10-27', 'YEAR');
+-----------------------+
|trunc(2015-10-27, YEAR)|
+-----------------------+
| 2015-01-01|
+-----------------------+
-- try_to_timestamp
SELECT try_to_timestamp('2016-12-31 00:12:00');
+-------------------------------------+
|try_to_timestamp(2016-12-31 00:12:00)|
+-------------------------------------+
| 2016-12-31 00:12:00|
+-------------------------------------+
SELECT try_to_timestamp('2016-12-31', 'yyyy-MM-dd');
+----------------------------------------+
|try_to_timestamp(2016-12-31, yyyy-MM-dd)|
+----------------------------------------+
| 2016-12-31 00:00:00|
+----------------------------------------+
SELECT try_to_timestamp('foo', 'yyyy-MM-dd');
+---------------------------------+
|try_to_timestamp(foo, yyyy-MM-dd)|
+---------------------------------+
| NULL|
+---------------------------------+
-- unix_date
SELECT unix_date(DATE("1970-01-02"));
+---------------------+
|unix_date(1970-01-02)|
+---------------------+
| 1|
+---------------------+
-- unix_micros
SELECT unix_micros(TIMESTAMP('1970-01-01 00:00:01Z'));
+---------------------------------+
|unix_micros(1970-01-01 00:00:01Z)|
+---------------------------------+
| 1000000|
+---------------------------------+
-- unix_millis
SELECT unix_millis(TIMESTAMP('1970-01-01 00:00:01Z'));
+---------------------------------+
|unix_millis(1970-01-01 00:00:01Z)|
+---------------------------------+
| 1000|
+---------------------------------+
-- unix_seconds
SELECT unix_seconds(TIMESTAMP('1970-01-01 00:00:01Z'));
+----------------------------------+
|unix_seconds(1970-01-01 00:00:01Z)|
+----------------------------------+
| 1|
+----------------------------------+
-- unix_timestamp
SELECT unix_timestamp();
+--------------------------------------------------------+
|unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss)|
+--------------------------------------------------------+
| 1708760216|
+--------------------------------------------------------+
SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
+--------------------------------------+
|unix_timestamp(2016-04-08, yyyy-MM-dd)|
+--------------------------------------+
| 1460041200|
+--------------------------------------+
-- weekday
SELECT weekday('2009-07-30');
+-------------------+
|weekday(2009-07-30)|
+-------------------+
| 3|
+-------------------+
-- weekofyear
SELECT weekofyear('2008-02-20');
+----------------------+
|weekofyear(2008-02-20)|
+----------------------+
| 8|
+----------------------+
-- window
SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, start;
+---+-------------------+-------------------+---+
| a| start| end|cnt|
+---+-------------------+-------------------+---+
| A1|2021-01-01 00:00:00|2021-01-01 00:05:00| 2|
| A1|2021-01-01 00:05:00|2021-01-01 00:10:00| 1|
| A2|2021-01-01 00:00:00|2021-01-01 00:05:00| 1|
+---+-------------------+-------------------+---+
SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '10 minutes', '5 minutes') ORDER BY a, start;
+---+-------------------+-------------------+---+
| a| start| end|cnt|
+---+-------------------+-------------------+---+
| A1|2020-12-31 23:55:00|2021-01-01 00:05:00| 2|
| A1|2021-01-01 00:00:00|2021-01-01 00:10:00| 3|
| A1|2021-01-01 00:05:00|2021-01-01 00:15:00| 1|
| A2|2020-12-31 23:55:00|2021-01-01 00:05:00| 1|
| A2|2021-01-01 00:00:00|2021-01-01 00:10:00| 1|
+---+-------------------+-------------------+---+
-- window_time
SELECT a, window.start as start, window.end as end, window_time(window), cnt FROM (SELECT a, window, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, window.start);
+---+-------------------+-------------------+--------------------+---+
| a| start| end| window_time(window)|cnt|
+---+-------------------+-------------------+--------------------+---+
| A1|2021-01-01 00:00:00|2021-01-01 00:05:00|2021-01-01 00:04:...| 2|
| A1|2021-01-01 00:05:00|2021-01-01 00:10:00|2021-01-01 00:09:...| 1|
| A2|2021-01-01 00:00:00|2021-01-01 00:05:00|2021-01-01 00:04:...| 1|
+---+-------------------+-------------------+--------------------+---+
-- year
SELECT year('2016-07-30');
+----------------+
|year(2016-07-30)|
+----------------+
| 2016|
+----------------+
JSON 函數
| 函數 | 說明 |
|---|---|
| from_json(jsonStr, schema[, options]) | 傳回一個具有給定 `jsonStr` 和 `schema` 的結構值。 |
| get_json_object(json_txt, path) | 從 `path` 萃取 JSON 物件。 |
| json_array_length(jsonArray) | 傳回最外層 JSON 陣列中的元素數量。 |
| json_object_keys(json_object) | 傳回最外層 JSON 物件的所有金鑰，作為陣列。 |
| json_tuple(jsonStr, p1, p2, ..., pn) | 傳回一個像 `get_json_object` 函數的元組，但它會採用多個名稱。所有輸入參數和輸出欄位類型都是字串。 |
| schema_of_json(json[, options]) | 傳回 JSON 字串的 DDL 格式的結構。 |
| to_json(expr[, options]) | 傳回一個具有給定結構值的 JSON 字串 |
範例
-- from_json
SELECT from_json('{"a":1, "b":0.8}', 'a INT, b DOUBLE');
+---------------------------+
|from_json({"a":1, "b":0.8})|
+---------------------------+
| {1, 0.8}|
+---------------------------+
SELECT from_json('{"time":"26/08/2015"}', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
+--------------------------------+
|from_json({"time":"26/08/2015"})|
+--------------------------------+
| {2015-08-26 00:00...|
+--------------------------------+
SELECT from_json('{"teacher": "Alice", "student": [{"name": "Bob", "rank": 1}, {"name": "Charlie", "rank": 2}]}', 'STRUCT<teacher: STRING, student: ARRAY<STRUCT<name: STRING, rank: INT>>>');
+--------------------------------------------------------------------------------------------------------+
|from_json({"teacher": "Alice", "student": [{"name": "Bob", "rank": 1}, {"name": "Charlie", "rank": 2}]})|
+--------------------------------------------------------------------------------------------------------+
| {Alice, [{Bob, 1}...|
+--------------------------------------------------------------------------------------------------------+
-- get_json_object
SELECT get_json_object('{"a":"b"}', '$.a');
+-------------------------------+
|get_json_object({"a":"b"}, $.a)|
+-------------------------------+
| b|
+-------------------------------+
-- json_array_length
SELECT json_array_length('[1,2,3,4]');
+----------------------------+
|json_array_length([1,2,3,4])|
+----------------------------+
| 4|
+----------------------------+
SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
+------------------------------------------------+
|json_array_length([1,2,3,{"f1":1,"f2":[5,6]},4])|
+------------------------------------------------+
| 5|
+------------------------------------------------+
SELECT json_array_length('[1,2');
+-----------------------+
|json_array_length([1,2)|
+-----------------------+
| NULL|
+-----------------------+
-- json_object_keys
SELECT json_object_keys('{}');
+--------------------+
|json_object_keys({})|
+--------------------+
| []|
+--------------------+
SELECT json_object_keys('{"key": "value"}');
+----------------------------------+
|json_object_keys({"key": "value"})|
+----------------------------------+
| [key]|
+----------------------------------+
SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
+--------------------------------------------------------+
|json_object_keys({"f1":"abc","f2":{"f3":"a", "f4":"b"}})|
+--------------------------------------------------------+
| [f1, f2]|
+--------------------------------------------------------+
-- json_tuple
SELECT json_tuple('{"a":1, "b":2}', 'a', 'b');
+---+---+
| c0| c1|
+---+---+
| 1| 2|
+---+---+
-- schema_of_json
SELECT schema_of_json('[{"col":0}]');
+---------------------------+
|schema_of_json([{"col":0}])|
+---------------------------+
| ARRAY<STRUCT<col:...|
+---------------------------+
SELECT schema_of_json('[{"col":01}]', map('allowNumericLeadingZeros', 'true'));
+----------------------------+
|schema_of_json([{"col":01}])|
+----------------------------+
| ARRAY<STRUCT<col:...|
+----------------------------+
-- to_json
SELECT to_json(named_struct('a', 1, 'b', 2));
+---------------------------------+
|to_json(named_struct(a, 1, b, 2))|
+---------------------------------+
| {"a":1,"b":2}|
+---------------------------------+
SELECT to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
+-----------------------------------------------------------------+
|to_json(named_struct(time, to_timestamp(2015-08-26, yyyy-MM-dd)))|
+-----------------------------------------------------------------+
| {"time":"26/08/20...|
+-----------------------------------------------------------------+
SELECT to_json(array(named_struct('a', 1, 'b', 2)));
+----------------------------------------+
|to_json(array(named_struct(a, 1, b, 2)))|
+----------------------------------------+
| [{"a":1,"b":2}]|
+----------------------------------------+
SELECT to_json(map('a', named_struct('b', 1)));
+-----------------------------------+
|to_json(map(a, named_struct(b, 1)))|
+-----------------------------------+
| {"a":{"b":1}}|
+-----------------------------------+
SELECT to_json(map(named_struct('a', 1),named_struct('b', 2)));
+----------------------------------------------------+
|to_json(map(named_struct(a, 1), named_struct(b, 2)))|
+----------------------------------------------------+
| {"[1]":{"b":2}}|
+----------------------------------------------------+
SELECT to_json(map('a', 1));
+------------------+
|to_json(map(a, 1))|
+------------------+
| {"a":1}|
+------------------+
SELECT to_json(array(map('a', 1)));
+-------------------------+
|to_json(array(map(a, 1)))|
+-------------------------+
| [{"a":1}]|
+-------------------------+
數學函數
| 函數 | 說明 |
|---|---|
| expr1 % expr2 | 傳回 `expr1`/`expr2` 之後的餘數。 |
| expr1 * expr2 | 傳回 `expr1`*`expr2`。 |
| expr1 + expr2 | 傳回 `expr1`+`expr2`。 |
| expr1 - expr2 | 傳回 `expr1`-`expr2`。 |
| expr1 / expr2 | 傳回 `expr1`/`expr2`。它總是執行浮點數除法。 |
| abs(expr) | 傳回數字或區間值的絕對值。 |
| acos(expr) | 傳回 `expr` 的反餘弦（又稱反正弦），如同由 `java.lang.Math.acos` 計算。 |
| acosh(expr) | 傳回 `expr` 的反雙曲餘弦。 |
| asin(expr) | 傳回 `expr` 的反正弦（又稱反正弦），如同由 `java.lang.Math.asin` 計算。 |
| asinh(expr) | 傳回 `expr` 的反雙曲正弦。 |
| atan(expr) | 傳回 `expr` 的反正切（又稱反正切），如同由 `java.lang.Math.atan` 計算 |
| atan2(exprY, exprX) | 傳回平面中正 x 軸與由座標 (`exprX`, `exprY`) 給定的點之間的弧度角，如同由 `java.lang.Math.atan2` 計算。 |
| atanh(expr) | 傳回 `expr` 的反雙曲正切。 |
| bin(expr) | 傳回以二進制表示的長整數值 `expr` 的字串表示形式。 |
| bround(expr, d) | 傳回使用 HALF_EVEN 捨入模式將 `expr` 捨入到小數點後 `d` 位。 |
| cbrt(expr) | 傳回 `expr` 的立方根。 |
| ceil(expr[, scale]) | 傳回四捨五入後最小的數字，不會小於 `expr`。可以指定一個選用的 `scale` 參數來控制四捨五入的行為。 |
| ceiling(expr[, scale]) | 傳回四捨五入後最小的數字，不會小於 `expr`。可以指定一個選用的 `scale` 參數來控制四捨五入的行為。 |
| conv(num, from_base, to_base) | 將 `num` 從 `from_base` 轉換成 `to_base`。 |
| cos(expr) | 傳回 `expr` 的餘弦，就像 `java.lang.Math.cos` 計算的一樣。 |
| cosh(expr) | 傳回 `expr` 的雙曲餘弦，就像 `java.lang.Math.cosh` 計算的一樣。 |
| cot(expr) | 傳回 `expr` 的餘切，就像 `1/java.lang.Math.tan` 計算的一樣。 |
| csc(expr) | 傳回 `expr` 的餘割，就像 `1/java.lang.Math.sin` 計算的一樣。 |
| degrees(expr) | 將弧度轉換成角度。 |
| expr1 div expr2 | 將 `expr1` 除以 `expr2`。如果運算元為 NULL 或 `expr2` 為 0，則傳回 NULL。結果會轉換成 long。 |
| e() | 傳回歐拉數 e。 |
| exp(expr) | 傳回 e 的 `expr` 次方。 |
| expm1(expr) - 傳回 exp(`expr`) | 1. |
| factorial(expr) | 傳回 `expr` 的階乘。`expr` 的範圍為 [0..20]。否則為 null。 |
| floor(expr[, scale]) | 傳回四捨五入後最大的數字，不會大於 `expr`。可以指定一個選用的 `scale` 參數來控制四捨五入的行為。 |
| greatest(expr, ...) | 傳回所有參數中最大的值，會略過 null 值。 |
| hex(expr) | 將 `expr` 轉換成十六進位。 |
| hypot(expr1, expr2) | 傳回 sqrt(`expr1`**2 + `expr2`**2)。 |
| least(expr, ...) | 傳回所有參數中最小的值，會略過 null 值。 |
| ln(expr) | 傳回 `expr` 的自然對數 (底數為 e)。 |
| log(base, expr) | 傳回 `expr` 的對數，底數為 `base`。 |
| log10(expr) | 傳回 `expr` 的對數，底數為 10。 |
| log1p(expr) | 傳回 log(1 + `expr`)。 |
| log2(expr) | 傳回 `expr` 的對數，底數為 2。 |
| 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` 次方。 |
| 弧度(expr) | 將度數轉換為弧度。 |
| rand([種子]) | 傳回一個隨機值，其獨立且同分布 (i.i.d.) 的均勻分布值位於 [0, 1) 中。 |
| randn([種子]) | 傳回一個隨機值，其獨立且同分布 (i.i.d.) 的值取自標準常態分布。 |
| random([種子]) | 傳回一個隨機值，其獨立且同分布 (i.i.d.) 的均勻分布值位於 [0, 1) 中。 |
| rint(expr) | 傳回與參數值最接近，且等於數學整數的雙精度值。 |
| round(expr, d) | 使用 HALF_UP 捨入模式，將 `expr` 捨入到小數點後 `d` 位。 |
| sec(expr) | 傳回 `expr` 的正割，如同由 `1/java.lang.Math.cos` 計算。 |
| shiftleft(base, expr) | 位元左移。 |
| sign(expr) | 傳回 -1.0、0.0 或 1.0，表示 `expr` 為負數、0 或正數。 |
| signum(expr) | 傳回 -1.0、0.0 或 1.0，表示 `expr` 為負數、0 或正數。 |
| sin(expr) | 傳回 `expr` 的正弦，如同由 `java.lang.Math.sin` 計算。 |
| sinh(expr) | 傳回 `expr` 的雙曲正弦，如同由 `java.lang.Math.sinh` 計算。 |
| sqrt(expr) | 傳回 `expr` 的平方根。 |
| tan(expr) | 傳回 `expr` 的正切，如同由 `java.lang.Math.tan` 計算。 |
| tanh(expr) | 傳回 `expr` 的雙曲正切，如同由 `java.lang.Math.tanh` 計算。 |
| try_add(expr1, expr2) | 傳回 `expr1` 和 `expr2` 的總和，如果發生溢位，結果為 null。可接受的輸入類型與 `+` 算子相同。 |
| try_divide(被除數, 除數) | 傳回 `被除數`/`除數`。它總是執行浮點數除法。如果 `expr2` 為 0，其結果總是 null。`被除數` 必須是數字或區間。`除數` 必須是數字。 |
| try_multiply(expr1, expr2) | 傳回 `expr1`*`expr2`，如果發生溢位，結果為 null。可接受的輸入類型與 `*` 算子相同。 |
| try_subtract(expr1, expr2) | 傳回 `expr1`-`expr2`，如果發生溢位，結果為 null。可接受的輸入類型與 `-` 算子相同。 |
| unhex(expr) | 將十六進位 `expr` 轉換為二進位。 |
| width_bucket(值, 最小值, 最大值, 區間數) | 傳回 `值` 在等寬直方圖中會被分配到的區間號碼，該直方圖有 `區間數` 個區間，範圍從 `最小值` 到 `最大值`。 |
範例
-- %
SELECT 2 % 1.8;
+---------+
|(2 % 1.8)|
+---------+
| 0.2|
+---------+
SELECT MOD(2, 1.8);
+-----------+
|mod(2, 1.8)|
+-----------+
| 0.2|
+-----------+
-- *
SELECT 2 * 3;
+-------+
|(2 * 3)|
+-------+
| 6|
+-------+
-- +
SELECT 1 + 2;
+-------+
|(1 + 2)|
+-------+
| 3|
+-------+
-- -
SELECT 2 - 1;
+-------+
|(2 - 1)|
+-------+
| 1|
+-------+
-- /
SELECT 3 / 2;
+-------+
|(3 / 2)|
+-------+
| 1.5|
+-------+
SELECT 2L / 2L;
+-------+
|(2 / 2)|
+-------+
| 1.0|
+-------+
-- abs
SELECT abs(-1);
+-------+
|abs(-1)|
+-------+
| 1|
+-------+
SELECT abs(INTERVAL -'1-1' YEAR TO MONTH);
+----------------------------------+
|abs(INTERVAL '-1-1' YEAR TO MONTH)|
+----------------------------------+
| INTERVAL '1-1' YE...|
+----------------------------------+
-- acos
SELECT acos(1);
+-------+
|ACOS(1)|
+-------+
| 0.0|
+-------+
SELECT acos(2);
+-------+
|ACOS(2)|
+-------+
| NaN|
+-------+
-- acosh
SELECT acosh(1);
+--------+
|ACOSH(1)|
+--------+
| 0.0|
+--------+
SELECT acosh(0);
+--------+
|ACOSH(0)|
+--------+
| NaN|
+--------+
-- asin
SELECT asin(0);
+-------+
|ASIN(0)|
+-------+
| 0.0|
+-------+
SELECT asin(2);
+-------+
|ASIN(2)|
+-------+
| NaN|
+-------+
-- asinh
SELECT asinh(0);
+--------+
|ASINH(0)|
+--------+
| 0.0|
+--------+
-- atan
SELECT atan(0);
+-------+
|ATAN(0)|
+-------+
| 0.0|
+-------+
-- atan2
SELECT atan2(0, 0);
+-----------+
|ATAN2(0, 0)|
+-----------+
| 0.0|
+-----------+
-- atanh
SELECT atanh(0);
+--------+
|ATANH(0)|
+--------+
| 0.0|
+--------+
SELECT atanh(2);
+--------+
|ATANH(2)|
+--------+
| NaN|
+--------+
-- bin
SELECT bin(13);
+-------+
|bin(13)|
+-------+
| 1101|
+-------+
SELECT bin(-13);
+--------------------+
| bin(-13)|
+--------------------+
|11111111111111111...|
+--------------------+
SELECT bin(13.3);
+---------+
|bin(13.3)|
+---------+
| 1101|
+---------+
-- bround
SELECT bround(2.5, 0);
+--------------+
|bround(2.5, 0)|
+--------------+
| 2|
+--------------+
SELECT bround(25, -1);
+--------------+
|bround(25, -1)|
+--------------+
| 20|
+--------------+
-- cbrt
SELECT cbrt(27.0);
+----------+
|CBRT(27.0)|
+----------+
| 3.0|
+----------+
-- ceil
SELECT ceil(-0.1);
+----------+
|CEIL(-0.1)|
+----------+
| 0|
+----------+
SELECT ceil(5);
+-------+
|CEIL(5)|
+-------+
| 5|
+-------+
SELECT ceil(3.1411, 3);
+---------------+
|ceil(3.1411, 3)|
+---------------+
| 3.142|
+---------------+
SELECT ceil(3.1411, -3);
+----------------+
|ceil(3.1411, -3)|
+----------------+
| 1000|
+----------------+
-- ceiling
SELECT ceiling(-0.1);
+-------------+
|ceiling(-0.1)|
+-------------+
| 0|
+-------------+
SELECT ceiling(5);
+----------+
|ceiling(5)|
+----------+
| 5|
+----------+
SELECT ceiling(3.1411, 3);
+------------------+
|ceiling(3.1411, 3)|
+------------------+
| 3.142|
+------------------+
SELECT ceiling(3.1411, -3);
+-------------------+
|ceiling(3.1411, -3)|
+-------------------+
| 1000|
+-------------------+
-- conv
SELECT conv('100', 2, 10);
+----------------+
|conv(100, 2, 10)|
+----------------+
| 4|
+----------------+
SELECT conv(-10, 16, -10);
+------------------+
|conv(-10, 16, -10)|
+------------------+
| -16|
+------------------+
-- cos
SELECT cos(0);
+------+
|COS(0)|
+------+
| 1.0|
+------+
-- cosh
SELECT cosh(0);
+-------+
|COSH(0)|
+-------+
| 1.0|
+-------+
-- cot
SELECT cot(1);
+------------------+
| COT(1)|
+------------------+
|0.6420926159343306|
+------------------+
-- csc
SELECT csc(1);
+------------------+
| CSC(1)|
+------------------+
|1.1883951057781212|
+------------------+
-- degrees
SELECT degrees(3.141592653589793);
+--------------------------+
|DEGREES(3.141592653589793)|
+--------------------------+
| 180.0|
+--------------------------+
-- div
SELECT 3 div 2;
+---------+
|(3 div 2)|
+---------+
| 1|
+---------+
SELECT INTERVAL '1-1' YEAR TO MONTH div INTERVAL '-1' MONTH;
+------------------------------------------------------+
|(INTERVAL '1-1' YEAR TO MONTH div INTERVAL '-1' MONTH)|
+------------------------------------------------------+
| -13|
+------------------------------------------------------+
-- e
SELECT e();
+-----------------+
| E()|
+-----------------+
|2.718281828459045|
+-----------------+
-- exp
SELECT exp(0);
+------+
|EXP(0)|
+------+
| 1.0|
+------+
-- expm1
SELECT expm1(0);
+--------+
|EXPM1(0)|
+--------+
| 0.0|
+--------+
-- factorial
SELECT factorial(5);
+------------+
|factorial(5)|
+------------+
| 120|
+------------+
-- floor
SELECT floor(-0.1);
+-----------+
|FLOOR(-0.1)|
+-----------+
| -1|
+-----------+
SELECT floor(5);
+--------+
|FLOOR(5)|
+--------+
| 5|
+--------+
SELECT floor(3.1411, 3);
+----------------+
|floor(3.1411, 3)|
+----------------+
| 3.141|
+----------------+
SELECT floor(3.1411, -3);
+-----------------+
|floor(3.1411, -3)|
+-----------------+
| 0|
+-----------------+
-- greatest
SELECT greatest(10, 9, 2, 4, 3);
+------------------------+
|greatest(10, 9, 2, 4, 3)|
+------------------------+
| 10|
+------------------------+
-- hex
SELECT hex(17);
+-------+
|hex(17)|
+-------+
| 11|
+-------+
SELECT hex('Spark SQL');
+------------------+
| hex(Spark SQL)|
+------------------+
|537061726B2053514C|
+------------------+
-- hypot
SELECT hypot(3, 4);
+-----------+
|HYPOT(3, 4)|
+-----------+
| 5.0|
+-----------+
-- least
SELECT least(10, 9, 2, 4, 3);
+---------------------+
|least(10, 9, 2, 4, 3)|
+---------------------+
| 2|
+---------------------+
-- ln
SELECT ln(1);
+-----+
|ln(1)|
+-----+
| 0.0|
+-----+
-- log
SELECT log(10, 100);
+------------+
|LOG(10, 100)|
+------------+
| 2.0|
+------------+
-- log10
SELECT log10(10);
+---------+
|LOG10(10)|
+---------+
| 1.0|
+---------+
-- log1p
SELECT log1p(0);
+--------+
|LOG1P(0)|
+--------+
| 0.0|
+--------+
-- log2
SELECT log2(2);
+-------+
|LOG2(2)|
+-------+
| 1.0|
+-------+
-- mod
SELECT 2 % 1.8;
+---------+
|(2 % 1.8)|
+---------+
| 0.2|
+---------+
SELECT MOD(2, 1.8);
+-----------+
|mod(2, 1.8)|
+-----------+
| 0.2|
+-----------+
-- negative
SELECT negative(1);
+-----------+
|negative(1)|
+-----------+
| -1|
+-----------+
-- pi
SELECT pi();
+-----------------+
| PI()|
+-----------------+
|3.141592653589793|
+-----------------+
-- pmod
SELECT pmod(10, 3);
+-----------+
|pmod(10, 3)|
+-----------+
| 1|
+-----------+
SELECT pmod(-10, 3);
+------------+
|pmod(-10, 3)|
+------------+
| 2|
+------------+
-- positive
SELECT positive(1);
+-----+
|(+ 1)|
+-----+
| 1|
+-----+
-- pow
SELECT pow(2, 3);
+---------+
|pow(2, 3)|
+---------+
| 8.0|
+---------+
-- power
SELECT power(2, 3);
+-----------+
|POWER(2, 3)|
+-----------+
| 8.0|
+-----------+
-- radians
SELECT radians(180);
+-----------------+
| RADIANS(180)|
+-----------------+
|3.141592653589793|
+-----------------+
-- rand
SELECT rand();
+------------------+
| rand()|
+------------------+
|0.7211420708112387|
+------------------+
SELECT rand(0);
+------------------+
| rand(0)|
+------------------+
|0.7604953758285915|
+------------------+
SELECT rand(null);
+------------------+
| rand(NULL)|
+------------------+
|0.7604953758285915|
+------------------+
-- randn
SELECT randn();
+-------------------+
| randn()|
+-------------------+
|-0.8175603217732732|
+-------------------+
SELECT randn(0);
+------------------+
| randn(0)|
+------------------+
|1.6034991609278433|
+------------------+
SELECT randn(null);
+------------------+
| randn(NULL)|
+------------------+
|1.6034991609278433|
+------------------+
-- random
SELECT random();
+-----------------+
| rand()|
+-----------------+
|0.394205008255365|
+-----------------+
SELECT random(0);
+------------------+
| rand(0)|
+------------------+
|0.7604953758285915|
+------------------+
SELECT random(null);
+------------------+
| rand(NULL)|
+------------------+
|0.7604953758285915|
+------------------+
-- rint
SELECT rint(12.3456);
+-------------+
|rint(12.3456)|
+-------------+
| 12.0|
+-------------+
-- round
SELECT round(2.5, 0);
+-------------+
|round(2.5, 0)|
+-------------+
| 3|
+-------------+
-- sec
SELECT sec(0);
+------+
|SEC(0)|
+------+
| 1.0|
+------+
-- shiftleft
SELECT shiftleft(2, 1);
+---------------+
|shiftleft(2, 1)|
+---------------+
| 4|
+---------------+
-- sign
SELECT sign(40);
+--------+
|sign(40)|
+--------+
| 1.0|
+--------+
SELECT sign(INTERVAL -'100' YEAR);
+--------------------------+
|sign(INTERVAL '-100' YEAR)|
+--------------------------+
| -1.0|
+--------------------------+
-- signum
SELECT signum(40);
+----------+
|SIGNUM(40)|
+----------+
| 1.0|
+----------+
SELECT signum(INTERVAL -'100' YEAR);
+----------------------------+
|SIGNUM(INTERVAL '-100' YEAR)|
+----------------------------+
| -1.0|
+----------------------------+
-- sin
SELECT sin(0);
+------+
|SIN(0)|
+------+
| 0.0|
+------+
-- sinh
SELECT sinh(0);
+-------+
|SINH(0)|
+-------+
| 0.0|
+-------+
-- sqrt
SELECT sqrt(4);
+-------+
|SQRT(4)|
+-------+
| 2.0|
+-------+
-- tan
SELECT tan(0);
+------+
|TAN(0)|
+------+
| 0.0|
+------+
-- tanh
SELECT tanh(0);
+-------+
|TANH(0)|
+-------+
| 0.0|
+-------+
-- try_add
SELECT try_add(1, 2);
+-------------+
|try_add(1, 2)|
+-------------+
| 3|
+-------------+
SELECT try_add(2147483647, 1);
+----------------------+
|try_add(2147483647, 1)|
+----------------------+
| NULL|
+----------------------+
SELECT try_add(date'2021-01-01', 1);
+-----------------------------+
|try_add(DATE '2021-01-01', 1)|
+-----------------------------+
| 2021-01-02|
+-----------------------------+
SELECT try_add(date'2021-01-01', interval 1 year);
+---------------------------------------------+
|try_add(DATE '2021-01-01', INTERVAL '1' YEAR)|
+---------------------------------------------+
| 2022-01-01|
+---------------------------------------------+
SELECT try_add(timestamp'2021-01-01 00:00:00', interval 1 day);
+----------------------------------------------------------+
|try_add(TIMESTAMP '2021-01-01 00:00:00', INTERVAL '1' DAY)|
+----------------------------------------------------------+
| 2021-01-02 00:00:00|
+----------------------------------------------------------+
SELECT try_add(interval 1 year, interval 2 year);
+---------------------------------------------+
|try_add(INTERVAL '1' YEAR, INTERVAL '2' YEAR)|
+---------------------------------------------+
| INTERVAL '3' YEAR|
+---------------------------------------------+
-- try_divide
SELECT try_divide(3, 2);
+----------------+
|try_divide(3, 2)|
+----------------+
| 1.5|
+----------------+
SELECT try_divide(2L, 2L);
+----------------+
|try_divide(2, 2)|
+----------------+
| 1.0|
+----------------+
SELECT try_divide(1, 0);
+----------------+
|try_divide(1, 0)|
+----------------+
| NULL|
+----------------+
SELECT try_divide(interval 2 month, 2);
+---------------------------------+
|try_divide(INTERVAL '2' MONTH, 2)|
+---------------------------------+
| INTERVAL '0-1' YE...|
+---------------------------------+
SELECT try_divide(interval 2 month, 0);
+---------------------------------+
|try_divide(INTERVAL '2' MONTH, 0)|
+---------------------------------+
| NULL|
+---------------------------------+
-- try_multiply
SELECT try_multiply(2, 3);
+------------------+
|try_multiply(2, 3)|
+------------------+
| 6|
+------------------+
SELECT try_multiply(-2147483648, 10);
+-----------------------------+
|try_multiply(-2147483648, 10)|
+-----------------------------+
| NULL|
+-----------------------------+
SELECT try_multiply(interval 2 year, 3);
+----------------------------------+
|try_multiply(INTERVAL '2' YEAR, 3)|
+----------------------------------+
| INTERVAL '6-0' YE...|
+----------------------------------+
-- try_subtract
SELECT try_subtract(2, 1);
+------------------+
|try_subtract(2, 1)|
+------------------+
| 1|
+------------------+
SELECT try_subtract(-2147483648, 1);
+----------------------------+
|try_subtract(-2147483648, 1)|
+----------------------------+
| NULL|
+----------------------------+
SELECT try_subtract(date'2021-01-02', 1);
+----------------------------------+
|try_subtract(DATE '2021-01-02', 1)|
+----------------------------------+
| 2021-01-01|
+----------------------------------+
SELECT try_subtract(date'2021-01-01', interval 1 year);
+--------------------------------------------------+
|try_subtract(DATE '2021-01-01', INTERVAL '1' YEAR)|
+--------------------------------------------------+
| 2020-01-01|
+--------------------------------------------------+
SELECT try_subtract(timestamp'2021-01-02 00:00:00', interval 1 day);
+---------------------------------------------------------------+
|try_subtract(TIMESTAMP '2021-01-02 00:00:00', INTERVAL '1' DAY)|
+---------------------------------------------------------------+
| 2021-01-01 00:00:00|
+---------------------------------------------------------------+
SELECT try_subtract(interval 2 year, interval 1 year);
+--------------------------------------------------+
|try_subtract(INTERVAL '2' YEAR, INTERVAL '1' YEAR)|
+--------------------------------------------------+
| INTERVAL '1' YEAR|
+--------------------------------------------------+
-- unhex
SELECT decode(unhex('537061726B2053514C'), 'UTF-8');
+----------------------------------------+
|decode(unhex(537061726B2053514C), UTF-8)|
+----------------------------------------+
| Spark SQL|
+----------------------------------------+
-- width_bucket
SELECT width_bucket(5.3, 0.2, 10.6, 5);
+-------------------------------+
|width_bucket(5.3, 0.2, 10.6, 5)|
+-------------------------------+
| 3|
+-------------------------------+
SELECT width_bucket(-2.1, 1.3, 3.4, 3);
+-------------------------------+
|width_bucket(-2.1, 1.3, 3.4, 3)|
+-------------------------------+
| 0|
+-------------------------------+
SELECT width_bucket(8.1, 0.0, 5.7, 4);
+------------------------------+
|width_bucket(8.1, 0.0, 5.7, 4)|
+------------------------------+
| 5|
+------------------------------+
SELECT width_bucket(-0.9, 5.2, 0.5, 2);
+-------------------------------+
|width_bucket(-0.9, 5.2, 0.5, 2)|
+-------------------------------+
| 3|
+-------------------------------+
SELECT width_bucket(INTERVAL '0' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
+--------------------------------------------------------------------------+
|width_bucket(INTERVAL '0' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10)|
+--------------------------------------------------------------------------+
| 1|
+--------------------------------------------------------------------------+
SELECT width_bucket(INTERVAL '1' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
+--------------------------------------------------------------------------+
|width_bucket(INTERVAL '1' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10)|
+--------------------------------------------------------------------------+
| 2|
+--------------------------------------------------------------------------+
SELECT width_bucket(INTERVAL '0' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
+-----------------------------------------------------------------------+
|width_bucket(INTERVAL '0' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10)|
+-----------------------------------------------------------------------+
| 1|
+-----------------------------------------------------------------------+
SELECT width_bucket(INTERVAL '1' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
+-----------------------------------------------------------------------+
|width_bucket(INTERVAL '1' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10)|
+-----------------------------------------------------------------------+
| 2|
+-----------------------------------------------------------------------+
字串函數
| 函數 | 說明 |
|---|---|
| ascii(str) | 傳回 `str` 第一個字元的數字值。 |
| base64(bin) | 將參數從二進位 `bin` 轉換為 base 64 字串。 |
| bit_length(expr) | 傳回字串資料的位元長度或二進位資料的位元數。 |
| btrim(str) | 移除 `str` 前後緣的空白字元。 |
| btrim(str, trimStr) | 移除 `str` 前後緣的 `trimStr` 字元。 |
| char(expr) | 傳回二進位等同於 `expr` 的 ASCII 字元。如果 n 大於 256，結果等同於 chr(n % 256) |
| char_length(expr) | 傳回字串資料的字元長度或二進位資料的位元組數。字串資料的長度包含尾端的空白。二進位資料的長度包含二進位零。 |
| character_length(expr) | 傳回字串資料的字元長度或二進位資料的位元組數。字串資料的長度包含尾端的空白。二進位資料的長度包含二進位零。 |
| chr(expr) | 傳回二進位等同於 `expr` 的 ASCII 字元。如果 n 大於 256，結果等同於 chr(n % 256) |
| concat_ws(sep[, str | array(str)]+) | 傳回以 `sep` 分隔的字串串接，略過 null 值。 |
| contains(left, right) | 傳回布林值。如果在 left 中找到 right，則值為 True。如果任一輸入表達式為 NULL，則傳回 NULL。否則，傳回 False。left 或 right 必須為 STRING 或 BINARY 類型。 |
| decode(bin, charset) | 使用第二個參數的字元集對第一個參數進行解碼。 |
| decode(expr, search, result [, search, result ] ... [, default]) | 依序將 expr 與每個 search 值進行比較。如果 expr 等於某個 search 值，decode 會傳回對應的 result。如果找不到符合的項目，則會傳回 default。如果省略 default，則會傳回 null。 |
| elt(n, input1, input2, ...) | 傳回第 `n` 個輸入，例如，當 `n` 為 2 時傳回 `input2`。如果索引超過陣列長度，且 `spark.sql.ansi.enabled` 設為 false，則函數會傳回 NULL。如果 `spark.sql.ansi.enabled` 設為 true，則會對無效索引擲回 ArrayIndexOutOfBoundsException。 |
| encode(str, charset) | 使用第二個參數的字元集對第一個參數進行編碼。 |
| 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) | 傳回 `substr` 在 `str` 中第一次出現的 (以 1 為基底的) 索引。 |
| lcase(str) | 傳回 `str`，其中所有字元都轉換為小寫。 |
| left(str, len) | 傳回字串 `str` 中最左邊的 `len` 個字元（`len` 可以是字串類型），如果 `len` 小於或等於 0，結果會是空字串。 |
| len(expr) | 傳回字串資料的字元長度或二進位資料的位元組數。字串資料的長度包含尾端的空白。二進位資料的長度包含二進位零。 |
| length(expr) | 傳回字串資料的字元長度或二進位資料的位元組數。字串資料的長度包含尾端的空白。二進位資料的長度包含二進位零。 |
| levenshtein(str1, str2[, threshold]) | 傳回兩個給定字串之間的 Levenshtein 距離。如果設定 threshold 且距離大於它，傳回 -1。 |
| locate(substr, str[, pos]) | 傳回 `substr` 在 `str` 中第一次出現的位置，位置在 `pos` 之後。給定的 `pos` 和傳回值都是以 1 為基底。 |
| lower(str) | 傳回 `str`，其中所有字元都轉換為小寫。 |
| lpad(str, len[, pad]) | 傳回 `str`，左邊以 `pad` 填補，長度為 `len`。如果 `str` 長於 `len`，傳回值會縮短為 `len` 個字元或位元組。如果未指定 `pad`，如果 `str` 是字元字串，則會在左側以空白字元填補；如果是位元組序列，則會以零填補。 |
| ltrim(str) | 移除 `str` 中開頭的空白字元。 |
| luhn_check(str ) | 檢查一串數字是否根據 Luhn 演算法有效。此檢查總和函式廣泛應用於信用卡號碼和政府身分證號碼，用於區分有效的號碼和輸入錯誤的號碼。 |
| mask(input[, upperChar, lowerChar, digitChar, otherChar]) | 遮罩給定的字串值。此函式會將字元替換為 'X' 或 'x'，並將數字替換為 'n'。這對於建立已移除機密資訊的資料表副本很有用。 |
| octet_length(expr) | 傳回字串資料的位元組長度或二進位資料的位元組數。 |
| overlay(input, replace, pos[, len]) | 將 `input` 從 `pos` 開始且長度為 `len` 的部分替換為 `replace`。 |
| position(substr, str[, pos]) | 傳回 `substr` 在 `str` 中第一次出現的位置，位置在 `pos` 之後。給定的 `pos` 和傳回值都是以 1 為基底。 |
| printf(strfmt, obj, ...) | 傳回一個由 printf 格式字串所格式化的字串。 |
| regexp_count(str, regexp) | 傳回正規表示式模式 `regexp` 在字串 `str` 中比對到的次數。 |
| regexp_extract(str, regexp[, idx]) | 從 `str` 中擷取第一個符合 `regexp` 表達式且對應於正規群組索引的字串。 |
| regexp_extract_all(str, regexp[, idx]) | 從 `str` 中萃取符合 `regexp` 表達式的所有字串，並對應到 regex 群組索引。 |
| regexp_instr(str, regexp) | 在字串中搜尋正規表示式，並傳回一個整數表示匹配子字串的起始位置。位置從 1 開始，而非 0。如果找不到匹配項，則傳回 0。 |
| regexp_replace(str, regexp, rep[, position]) | 用 `rep` 取代 `str` 中所有符合 `regexp` 的子字串。 |
| regexp_substr(str, regexp) | 傳回在字串 `str` 中符合正規表示式 `regexp` 的子字串。如果找不到正規表示式，則結果為 null。 |
| repeat(str, n) | 傳回重複給定字串值 n 次的字串。 |
| replace(str, search[, replace]) | 用 `replace` 取代 `search` 的所有出現。 |
| right(str, len) | 傳回字串 `str` 最右邊的 `len`(`len` 可以是字串類型) 個字元，如果 `len` 小於或等於 0，則結果為空字串。 |
| rpad(str, len[, pad]) | 傳回 `str`，右邊以 `pad` 填補到長度 `len`。如果 `str` 長於 `len`，則傳回值會縮短到 `len` 個字元。如果未指定 `pad`，如果 `str` 是字元串，則會在右側以空白字元填補，如果是二進位字串，則會以零填補。 |
| rtrim(str) | 移除 `str` 中尾端的空白字元。 |
| sentences(str[, lang, country]) | 將 `str` 分割成一個字串陣列的陣列。 |
| soundex(str) | 傳回字串的 Soundex 編碼。 |
| space(n) | 傳回一個由 `n` 個空白組成的字串。 |
| split(str, regex, limit) | 在符合 `regex` 的出現處分割 `str`，並傳回長度最多為 `limit` 的陣列 |
| split_part(str, delimiter, partNum) | 依據分隔符號分割 `str`，並傳回分割後指定的部份 (從 1 開始)。如果任何輸入為 null，則傳回 null。如果 `partNum` 超出分割部分的範圍，則傳回空字串。如果 `partNum` 為 0，則會擲回錯誤。如果 `partNum` 為負數，則從字串的尾端開始倒數計算部分。如果 `delimiter` 為空字串，則不會分割 `str`。 |
| startswith(left, right) | 傳回布林值。如果 left 以 right 開頭，則值為 True。如果任一輸入表達式為 NULL，則傳回 NULL。否則，傳回 False。left 或 right 都必須為 STRING 或 BINARY 類型。 |
| substr(str, pos[, len]) | 傳回 `str` 從 `pos` 開始長度為 `len` 的子字串，或從 `pos` 開始長度為 `len` 的位元組陣列切片。 |
| substr(str FROM pos[ FOR len]]) | 傳回 `str` 從 `pos` 開始長度為 `len` 的子字串，或從 `pos` 開始長度為 `len` 的位元組陣列切片。 |
| substring(str, pos[, len]) | 傳回 `str` 從 `pos` 開始長度為 `len` 的子字串，或從 `pos` 開始長度為 `len` 的位元組陣列切片。 |
| substring(str FROM pos[ FOR len]]) | 傳回 `str` 從 `pos` 開始長度為 `len` 的子字串，或從 `pos` 開始長度為 `len` 的位元組陣列切片。 |
| substring_index(str, delim, count) | 傳回 `str` 中在分隔符號 `delim` 出現 `count` 次之前的所有子字串。如果 `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, fmt) | 根據字串格式 'fmt' 將字串 'expr' 轉換為數字。如果轉換失敗，會擲回例外。格式可以包含下列字元，不區分大小寫：'0' 或 '9'：指定 0 到 9 之間的預期數字。格式字串中的 0 或 9 序列會比對輸入字串中的數字序列。如果 0/9 序列開頭為 0，且在小數點之前，它只能比對相同大小的數字序列。否則，如果序列開頭為 9 或在小數點之後，它可以比對相同大小或更小的數字序列。'.' 或 'D'：指定小數點的位置 (選擇性，只允許一次)。',' 或 'G'：指定群組 (千位) 分隔符號 (,) 的位置。每個群組分隔符號的左右兩側都必須有 0 或 9。'expr' 必須與數字大小相關的群組分隔符號相符。'$'：指定 $ 貨幣符號的位置。此字元只能指定一次。'S' 或 'MI'：指定 '-' 或 '+' 符號的位置 (選擇性，只允許在格式字串的開頭或結尾出現一次)。請注意，'S' 允許 '-'，但 'MI' 不允許。'PR'：只允許在格式字串的結尾；指定 'expr' 使用尖括號括起來表示負數 (『<1>』)。 |
| to_varchar(numberExpr, formatExpr) | 根據 `formatExpr` 將 `numberExpr` 轉換為字串。如果轉換失敗，會擲回例外。格式可以包含下列字元，不區分大小寫：'0' 或 '9'：指定 0 到 9 之間的預期數字。格式字串中的 0 或 9 序列會比對輸入值中的數字序列，產生與格式字串中對應序列長度相同的結果字串。如果 0/9 序列包含的數字比小數值對應的部分多、開頭為 0，且在小數點之前，則結果字串會在左邊補上 0。否則，會補上空白。'.' 或 'D'：指定小數點的位置 (選擇性，只允許一次)。',' 或 'G'：指定群組 (千位) 分隔符號 (,) 的位置。每個群組分隔符號的左右兩側都必須有 0 或 9。'$'：指定 $ 貨幣符號的位置。此字元只能指定一次。'S' 或 'MI'：指定 '-' 或 '+' 符號的位置 (選擇性，只允許在格式字串的開頭或結尾出現一次)。請注意，'S' 會為正值印出 '+'，但 'MI' 會印出空白。'PR'：只允許在格式字串的結尾；指定如果輸入值為負數，結果字串會以尖括號括起來 (『<1>』)。 |
| translate(input, from, to) | 將 `input` 字串翻譯，方法是將 `from` 字串中的字元替換為 `to` 字串中的對應字元。 |
| trim(str) | 移除 `str` 前後緣的空白字元。 |
| trim(BOTH FROM str) | 移除 `str` 前後緣的空白字元。 |
| trim(LEADING FROM str) | 移除 `str` 中開頭的空白字元。 |
| trim(TRAILING FROM str) | 移除 `str` 中尾端的空白字元。 |
| trim(trimStr FROM str) | 移除 `str` 前後緣的 `trimStr` 字元。 |
| trim(BOTH trimStr FROM str) | 移除 `str` 前後緣的 `trimStr` 字元。 |
| trim(LEADING trimStr FROM str) | 從 `str` 中移除開頭的 `trimStr` 字元。 |
| trim(TRAILING trimStr FROM str) | 從 `str` 中移除結尾的 `trimStr` 字元。 |
| try_to_binary(str[, fmt]) | 這是 `to_binary` 的特殊版本，它執行相同的操作，但如果無法執行轉換，則會傳回 NULL 值，而不是引發錯誤。 |
| try_to_number(expr, fmt) | 根據字串格式 `fmt` 將字串 'expr' 轉換為數字。如果字串 'expr' 與預期的格式不符，則傳回 NULL。此格式遵循與 to_number 函數相同的語意。 |
| ucase(str) | 傳回所有字元都轉換為大寫的 `str`。 |
| unbase64(str) | 將引數從 base 64 字串 `str` 轉換為二進位。 |
| upper(str) | 傳回所有字元都轉換為大寫的 `str`。 |
範例
-- ascii
SELECT ascii('222');
+----------+
|ascii(222)|
+----------+
| 50|
+----------+
SELECT ascii(2);
+--------+
|ascii(2)|
+--------+
| 50|
+--------+
-- base64
SELECT base64('Spark SQL');
+-----------------+
|base64(Spark SQL)|
+-----------------+
| U3BhcmsgU1FM|
+-----------------+
SELECT base64(x'537061726b2053514c');
+-----------------------------+
|base64(X'537061726B2053514C')|
+-----------------------------+
| U3BhcmsgU1FM|
+-----------------------------+
-- bit_length
SELECT bit_length('Spark SQL');
+---------------------+
|bit_length(Spark SQL)|
+---------------------+
| 72|
+---------------------+
SELECT bit_length(x'537061726b2053514c');
+---------------------------------+
|bit_length(X'537061726B2053514C')|
+---------------------------------+
| 72|
+---------------------------------+
-- btrim
SELECT btrim(' SparkSQL ');
+----------------------+
|btrim( SparkSQL )|
+----------------------+
| SparkSQL|
+----------------------+
SELECT btrim(encode(' SparkSQL ', 'utf-8'));
+-------------------------------------+
|btrim(encode( SparkSQL , utf-8))|
+-------------------------------------+
| SparkSQL|
+-------------------------------------+
SELECT btrim('SSparkSQLS', 'SL');
+---------------------+
|btrim(SSparkSQLS, SL)|
+---------------------+
| parkSQ|
+---------------------+
SELECT btrim(encode('SSparkSQLS', 'utf-8'), encode('SL', 'utf-8'));
+---------------------------------------------------+
|btrim(encode(SSparkSQLS, utf-8), encode(SL, utf-8))|
+---------------------------------------------------+
| parkSQ|
+---------------------------------------------------+
-- char
SELECT char(65);
+--------+
|char(65)|
+--------+
| A|
+--------+
-- char_length
SELECT char_length('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT char_length(x'537061726b2053514c');
+----------------------------------+
|char_length(X'537061726B2053514C')|
+----------------------------------+
| 9|
+----------------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- character_length
SELECT character_length('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
SELECT character_length(x'537061726b2053514c');
+---------------------------------------+
|character_length(X'537061726B2053514C')|
+---------------------------------------+
| 9|
+---------------------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- chr
SELECT chr(65);
+-------+
|chr(65)|
+-------+
| A|
+-------+
-- concat_ws
SELECT concat_ws(' ', 'Spark', 'SQL');
+------------------------+
|concat_ws( , Spark, SQL)|
+------------------------+
| Spark SQL|
+------------------------+
SELECT concat_ws('s');
+------------+
|concat_ws(s)|
+------------+
| |
+------------+
SELECT concat_ws('/', 'foo', null, 'bar');
+----------------------------+
|concat_ws(/, foo, NULL, bar)|
+----------------------------+
| foo/bar|
+----------------------------+
SELECT concat_ws(null, 'Spark', 'SQL');
+---------------------------+
|concat_ws(NULL, Spark, SQL)|
+---------------------------+
| NULL|
+---------------------------+
-- contains
SELECT contains('Spark SQL', 'Spark');
+--------------------------+
|contains(Spark SQL, Spark)|
+--------------------------+
| true|
+--------------------------+
SELECT contains('Spark SQL', 'SPARK');
+--------------------------+
|contains(Spark SQL, SPARK)|
+--------------------------+
| false|
+--------------------------+
SELECT contains('Spark SQL', null);
+-------------------------+
|contains(Spark SQL, NULL)|
+-------------------------+
| NULL|
+-------------------------+
SELECT contains(x'537061726b2053514c', x'537061726b');
+----------------------------------------------+
|contains(X'537061726B2053514C', X'537061726B')|
+----------------------------------------------+
| true|
+----------------------------------------------+
-- decode
SELECT decode(encode('abc', 'utf-8'), 'utf-8');
+---------------------------------+
|decode(encode(abc, utf-8), utf-8)|
+---------------------------------+
| abc|
+---------------------------------+
SELECT decode(2, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic');
+----------------------------------------------------------------------------------+
|decode(2, 1, Southlake, 2, San Francisco, 3, New Jersey, 4, Seattle, Non domestic)|
+----------------------------------------------------------------------------------+
| San Francisco|
+----------------------------------------------------------------------------------+
SELECT decode(6, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic');
+----------------------------------------------------------------------------------+
|decode(6, 1, Southlake, 2, San Francisco, 3, New Jersey, 4, Seattle, Non domestic)|
+----------------------------------------------------------------------------------+
| Non domestic|
+----------------------------------------------------------------------------------+
SELECT decode(6, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle');
+--------------------------------------------------------------------+
|decode(6, 1, Southlake, 2, San Francisco, 3, New Jersey, 4, Seattle)|
+--------------------------------------------------------------------+
| NULL|
+--------------------------------------------------------------------+
SELECT decode(null, 6, 'Spark', NULL, 'SQL', 4, 'rocks');
+-------------------------------------------+
|decode(NULL, 6, Spark, NULL, SQL, 4, rocks)|
+-------------------------------------------+
| SQL|
+-------------------------------------------+
-- elt
SELECT elt(1, 'scala', 'java');
+-------------------+
|elt(1, scala, java)|
+-------------------+
| scala|
+-------------------+
SELECT elt(2, 'a', 1);
+------------+
|elt(2, a, 1)|
+------------+
| 1|
+------------+
-- encode
SELECT encode('abc', 'utf-8');
+------------------+
|encode(abc, utf-8)|
+------------------+
| [61 62 63]|
+------------------+
-- endswith
SELECT endswith('Spark SQL', 'SQL');
+------------------------+
|endswith(Spark SQL, SQL)|
+------------------------+
| true|
+------------------------+
SELECT endswith('Spark SQL', 'Spark');
+--------------------------+
|endswith(Spark SQL, Spark)|
+--------------------------+
| false|
+--------------------------+
SELECT endswith('Spark SQL', null);
+-------------------------+
|endswith(Spark SQL, NULL)|
+-------------------------+
| NULL|
+-------------------------+
SELECT endswith(x'537061726b2053514c', x'537061726b');
+----------------------------------------------+
|endswith(X'537061726B2053514C', X'537061726B')|
+----------------------------------------------+
| false|
+----------------------------------------------+
SELECT endswith(x'537061726b2053514c', x'53514c');
+------------------------------------------+
|endswith(X'537061726B2053514C', X'53514C')|
+------------------------------------------+
| true|
+------------------------------------------+
-- find_in_set
SELECT find_in_set('ab','abc,b,ab,c,def');
+-------------------------------+
|find_in_set(ab, abc,b,ab,c,def)|
+-------------------------------+
| 3|
+-------------------------------+
-- format_number
SELECT format_number(12332.123456, 4);
+------------------------------+
|format_number(12332.123456, 4)|
+------------------------------+
| 12,332.1235|
+------------------------------+
SELECT format_number(12332.123456, '##################.###');
+---------------------------------------------------+
|format_number(12332.123456, ##################.###)|
+---------------------------------------------------+
| 12332.123|
+---------------------------------------------------+
-- format_string
SELECT format_string("Hello World %d %s", 100, "days");
+-------------------------------------------+
|format_string(Hello World %d %s, 100, days)|
+-------------------------------------------+
| Hello World 100 days|
+-------------------------------------------+
-- initcap
SELECT initcap('sPark sql');
+------------------+
|initcap(sPark sql)|
+------------------+
| Spark Sql|
+------------------+
-- instr
SELECT instr('SparkSQL', 'SQL');
+--------------------+
|instr(SparkSQL, SQL)|
+--------------------+
| 6|
+--------------------+
-- lcase
SELECT lcase('SparkSql');
+---------------+
|lcase(SparkSql)|
+---------------+
| sparksql|
+---------------+
-- left
SELECT left('Spark SQL', 3);
+------------------+
|left(Spark SQL, 3)|
+------------------+
| Spa|
+------------------+
SELECT left(encode('Spark SQL', 'utf-8'), 3);
+---------------------------------+
|left(encode(Spark SQL, utf-8), 3)|
+---------------------------------+
| [53 70 61]|
+---------------------------------+
-- len
SELECT len('Spark SQL ');
+---------------+
|len(Spark SQL )|
+---------------+
| 10|
+---------------+
SELECT len(x'537061726b2053514c');
+--------------------------+
|len(X'537061726B2053514C')|
+--------------------------+
| 9|
+--------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- length
SELECT length('Spark SQL ');
+------------------+
|length(Spark SQL )|
+------------------+
| 10|
+------------------+
SELECT length(x'537061726b2053514c');
+-----------------------------+
|length(X'537061726B2053514C')|
+-----------------------------+
| 9|
+-----------------------------+
SELECT CHAR_LENGTH('Spark SQL ');
+-----------------------+
|char_length(Spark SQL )|
+-----------------------+
| 10|
+-----------------------+
SELECT CHARACTER_LENGTH('Spark SQL ');
+----------------------------+
|character_length(Spark SQL )|
+----------------------------+
| 10|
+----------------------------+
-- levenshtein
SELECT levenshtein('kitten', 'sitting');
+----------------------------+
|levenshtein(kitten, sitting)|
+----------------------------+
| 3|
+----------------------------+
SELECT levenshtein('kitten', 'sitting', 2);
+-------------------------------+
|levenshtein(kitten, sitting, 2)|
+-------------------------------+
| -1|
+-------------------------------+
-- locate
SELECT locate('bar', 'foobarbar');
+-------------------------+
|locate(bar, foobarbar, 1)|
+-------------------------+
| 4|
+-------------------------+
SELECT locate('bar', 'foobarbar', 5);
+-------------------------+
|locate(bar, foobarbar, 5)|
+-------------------------+
| 7|
+-------------------------+
SELECT POSITION('bar' IN 'foobarbar');
+-------------------------+
|locate(bar, foobarbar, 1)|
+-------------------------+
| 4|
+-------------------------+
-- lower
SELECT lower('SparkSql');
+---------------+
|lower(SparkSql)|
+---------------+
| sparksql|
+---------------+
-- lpad
SELECT lpad('hi', 5, '??');
+---------------+
|lpad(hi, 5, ??)|
+---------------+
| ???hi|
+---------------+
SELECT lpad('hi', 1, '??');
+---------------+
|lpad(hi, 1, ??)|
+---------------+
| h|
+---------------+
SELECT lpad('hi', 5);
+--------------+
|lpad(hi, 5, )|
+--------------+
| hi|
+--------------+
SELECT hex(lpad(unhex('aabb'), 5));
+--------------------------------+
|hex(lpad(unhex(aabb), 5, X'00'))|
+--------------------------------+
| 000000AABB|
+--------------------------------+
SELECT hex(lpad(unhex('aabb'), 5, unhex('1122')));
+--------------------------------------+
|hex(lpad(unhex(aabb), 5, unhex(1122)))|
+--------------------------------------+
| 112211AABB|
+--------------------------------------+
-- ltrim
SELECT ltrim(' SparkSQL ');
+----------------------+
|ltrim( SparkSQL )|
+----------------------+
| SparkSQL |
+----------------------+
-- luhn_check
SELECT luhn_check('8112189876');
+----------------------+
|luhn_check(8112189876)|
+----------------------+
| true|
+----------------------+
SELECT luhn_check('79927398713');
+-----------------------+
|luhn_check(79927398713)|
+-----------------------+
| true|
+-----------------------+
SELECT luhn_check('79927398714');
+-----------------------+
|luhn_check(79927398714)|
+-----------------------+
| false|
+-----------------------+
-- mask
SELECT mask('abcd-EFGH-8765-4321');
+----------------------------------------+
|mask(abcd-EFGH-8765-4321, X, x, n, NULL)|
+----------------------------------------+
| xxxx-XXXX-nnnn-nnnn|
+----------------------------------------+
SELECT mask('abcd-EFGH-8765-4321', 'Q');
+----------------------------------------+
|mask(abcd-EFGH-8765-4321, Q, x, n, NULL)|
+----------------------------------------+
| xxxx-QQQQ-nnnn-nnnn|
+----------------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q');
+--------------------------------+
|mask(AbCD123-@$#, Q, q, n, NULL)|
+--------------------------------+
| QqQQnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#');
+--------------------------------+
|mask(AbCD123-@$#, X, x, n, NULL)|
+--------------------------------+
| XxXXnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q');
+--------------------------------+
|mask(AbCD123-@$#, Q, x, n, NULL)|
+--------------------------------+
| QxQQnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q');
+--------------------------------+
|mask(AbCD123-@$#, Q, q, n, NULL)|
+--------------------------------+
| QqQQnnn-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q', 'd');
+--------------------------------+
|mask(AbCD123-@$#, Q, q, d, NULL)|
+--------------------------------+
| QqQQddd-@$#|
+--------------------------------+
SELECT mask('AbCD123-@$#', 'Q', 'q', 'd', 'o');
+-----------------------------+
|mask(AbCD123-@$#, Q, q, d, o)|
+-----------------------------+
| QqQQdddoooo|
+-----------------------------+
SELECT mask('AbCD123-@$#', NULL, 'q', 'd', 'o');
+--------------------------------+
|mask(AbCD123-@$#, NULL, q, d, o)|
+--------------------------------+
| AqCDdddoooo|
+--------------------------------+
SELECT mask('AbCD123-@$#', NULL, NULL, 'd', 'o');
+-----------------------------------+
|mask(AbCD123-@$#, NULL, NULL, d, o)|
+-----------------------------------+
| AbCDdddoooo|
+-----------------------------------+
SELECT mask('AbCD123-@$#', NULL, NULL, NULL, 'o');
+--------------------------------------+
|mask(AbCD123-@$#, NULL, NULL, NULL, o)|
+--------------------------------------+
| AbCD123oooo|
+--------------------------------------+
SELECT mask(NULL, NULL, NULL, NULL, 'o');
+-------------------------------+
|mask(NULL, NULL, NULL, NULL, o)|
+-------------------------------+
| NULL|
+-------------------------------+
SELECT mask(NULL);
+-------------------------+
|mask(NULL, X, x, n, NULL)|
+-------------------------+
| NULL|
+-------------------------+
SELECT mask('AbCD123-@$#', NULL, NULL, NULL, NULL);
+-----------------------------------------+
|mask(AbCD123-@$#, NULL, NULL, NULL, NULL)|
+-----------------------------------------+
| AbCD123-@$#|
+-----------------------------------------+
-- octet_length
SELECT octet_length('Spark SQL');
+-----------------------+
|octet_length(Spark SQL)|
+-----------------------+
| 9|
+-----------------------+
SELECT octet_length(x'537061726b2053514c');
+-----------------------------------+
|octet_length(X'537061726B2053514C')|
+-----------------------------------+
| 9|
+-----------------------------------+
-- overlay
SELECT overlay('Spark SQL' PLACING '_' FROM 6);
+----------------------------+
|overlay(Spark SQL, _, 6, -1)|
+----------------------------+
| Spark_SQL|
+----------------------------+
SELECT overlay('Spark SQL' PLACING 'CORE' FROM 7);
+-------------------------------+
|overlay(Spark SQL, CORE, 7, -1)|
+-------------------------------+
| Spark CORE|
+-------------------------------+
SELECT overlay('Spark SQL' PLACING 'ANSI ' FROM 7 FOR 0);
+-------------------------------+
|overlay(Spark SQL, ANSI , 7, 0)|
+-------------------------------+
| Spark ANSI SQL|
+-------------------------------+
SELECT overlay('Spark SQL' PLACING 'tructured' FROM 2 FOR 4);
+-----------------------------------+
|overlay(Spark SQL, tructured, 2, 4)|
+-----------------------------------+
| Structured SQL|
+-----------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('_', 'utf-8') FROM 6);
+----------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(_, utf-8), 6, -1)|
+----------------------------------------------------------+
| [53 70 61 72 6B 5...|
+----------------------------------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('CORE', 'utf-8') FROM 7);
+-------------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(CORE, utf-8), 7, -1)|
+-------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+-------------------------------------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('ANSI ', 'utf-8') FROM 7 FOR 0);
+-------------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(ANSI , utf-8), 7, 0)|
+-------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+-------------------------------------------------------------+
SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('tructured', 'utf-8') FROM 2 FOR 4);
+-----------------------------------------------------------------+
|overlay(encode(Spark SQL, utf-8), encode(tructured, utf-8), 2, 4)|
+-----------------------------------------------------------------+
| [53 74 72 75 63 7...|
+-----------------------------------------------------------------+
-- position
SELECT position('bar', 'foobarbar');
+---------------------------+
|position(bar, foobarbar, 1)|
+---------------------------+
| 4|
+---------------------------+
SELECT position('bar', 'foobarbar', 5);
+---------------------------+
|position(bar, foobarbar, 5)|
+---------------------------+
| 7|
+---------------------------+
SELECT POSITION('bar' IN 'foobarbar');
+-------------------------+
|locate(bar, foobarbar, 1)|
+-------------------------+
| 4|
+-------------------------+
-- printf
SELECT printf("Hello World %d %s", 100, "days");
+------------------------------------+
|printf(Hello World %d %s, 100, days)|
+------------------------------------+
| Hello World 100 days|
+------------------------------------+
-- regexp_count
SELECT regexp_count('Steven Jones and Stephen Smith are the best players', 'Ste(v|ph)en');
+------------------------------------------------------------------------------+
|regexp_count(Steven Jones and Stephen Smith are the best players, Ste(v|ph)en)|
+------------------------------------------------------------------------------+
| 2|
+------------------------------------------------------------------------------+
SELECT regexp_count('abcdefghijklmnopqrstuvwxyz', '[a-z]{3}');
+--------------------------------------------------+
|regexp_count(abcdefghijklmnopqrstuvwxyz, [a-z]{3})|
+--------------------------------------------------+
| 8|
+--------------------------------------------------+
-- regexp_extract
SELECT regexp_extract('100-200', '(\\d+)-(\\d+)', 1);
+---------------------------------------+
|regexp_extract(100-200, (\d+)-(\d+), 1)|
+---------------------------------------+
| 100|
+---------------------------------------+
-- regexp_extract_all
SELECT regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)', 1);
+----------------------------------------------------+
|regexp_extract_all(100-200, 300-400, (\d+)-(\d+), 1)|
+----------------------------------------------------+
| [100, 300]|
+----------------------------------------------------+
-- regexp_instr
SELECT regexp_instr('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.apache.org', '.', 2);
+-------------------------------------+
|substring_index(www.apache.org, ., 2)|
+-------------------------------------+
| www.apache|
+-------------------------------------+
-- to_binary
SELECT to_binary('abc', 'utf-8');
+---------------------+
|to_binary(abc, utf-8)|
+---------------------+
| [61 62 63]|
+---------------------+
-- to_char
SELECT to_char(454, '999');
+-----------------+
|to_char(454, 999)|
+-----------------+
| 454|
+-----------------+
SELECT to_char(454.00, '000D00');
+-----------------------+
|to_char(454.00, 000D00)|
+-----------------------+
| 454.00|
+-----------------------+
SELECT to_char(12454, '99G999');
+----------------------+
|to_char(12454, 99G999)|
+----------------------+
| 12,454|
+----------------------+
SELECT to_char(78.12, '$99.99');
+----------------------+
|to_char(78.12, $99.99)|
+----------------------+
| $78.12|
+----------------------+
SELECT to_char(-12454.8, '99G999D9S');
+----------------------------+
|to_char(-12454.8, 99G999D9S)|
+----------------------------+
| 12,454.8-|
+----------------------------+
-- to_number
SELECT to_number('454', '999');
+-------------------+
|to_number(454, 999)|
+-------------------+
| 454|
+-------------------+
SELECT to_number('454.00', '000.00');
+-------------------------+
|to_number(454.00, 000.00)|
+-------------------------+
| 454.00|
+-------------------------+
SELECT to_number('12,454', '99,999');
+-------------------------+
|to_number(12,454, 99,999)|
+-------------------------+
| 12454|
+-------------------------+
SELECT to_number('$78.12', '$99.99');
+-------------------------+
|to_number($78.12, $99.99)|
+-------------------------+
| 78.12|
+-------------------------+
SELECT to_number('12,454.8-', '99,999.9S');
+-------------------------------+
|to_number(12,454.8-, 99,999.9S)|
+-------------------------------+
| -12454.8|
+-------------------------------+
-- to_varchar
SELECT to_varchar(454, '999');
+-----------------+
|to_char(454, 999)|
+-----------------+
| 454|
+-----------------+
SELECT to_varchar(454.00, '000D00');
+-----------------------+
|to_char(454.00, 000D00)|
+-----------------------+
| 454.00|
+-----------------------+
SELECT to_varchar(12454, '99G999');
+----------------------+
|to_char(12454, 99G999)|
+----------------------+
| 12,454|
+----------------------+
SELECT to_varchar(78.12, '$99.99');
+----------------------+
|to_char(78.12, $99.99)|
+----------------------+
| $78.12|
+----------------------+
SELECT to_varchar(-12454.8, '99G999D9S');
+----------------------------+
|to_char(-12454.8, 99G999D9S)|
+----------------------------+
| 12,454.8-|
+----------------------------+
-- translate
SELECT translate('AaBbCc', 'abc', '123');
+---------------------------+
|translate(AaBbCc, abc, 123)|
+---------------------------+
| A1B2C3|
+---------------------------+
-- trim
SELECT trim(' SparkSQL ');
+---------------------+
|trim( SparkSQL )|
+---------------------+
| SparkSQL|
+---------------------+
SELECT trim(BOTH FROM ' SparkSQL ');
+---------------------+
|trim( SparkSQL )|
+---------------------+
| SparkSQL|
+---------------------+
SELECT trim(LEADING FROM ' SparkSQL ');
+----------------------+
|ltrim( SparkSQL )|
+----------------------+
| SparkSQL |
+----------------------+
SELECT trim(TRAILING FROM ' SparkSQL ');
+----------------------+
|rtrim( SparkSQL )|
+----------------------+
| SparkSQL|
+----------------------+
SELECT trim('SL' FROM 'SSparkSQLS');
+-----------------------------+
|TRIM(BOTH SL FROM SSparkSQLS)|
+-----------------------------+
| parkSQ|
+-----------------------------+
SELECT trim(BOTH 'SL' FROM 'SSparkSQLS');
+-----------------------------+
|TRIM(BOTH SL FROM SSparkSQLS)|
+-----------------------------+
| parkSQ|
+-----------------------------+
SELECT trim(LEADING 'SL' FROM 'SSparkSQLS');
+--------------------------------+
|TRIM(LEADING SL FROM SSparkSQLS)|
+--------------------------------+
| parkSQLS|
+--------------------------------+
SELECT trim(TRAILING 'SL' FROM 'SSparkSQLS');
+---------------------------------+
|TRIM(TRAILING SL FROM SSparkSQLS)|
+---------------------------------+
| SSparkSQ|
+---------------------------------+
-- try_to_binary
SELECT try_to_binary('abc', 'utf-8');
+-------------------------+
|try_to_binary(abc, utf-8)|
+-------------------------+
| [61 62 63]|
+-------------------------+
select try_to_binary('a!', 'base64');
+-------------------------+
|try_to_binary(a!, base64)|
+-------------------------+
| NULL|
+-------------------------+
select try_to_binary('abc', 'invalidFormat');
+---------------------------------+
|try_to_binary(abc, invalidFormat)|
+---------------------------------+
| NULL|
+---------------------------------+
-- try_to_number
SELECT try_to_number('454', '999');
+-----------------------+
|try_to_number(454, 999)|
+-----------------------+
| 454|
+-----------------------+
SELECT try_to_number('454.00', '000.00');
+-----------------------------+
|try_to_number(454.00, 000.00)|
+-----------------------------+
| 454.00|
+-----------------------------+
SELECT try_to_number('12,454', '99,999');
+-----------------------------+
|try_to_number(12,454, 99,999)|
+-----------------------------+
| 12454|
+-----------------------------+
SELECT try_to_number('$78.12', '$99.99');
+-----------------------------+
|try_to_number($78.12, $99.99)|
+-----------------------------+
| 78.12|
+-----------------------------+
SELECT try_to_number('12,454.8-', '99,999.9S');
+-----------------------------------+
|try_to_number(12,454.8-, 99,999.9S)|
+-----------------------------------+
| -12454.8|
+-----------------------------------+
-- ucase
SELECT ucase('SparkSql');
+---------------+
|ucase(SparkSql)|
+---------------+
| SPARKSQL|
+---------------+
-- unbase64
SELECT unbase64('U3BhcmsgU1FM');
+----------------------+
|unbase64(U3BhcmsgU1FM)|
+----------------------+
| [53 70 61 72 6B 2...|
+----------------------+
-- upper
SELECT upper('SparkSql');
+---------------+
|upper(SparkSql)|
+---------------+
| SPARKSQL|
+---------------+
條件函數
| 函數 | 說明 |
|---|---|
| coalesce(expr1, expr2, ...) | 如果存在，則傳回第一個非 null 引數。否則，為 null。 |
| if(expr1, expr2, expr3) | 如果 `expr1` 評估為 true，則傳回 `expr2`；否則傳回 `expr3`。 |
| ifnull(expr1, expr2) | 如果 `expr1` 為 null，則傳回 `expr2`，否則傳回 `expr1`。 |
| nanvl(expr1, expr2) | 如果 `expr1` 不是 NaN，則傳回 `expr1`，否則傳回 `expr2`。 |
| nullif(expr1, expr2) | 如果 `expr1` 等於 `expr2`，則傳回 null，否則傳回 `expr1`。 |
| nvl(expr1, expr2) | 如果 `expr1` 為 null，則傳回 `expr2`，否則傳回 `expr1`。 |
| nvl2(expr1, expr2, expr3) | 如果 `expr1` 不為 null，則傳回 `expr2`，否則傳回 `expr3`。 |
| CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4]* [ELSE expr5] END | 當 `expr1` = true 時，傳回 `expr2`；否則當 `expr3` = true 時，傳回 `expr4`；否則傳回 `expr5`。 |
範例
-- coalesce
SELECT coalesce(NULL, 1, NULL);
+-----------------------+
|coalesce(NULL, 1, NULL)|
+-----------------------+
| 1|
+-----------------------+
-- if
SELECT if(1 < 2, 'a', 'b');
+-------------------+
|(IF((1 < 2), a, b))|
+-------------------+
| a|
+-------------------+
-- ifnull
SELECT ifnull(NULL, array('2'));
+----------------------+
|ifnull(NULL, array(2))|
+----------------------+
| [2]|
+----------------------+
-- nanvl
SELECT nanvl(cast('NaN' as double), 123);
+-------------------------------+
|nanvl(CAST(NaN AS DOUBLE), 123)|
+-------------------------------+
| 123.0|
+-------------------------------+
-- nullif
SELECT nullif(2, 2);
+------------+
|nullif(2, 2)|
+------------+
| NULL|
+------------+
-- nvl
SELECT nvl(NULL, array('2'));
+-------------------+
|nvl(NULL, array(2))|
+-------------------+
| [2]|
+-------------------+
-- nvl2
SELECT nvl2(NULL, 2, 1);
+----------------+
|nvl2(NULL, 2, 1)|
+----------------+
| 1|
+----------------+
-- when
SELECT CASE WHEN 1 > 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
+-----------------------------------------------------------+
|CASE WHEN (1 > 0) THEN 1 WHEN (2 > 0) THEN 2.0 ELSE 1.2 END|
+-----------------------------------------------------------+
| 1.0|
+-----------------------------------------------------------+
SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
+-----------------------------------------------------------+
|CASE WHEN (1 < 0) THEN 1 WHEN (2 > 0) THEN 2.0 ELSE 1.2 END|
+-----------------------------------------------------------+
| 2.0|
+-----------------------------------------------------------+
SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 < 0 THEN 2.0 END;
+--------------------------------------------------+
|CASE WHEN (1 < 0) THEN 1 WHEN (2 < 0) THEN 2.0 END|
+--------------------------------------------------+
| NULL|
+--------------------------------------------------+
位元函數
| 函數 | 說明 |
|---|---|
| expr1 & expr2 | 傳回 `expr1` 和 `expr2` 的位元 AND 結果。 |
| expr1 ^ expr2 | 傳回 `expr1` 和 `expr2` 的位元獨佔 OR 結果。 |
| bit_count(expr) | 傳回參數 expr 中設定的位元數，為未簽署的 64 位元整數，或在參數為 NULL 時傳回 NULL。 |
| bit_get(expr, pos) | 傳回指定位置的位元值 (0 或 1)。位置從右到左編號，從 0 開始。位置參數不能為負值。 |
| getbit(expr, pos) | 傳回指定位置的位元值 (0 或 1)。位置從右到左編號，從 0 開始。位置參數不能為負值。 |
| shiftright(base, expr) | 位元 (有號) 右移。 |
| shiftrightunsigned(base, expr) | 位元未簽署右移。 |
| expr1 | expr2 | 傳回 `expr1` 和 `expr2` 的位元 OR 運算結果。 |
| ~ expr | 傳回 `expr` 的位元 NOT 運算結果。 |
範例
-- &
SELECT 3 & 5;
+-------+
|(3 & 5)|
+-------+
| 1|
+-------+
-- ^
SELECT 3 ^ 5;
+-------+
|(3 ^ 5)|
+-------+
| 6|
+-------+
-- bit_count
SELECT bit_count(0);
+------------+
|bit_count(0)|
+------------+
| 0|
+------------+
-- bit_get
SELECT bit_get(11, 0);
+--------------+
|bit_get(11, 0)|
+--------------+
| 1|
+--------------+
SELECT bit_get(11, 2);
+--------------+
|bit_get(11, 2)|
+--------------+
| 0|
+--------------+
-- getbit
SELECT getbit(11, 0);
+-------------+
|getbit(11, 0)|
+-------------+
| 1|
+-------------+
SELECT getbit(11, 2);
+-------------+
|getbit(11, 2)|
+-------------+
| 0|
+-------------+
-- shiftright
SELECT shiftright(4, 1);
+----------------+
|shiftright(4, 1)|
+----------------+
| 2|
+----------------+
-- shiftrightunsigned
SELECT shiftrightunsigned(4, 1);
+------------------------+
|shiftrightunsigned(4, 1)|
+------------------------+
| 2|
+------------------------+
-- |
SELECT 3 | 5;
+-------+
|(3 | 5)|
+-------+
| 7|
+-------+
-- ~
SELECT ~ 0;
+---+
| ~0|
+---+
| -1|
+---+
轉換函式
| 函數 | 說明 |
|---|---|
| bigint(expr) | 將值 `expr` 轉換為目標資料類型 `bigint`。 |
| binary(expr) | 將值 `expr` 轉換為目標資料類型 `binary`。 |
| boolean(expr) | 將值 `expr` 轉換為目標資料類型 `boolean`。 |
| cast(expr AS type) | 將值 `expr` 轉換為目標資料類型 `type`。 |
| date(expr) | 將值 `expr` 轉換為目標資料類型 `date`。 |
| decimal(expr) | 將值 `expr` 轉換為目標資料類型 `decimal`。 |
| double(expr) | 將值 `expr` 轉換為目標資料類型 `double`。 |
| float(expr) | 將值 `expr` 轉換為目標資料類型 `float`。 |
| int(expr) | 將值 `expr` 轉換為目標資料類型 `int`。 |
| smallint(expr) | 將值 `expr` 轉換為目標資料類型 `smallint`。 |
| string(expr) | 將值 `expr` 轉換為目標資料類型 `string`。 |
| timestamp(expr) | 將值 `expr` 轉換為目標資料類型 `timestamp`。 |
| tinyint(expr) | 將值 `expr` 轉換為目標資料類型 `tinyint`。 |
範例
-- cast
SELECT cast('10' as int);
+---------------+
|CAST(10 AS INT)|
+---------------+
| 10|
+---------------+
謂詞函式
| 函數 | 說明 |
|---|---|
| ! expr | 邏輯非。 |
| expr1 < expr2 | 如果 `expr1` 小於 `expr2`，傳回 true。 |
| expr1 <= expr2 | 如果 `expr1` 小於或等於 `expr2`，傳回 true。 |
| expr1 <=> expr2 | 對於非 NULL 參數，傳回與 EQUAL(=) 算子相同的結果，但如果兩個參數都是 NULL，傳回 true；如果其中一個參數為 NULL，傳回 false。 |
| expr1 = expr2 | 如果 `expr1` 等於 `expr2`，傳回 true，否則傳回 false。 |
| expr1 == expr2 | 如果 `expr1` 等於 `expr2`，傳回 true，否則傳回 false。 |
| expr1 > expr2 | 如果 `expr1` 大於 `expr2`，則傳回 true。 |
| expr1 >= expr2 | 如果 `expr1` 大於或等於 `expr2`，則傳回 true。 |
| expr1 and expr2 | 邏輯 AND。 |
| 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 | 邏輯 OR。 |
| regexp(str, regexp) | 如果 `str` 與 `regexp` 相符，則傳回 true；否則傳回 false。 |
| regexp_like(str, regexp) | 如果 `str` 與 `regexp` 相符，則傳回 true；否則傳回 false。 |
| rlike(str, regexp) | 如果 `str` 與 `regexp` 相符，則傳回 true；否則傳回 false。 |
範例
-- !
SELECT ! true;
+----------+
|(NOT true)|
+----------+
| false|
+----------+
SELECT ! false;
+-----------+
|(NOT false)|
+-----------+
| true|
+-----------+
SELECT ! NULL;
+----------+
|(NOT NULL)|
+----------+
| NULL|
+----------+
-- <
SELECT 1 < 2;
+-------+
|(1 < 2)|
+-------+
| true|
+-------+
SELECT 1.1 < '1';
+---------+
|(1.1 < 1)|
+---------+
| false|
+---------+
SELECT to_date('2009-07-30 04:17:52') < to_date('2009-07-30 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) < to_date(2009-07-30 04:17:52))|
+-------------------------------------------------------------+
| false|
+-------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') < to_date('2009-08-01 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) < to_date(2009-08-01 04:17:52))|
+-------------------------------------------------------------+
| true|
+-------------------------------------------------------------+
SELECT 1 < NULL;
+----------+
|(1 < NULL)|
+----------+
| NULL|
+----------+
-- <=
SELECT 2 <= 2;
+--------+
|(2 <= 2)|
+--------+
| true|
+--------+
SELECT 1.0 <= '1';
+----------+
|(1.0 <= 1)|
+----------+
| true|
+----------+
SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-07-30 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) <= to_date(2009-07-30 04:17:52))|
+--------------------------------------------------------------+
| true|
+--------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-08-01 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) <= to_date(2009-08-01 04:17:52))|
+--------------------------------------------------------------+
| true|
+--------------------------------------------------------------+
SELECT 1 <= NULL;
+-----------+
|(1 <= NULL)|
+-----------+
| NULL|
+-----------+
-- <=>
SELECT 2 <=> 2;
+---------+
|(2 <=> 2)|
+---------+
| true|
+---------+
SELECT 1 <=> '1';
+---------+
|(1 <=> 1)|
+---------+
| true|
+---------+
SELECT true <=> NULL;
+---------------+
|(true <=> NULL)|
+---------------+
| false|
+---------------+
SELECT NULL <=> NULL;
+---------------+
|(NULL <=> NULL)|
+---------------+
| true|
+---------------+
-- =
SELECT 2 = 2;
+-------+
|(2 = 2)|
+-------+
| true|
+-------+
SELECT 1 = '1';
+-------+
|(1 = 1)|
+-------+
| true|
+-------+
SELECT true = NULL;
+-------------+
|(true = NULL)|
+-------------+
| NULL|
+-------------+
SELECT NULL = NULL;
+-------------+
|(NULL = NULL)|
+-------------+
| NULL|
+-------------+
-- ==
SELECT 2 == 2;
+-------+
|(2 = 2)|
+-------+
| true|
+-------+
SELECT 1 == '1';
+-------+
|(1 = 1)|
+-------+
| true|
+-------+
SELECT true == NULL;
+-------------+
|(true = NULL)|
+-------------+
| NULL|
+-------------+
SELECT NULL == NULL;
+-------------+
|(NULL = NULL)|
+-------------+
| NULL|
+-------------+
-- >
SELECT 2 > 1;
+-------+
|(2 > 1)|
+-------+
| true|
+-------+
SELECT 2 > 1.1;
+-------+
|(2 > 1)|
+-------+
| true|
+-------+
SELECT to_date('2009-07-30 04:17:52') > to_date('2009-07-30 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) > to_date(2009-07-30 04:17:52))|
+-------------------------------------------------------------+
| false|
+-------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') > to_date('2009-08-01 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) > to_date(2009-08-01 04:17:52))|
+-------------------------------------------------------------+
| false|
+-------------------------------------------------------------+
SELECT 1 > NULL;
+----------+
|(1 > NULL)|
+----------+
| NULL|
+----------+
-- >=
SELECT 2 >= 1;
+--------+
|(2 >= 1)|
+--------+
| true|
+--------+
SELECT 2.0 >= '2.1';
+------------+
|(2.0 >= 2.1)|
+------------+
| false|
+------------+
SELECT to_date('2009-07-30 04:17:52') >= to_date('2009-07-30 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) >= to_date(2009-07-30 04:17:52))|
+--------------------------------------------------------------+
| true|
+--------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') >= to_date('2009-08-01 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) >= to_date(2009-08-01 04:17:52))|
+--------------------------------------------------------------+
| false|
+--------------------------------------------------------------+
SELECT 1 >= NULL;
+-----------+
|(1 >= NULL)|
+-----------+
| NULL|
+-----------+
-- and
SELECT true and true;
+---------------+
|(true AND true)|
+---------------+
| true|
+---------------+
SELECT true and false;
+----------------+
|(true AND false)|
+----------------+
| false|
+----------------+
SELECT true and NULL;
+---------------+
|(true AND NULL)|
+---------------+
| NULL|
+---------------+
SELECT false and NULL;
+----------------+
|(false AND NULL)|
+----------------+
| false|
+----------------+
-- ilike
SELECT ilike('Spark', '_Park');
+-------------------+
|ilike(Spark, _Park)|
+-------------------+
| true|
+-------------------+
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT '%SystemDrive%\Users\John' ilike '\%SystemDrive\%\\users%';
+--------------------------------------------------------+
|ilike(%SystemDrive%\Users\John, \%SystemDrive\%\\users%)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT '%SystemDrive%\\USERS\\John' ilike '\%SystemDrive\%\\\\Users%';
+--------------------------------------------------------+
|ilike(%SystemDrive%\USERS\John, \%SystemDrive\%\\Users%)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
SELECT '%SystemDrive%/Users/John' ilike '/%SYSTEMDrive/%//Users%' ESCAPE '/';
+--------------------------------------------------------+
|ilike(%SystemDrive%/Users/John, /%SYSTEMDrive/%//Users%)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
-- in
SELECT 1 in(1, 2, 3);
+----------------+
|(1 IN (1, 2, 3))|
+----------------+
| true|
+----------------+
SELECT 1 in(2, 3, 4);
+----------------+
|(1 IN (2, 3, 4))|
+----------------+
| false|
+----------------+
SELECT named_struct('a', 1, 'b', 2) in(named_struct('a', 1, 'b', 1), named_struct('a', 1, 'b', 3));
+----------------------------------------------------------------------------------+
|(named_struct(a, 1, b, 2) IN (named_struct(a, 1, b, 1), named_struct(a, 1, b, 3)))|
+----------------------------------------------------------------------------------+
| false|
+----------------------------------------------------------------------------------+
SELECT named_struct('a', 1, 'b', 2) in(named_struct('a', 1, 'b', 2), named_struct('a', 1, 'b', 3));
+----------------------------------------------------------------------------------+
|(named_struct(a, 1, b, 2) IN (named_struct(a, 1, b, 2), named_struct(a, 1, b, 3)))|
+----------------------------------------------------------------------------------+
| true|
+----------------------------------------------------------------------------------+
-- isnan
SELECT isnan(cast('NaN' as double));
+--------------------------+
|isnan(CAST(NaN AS DOUBLE))|
+--------------------------+
| true|
+--------------------------+
-- isnotnull
SELECT isnotnull(1);
+---------------+
|(1 IS NOT NULL)|
+---------------+
| true|
+---------------+
-- isnull
SELECT isnull(1);
+-----------+
|(1 IS NULL)|
+-----------+
| false|
+-----------+
-- like
SELECT like('Spark', '_park');
+----------------+
|Spark LIKE _park|
+----------------+
| true|
+----------------+
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT '%SystemDrive%\Users\John' like '\%SystemDrive\%\\Users%';
+-----------------------------------------------------+
|%SystemDrive%\Users\John LIKE \%SystemDrive\%\\Users%|
+-----------------------------------------------------+
| true|
+-----------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT '%SystemDrive%\\Users\\John' like '\%SystemDrive\%\\\\Users%';
+-----------------------------------------------------+
|%SystemDrive%\Users\John LIKE \%SystemDrive\%\\Users%|
+-----------------------------------------------------+
| true|
+-----------------------------------------------------+
SELECT '%SystemDrive%/Users/John' like '/%SystemDrive/%//Users%' ESCAPE '/';
+-----------------------------------------------------+
|%SystemDrive%/Users/John LIKE /%SystemDrive/%//Users%|
+-----------------------------------------------------+
| true|
+-----------------------------------------------------+
-- not
SELECT not true;
+----------+
|(NOT true)|
+----------+
| false|
+----------+
SELECT not false;
+-----------+
|(NOT false)|
+-----------+
| true|
+-----------+
SELECT not NULL;
+----------+
|(NOT NULL)|
+----------+
| NULL|
+----------+
-- or
SELECT true or false;
+---------------+
|(true OR false)|
+---------------+
| true|
+---------------+
SELECT false or false;
+----------------+
|(false OR false)|
+----------------+
| false|
+----------------+
SELECT true or NULL;
+--------------+
|(true OR NULL)|
+--------------+
| true|
+--------------+
SELECT false or NULL;
+---------------+
|(false OR NULL)|
+---------------+
| NULL|
+---------------+
-- regexp
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT regexp('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
+--------------------------------------------------------+
|REGEXP(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT regexp('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
+--------------------------------------------------------+
|REGEXP(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+--------------------------------------------------------+
| true|
+--------------------------------------------------------+
-- regexp_like
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT regexp_like('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
+-------------------------------------------------------------+
|REGEXP_LIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------------+
| true|
+-------------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT regexp_like('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
+-------------------------------------------------------------+
|REGEXP_LIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------------+
| true|
+-------------------------------------------------------------+
-- rlike
SET spark.sql.parser.escapedStringLiterals=true;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....| true|
+--------------------+-----+
SELECT rlike('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
+-------------------------------------------------------+
|RLIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------+
| true|
+-------------------------------------------------------+
SET spark.sql.parser.escapedStringLiterals=false;
+--------------------+-----+
| key|value|
+--------------------+-----+
|spark.sql.parser....|false|
+--------------------+-----+
SELECT rlike('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
+-------------------------------------------------------+
|RLIKE(%SystemDrive%\Users\John, %SystemDrive%\\Users.*)|
+-------------------------------------------------------+
| true|
+-------------------------------------------------------+
Csv 函數
| 函數 | 說明 |
|---|---|
| from_csv(csvStr, schema[, options]) | 傳回一個具有給定 `csvStr` 和 `schema` 的結構值。 |
| schema_of_csv(csv[, options]) | 傳回 CSV 字串的 DDL 格式中的 schema。 |
| to_csv(expr[, options]) | 傳回一個具有給定結構值的 CSV 字串 |
範例
-- from_csv
SELECT from_csv('1, 0.8', 'a INT, b DOUBLE');
+----------------+
|from_csv(1, 0.8)|
+----------------+
| {1, 0.8}|
+----------------+
SELECT from_csv('26/08/2015', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
+--------------------+
|from_csv(26/08/2015)|
+--------------------+
|{2015-08-26 00:00...|
+--------------------+
-- schema_of_csv
SELECT schema_of_csv('1,abc');
+--------------------+
|schema_of_csv(1,abc)|
+--------------------+
|STRUCT<_c0: INT, ...|
+--------------------+
-- to_csv
SELECT to_csv(named_struct('a', 1, 'b', 2));
+--------------------------------+
|to_csv(named_struct(a, 1, b, 2))|
+--------------------------------+
| 1,2|
+--------------------------------+
SELECT to_csv(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
+----------------------------------------------------------------+
|to_csv(named_struct(time, to_timestamp(2015-08-26, yyyy-MM-dd)))|
+----------------------------------------------------------------+
| 26/08/2015|
+----------------------------------------------------------------+
雜項函數
| 函數 | 說明 |
|---|---|
| aes_decrypt(expr, key[, mode[, padding[, aad]]]) | 使用 `mode` 和 `padding` 中的 AES 解密 `expr` 的值。支援 16、24 和 32 位元組的密鑰長度。支援的 (`mode`, `padding`) 組合為 ('ECB', 'PKCS'), ('GCM', 'NONE') 和 ('CBC', 'PKCS')。GCM 僅支援額外的驗證資料 (AAD)。如果提供用於加密，則必須提供相同的 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')。僅 CBC 和 GCM 模式支援選用的初始化向量 (IV)。這些向量必須為 CBC 的 16 位元組和 GCM 的 12 位元組。如果未提供，將產生一個隨機向量並將其附加到輸出的前面。僅 GCM 支援選用的額外驗證資料 (AAD)。如果提供用於加密，則必須提供相同的 AAD 值進行解密。預設模式為 GCM。 |
| assert_true(expr) | 如果 `expr` 不為 true，則擲回例外。 |
| bitmap_bit_position(child) | 傳回給定輸入子式表達式的位元位置。 |
| bitmap_bucket_number(child) | 傳回給定輸入子式表達式的區段編號。 |
| bitmap_count(child) | 傳回子位元圖中設定的位元數。 |
| current_catalog() | 傳回目前的目錄。 |
| current_database() | 傳回目前的資料庫。 |
| current_schema() | 傳回目前的資料庫。 |
| current_user() | 目前執行內容的使用者名稱。 |
| equal_null(expr1, expr2) | 對於非 NULL 參數，傳回與 EQUAL(=) 算子相同的結果，但如果兩個參數都是 NULL，傳回 true；如果其中一個參數為 NULL，傳回 false。 |
| hll_sketch_estimate(expr) | 傳回給定 Datasketches HllSketch 二進位表示法的估計唯一值數量。 |
| hll_union(first, second, allowDifferentLgConfigK) | 使用 Datasketches Union 物件合併兩個 Datasketches HllSketch 物件的二進位表示法。將 allowDifferentLgConfigK 設定為 true 以允許合併具有不同 lgConfigK 值的草圖（預設為 false）。 |
| input_file_block_length() | 傳回正在讀取的區段長度，如果不可用，則傳回 -1。 |
| input_file_block_start() | 傳回正在讀取的區段起始偏移量，如果不可用，則傳回 -1。 |
| input_file_name() | 傳回正在讀取的檔案名稱，如果不可用，則傳回空字串。 |
| java_method(class, method[, arg1[, arg2 ..]]) | 使用反射呼叫方法。 |
| monotonically_increasing_id() | 傳回單調遞增的 64 位元整數。保證產生的 ID 單調遞增且唯一，但不是連續的。目前的實作將分割區 ID 放置在上層 31 位元，而下層 33 位元表示每個分割區中的記錄編號。假設資料框少於 10 億個分割區，且每個分割區少於 80 億個記錄。此函式是非確定性的，因為其結果取決於分割區 ID。 |
| reflect(class, method[, arg1[, arg2 ..]]) | 使用反射呼叫方法。 |
| spark_partition_id() | 傳回目前的分割區 ID。 |
| try_aes_decrypt(expr, key[, mode[, padding[, aad]]]) | 這是 `aes_decrypt` 的特殊版本，執行相同的操作，但如果無法執行解密，則傳回 NULL 值，而不是引發錯誤。 |
| typeof(expr) | 傳回輸入資料類型的 DDL 格式類型字串。 |
| user() | 目前執行內容的使用者名稱。 |
| uuid() | 傳回通用唯一識別碼 (UUID) 字串。值傳回為標準 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, , ))|
+-----------------------------------------------------------+
| F30133168D792EAF3...|
+-----------------------------------------------------------+
SELECT hex(aes_encrypt('Spark SQL', '0000111122223333', 'GCM'));
+---------------------------------------------------------------+
|hex(aes_encrypt(Spark SQL, 0000111122223333, GCM, DEFAULT, , ))|
+---------------------------------------------------------------+
| 71E5323818BDA5893...|
+---------------------------------------------------------------+
SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'ECB', 'PKCS'));
+---------------------------------------------------------------+
|base64(aes_encrypt(Spark SQL, 1234567890abcdef, ECB, PKCS, , ))|
+---------------------------------------------------------------+
| 3lmwu+Mw0H3fi5NDv...|
+---------------------------------------------------------------+
SELECT base64(aes_encrypt('Apache Spark', '1234567890abcdef', 'CBC', 'DEFAULT'));
+---------------------------------------------------------------------+
|base64(aes_encrypt(Apache Spark, 1234567890abcdef, CBC, DEFAULT, , ))|
+---------------------------------------------------------------------+
| XPholDIX2Fq0MIEf4...|
+---------------------------------------------------------------------+
SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'CBC', 'DEFAULT', unhex('00000000000000000000000000000000')));
+---------------------------------------------------------------------------------------------------------------------+
|base64(aes_encrypt(Spark, abcdefghijklmnop12345678ABCDEFGH, CBC, DEFAULT, unhex(00000000000000000000000000000000), ))|
+---------------------------------------------------------------------------------------------------------------------+
| AAAAAAAAAAAAAAAAA...|
+---------------------------------------------------------------------------------------------------------------------+
SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'GCM', 'DEFAULT', unhex('000000000000000000000000'), 'This is an AAD mixed into the input'));
+------------------------------------------------------------------------------------------------------------------------------------------------+
|base64(aes_encrypt(Spark, abcdefghijklmnop12345678ABCDEFGH, GCM, DEFAULT, unhex(000000000000000000000000), This is an AAD mixed into the input))|
+------------------------------------------------------------------------------------------------------------------------------------------------+
| AAAAAAAAAAAAAAAAQ...|
+------------------------------------------------------------------------------------------------------------------------------------------------+
-- assert_true
SELECT assert_true(0 < 1);
+--------------------------------------------+
|assert_true((0 < 1), '(0 < 1)' is not true!)|
+--------------------------------------------+
| NULL|
+--------------------------------------------+
-- bitmap_bit_position
SELECT bitmap_bit_position(1);
+----------------------+
|bitmap_bit_position(1)|
+----------------------+
| 0|
+----------------------+
SELECT bitmap_bit_position(123);
+------------------------+
|bitmap_bit_position(123)|
+------------------------+
| 122|
+------------------------+
-- bitmap_bucket_number
SELECT bitmap_bucket_number(123);
+-------------------------+
|bitmap_bucket_number(123)|
+-------------------------+
| 1|
+-------------------------+
SELECT bitmap_bucket_number(0);
+-----------------------+
|bitmap_bucket_number(0)|
+-----------------------+
| 0|
+-----------------------+
-- bitmap_count
SELECT bitmap_count(X '1010');
+---------------------+
|bitmap_count(X'1010')|
+---------------------+
| 2|
+---------------------+
SELECT bitmap_count(X 'FFFF');
+---------------------+
|bitmap_count(X'FFFF')|
+---------------------+
| 16|
+---------------------+
SELECT bitmap_count(X '0');
+-------------------+
|bitmap_count(X'00')|
+-------------------+
| 0|
+-------------------+
-- current_catalog
SELECT current_catalog();
+-----------------+
|current_catalog()|
+-----------------+
| spark_catalog|
+-----------------+
-- current_database
SELECT current_database();
+------------------+
|current_database()|
+------------------+
| default|
+------------------+
-- current_schema
SELECT current_schema();
+------------------+
|current_database()|
+------------------+
| default|
+------------------+
-- current_user
SELECT current_user();
+--------------+
|current_user()|
+--------------+
| jungtaek.lim|
+--------------+
-- equal_null
SELECT equal_null(3, 3);
+----------------+
|equal_null(3, 3)|
+----------------+
| true|
+----------------+
SELECT equal_null(1, '11');
+-----------------+
|equal_null(1, 11)|
+-----------------+
| false|
+-----------------+
SELECT equal_null(true, NULL);
+----------------------+
|equal_null(true, NULL)|
+----------------------+
| false|
+----------------------+
SELECT equal_null(NULL, 'abc');
+---------------------+
|equal_null(NULL, abc)|
+---------------------+
| false|
+---------------------+
SELECT equal_null(NULL, NULL);
+----------------------+
|equal_null(NULL, NULL)|
+----------------------+
| true|
+----------------------+
-- hll_sketch_estimate
SELECT hll_sketch_estimate(hll_sketch_agg(col)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
+--------------------------------------------+
|hll_sketch_estimate(hll_sketch_agg(col, 12))|
+--------------------------------------------+
| 3|
+--------------------------------------------+
-- hll_union
SELECT hll_sketch_estimate(hll_union(hll_sketch_agg(col1), hll_sketch_agg(col2))) FROM VALUES (1, 4), (1, 4), (2, 5), (2, 5), (3, 6) tab(col1, col2);
+-----------------------------------------------------------------------------------------+
|hll_sketch_estimate(hll_union(hll_sketch_agg(col1, 12), hll_sketch_agg(col2, 12), false))|
+-----------------------------------------------------------------------------------------+
| 6|
+-----------------------------------------------------------------------------------------+
-- input_file_block_length
SELECT input_file_block_length();
+-------------------------+
|input_file_block_length()|
+-------------------------+
| -1|
+-------------------------+
-- input_file_block_start
SELECT input_file_block_start();
+------------------------+
|input_file_block_start()|
+------------------------+
| -1|
+------------------------+
-- input_file_name
SELECT input_file_name();
+-----------------+
|input_file_name()|
+-----------------+
| |
+-----------------+
-- java_method
SELECT java_method('java.util.UUID', 'randomUUID');
+---------------------------------------+
|java_method(java.util.UUID, randomUUID)|
+---------------------------------------+
| fe37ceed-2d82-464...|
+---------------------------------------+
SELECT java_method('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
+-----------------------------------------------------------------------------+
|java_method(java.util.UUID, fromString, a5cf6c42-0c85-418f-af6c-3e4e5b1328f2)|
+-----------------------------------------------------------------------------+
| a5cf6c42-0c85-418...|
+-----------------------------------------------------------------------------+
-- monotonically_increasing_id
SELECT monotonically_increasing_id();
+-----------------------------+
|monotonically_increasing_id()|
+-----------------------------+
| 0|
+-----------------------------+
-- reflect
SELECT reflect('java.util.UUID', 'randomUUID');
+-----------------------------------+
|reflect(java.util.UUID, randomUUID)|
+-----------------------------------+
| 7fec8bae-b80b-448...|
+-----------------------------------+
SELECT reflect('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
+-------------------------------------------------------------------------+
|reflect(java.util.UUID, fromString, a5cf6c42-0c85-418f-af6c-3e4e5b1328f2)|
+-------------------------------------------------------------------------+
| a5cf6c42-0c85-418...|
+-------------------------------------------------------------------------+
-- spark_partition_id
SELECT spark_partition_id();
+--------------------+
|SPARK_PARTITION_ID()|
+--------------------+
| 0|
+--------------------+
-- try_aes_decrypt
SELECT try_aes_decrypt(unhex('6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
+------------------------------------------------------------------------------------------------------------------------------------+
|try_aes_decrypt(unhex(6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210), 0000111122223333, GCM, DEFAULT, )|
+------------------------------------------------------------------------------------------------------------------------------------+
| [53 70 61 72 6B 2...|
+------------------------------------------------------------------------------------------------------------------------------------+
SELECT try_aes_decrypt(unhex('----------468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
+------------------------------------------------------------------------------------------------------------------------------------+
|try_aes_decrypt(unhex(----------468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210), 0000111122223333, GCM, DEFAULT, )|
+------------------------------------------------------------------------------------------------------------------------------------+
| NULL|
+------------------------------------------------------------------------------------------------------------------------------------+
-- typeof
SELECT typeof(1);
+---------+
|typeof(1)|
+---------+
| int|
+---------+
SELECT typeof(array(1));
+----------------+
|typeof(array(1))|
+----------------+
| array<int>|
+----------------+
-- user
SELECT user();
+--------------+
|current_user()|
+--------------+
| jungtaek.lim|
+--------------+
-- uuid
SELECT uuid();
+--------------------+
| uuid()|
+--------------------+
|55eab4e0-7a6a-444...|
+--------------------+
-- version
SELECT version();
+--------------------+
| version()|
+--------------------+
|3.5.1 fd86f85e181...|
+--------------------+
產生器函數
| 函數 | 說明 |
|---|---|
| explode(expr) | 將陣列 `expr` 的元素分隔成多列，或將映射 `expr` 的元素分隔成多列和多欄。除非另有指定，否則使用預設欄位名稱 `col` 作為陣列的元素，或使用 `key` 和 `value` 作為映射的元素。 |
| explode_outer(expr) | 將陣列 `expr` 的元素分隔成多列，或將映射 `expr` 的元素分隔成多列和多欄。除非另有指定，否則使用預設欄位名稱 `col` 作為陣列的元素，或使用 `key` 和 `value` 作為映射的元素。 |
| inline(expr) | 將結構陣列展開成一個表格。除非另有指定，否則預設使用欄位名稱 col1、col2 等。 |
| inline_outer(expr) | 將結構陣列展開成一個表格。除非另有指定，否則預設使用欄位名稱 col1、col2 等。 |
| posexplode(expr) | 將陣列 `expr` 的元素分隔成具有位置的多列，或將映射 `expr` 的元素分隔成具有位置的多列和多欄。除非另有指定，否則使用欄位名稱 `pos` 作為位置，使用 `col` 作為陣列的元素，或使用 `key` 和 `value` 作為映射的元素。 |
| posexplode_outer(expr) | 將陣列 `expr` 的元素分隔成具有位置的多列，或將映射 `expr` 的元素分隔成具有位置的多列和多欄。除非另有指定，否則使用欄位名稱 `pos` 作為位置，使用 `col` 作為陣列的元素，或使用 `key` 和 `value` 作為映射的元素。 |
| stack(n, expr1, ..., exprk) | 將 `expr1`、...、`exprk` 分隔成 `n` 列。除非另有指定，否則預設使用欄位名稱 col0、col1 等。 |
範例
-- explode
SELECT explode(array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT explode(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT * FROM explode(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
-- explode_outer
SELECT explode_outer(array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT explode_outer(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
SELECT * FROM explode_outer(collection => array(10, 20));
+---+
|col|
+---+
| 10|
| 20|
+---+
-- inline
SELECT inline(array(struct(1, 'a'), struct(2, 'b')));
+----+----+
|col1|col2|
+----+----+
| 1| a|
| 2| b|
+----+----+
-- inline_outer
SELECT inline_outer(array(struct(1, 'a'), struct(2, 'b')));
+----+----+
|col1|col2|
+----+----+
| 1| a|
| 2| b|
+----+----+
-- posexplode
SELECT posexplode(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
SELECT * FROM posexplode(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
-- posexplode_outer
SELECT posexplode_outer(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
SELECT * FROM posexplode_outer(array(10,20));
+---+---+
|pos|col|
+---+---+
| 0| 10|
| 1| 20|
+---+---+
-- stack
SELECT stack(2, 1, 2, 3);
+----+----+
|col0|col1|
+----+----+
| 1| 2|
| 3|NULL|
+----+----+