相关推荐recommended
Flink SQL之常用函数(二)
作者:mmseoamin日期:2023-12-21

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