WHCSRL 技术网

Hive-窗口函数/开窗函数(重点理解~~~)

OVER函数

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

CURRENT ROW:当前行
n PRECEDING:往前 n 行数据
n FOLLOWING:往后 n 行数据
UNBOUNDED:起点,
CURRENT ROW:当前行
n PRECEDING:往前 n 行数据
n FOLLOWING:往后 n 行数据
UNBOUNDED:起点,
UNBOUNDED PRECEDING 表示从前面的起点,
UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n,default_val):往前第 n 行数据
LEAD(col,n, default_val):往后第 n 行数据

数据准备:

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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

按需求查询数据:

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

思考:在做这个需求的时候,需要用到substring函数,因此,查看substring方法的具体用法。

hive (default)> desc function substring;
OK
tab_name
substring(str, pos[, len]) - returns the substring of str that starts at pos and is of length len orsubstring(bin, pos[, len]) - returns the slice of byte array that starts at pos and is of length len
Time taken: 0.093 seconds, Fetched: 1 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5

截取字符串,start定义为pos,截取长度为len。

hive (default)> select substring(orderdate,0,7) from business;
OK
_c0
2017-01
2017-01
2017-02
2017-01
2017-01
2017-04
2017-01
2017-01
2017-04
2017-04
2017-05
2017-04
2017-06
2017-04
Time taken: 0.218 seconds, Fetched: 14 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

如果是用以下的sql的话

select 
    name,
    count(*) 
from business 
where substring(orderdate,0,7)='2017-04'
group by name;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

他所得出的结果是根据name的个数来进行计算的,结果如下:

OK
name	_c1
jack	1
mart	4
  • 1
  • 2
  • 3
  • 4

这个结果和我们题目中的需求不相符,我们需要的是,前面是name,后面是加总人数,在这里只要稍作修改,就能扭转结果,如下:

select 
    name,
    count(*) over()
from business 
where substring(orderdate,0,7)='2017-04'
group by name;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

结果:

name	count_window_0
jack	2
mart	2
  • 1
  • 2
  • 3

这里通过添加over()就将结果改变了,深入理解over的用法,over他的这个用法的话,相当于再name的后面进行开窗,开窗函数,每个name都开窗,本来name有14行,count(*)只有一行,现在count也有14行.

(2) 查询顾客的购买明细及月购买总额

第一步,计算出顾客的购买明细以及购买总额:

select 
    name,
    orderdate,
    cost,
    sum(cost) over(partition by name)
from business;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

第二部计算出顾客的购买明细以及月购买总额:

select 
    name,
    orderdate,
    cost,
    sum(cost) over(partition by name,month(orderdate))
from business;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

结果如下:

OK
name	orderdate	cost	sum_window_0
jack	2017-01-01	10 	111.0
jack	2017-01-05	46 	111.0
jack	2017-01-08	55 	111.0
jack	2017-02-03	23 	23.0
jack	2017-04-06	42 	42.0
mart	2017-04-13	94	299.0
mart	2017-04-08	62 	299.0
mart	2017-04-09	68 	299.0
mart	2017-04-11	75 	299.0
neil	2017-05-10	12 	12.0
neil	2017-06-12	80 	80.0
tony	2017-01-04	29 	94.0
tony	2017-01-07	50 	94.0
tony	2017-01-02	15 	94.0
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

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

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

结果如下:

OK
name	orderdate	cost	sum_window_0
jack	2017-01-01	10 	10.0
jack	2017-01-05	46 	56.0
jack	2017-01-08	55 	111.0
jack	2017-02-03	23 	134.0
jack	2017-04-06	42 	176.0
mart	2017-04-08	62 	62.0
mart	2017-04-09	68 	130.0
mart	2017-04-11	75 	205.0
mart	2017-04-13	94	299.0
neil	2017-05-10	12 	12.0
neil	2017-06-12	80 	92.0
tony	2017-01-02	15 	15.0
tony	2017-01-04	29 	44.0
tony	2017-01-07	50 	94.0
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

根据name 进行分区,然后每一行的开窗,都是从第一行开到当前行,进行一个累加。除了上面那种写法还有另外一种写法,因为上面在order by 之后没有指定窗口,默认值的话就是第一行开窗到当前行, 相当于对上面那种写法的补全:

select 
    name,
    orderdate,
    cost,
    sum(cost) over(partition by name order by orderdate rows 
        between unbounded preceding and current row)
from business;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

结果如下:

