数据湖计算 DLC 其他函数-SQL 语法-文档中心-腾讯云

FIELD
函数语法：
FIELD(<val> T, <val1> T, <val2> T, ...)
支持引擎：Presto
使用说明：返回 val1、val2…列表中 val 的索引，如果未找到则返回0。
支持所有基元类型，使用 str.equals（x）比较参数。如果 val 为 NULL，则返回值为0。
返回类型：integer
示例：
select field('world', 'say', 'hello', 'world');
 3
COALESCE
函数语法：
﻿
COALESCE(<expr1> T, <expr2> T)
支持引擎：SparkSQL、Presto
使用说明：如果存在，则返回第一个非空参数。否则返回 null。
返回类型：integer
示例：
> SELECT coalesce(NULL, 1, NULL);
 1
EXPLODE
函数语法：
﻿
EXPLODE(<expr> array<T>|map<K, V>)
支持引擎：SparkSQL
使用说明：将 array 类型的 expr 的元素分隔为多行，或将 map 类型的 expr 分隔为多个行和列。对数组的元素使用默认列名 col，或对映射的元素使用 key 和 value。
返回类型：row(col T) | row(key K, value V)
示例：
SELECT explode(array(10, 20));
 10
 20
EXPLODE_OUTER
函数语法：
﻿
EXPLODE_OUTER(<expr> array<T>|map<K, V>)
支持引擎：SparkSQL
使用说明：将 array 类型的 expr 的元素分隔为多行，或将 map 类型的 expr 分隔为多个行和列。对数组的元素使用默认列名 col，或对映射的元素使用 key 和 value。
返回类型：row(col T) | row(key K, value V)
示例：
SELECT explode_outer(array(10, 20));
 10
 20
GREATEST
函数语法：
﻿
GREATEST(<expr1> T, <expr2> T, ...>)
支持引擎：SparkSQL、Presto
使用说明：返回所有参数中的最大值，跳过空值。
返回类型：T
示例：
> SELECT greatest(10, 9, 2, 4, 3);
 10
IF
函数语法：
﻿
IF(<expr1> boolean, <expr2> T, <expr3> U)
支持引擎：SparkSQL、Presto
使用说明：如果expr1的计算结果为true，则返回expr2；否则返回expr3。
返回类型：T|U
示例：
> SELECT if(1 < 2, 'a', 'b');
 a
INLINE
函数语法：
﻿
INLINE(a array<struct<f1:T1,...,fn:Tn>>)
支持引擎：SparkSQL
使用说明：将结构数组分解为表。默认情况下使用列名col1、col2等。
返回类型：row(T1, ..., Tn)
示例：
> SELECT inline(array(struct(1, 'a'), struct(2, 'b')));
 1  a
 2  b
INLINE_OUTER
函数语法：
﻿
INLINE_OUTER(a array<struct<f1:T1,...,fn:Tn>>)
支持引擎：SparkSQL
使用说明：将结构数组分解为表。默认情况下使用列名col1、col2等。
返回类型：row(T1, ..., Tn)
示例：
> SELECT inline(array(struct(1, 'a'), struct(2, 'b')));
 1  a
 2  b
IN
函数语法：
﻿
<expr1> IN(<expr2> T, <expr3> T, ...)
支持引擎：SparkSQL、Presto
使用说明：如果expr1等于任何exprn，则返回true。
返回类型：boolean
示例：
> SELECT 1 in(1, 2, 3);
 true
> SELECT 1 in(2, 3, 4);
 false
