Flink SQL之常用函数(二)

1.时间函数

time interval unit时间单位：SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，或YEAR。
DATE_FORMAT(timestamp, string) 此功能存在严重错误，暂时不要使用。
CURRENT_DATE：UTC 时区系统日期

例如：select CURRENT_DATE; 返回 2021-10-08

CURRENT_TIME：UTC 时区系统时间

例如：select CURRENT_TIME; 返回 09:25:28.137

CURRENT_TIMESTAMP：UTC 时区系统时间戳，TIMESTAMP 类型

例如：select CURRENT_TIMESTAMP; 返回 2021-10-08T09:23:15.180 --注意和时区有关系

LOCALTIMESTAMP 本地时区系统时间， TIMESTAMP 类型

例如：select LOCALTIMESTAMP; 返回 2021-10-08T17:19:47.787

LOCALTIME 系统时间字符串（HH:mm:ss）

例如：select LOCALTIME；返回 17:22:16.212

EXTRACT(timeintervalunit FROM temporal) 取日期中的年/月/日返回整数

例如：select EXTRACT(DAY FROM DATE '2006-06-05'); 返回日为 5
例如：select EXTRACT(MONTH FROM DATE '2006-06-05'); 返回月为 6
例如：select EXTRACT(YEAR FROM DATE '2006-06-05'); 返回年为 2006

YEAR(date) 年份

例如：select YEAR(TO_DATE('2021-10-08 08:45:17')); 返回2021

QUARTER(date) 几季度

例如：select QUARTER(TO_DATE('2021-10-08 08:45:17')); 返回4

MONTH(date) 几月

例如：select MONTH(TO_DATE('2021-10-08 08:45:17')); 返回 10

WEEK(date) 几周

例如：select WEEK(TO_DATE('2021-10-08 08:45:17')); 返回 40

DAYOFYEAR(date) 一年中的第几天

例如：select DAYOFYEAR(TO_DATE('2021-10-08 08:45:17')); 返回281

DAYOFMONTH(date) 一月中的第几天

例如：select DAYOFMONTH(TO_DATE('2021-10-08 08:45:17')); 返回8
例如：select DAYOFMONTH(TO_DATE(FROM_UNIXTIME(1631061917))); -- 结果：8
例如：select DAYOFMONTH(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))); --参数为DATE类型当前时间为月份中的多少号 14号

DAYOFWEEK(date) 一周中的第几天

例如：select DAYOFWEEK(TO_DATE('2021-10-08 08:45:17'));

HOUR(timestamp) 返回小时数

例如：select HOUR(TIMESTAMP '2021-09-08 08:45:17'); 返回 8

MINUTE(timestamp) 返回分钟数

例如：select MINUTE(TIMESTAMP '2021-09-08 08:45:17'); 返回 45

SECOND(timestamp) 返回秒数

例如：select SECOND(TIMESTAMP '2021-09-08 08:45:17'); 返回 17

FLOOR(timepoint TO timeintervalunit) 向下取整分钟

例如：select FLOOR(TIME '12:44:31' TO MINUTE); 返回 12:44

CEIL(timepoint TO timeintervalunit) 向上取整分钟，回合时间点到时间单元timeintervalunit。

例如：select CEIL(TIME '12:44:31' TO MINUTE); 返回 12:45

TO_DATE(string1[, string2]) 将日期时间字符串（yyyy-MM-dd HH:mm:ss）转为 DATE 类型

例如：select TO_DATE(FROM_UNIXTIME(1631061917)) 结果为 2021-09-08 ，因 TO_DATE 是转为日
例如：select TO_DATE('2021-09-08 08:45:17'); 返回 2021-09-08

TO_TIMESTAMP(string1[, string2]) 将时间字符串转为 TIMESTAMP 类型

例如：select TO_TIMESTAMP('2021-09-08 08:45:17','yyyy-MM-dd HH:mm:ss'); 返回 2021-09-08T08:45:17 到秒
例如：select TO_TIMESTAMP('2021-09-08 08:45','yyyy-MM-dd HH:mm'); 返回 2021-09-08T08:45 到分钟

NOW() 系统时间，返回 TIMESTAMP 类型

例如：select NOW(); 返回 2021-10-08T08:50:01.486

UNIX_TIMESTAMP() 系统时间戳，返回整数

例如：select UNIX_TIMESTAMP(); 返回 1633680986

