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]) 连接字符串表达式的值并在它们之间放置分隔符值。字符串末尾不添加分隔符。分隔符的默认值为“,”。