目录
系统内置函数
查看系统内置函数:show functions;
查看一个函数的用法:desc funtion 函数名;
查看一个函数的具体用法:desc function extended 函数名;
时间处理函数
1、unix_timestamp:
select unix_timestamp();
返回当前时间的时间戳
select unix_timestamp('2021-11-03 14:42:33');
返回指定日期(须为标准化格式)的时间戳
select unix_timestamp('20211103 14:42:33','yyyyMMdd HH:mm:ss');
返回指定日期(任意格式,需要将字符串日期格式化)的时间戳
注:unix_timestamp 可以将任意不规则的日期格式转化为时间戳,再通过 from_unixtime 就可转成需要的日期格式!
注:日期格式(2022-03-05)
yyyy-MM-dd
2022-03-05
yy-M-dd
22-3-05
yyyy-MM-d
2022-03-5
2、from_unixtime:将时间戳以指定的时间格式返回(不指定的话,返回标准化时间)
select from_unixtime(1635921753,'yyyy--MM/dd HH:mm:ss');
from_utc_timestamp(ts*1000,'GMT+8'):将时间戳转化为指定时区的时间(GMT+8表示东八区;函数默认单位为毫秒,ts单位为秒,所以要*1000)
例:把时间戳转化为固定格式
date_format(from_utc_timestamp(1635921753000,'GMT+8'), 'yyyy-MM-dd HH:mm:ss')
3、current_timestamp:返回当前时间,精确到毫秒(注:返回的是标准化格式时间,而不是时间戳)
select current_timestamp;
例:2022-03-24 17:33:45.246
4、current_date:返回当前日期,精确到天
select current_date;
5、to_date:提取一个字符串中的日期部分,这个日期必须符合标准化时间格式
select to_date('2021-11-03 14:42:33');
6、提取年、月、日、时、分、秒
select year ('2021-11-03 14:42:33');
select month ('2021-11-03 14:42:33');
select day ('2021-11-03 14:42:33');
select hour ('2021-11-03 14:42:33');
select minute ('2021-11-03 14:42:33');
select second ('2021-11-03 14:42:33');
7、weekofyear:计算指定日期是当年的第几周
select weekofyear('2021-11-03 14:42:33');
8、dayofmonth:计算指定日期是当月的第几天
select dayofmonth('2021-11-03 14:42:33');
9、months_between(date1,date2):计算date1和date2之间相距几个月,前减后
select months_between('2020-11-03 14:42:33','2021-11-03 14:42:33');
10、add_months:计算指定日期,加上指定月数后的日期
select add_months('2020-11-03', 12);
11、datediff:计算两个日期相距天数,前减后
select datediff('2020-11-03 14:42:33','2021-11-03 14:42:33');
12、date_add:计算指定日期,加上指定天数后的日期
select date_add('2020-11-03', 12);
13、date_sub:计算指定日期,减去指定天数后的日期
select date_sub('2020-11-03', 12);
14、last_day:计算指定日期当月的最后一天
select last_day('2020-11-03 14:42:33');
15、date_format:将标准化格式的时间,改为其他任意格式(字符串--->日期)
select date_format('2020-11-03 14:42:33','yyyy&MM&dd HHmmss');
字符串处理函数
concat(第一个字符串,'分隔符',第二个字符串)
concat(多个字符串)
--连接传入的所有字符串
--拼接传入的所有字符串
concat_ws('分隔符',多个字符串/字符串数组)
- 用指定连接符连接传入的参数,返回一个字符串
(用此函数可以将一个数组转化为一个字符串)- 可以连接两个字段
upper
小写转大写
lower
大写转小写
trim
去掉字符串前后的空格
lpad(str, len, pad)
将传入的字符串用pad向左补齐到len个长度
rpad(str, len, pad)
将传入的字符串用pad向右补齐到len个长度
str_to_map(str,'不同kv的分隔符','kv内部的分隔符')
字符串转map集合(注:字符串结构必须为str=str的形式)
substring(str,startIndex,length)
从一个字符串的指定位置开始剪切,剪切指定长度
split(str,separator)
用指定的分隔符,切割字符串,返回一个切割后的数组
regexp_replace(str,regexp,rep)
将传入的字符串中的regexp用rep进行替换
stuff(param1, startIndex, length, param2)
引用>
将param1中自startIndex(SQL中都是从1开始,而非0)起,删除length个字符,然后用param2替换删掉的字符。
- param1:一个字符数据表达式。param1可以是常量、变量,也可以是字符列或二进制数据列。
- startIndex:一个整数值,指定删除和插入的开始位置。如果 startIndex或 length 为负,则返回空字符串。如果startIndex比param1长,则返回空字符串。startIndex可以是 bigint 类型。
- length:一个整数,指定要删除的字符数。如果 length 比param1长,则最多删除到param1 中的最后一个字符。length 可以是 bigint 类型。
- param2,返回类型。如果param1是受支持的字符数据类型,则返回字符数据。如果param1是一个受支持的 binary 数据类型,则返回二进制数据。
数字处理函数
sum
求和,自动过滤掉null值,可开窗
avg
求平均数,自动过滤掉null值,可开窗
max
求最大值,自动过滤掉null值,可开窗
min
求最小值,自动过滤掉null值,可开窗
count 计数 ,可开窗
round
四舍五入,可以传一个参数,默认取整;传第二个参数保留小数的个数
例: round(12.34) --->12
round(12.34,1) --->12.3
round(12.34,-1) --->10
floor
向下取整
例: floor(12.56) --->12
ceil/ceiling
向上取整
例: ceil(12.56) --->13
集合处理函数
size
返回一个数组中的元素个数
map_keys
返回一个map中所有的key组成的数组
map_values
返回一个map中所有的value组成的数组
array_contains
判断一个数组中是否包含某元素
sort_array
将数组中的元素排序后返回
类型转换函数
cast (字段 as 数据类型)
cast 做强制类型转化,不做四舍五入
例:cast(age as bigint)
cast(123.5678 as decimal(16,2)) --->123.56
窗口函数
lead(字段,n,默认值) 当前行的后 n 行,后两个参数可省略,默认 n=1,默认值为 null lag(字段,n,默认值) 当前行的前 n 行,后两个参数可省略,默认 n=1,默认值为 null first_value(字段)
first_value(字段,true)
分组内的第一行
true 表示取分组内第一个不为 null 的值
last_value(字段)
last_value(字段,true)
分组内的最后一行
true 表示取分组内第一个不为 null 的值
上面函数 over(partition by 字段名 order by 字段名 window clause)
控制范围
例:
rows between unbounded preceding and unbounded following :从数据范围的上边界到下边界
rows between 2 preceding and unbounded following :从往前数2行到下边界
rows between current row and unbounded following :从当前行到下边界
rows between 2 preceding and current row : 从往前数两行到当前行
rows between 2 preceding and 3 following :从往前数2行到往后数3行
rows between unbounded preceding and current row :从上边界到当前行
注意:
- 当开窗函数中有order by的时候,假如不写rows between,那么数据的计算范围相当于 rows between unbounded preceding and current row
- first_value()、last_value()、sum()、min()、max()、avg()、count() 受order by 约束!
- lag() 、lead() 和排序函数不受 order by 约束!
排名函数
rank()
允许并列排名,会跳号
dense_rank()
允许并列,不跳号
row_number()
行号,不并列,不跳号
cume_dist()
行数/分组内总行数
PERCENT_RANK()
行数 -1 /分组内总行数 -1
ntile(n)
将数据平均分配到 n 个桶中;如果不能平均分配,则优先分配较小编号的桶
注:排名函数都可参与开窗!