ISNAN
函数语法：
﻿
ISNAN(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：如果expr为NaN，则返回true，否则返回false。
返回类型：boolean
示例：
> SELECT isnan(cast('NaN' as double));
 true
IFNULL
函数语法：
﻿
IFNULL(<expr1> T, <expr2> U)
支持引擎：SparkSQL
使用说明：如果expr1为null，则返回expr2，否则返回expr1。
返回类型：T|U
示例：
> SELECT ifnull(NULL, array('2'));
 ["2"]
ISNULL
函数语法：
﻿
ISNULL(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：如果expr为null，则返回true，否则返回false。
返回类型：boolean
示例：
> SELECT isnull(1);
 false
ISNOTNULL
函数语法：
﻿
ISNOTNULL(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：如果expr不为null，则返回true，否则返回false。
返回类型：boolean
示例：
> SELECT isnotnull(1);
 true
LEAST
函数语法：
﻿
LEAST(<expr1> T, <expr2> T, ...)
支持引擎：SparkSQL、Presto
使用说明：返回所有参数中的最小值，跳过null。
返回类型：T
示例：
> SELECT least(10, 9, 2, 4, 3);
 2
NANVL
函数语法：
﻿
NANVL(<expr1> T, <expr2> U)
支持引擎：SparkSQL、Presto
使用说明：nanvl（expr1，expr2），如果expr1不是NaN，则返回expr1；否则返回expr2。
返回类型：T|U
示例：
> SELECT nanvl(cast('NaN' as double), 123);
 123.0
NULLIF
函数语法：
﻿
NULLIF(<expr1> T, <expr2> U)
支持引擎：SparkSQL、Presto
使用说明：如果expr1等于expr2，则返回null，否则返回expr1。
返回类型：T
示例：
> SELECT nullif(2, 2);
 NULL
NVL
函数语法：
﻿
NVL(<expr1> T, <expr2> U)
支持引擎：SparkSQL、Presto
使用说明：如果expr1为null，则返回expr2，否则返回expr1。
返回类型：T|U
示例：
> SELECT nvl(NULL, array('2'));
 ["2"]
NVL2
函数语法：
﻿
NVL2(<expr1> T1, <expr2> T2, <expr3> T3)
支持引擎：SparkSQL、Presto
使用说明：如果expr1不为null，则返回expr2，否则返回expr3。
返回类型：T2|T3
示例：
> SELECT nvl2(NULL, 2, 1);
 1
POSEXPLODE
函数语法：
﻿
POSEXPLODE(<expr> array<T>|map<K, V>)
支持引擎：SparkSQL
使用说明：将array类型的expr的元素分隔为多行，或将map类型的expr分隔为多个行和列。使用列名pos表示位置,对数组的元素使用默认列名col，或对映射的元素使用key和value。
返回类型：row(pos integer, col T)|row(row integer, key K, value V)
示例：
> SELECT posexplode(array(10,20));
 0  10
 1  20
POSEXPLODE_OUTER
函数语法：
﻿
POSEXPLODE_OUTER(<expr> array<T>|map<K, V>)
支持引擎：SparkSQL
使用说明：将array类型的expr的元素分隔为多行，或将map类型的expr分隔为多个行和列。使用列名pos表示位置,对数组的元素使用默认列名col，或对映射的元素使用key和value。
返回类型：row(pos integer, col T)|row(row integer, key K, value V)
示例：
> SELECT posexplode_outer(array(10,20));
 0  10
 1  20
STACK
函数语法：
﻿
STACK(<n> integer, <expr0> T0, ..., <expr1> T1)
支持引擎：SparkSQL
使用说明：堆栈（n，expr1，…，exprk）-将expr1、…、exprk分隔为n行。默认情况下使用列名col0、col1等。
返回类型：row(col0 T0, ..., coln Tn)
示例：
> SELECT stack(2, 1, 2, 3);
 1  2
 3  NULL
ASSERT_TRUE
函数语法：
﻿
ASSERT_TRUE(<expr> boolean)
支持引擎：SparkSQL、Presto
使用说明：如果expr不为true，则抛出异常。
返回类型：boolean
示例：
> SELECT assert_true(0 < 1);
 NULL
RAISE_ERROR
函数语法：
﻿
RAISE_ERROR(<error> string)
支持引擎：SparkSQL、Presto
使用说明：抛出expr异常。
返回类型：string
示例：
> SELECT raise_error('custom error message');
 java.lang.RuntimeException
 custom error message
SPARK_PARTITION_ID
函数语法：
﻿
SPARK_PARTITION_ID()
支持引擎：SparkSQL
使用说明：返回当前分区id。
返回类型：integer
示例：
> SELECT spark_partition_id();
 0
INPUT_FILE_NAME
函数语法：
﻿
INPUT_FILE_NAME()
支持引擎：SparkSQL
使用说明：返回正在读取的文件的名称，如果不可用，则返回空字符串。
返回类型：string
示例：
> SELECT input_file_name();
INPUT_FILE_BLOCK_START
函数语法：
﻿
INPUT_FILE_BLOCK_START()
支持引擎：SparkSQL
使用说明：返回正在读取的块的开始偏移量，如果不可用，则返回-1。
返回类型：integer
示例：
> SELECT input_file_block_start();
 -1
INPUT_FILE_BLOCK_LENGTH
函数语法：
﻿
INPUT_FILE_BLOCK_LENGTH()
支持引擎：SparkSQL
使用说明：返回正在读取的块的长度，如果不可用，则返回-1。
返回类型：integer
示例：
> SELECT input_file_block_length();
 -1
MONOTONICALLY_INCREASING_ID
函数语法：
﻿
MONOTONICALLY_INCREASING_ID()
支持引擎：SparkSQL
使用说明：返回单调递增的64位整数。生成的ID保证单调递增且唯一，但不是连续的。当前实现将分区ID放在高31位，低33位表示每个分区内的记录号。假设数据帧的分区少于10亿，每个分区的记录少于80亿条。该函数是不确定的，因为其结果取决于分区ID。
返回类型：bigint
示例：
> SELECT monotonically_increasing_id();
 0
CURRENT_DATABASE
函数语法：
﻿
CURRENT_DATABASE()
支持引擎：SparkSQL
使用说明：返回当前database。
返回类型：string
示例：
> SELECT current_database();
 default
CURRENT_CATALOG
函数语法：
﻿
CURRENT_CATALOG()
支持引擎：SparkSQL
使用说明：返回当前catalog
返回类型：string
示例：
> SELECT current_catalog();
 spark_catalog
CURRENT_USER
函数语法：
﻿
CURRENT_USER()
支持引擎：SparkSQL、Presto
使用说明：返回当前用户
返回类型：string
示例：
> SELECT current_user();
REFLECT
函数语法：
﻿
REFLECT(<class> string, <method> string[, <arg1> T1[, <arg2> T2, ...]])
支持引擎：SparkSQL、Presto
使用说明：调用具有反射的方法。
返回类型：string
示例：
> select reflect('java.lang.Math', 'abs', -1);
 1
JAVA_METHOD
函数语法：
﻿
JAVA_METHOD(<class> string, <method> string[, <arg1> T1[, <arg2> T2, ...]])
支持引擎：SparkSQL、Presto
使用说明：调用具有反射的方法。
返回类型：string
示例：
> select JAVA_METHOD('java.lang.Math', 'abs', -1);
 1
VERSION
函数语法：
﻿
VERSION()
支持引擎：SparkSQL、Presto
使用说明：返回引擎版本。
返回类型：string
示例：
> select VERSION()
 3.0.0 rce61711a5fa54ab34fc74d86d521ecaeea6b072a
TYPEOF
函数语法：
﻿
TYPEOF(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：返回expr的数据类型
返回类型：string
示例：
> SELECT typeof(1);
 int
> SELECT typeof(array(1));
 array<int>
CAST
函数语法：
﻿
CAST(<expr> AS <type>)
支持引擎：SparkSQL、Presto
使用说明：将expr转换为type类型
返回类型：<type>
示例：
> SELECT cast('10' as int);
 10
BOOLEAN
函数语法：
﻿
BOOLEAN(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：将expr转换为boolean类型
返回类型：boolean
示例：
> SELECT boolean(1);
 true
BIGINT
函数语法：
﻿
BIGINT(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：强制类型转换为bigint
返回类型：bigint
示例：
> select bigint(0);
 0
BINARY
函数语法：
﻿
BINARY(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：强制类型转换为BINARY
返回类型：binary
示例：
> select binary(65);
 A
DOUBLE
函数语法：
﻿
DOUBLE(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：强制类型转换为double
返回类型：double
示例：
select double(1);
 1.0
FLOAT
函数语法：
﻿
FLOAT(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：强制类型转换为float
返回类型：float
示例：
> select float(1);
 1.0
INT
函数语法：
﻿
INT(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：强制类型转换为integer
返回类型：integer
示例：
> select int(1.0);
 1
SMALLINT
函数语法：
﻿
SMALLINT(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：强制转换为smallint类型
返回类型：smallint
示例：
select typeof(smallint(1));
 smallint
STRING
函数语法：
﻿
STRING(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：强制类型转换为string
返回类型：string
示例：
> select typeof(string(1));
 string
TINYINT
函数语法：
﻿
TINYINT(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：强制类型转换为tinyint
返回类型：tinyint
示例：
> select typeof(tinyint(1));
 tinyint
DECIMAL
函数语法：
DECIMAL(<expr> T)
支持引擎：SparkSQL、Presto
使用说明：强制类型转换为decimal
返回类型：decimal
示例：
> select typeof(decimal(1));
 decimal(10, 0)
GET_IDCARD_BIRTHDAY
函数语法：
GET_IDCARD_BIRTHDAY(<string> idcardno)
支持引擎：SparkSQL
使用说明：获取身份证号中的出生日期
idcardno：必填，string 类型，必须是15号或18位身份证号码，会校验身份证合理性。不允许其他非 null 型输入。输入为 null 时返回为 null。
返回类型：date
示例（以下测试身份证号为随机样例，不代表正确身份证号）
> SELECT get_idcard_birthday('421081199001011222');
1990-01-01
GET_IDCARD_SEX
函数语法：
GET_IDCARD_SEX(<string> idcardno)
支持引擎：SparkSQL
使用说明：获取身份证号中的性别
idcardno：必填，string 类型，必须是15号或18位身份证号码，会校验身份证合理性。不允许其他非 null 型输入。输入为 null 时返回为 null。
返回类型：string
示例：（以下测试身份证号为随机样例，不代表正确身份证号）
> SELECT get_idcard_birthday('421081199001011222');
﻿
GET_IDCARD_AGE
函数语法：
GET_IDCARD_AGE(<string> idcardno)
支持引擎：SparkSQL
使用说明：获取身份证号中的年龄
idcardno：必填，string 类型，必须是15号或18位身份证号码，会校验身份证合理性。不允许其他非 null 型输入。输入为 null 时返回为 null。
返回类型：int
示例：（以下测试身份证号为随机样例，不代表正确身份证号）
> SELECT get_idcard_age('421081197001021233');
53
MAX_PT
函数语法：
MAX_PT(<const string> tableFullName)
支持引擎：SparkSQL
使用说明：获取指定分区表中的最大值
tableFullName：必填，string 类型，必须是常量值，否则会报错。tableFullName 由三段组成 catalog.db.table，其中 catalog 和 db 省略时会默认取当前 session 的配置，建议写全。
当输入的表不是分区表时，会报错。
该函数仅返回有数据的最大分区，由于是否有数据涉及到元数据中心，所以对外部表，需要注意是否有执行 ANALYZE 语句将分区的统计信息汇报到元数据中。
分区值按字典序排列取最大值。当有多级分区时，仅取第一级分区做排序。
返回类型：string
示例：
> SELECT max_pt('test.tableName');
20231024
> select * from test.tableName where dt=max_pt('test.tableName');
等同于select * from test.tableName where dt='20231014';
TRANS_ARRAY
函数语法：
TRANS_ARRAY(<int> numKeys, <string> separator, <key1>, <key2>, ..., <col1>, <col2>, ...)
支持引擎：SparkSQL
使用说明：将指定的多列 cols 拆分并转置成多行，同时支持指定部分列 keys 作为转置的 key。
numKeys：int 型，必填，表示作为转置的 keys 列的个数，必须大于等于0。
separator：string 类型，必填，当 cols 为字符串时，需要根据 separator 做拆分；当 cols 为数组时，该参数可随意填写。
keys：任意类型，个数由 numKeys 决定。
cols：string 类型或数组类型，指定的列中除去 keys 都视为 cols，即要拆分和转置的列。所有 cols 的类型必须相同，即全部为字符串，或者全部为数组，也可以特殊支持字符串和字符串数组两种类型组合的 cols。当没有 cols 时，只会输出一行。
注意1：keys 和 cols 两者的数量之和必须大于0。
注意2：当 cols 拆分后的长度不相等时，最终转置后的行数以最长的那一列为准，其他列补 NULL。
返回类型：任意类型。keys 的类型保持不变，cols 类型为字符串或数组的元素类型。
示例：
> SELECT trans_array(1, ',', key, trans1, trans2) as (key, col1, col2) from values ('1', '2,3', array('4', '5')) as tab (key, trans1, trans2);
1 2 4
1 3 5
> SELECT trans_array(0, ',', key, trans1, trans2) as (key, col1, col2) from values ('1', '2,3', array('4')) as tab (key, trans1, trans2);
1 2 4
NULL 3 NULL
> SELECT trans_array(3, ',', key, trans1, trans2) as (key, col1, col2) from values ('1', '2,3', array('4', '5')) as tab (key, trans1, trans2);
12,3 [4,5]
TRANS_COLS
函数语法：
TRANS_COLS(<int> numKeys, <key1>, <key2>, ..., <col1>, <col2>, ...)
支持引擎：SparkSQL
使用说明：将指定的多列cols转置成多行，同时支持指定部分列keys作为转置的key。
numKeys：int型，必填，表示作为转置的keys列的个数，必须大于等于0。
keys：任意类型，个数由numKeys决定。
cols：任意类型，指定的列中除去keys都视为cols，即要转置的列。所有cols的类型必须相同。当没有cols时，只会输出一行。
注意：keys和cols两者的数量之和必须大于0。
返回类型：任意类型。输出会包含一列idx，表示该行在转置的所有行中的序号，keys的类型保持不变，cols类型保持不变。
示例：
> SELECT trans_cols(1,sid,ip1,ip2) as (idx, sid, ip) from values ('s1','0.0.0.0','1.1.1.1') as tab(sid,ip1,ip2);
1 s1 0.0.0.0
2 s1 1.1.1.1
SAMPLE
函数语法：
SAMPLE(<int/long> totalParts[, <int/long> pointParts][, <col1>, <col2>, ...])
支持引擎：SparkSQL
使用说明：采样函数，对全局或按指定的列Hash值划分为totalParts份，并选择指定的第pointParts份结果返回。
totalParts：int/long型，必填，表示总共需要划分的分区数。
pointParts：int/long型，可填，表示要选择返回的第几份数据，默认值为1，必须小于等于totalParts。
cols：任意类型，可填，可以指定任意多列作为划分数据时的key，将按这些列的hash值来划分。
注意1：当cols不指定时，或者当前指定的cols全部为NULL时，为保证数据不倾斜，会使用随机值替代hash值做计算。而当cols指定时，只要有一列不为NULL，就会对hash值计算及分区结果有影响。
注意2：由于Hash值与随机值的种子时固定的，因此反复执行时只要数据顺序一致，单个partition内的采样数据也是一致的。对于存在小文件合并的场景，可能导致采样顺序变化，这个时候建议指定列做采样。
返回类型：boolean，为true表示被采样，为false表示未采样。
示例：
> select * from values (1, 'm1'), (2, 't2'), (3, 'z3') as tab(dt, uid) where sample(3, 1);
2 t2
→ select * from values (1, 'm1'), (2, 't2'), (3, 'z3') as tab(dt, uid) where sample(3, 1, dt);
2 t2
3 z3
TO_CHAR
函数语法：
TO_CHAR(<boolean|int|long|double|decimal> data [, <string> format])
TO_CHAR(<date|timestamp> data, string format)
支持引擎：SparkSQL
使用说明1：按指定模板格式化数字。format可以省略，即直接将数字转为字符串。否则，format的定义如下：
'0' or '9': 数字的占位符，对字符串首部，如果0占位则没有数字时用0替代，如果9占位则没有数字时用空格代替；对字符串尾部，如果没有数字统一用0代替。
'.' or 'D': 小数点的占位符，只允许出现一次。
',' or 'G': 逗号，例如百、百万的占位符，左右必须是0-9数字。
'$': 美元符，只能出现一次。
'S' or 'MI': 负号和正号的占位符，只能出现在字符串首尾最多一次。
'PR': 只允许出现在字符串结尾一次，当输入的数据是符负号时会给数字求正并加上尖括号。
使用说明2：按指定模板格式化日期或时间。format不能省略，定义如下：
yyyyMMdd HH:mm:ss.SSS中的任意字符组成的模板。
详细参考: https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html﻿
使用说明3：TO_CHAR支持隐式转换第一个参数的类型，当第一个参数为string类型时，函数会默认转为 timestamp 走模板格式化日期或时间的逻辑。
返回类型：string类型。
示例：
> select to_char(124.23);
124.23
> select to_char(124.23, '00999.9999');
<space><space>124.2300
> select to_char(4124.23, '9,999.99');
4,124.23
> select to_char(-124.23, '999.99S');
124.23-
> select to_char(-4124.23, '$9,999.99PR');
<$4,124.23>
> select to_char(date '2016-12-31 12:34:56', 'yyyyMMddHHmmss');
20161231123456
﻿
其他函数

本页目录：

FIELD

COALESCE

EXPLODE

EXPLODE_OUTER

GREATEST

IF

INLINE

INLINE_OUTER

IN

ISNAN

IFNULL

ISNULL

ISNOTNULL

LEAST

NANVL

NULLIF

NVL

NVL2

POSEXPLODE

POSEXPLODE_OUTER

STACK

ASSERT_TRUE

RAISE_ERROR

SPARK_PARTITION_ID

INPUT_FILE_NAME

INPUT_FILE_BLOCK_START

INPUT_FILE_BLOCK_LENGTH

MONOTONICALLY_INCREASING_ID

CURRENT_DATABASE

CURRENT_CATALOG

CURRENT_USER

REFLECT

JAVA_METHOD

VERSION

TYPEOF

CAST

BOOLEAN

BIGINT

BINARY

DOUBLE

FLOAT

INT

SMALLINT

STRING

TINYINT

DECIMAL

GET_IDCARD_BIRTHDAY

GET_IDCARD_SEX

GET_IDCARD_AGE

MAX_PT

TRANS_ARRAY

TRANS_COLS

SAMPLE

TO_CHAR
