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