SQL——常用函数

目录

系统内置函数

时间处理函数

字符串处理函数

数字处理函数

集合处理函数

类型转换函数

窗口函数

排名函数

---

系统内置函数

查看系统内置函数：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('分隔符',多个字符串/字符串数组) SQL——行列转换_Parker_1314的博客-CSDN博客	用指定连接符连接传入的参数，返回一个字符串 （用此函数可以将一个数组转化为一个字符串） 可以连接两个字段
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 个桶中；如果不能平均分配，则优先分配较小编号的桶

注：排名函数都可参与开窗！