7 | 史上最全大数据笔记-Hive函数

第八章 Hive函数

在Hive中，函数主要分两大类型，一种是内置函数，一种是用户自定义函数。

8.1 Hive内置函数

8.1.1 函数查看

 show functions;
 desc function functionName;

8.1.2 日期函数

1）当前系统时间函数：current_date()、current_timestamp()、unix_timestamp()

 -- 函数1：current_date();                
         当前系统日期                格式："yyyy-MM-dd"
 -- 函数2：current_timestamp();        
         当前系统时间戳：        格式:"yyyy-MM-dd HH:mm:ss.ms"
 -- 函数3：unix_timestamp();        
         当前系统时间戳        格式：距离1970年1月1日0点的秒数。

2）日期转时间戳函数：unix_timestamp()

 格式：unix_timestamp([date[,pattern]])
 案例：
 select unix_timestamp('1970-01-01 0:0:0'); -- 传入的日期时间是东八区的时间， 返回值是相对于子午线的时间来说的
 select unix_timestamp('1970-01-01 8:0:0'); 
 select unix_timestamp('0:0:0 1970-01-01',"HH:mm:ss yyyy-MM-dd"); 
 select unix_timestamp(current_date());

3）时间戳转日期函数：from_unixtime

 语法：from_unixtime(unix_time[,pattern]) 
 案例：
 select from_unixtime(1574092800); 
 select from_unixtime(1574096401,'yyyyMMdd'); 
 select from_unixtime(1574096401,'yyyy-MM-dd HH:mm:ss'); 
 select from_unixtime(0,'yyyy-MM-dd HH:mm:ss');
 select from_unixtime(-28800,'yyyy-MM-dd HH:mm:ss');

4）计算时间差函数：datediff()、months_between()

 格式：datediff(date1, date2) - Returns the number of days between date1 and date2
 select datediff('2019-11-20','2019-11-01');
 格式：months_between(date1, date2) - returns number of months between dates date1 and date2
 select months_between('2019-11-20','2019-11-01');
 select months_between('2019-10-30','2019-11-30');
 select months_between('2019-10-31','2019-11-30');
 select months_between('2019-11-00','2019-11-30');

5）日期时间分量函数：year()、month()、day()、hour()、minute()、second()

 案例：
 select year(current_date);
 select month(current_date);
 select day(current_date);
 select year(current_timestamp);
 select month(current_timestamp);
 select day(current_timestamp);
 select hour(current_timestamp);
 select minute(current_timestamp);
 select second(current_timestamp);
 ​
 select dayofmonth(current_date);
 select weekofyear(current_date)

6）日期定位函数：last_day()、next_day()

 --月末:
 select  last_day(current_date)
 --下周
 select next_day(current_date,'thursday');

7）日期加减函数：date_add()、date_sub()、add_months()

 格式：
 date_add(start_date, num_days)
 date_sub(start_date, num_days)
 案例：
 select date_add(current_date,1);
 select date_sub(current_date,90);
 select add_months(current_date,1);

定位案例：

 --当月第1天: 
 select date_sub(current_date,dayofmonth(current_date)-1)
 --下个月第1天:
 select  add_months(date_sub(current_date,dayofmonth(current_date)-1),1)

8） 字符串转日期：to_date()

 （字符串必须为：yyyy-MM-dd格式）
 ​
 select to_date('2017-01-01 12:12:12');

9）日期转字符串(格式化)函数：date_format

 select date_format(current_timestamp(),'yyyy-MM-dd HH:mm:ss');
 select date_format(current_date(),'yyyyMMdd');
 select date_format('2017-01-01','yyyy-MM-dd HH:mm:ss');  

8.1.3 字符串函数

 lower--（转小写）
 select lower('ABC');
 ​
 upper--（转大写）
 select upper('abc');
 ​
 length--（字符串长度，字符数）
 select length('abc');
 ​
 concat--（字符串拼接）
 select concat("A", 'B');
 ​
 concat_ws --（指定分隔符）
 select concat_ws('-','a' ,'b','c');
 ​
 substr--（求子串）
 select substr('abcde',3);
 ​
 split(str,regex)--切分字符串，返回数组。
 select split("a-b-c-d-e-f","-");

8.1.4 类型转换函数

 cast(value as type) -- 类型转换
 select cast('123' as int)+1;

8.1.5 数学函数

 round --四舍五入((42.3 =>42))
 select round(42.3);
 ​
 ceil --向上取整(42.3 =>43)
 select ceil(42.3);
 ​
 floor --向下取整(42.3 =>42)
 select floor(42.3);

8.1.6 其他常用函数

 nvl(value,default value):如果value为null,则使用default value，否则使用本身value.
 ​
 isnull()
 isnotnull()
 ​
 case when  then ....when ...then.. else... end
 if(p1,p2,p3)
 coalesce(col1,col2,col3...)返回第一个不为空的

8.2 窗口函数（重点）

8.2.1 窗口函数over简介

先来看一下这个需求：求每个部门的员工信息以及部门的平均工资。在mysql中如何实现呢

 SELECT emp.*, avg_sal
 FROM emp
         JOIN (
                 SELECT deptno
                         , round(AVG(ifnull(sal, 0))) AS avg_sal
                 FROM emp
                 GROUP BY deptno
         ) t
         ON emp.deptno = t.deptno
 ORDER BY deptno;
 ​
 ​
 select emp.*,(select avg(ifnull(sal,0)) from emp B where B.deptno = A.deptno )
 from emp A;

通过这个需求我们可以看到，如果要查询详细记录和聚合数据，必须要经过两次查询，比较麻烦。

这个时候&