hive函数

一、查看函数

show functions;   --查看所有的函数

desc function functionName; -查看某个具体的函数如何使用

二、基础函数

2.1、日期函数

1.current_date();        #当前系统日期        格式："yyyy-MM-dd"

2.current_timestamp();    #当前系统时间戳：    格式:"yyyy-MM-dd HH:mm:ss.ms"

3.unix_timestamp();    #当前系统时间戳    格式：距离1970年1月1日0点的秒数。

4.from_unixtime #时间戳转日期函数

5.#计算时间差函数

datediff()   #数据是前面的时间减去后面的时间，相差的天数

months_between()   #前面的时间减去后面时间的月数 ，可以精确到小数

6.date_add() #日期相加

7.date_sub  #日期相减

8.add_months() #月份相加

9.year()、month()、day()、hour()、minute()、second()  # 日期时间分量函数

10.last_day()、next_day() # 日期定位函数

11.to_date() #字符串转日期

12.date_format() #将日期转为字符串

2.2、字符串函数 

-- 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","-");
select concat_ws('&',split('a,b,c,d,f',','));
select replace('a,b,c,d,f',',','&');

2.3、数学函数

--round 四舍五入((42.3 =>42))
select round(42.3);
--ceil 向上取整(42.3 =>43)
select ceil(42.3);
--floor 向下取整(42.3 =>42)
select floor(42.3);
-- 求绝对值
select abs(-1);

三、窗口函数

   窗口函数（Window Functions）是 SQL 中的一种特殊类型的函数，用于在一组相关行上进行计算，而不是在整个表上进行计算。窗口函数允许你在不改变数据集的情况下，对数据进行聚合和排序等操作。这使得窗口函数在处理复杂的数据分析任务时非常有用。

3.1准备数据

order.txt

姓名,购买日期,购买数量
saml,2018-01-01,10
saml,2018-01-08,55
tony,2018-01-07,50
saml,2018-01-05,46
tony,2018-01-04,29
tony,2018-01-02,15
saml,2018-02-03,23
mart,2018-04-13,94
saml,2018-04-06,42
mart,2018-04-11,75
mart,2018-04-09,68
mart,2018-04-08,62
neil,2018-05-10,12
neil,2018-06-12,80

建表并且加载数据

-1. 创建order表:
create table if not exists t_order
(
    name      string,
    orderdate string,
    cost      int
)  row format delimited fields terminated by ',';
-2. 加载数据:
load data local inpath "/home/hivedata/order.txt" into table t_order; 

3.2、distribute by子句

在over窗口中进行分组,对某一字段进行分组统计,窗口大小就是同一个组的所有记录

语法： over(distribute by colname[,colname.....])

需求：查看顾客的购买明细及月购买总额

select *,sum(cost) over(distribute by substr(orderdate,1,7) ) from t_order ;

3.3、sort by子句

sort by子句会让输入的数据强制排序 （强调：当使用排序时，窗口会在组内逐行变大）

语法：  over([distribute by colname] [sort by colname [desc|asc]])

需求：查看顾客的购买明细及每个顾客的月购买总额,并且按照日期降序排序

select *,sum(cost) over(distribute by name,month(orderdate) sort by orderdate desc ) from t_order ;

可以使用partition by + order by 组合来代替distribute by+sort by组合 

select *,sum(cost) over(partition by name,month(orderdate) order by orderdate desc ) from t_order ;

3.4、window子句

如果要对窗口的结果做更细粒度的划分,那么就使用window子句,常见的有下面几个
PRECEDING：往前 
FOLLOWING：往后 
CURRENT ROW：当前行 
UNBOUNDED：起点，
UNBOUNDED PRECEDING：表示从前面的起点， 
UNBOUNDED FOLLOWING：表示到后面的终点 

select name,orderdate,cost,
       sum(cost) over() as sample1, -- 所有行相加
       sum(cost) over(partition by name) as sample2,-- 按name分组，组内数据相加
       sum(cost) over(partition by name order by orderdate) as sample3,-- 按name分组，组内数据累加
       sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,-- 与sample3一样，由起点到当前行的聚合
       sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, -- 当前行和前面一行做聚合
       sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,-- 当前行和前边一行及后面一行
       sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 -- 当前行及后面所有行
from t_order; 

四、序列函数

4.1、NTILE 

ntile 是Hive很强大的一个分析函数。可以看成是：它把有序的数据集合 平均分配 到 指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1

select name,orderdate,cost,
ntile(3) over(partition by name) -- 按照name进行分组,在分组内将数据切成3份
from t_order;

4.2、LAG和LEAD函数

ag返回当前数据行的前第n行的数据

语法：lag(colName,n[,default value]): 取字段的前第n个值。如果为null,显示默认值

lead返回当前数据行的后第n行的数据

需求:查询顾客上次购买的时间

select * ,lag(orderdate,1) over( partition by name order by orderdate ) from t_order;

4.3、FIRST_VALUE和LAST_VALUE

first_value 取分组内排序后，截止到当前行，第一个值

last_value 分组内排序后，截止到当前行，最后一个值

select name,orderdate,cost,
   first_value(orderdate) over(partition by name order by orderdate) as time1,
   last_value(orderdate) over(partition by name order by orderdate) as time2
from t_order;

五、排名函数

5.1、数据准备

stu_score.txt

1 gp1808 80
2 gp1808 92
3 gp1808 84
4 gp1808 86
5 gp1808 88
6 gp1808 70
7 gp1808 98
8 gp1808 84
9 gp1808 86
10 gp1807 90
11 gp1807 92
12 gp1807 84
13 gp1807 86
14 gp1807 88
15 gp1807 80
16 gp1807 92
17 gp1807 84
18 gp1807 86
19 gp1805 80
20 gp1805 92
21 gp1805 94
22 gp1805 86
23 gp1805 88
24 gp1805 80
25 gp1805 92
26 gp1805 94
27 gp1805 86

建表加载数据

create table if not exists stu_score(
userid int,
classno string,
score int
)
row format delimited 
fields terminated by ' ';

load data local inpath '/home/hivedata/stu_score.txt' overwrite into table stu_score;

5.2、row_number()

row_number从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列

需求：获取每次考试的排名情况

select *,
-- 没有并列，相同名次依顺序排
row_number() over(distribute by classno sort by score desc) rn1
from stu_score; 

5.3、rank()

 生成数据项在分组中的排名，排名相等会在名次中留下空位

 需求：获取每次考试的排名情况

select *,
-- rank()：有并列，相同名次空位
rank() over(distribute by classno sort by score desc) rn1
from stu_score;

5.4、dense_rank()

生成数据项在分组中的排名，排名相等会在名次中不会留下空位

需求：对每个班级的每次考试按照考试成绩倒序

select *,dense_rank() over(partition by classno order by  score desc) from stu_score;