UNIX_TIMESTAMP(string1[, string2]) 返回整数，时间字符串（默认情况下：yyyy-MM-dd HH:mm:ss 如果未指定）转换为 Unix 时间戳（以秒为单位）。

例如：select UNIX_TIMESTAMP('2021-09-08 08:45:17','yyyy-MM-dd HH:mm:ss'); 返回 1631061917

FROM_UNIXTIME(numeric[, string]) 将整数转为时间字符串（默认为 'yyyy-MM-dd HH:mm:ss'）类型

例如：select FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyy-MM-dd HH:mm'); 返回 2021-10-08 16:28 到分钟
例如：select FROM_UNIXTIME(1631061917); 返回 2021-09-08 08:45:17
例如：select FROM_UNIXTIME(UNIX_TIMESTAMP()); 返回当前日期 2021-10-08 16:18:32
例如：select FROM_UNIXTIME(1631061917) >TIMESTAMPADD(MINUTE, -5, NOW()); --结果： false

TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2) 时间戳差

例如：select TIMESTAMPDIFF(MINUTE,TIMESTAMP FROM_UNIXTIME(place_time/1000), UNIX_TIMESTAMP())<5 --近5分钟
例如：select TIMESTAMPDIFF(MINUTE, TIMESTAMP '2003-01-03 10:10:00', TIMESTAMP '2003-01-03 10:00:00'); --结果为-10 后面-前面的分钟差.

TIMESTAMPADD(timeintervalunit, interval, timepoint) 向前/后推时间。timeintervalunit时间单位：SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，或YEAR。

例如：select TIMESTAMPADD(MINUTE, -5, NOW()) ; --当前时间往前推5分钟: 2021-10-08T08:33:48.108 ,但可能因时区原因有显示的时间有差异
例如：select TIMESTAMPADD(SECOND, -5, TO_DATE(FROM_UNIXTIME(1631061917))); 按天取整后往前推5秒结果：2021-09-07T23:59:55 .往前推5秒钟 1631061917为 2021-09-08 08:45:17

CONVERT_TZ(string1, string2, string3) 将日期时间格式字符串从时区string2转换为时区string3

例如：select CONVERT_TZ('1970-01-01 00:00:00', 'UTC', 'America/Los_Angeles');

2.条件函数

CASE value WHEN value1_1 THEN result1 ELSE resultZ END 满足某一个值

例如：select CASE 'a' WHEN 'a' THEN 1 ELSE 0 END; 返回 1
例如：select CASE 'b' WHEN 'a' THEN 1 ELSE 0 END; 返回 0

CASE WHEN condition1 THEN result1 ELSE resultZ END 满足某一个条件时

例如：select CASE WHEN 10>5 THEN 1 ELSE 0 END; 返回 1
例如：select CASE WHEN 4>5 THEN 1 ELSE 0 END; 返回 0

NULLIF(value1, value2) 等值判断：如果value1等于value2则返回 NULL ；否则返回value1。

例如：NULLIF(5, 5)返回NULL；NULLIF(5, 0)返回 5。

COALESCE(value1, value2 [, value3 ]* ) 返回第一个不为 NULL 的值

例如：select COALESCE(NULL, 5,3); 返回5

IF(condition, true_value, false_value)

例如：select IF(10>5,1,0); 返回 1

IS_ALPHA(string) 字符串中的所有字符都是字母，则返回真，否则返回假。

例如：select IS_ALPHA('abc'); 返回true
例如：select IS_ALPHA('abc1'); 返回false

IS_DECIMAL(string) 如果string可以解析为有效数字，则返回 true ，否则返回 false。

例如：select IS_DECIMAL('123'); 返回true
例如：select IS_DECIMAL('abc1'); 返回false

IS_DIGIT(string) 如果字符串中的所有字符都是数字，则返回真，否则返回假。

例如：select IS_DIGIT('123'); 返回true
例如：select IS_DIGIT('abc1'); 返回false

3.类型强转函数

CAST(value AS type) 强转

例如：select CAST('42' AS INT); 返回 42；
例如：select CAST(NULL AS VARCHAR); 返回 VARCHAR 类型的 NULL。

4.集合函数

array [ value1,value2... ] 构建数组，数组名[n] 取数组的第n个元素。索引从 1 开始

例如：select arr[2] from (select array [1,4,5] arr)t; 返回 4
例如：select array ['a','b','c']; 返回 [a, b, c]