OK
name	orderdate	cost	sum_window_0
jack	2017-01-01	10 	10.0
jack	2017-01-05	46 	56.0
jack	2017-01-08	55 	111.0
jack	2017-02-03	23 	134.0
jack	2017-04-06	42 	176.0
mart	2017-04-08	62 	62.0
mart	2017-04-09	68 	130.0
mart	2017-04-11	75 	205.0
mart	2017-04-13	94	299.0
neil	2017-05-10	12 	12.0
neil	2017-06-12	80 	92.0
tony	2017-01-02	15 	15.0
tony	2017-01-04	29 	44.0
tony	2017-01-07	50 	94.0
Time taken: 103.08 seconds, Fetched: 14 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

注意:如果over()后面接的是空的话,可以理解成对每一行都元素都开窗了,用下面这个例子来理解:

//建表导数据
create table num(id string);
load data local inpath '/opt/module/datas/num.txt' into table num; 

hive (default)> select * from num;
OK
num.id
1
2
3
3
4
5

hive (default)> select id,sum(id) over() from num;
//查询结果:
id	sum_window_0
1	18.0
2	18.0
3	18.0
3	18.0
4	18.0
5	18.0


hive (default)> select id,sum(id) over(order by id) from num;
OK
id	sum_window_0
1	1.0
2	3.0
3	9.0
3	9.0
4	13.0
5	18.0
//注意:这里出现两个id为3的情况,进行开窗的时候,设置相同大小
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

如果排序中遇到两个id都是一样的,在开窗的时候,值为一样的会被统一默认是一样

(4)查看顾客上次的购买时间

通过使用lag函数,lag函数有三个参数,字段,往前多少行,默认值,下面这个例子加深理解:

select 
    name,
    orderdate,
    cost,
    lag(orderdate,1) over(partition by name order by orderdate)
from business;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

结果如下:

OK
name	orderdate	cost	lag_window_0
jack	2017-01-01	10 	NULL
jack	2017-01-05	46 	2017-01-01
jack	2017-01-08	55 	2017-01-05
jack	2017-02-03	23 	2017-01-08
jack	2017-04-06	42 	2017-02-03
mart	2017-04-08	62 	NULL
mart	2017-04-09	68 	2017-04-08
mart	2017-04-11	75 	2017-04-09
mart	2017-04-13	94	2017-04-11
neil	2017-05-10	12 	NULL
neil	2017-06-12	80 	2017-05-10
tony	2017-01-02	15 	NULL
tony	2017-01-04	29 	2017-01-02
tony	2017-01-07	50 	2017-01-04
Time taken: 99.989 seconds, Fetched: 14 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

我如果lag函数没有传入默认值的话,如果遇到NULL,则输出NULL.
想把NULL做修改,如果遇到的值为NULL,则修改为默认值,可以用下面这个hive sql:

select 
    name,
    orderdate,
    cost,
    lag(orderdate,1,'1990-01-01') over(partition by name order by orderdate)
from business;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

结果如下,如果遇到的是null,则输出默认值:

name	orderdate	cost	lag_window_0
jack	2017-01-01	10 	1990-01-01
jack	2017-01-05	46 	2017-01-01
jack	2017-01-08	55 	2017-01-05
jack	2017-02-03	23 	2017-01-08
jack	2017-04-06	42 	2017-02-03
mart	2017-04-08	62 	1990-01-01
mart	2017-04-09	68 	2017-04-08
mart	2017-04-11	75 	2017-04-09
mart	2017-04-13	94	2017-04-11
neil	2017-05-10	12 	1990-01-01
neil	2017-06-12	80 	2017-05-10
tony	2017-01-02	15 	1990-01-01
tony	2017-01-04	29 	2017-01-02
tony	2017-01-07	50 	2017-01-04
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

如果将默认值,写成字段本身,那么遇到NULL,则输出自己:

select 
    name,
    orderdate,
    cost,
    lag(orderdate,1,orderdate) over(partition by name order by orderdate)
from business;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

输出结果:

OK
name	orderdate	cost	lag_window_0
jack	2017-01-01	10 	2017-01-01
jack	2017-01-05	46 	2017-01-01
jack	2017-01-08	55 	2017-01-05
jack	2017-02-03	23 	2017-01-08
jack	2017-04-06	42 	2017-02-03
mart	2017-04-08	62 	2017-04-08
mart	2017-04-09	68 	2017-04-08
mart	2017-04-11	75 	2017-04-09
mart	2017-04-13	94	2017-04-11
neil	2017-05-10	12 	2017-05-10
neil	2017-06-12	80 	2017-05-10
tony	2017-01-02	15 	2017-01-02
tony	2017-01-04	29 	2017-01-02
tony	2017-01-07	50 	2017-01-04
Time taken: 39.097 seconds, Fetched: 14 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

