函数

查看所有自带函数

show functions;

查询函数怎么用

desc function [extended]详细显示 函数名
  • UDF一进一出 按行计量
  • UDAF多进一处
  • UDTF一进多出

UDF

NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如 果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数 都为 NULL ，则返回 NULL。

CASE WHEN THEN ELSE END

数据源

dept_id sex
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女
select
 dept_id,
 sum(case sex when '男' then 1 else 0 end) male_count,
 sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;

等同于if if(sex=‘男’,1,0)

CONCAT

拼接字符串

CONCAT_WS(separator, str1, str2,…)

第一个为分隔符

COLLECT_SET(col)

函数只接受基本数据类型，它的主要作用是将某字段的值进行去重 汇总，产生 Array 类型字段。

SET可以换为list差别在是否去重

UDTF

explode

可以将数组拆成多个行的数据

split(字段名,")

同java split

lateral view

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此 基础上可以对拆分后的数据进行聚合。

例：

string pageid

Array adid_list

“front_page”

[1, 2, 3]

“contact_page”

[3, 4, 5]

SELECT pageid, adid 
    FROM pageAds LATERAL VIEW explode(adid_list) adTable(侧写表名字) AS adid;

string pageid

int adid

“front_page”

1

“front_page”

2

“front_page”

3

“contact_page”

3

“contact_page”

4

“contact_page”

5

窗口函数

Over

窗口函数需要配合over使用

mysql8.0后支持了窗口函数

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变

partition by：可以理解为分为了几个窗口

CURRENT ROW：当前行

n PRECEDING：往前 n 行数据

n FOLLOWING：往后 n 行数据

UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING 表示到后面的终点

LAG(col,n,默认值)：往前第 n 行数据

LEAD(col,n, default_val)：往后第 n 行数据

NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对 于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

数据准备：name，orderdate，cost

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

（1） 查询在 2017 年 4 月份购买过的顾客及总人数

select name,count(*) over () 
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

（2） 查询顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) 
from business;

将每个顾客的 cost 按照日期进行累加

select *,sum(cost) over(partition by name order by orderdate)from business;
orderby 相当于累加
select *,sum(cost) over(partition by name  rows between  ubounded preceding[起始行] and current row[当前行])from business;

结果

jack	2017-01-05	46	46
jack	2017-01-08	55	101
jack	2017-01-01	10	111
jack	2017-04-06	42	153
jack	2017-02-03	23	176
mart	2017-04-13	94	94
mart	2017-04-11	75	169
mart	2017-04-09	68	237
mart	2017-04-08	62	299
neil	2017-05-10	12	12
neil	2017-06-12	80	92
tony	2017-01-04	29	29
tony	2017-01-02	15	44
tony	2017-01-07	50	94

注：若遇到一样的值用窗口函数的话会当成一个

例

id
1
2
3
3
select id ,sum(id) over(order by id) from test;
id sum(id)
1  1
2  3
3  6
3  6

Rank

RANK() 排序时允许重复，总数不会变

DENSE_RANK() 排序时允许重复，总数会减少

ROW_NUMBER() 不允许重复，会根据顺序计算

select *,rank() over(order by score desc) from score;
孙悟空	数学	95	1
大海	语文	94	2
孙悟空	语文	87	3
宋宋	数学	86	4
婷婷	数学	85	5
宋宋	英语	84	6
大海	英语	84	6
婷婷	英语	78	8
孙悟空	英语	68	9
婷婷	语文	65	10
宋宋	语文	64	11
大海	数学	56	12

宋宋在大海上面因为经过了shuffle反向溢写，数据相同不排序

其他常用函数

常用日期函数

unix_timestamp:返回当前或指定时间的时间戳(已过时)

select unix_timestamp();
unix_timestamp(void) is deprecated. Use current_timestamp instead.
unix_timestamp(void) is deprecated. Use 
1628579776
----------------------------------------
select unix_timestamp('2021-8-10','YYYY-MM-DD');
1609027200

current_timestamp

hive (default)> select current_timestamp;
OK
_c0
2021-08-10 15:17:37.051

from_unixtime(time,format)：将时间戳转为日期格式

select from_unixtime(1603843200);
2020-10-28 00:00:00

current_date：获取当前日期
select current_date;

to_date：抽取年月日部分

select to_date('2020-10-28 12:12:12');
2020-10-28
----------------------------------------
以下同理，但日期格式必须正确

year：获取年
select year(‘2020-10-28 12:12:12’);

month：获取月
select month(‘2020-10-28 12:12:12’);

day：获取日
select day(‘2020-10-28 12:12:12’);

hour：获取时
select hour(‘2020-10-28 12:12:12’);

minute：获取分
select minute(‘2020-10-28 12:12:12’);

second：获取秒
select second(‘2020-10-28 12:12:12’);

weekofyear：当前时间是一年中的第几周

dayofmonth：当前时间是一个月中的第几天

select weekofyear('2021-8-10 12:12:12');
32
----------------------------------------
select dayofmonth('2021-8-35 12:12:12');
4
//即使超出正常月份/日期大小不会报错会自动进下一月

months_between： 两个日期间的月份，前减后

select months_between('2020-04-01','2021-10-28');
-18.87096774

add_months：日期加减月

select add_months('2020-8-28',10);
2021-06-28

datediff：两个日期相差的天数

select datediff('2020-11-04','2021-8-28');
-297

date_add：日期加天数

date_sub：日期减天数

select date_add('2020-10-28',4);
select date_sub('2020-10-28',-4);

last_day：日期的当月的最后一天

select last_day('2020-08-40');
2020-09-30

date_format(date/timestamp/string, fmt)格式化日期

select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');
2020/10/28 12:12:12

数据取整

round： 四舍五入

select round(3.14);
3
select round(3.54);
4

ceil： 向上取整

select ceil(3.14);
select ceil(3.54);
4

floor： 向下取整

select floor(3.14);
select floor(3.54);
3

字符串操作

与Java的字符串方法差不多

upper： 转大写

lower： 转小写

length： 长度
select length(“atguigu”);

trim： 前后去空格
select trim(" atguigu ");

lpad： 向左补齐，到指定长度
select lpad(‘test’,5,‘g’);

rpad： 向右补齐，到指定长度
select rpad(‘test’,5,‘g’);

regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！

sub：截取字符串

split：按指定字符分割字符串

集合函数

类似于Java集合函数

size： 集合中元素的个数
select size(col) from table_name;

map_keys： 返回map中的key
select map_keys(col) from table_name;

map_values: 返回map中的value
select map_values(col) from table_name;

array_contains: 判断array中是否包含某个元素
select array_contains(col,‘test’) from table_name;

sort_array： 将array中的元素排序
select sort_array(col) from table_name;