map [key1,value1,key2,value2] 构建map集合，集合名[key值] 取key对应的value

例如：select mp[2] from (select map [1,'a',2,'b'] mp)t; 返回 b
例如：select map [1,'a',2,'b']; 返回 {1=a, 2=b}

CARDINALITY(map) 返回map 中的条目数。

例如：select CARDINALITY(map [1,'a',2,'b']); 返回 2

CARDINALITY(array) 返回array 中的元素个数。

例如：select CARDINALITY(array [1,2,3,0,0]); 返回5

ELEMENT(array) 返回数组的唯一元素（其基数应为 1）；如果数组为空，则返回 NULL 。如果数组有多个元素，则抛出异常。

例如：select ELEMENT(array [1,2,3,0,0]) ; --执行失败
例如：select ELEMENT(array [2]) ; 返回2

5.聚合函数

COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*) 默认情况下或使用 ALL，返回表达式不为 NULL的输入行数。对每个值的一个唯一实例使用 DISTINCT。

例如：COUNT(*) COUNT(1) 返回输入行数。

AVG([ ALL | DISTINCT ] expression) 默认情况下或使用关键字 ALL，返回所有输入行中表达式的平均值（算术平均值）。对每个值的一个唯一实例使用 DISTINCT。
SUM([ ALL | DISTINCT ] expression) 默认情况下或使用关键字 ALL，返回所有输入行的表达式总和。对每个值的一个唯一实例使用 DISTINCT。
MAX([ ALL | DISTINCT ] expression) 默认情况下或使用关键字 ALL，返回所有输入行中表达式的最大值。对每个值的一个唯一实例使用 DISTINCT。
MIN([ ALL | DISTINCT ] expression) 默认情况下或使用关键字 ALL，返回所有输入行中表达式的最小值。对每个值的一个唯一实例使用 DISTINCT。
STDDEV_POP([ ALL | DISTINCT ] expression) 默认情况下或使用关键字 ALL，返回所有输入行中表达式的总体标准偏差。对每个值的一个唯一实例使用 DISTINCT。
STDDEV_SAMP([ ALL | DISTINCT ] expression) 默认情况下或使用关键字 ALL，返回所有输入行中表达式的样本标准偏差。对每个值的一个唯一实例使用 DISTINCT。
VAR_POP([ ALL | DISTINCT ] expression) 默认情况下或使用关键字 ALL，返回所有输入行中表达式的总体方差（总体标准差的平方）。对每个值的一个唯一实例使用 DISTINCT。
VAR_SAMP([ ALL | DISTINCT ] expression) 默认情况下或使用关键字 ALL，返回所有输入行中表达式的样本方差（样本标准差的平方）。对每个值的一个唯一实例使用 DISTINCT。
COLLECT([ ALL | DISTINCT ] expression) 默认情况下或使用关键字 ALL，返回跨所有输入行的多组表达式。NULL 值将被忽略。对每个值的一个唯一实例使用 DISTINCT。
VARIANCE([ ALL | DISTINCT ] expression) VAR_SAMP() 的同义词。
RANK() 返回值在一组值中的排名。结果是 1 加上分区顺序中当前行之前或等于当前行的行数。这些值将在序列中产生间隙。
DENSE_RANK() 返回值在一组值中的排名。结果是一加先前分配的等级值。与函数 rank 不同，dense_rank 不会在排名序列中产生间隙。
ROW_NUMBER() 根据窗口分区内行的顺序，为每一行分配一个唯一的序列号，从一开始。
ROW_NUMBER 和 RANK 相似。ROW_NUMBER 按顺序对所有行进行编号（例如 1、2、3、4、5）。RANK 为平局提供相同的数值（例如 1、2、2、4、5）。
LEAD(expression [, offset] [, default] ) 返回窗口中当前行之前偏移第 th 行处的expression值。的默认值的偏移是1和默认值默认为NULL。
LAG(expression [, offset] [, default]) 返回窗口中当前行后偏移第 th 行处的expression值。的默认值的偏移是1和默认值默认为NULL。
FIRST_VALUE(expression) 返回一组有序值中的第一个值。
LAST_VALUE(expression) 返回一组有序值中的最后一个值。
LISTAGG(expression [, separator]) 连接字符串表达式的值并在它们之间放置分隔符值。字符串末尾不添加分隔符。分隔符的默认值为“,”。