思路:通过开窗函数进行分组,然后实现topN的排序
- OVER函数
- 按需求查询数据:
- （1）查询在 2017 年 4 月份购买过的顾客及总人数
- （2） 查询顾客的购买明细及月购买总额
- （3）将每个顾客的 cost 按照日期进行累加
- （4）查看顾客上次的购买时间
- （5） 查询前 20%时间的订单信息
- RANK函数
- 使用rank()函数进行排序:
- 使用DESENS_RANK():
- 使用ROW_NUMBER():
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创建business表:
create table business(
name string,
orderdate string,
cost string
)
row format delimited fields terminated by ',';将数据导入到business表当中:
load data local inpath '/opt/module/datas/business.txt'
overwrite into table business;查看一下数据:
hive (default)> select * from business;
OK
business.name business.orderdate business.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
Time taken: 0.335 seconds, Fetched: 14 row(s)按需求查询数据:
（1）查询在 2017 年 4 月份购买过的顾客及总人数
思考:做这道题的思路是,要取出2017年4月份,购买过的顾客,也就是name,最后再求总人数,求总人数的话是想到使用count()来计算,而name字段是有多行,count()只有一行,因此要将他们一同输出想到的就是使用开窗函数。
然后求顾客的话,要对他们去重,在开窗函数中根据name进行分区,然后where条件筛选出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)截取字符串,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)如果是用以下的sql的话
select
name,
count(*)
from business
where substring(orderdate,0,7)='2017-04'
group by name;他所得出的结果是根据name的个数来进行计算的,结果如下:
OK
name _c1
jack 1
mart 4这个结果和我们题目中的需求不相符,我们需要的是,前面是name,后面是加总人数,在这里只要稍作修改,就能扭转结果,如下:
select
name,
count(*) over()
from business
where substring(orderdate,0,7)='2017-04'
group by name;结果:
name count_window_0
jack 2
mart 2在这里的话,我们知道over()函数也可以和group by 联合使用。
这里有一点也是需要注意的,很多人会想到在上面进行分区的时候,如果直接在over()函数中写paritition by name的话,似乎也是可以进行分组的。这里要解释一下,如果那么做的话,结果是没有去重,按照上面的语句执行:
select
name,
count(*) over(partition by name)
from business
where substring(orderdate,0,7)='2017-04';结果:
OK
name count_window_0
jack 1
mart 4
mart 4
mart 4
mart 4
Time taken: 49.564 seconds, Fetched: 5 row(s)很明显,上面的结果比较冗余。
这里通过添加over()就将结果改变了,深入理解over的用法,over他的这个用法的话,相当于在name的后面进行开窗,每个name都开窗,本来name有14行,count(*)只有一行,现在count也有14行.注意一下,over()括号里面没有添加任何字段的时候,每行的数据都有一样的窗口大小。
（2） 查询顾客的购买明细及月购买总额
第一步,计算出顾客的购买明细以及购买总额:
select
name,
orderdate,
cost,
sum(cost) over(partition by name)
from business;第二部计算出顾客的购买明细以及月购买总额:
select
name,
orderdate,
cost,
sum(cost) over(partition by name,month(orderdate))
from business;结果如下:
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（3）将每个顾客的 cost 按照日期进行累加
select
name,
orderdate,
cost,
sum(cost) over(partition by name order by orderdate)
from business;结果如下:
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根据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;结果如下:
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)注意:如果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的情况,进行开窗的时候,设置相同大小如果排序中遇到两个id都是一样的,在开窗的时候,值为一样的会被统一默认是一样
（4）查看顾客上次的购买时间
通过使用lag函数可以选取之前的数据,lag函数有三个参数,字段,往前多少行,默认值,下面这个例子加深理解:
select
name,
orderdate,
cost,
lag(orderdate,1) over(partition by name order by orderdate)
from business;结果如下:
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)我如果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;结果如下,如果遇到的是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本来第一行数据的最后一个字段应该是为null,现在则输出了默认值。
如果将默认值,写成字段本身,那么遇到NULL,则输出自己:
select
name,
orderdate,
cost,
lag(orderdate,1,orderdate) over(partition by name order by orderdate)
from business;输出结果:
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)第一行的数据本来是为null,现在输出的他本身。
那么lead函数,就是往后n行:
select
name,
orderdate,
cost,
lead(orderdate,1,orderdate) over(partition by name order by orderdate)
from business;最后一行的结果为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)（5） 查询前 20%时间的订单信息
通过ntile函数对数据进行分组,要取前20%,分成5组,每一组就是20%
ntile(5)表示将数据数据分成5组:
select
name,
orderdate,
cost,
ntile(5) over( order by orderdate) groupId
from business;结果如下:
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以上是分组的结果,再选取出前20%:
select
name,
orderdate,
cost
from
(select
name,
orderdate,
cost,
ntile(5) over( order by orderdate) groupId
from business)t1
where groupId=1;前20%,输出结果如下:
OK
name orderdate cost
jack 2017-01-01 10
tony 2017-01-02 15
tony 2017-01-04 29如果要取中间的20%也行。
RANK函数
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算.
RANK函数后面必须要跟着OVER.
数据准备 vim scoretxt:
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78创建表导入数据:
create table score (
name string,
subject string,
score int
)
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/datas/score.txt'
into table score;使用rank()函数进行排序:
hive (default)> select *,rank() over(order by score) from score;查询结果:
上面的查询结果,认真观察就会注意到遇到相同的,排名第6就会并排,没有排名第7.
使用DESENS_RANK():
hive (default)> SELECT *,DENSE_rank() over(order by score) FROM score;查询结果如下:
注意到了,总共是抓取12行,但是其排名也就11行
使用ROW_NUMBER():
SELECT *,ROW_NUMBER() over(order by score) FROM score;查询结果:
根据各个学科的成绩来进行排序:
SELECT *,ROW_NUMBER() over(partition by subject order by score) FROM score;查询结果:
需求:取每个学科的前三名:
第一步,对每个学科进行排名:
SELECT *,
ROW_NUMBER() over(partition by subject order by score desc ) rk
FROM score;再根据rk字段取排名前三的:
select
name,
subject,
score
from
(SELECT *,
ROW_NUMBER() over(partition by subject order by score desc ) rk
FROM score)t1
where rk<=3;查询结果:
这样每个学科取得了前三名。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