那么lead函数,就是往后n行:

select 
    name,
    orderdate,
    cost,
    lead(orderdate,1,orderdate) over(partition by name order by orderdate)
from business;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

最后一行的结果为null,输出自己,结果如下:

OK
name	orderdate	cost	lead_window_0
jack	2017-01-01	10 	2017-01-05
jack	2017-01-05	46 	2017-01-08
jack	2017-01-08	55 	2017-02-03
jack	2017-02-03	23 	2017-04-06
jack	2017-04-06	42 	2017-04-06
mart	2017-04-08	62 	2017-04-09
mart	2017-04-09	68 	2017-04-11
mart	2017-04-11	75 	2017-04-13
mart	2017-04-13	94	2017-04-13
neil	2017-05-10	12 	2017-06-12
neil	2017-06-12	80 	2017-06-12
tony	2017-01-02	15 	2017-01-04
tony	2017-01-04	29 	2017-01-07
tony	2017-01-07	50 	2017-01-07
Time taken: 13.057 seconds, Fetched: 14 row(s)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

(5) 查询前 20%%%%时间的订单信息

通过ntile函数对数据进行分组,要取前20%%%%,分成5组,每一组就是20%%%%
ntile(5)表示将数据数据分成5组:

select 
    name,
    orderdate,
    cost,
    ntile(5) over( order by orderdate) groupId
from business;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

结果如下:

name	orderdate	cost	groupid
jack	2017-01-01	10 	1
tony	2017-01-02	15 	1
tony	2017-01-04	29 	1
jack	2017-01-05	46 	2
tony	2017-01-07	50 	2
jack	2017-01-08	55 	2
jack	2017-02-03	23 	3
jack	2017-04-06	42 	3
mart	2017-04-08	62 	3
mart	2017-04-09	68 	4
mart	2017-04-11	75 	4
mart	2017-04-13	94	4
neil	2017-05-10	12 	5
neil	2017-06-12	80 	5
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

以上是分组的结果,再选取出前20%%%%:

select 
    name,
    orderdate,
    cost
from 
(select 
    name,
    orderdate,
    cost,
    ntile(5) over( order by orderdate) groupId
from business)t1
where groupId=1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

前20%%%%,输出结果如下:

OK
name	orderdate	cost
jack	2017-01-01	10 
tony	2017-01-02	15 
tony	2017-01-04	29 
  • 1
  • 2
  • 3
  • 4
  • 5

如果要取中间的20%%%%也行。

RANK函数

RANK() 排序相同时会重复,总数不会变
DENSE_RANK() 排序相同时会重复,总数会减少
ROW_NUMBER() 会根据顺序计算.
RANK函数后面必须要跟着OVER.

数据准备 vim scoretxt:

孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

创建表导入数据:

create table score (
name string,
subject string,
score int
)
row format delimited fields terminated by '	';

load data local inpath '/opt/module/datas/score.txt'
into table score;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

使用rank()函数进行排序:

hive (default)> select *,rank() over(order by score) from score;
  • 1

查询结果:

在这里插入图片描述
上面的查询结果,认真观察就会注意到遇到相同的,排名第6就会并排,没有排名第7.

使用DESENS_RANK():

hive (default)> SELECT *,DENSE_rank() over(order by score) FROM score;
  • 1

查询结果如下:
在这里插入图片描述

注意到了,总共是抓取12行,但是其排名也就11行

使用ROW_NUMBER():

SELECT *,ROW_NUMBER() over(order by score) FROM score;
  • 1

查询结果:

在这里插入图片描述

根据各个学科的成绩来进行排序:

SELECT *,ROW_NUMBER() over(partition by subject order by score) FROM score;
  • 1

查询结果:在这里插入图片描述
需求:取每个学科的前三名:

第一步,对每个学科进行排名:

SELECT *,
ROW_NUMBER() over(partition by subject order by score desc )  rk
FROM score;
  • 1
  • 2
  • 3

再根据rk字段取排名前三的:

select 
    name,
    subject,
    score
from 
(SELECT *,
ROW_NUMBER() over(partition by subject order by score desc )  rk
FROM score)t1
where rk<=3;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

查询结果:
在这里插入图片描述
这样每个学科取得了前三名。

推荐阅读