目录
一、窗口函数语法及说明
什么是窗口函数?
常见分析函数：
over()函数
二、hive窗口函数应用案例(1)
1、准备数据(1)
2、函数操作
2.1、聚合型窗口函数的使用[count(...) over()/sum(...) over()/ avg(...) over()/...]
2.2、over()函数中的窗口范围使用
2.3、lag(col,n)、lead(col,n)、ntile(n) 、first_value、last_value分析函数的使用
三、hive窗口函数应用案例(2)
1、准备数据(2)
2、函数操作
2.1、row_number()函数、rank()函数、dense_rank()函数与over()函数合用。
2.2、over()函数中的窗口范围使用
一、窗口函数语法及说明
什么是窗口函数?
窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。 大家都知道聚合函数是将某列中多行的值合并为一行，比如sum、count等。 而窗口函数则可以在本行内做运算，得到多行的结果，即每一行对应一行的值。 通用的窗口函数可以用下面的语法来概括：
分析函数 over([partition by 列名] [order by 列名 [rows between 开始位置 and 结束位置]])常见分析函数：
聚合类
avg()、sum()、count()、max()、min()排名类
row_number() 按照值排序时产生一个自增编号，不会重复
rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位
dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位其他类
lag(列名,往前的行数n,[行数为null时的默认值，不指定为null]) --往前第n行
lead(列名,往后的行数n,[行数为null时的默认值，不指定为null]) --往后第n行
first_value取分组内排序后，截止到当前行，第一个值。
last_value取分组内排序后，截止到当前行，最后一个值。
ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号
over()函数
over()全局扫描，over(order by col)按照行(字段排序)扫描，over(partition by col)按照分区(组)扫描
COVER() ：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化 -- cover()
over()函数中的窗口范围说明：
current row ：当前行
n percending ：往前n行数据
n following ：往后n行数据
unbounded ：起点
unbounded perceding ：表示从前面的起点
unbounded following ：表示到后面的终点
二、hive窗口函数应用案例(1)
1、准备数据(1)
-
--数据字段说明：用户名(username),购买日期(buydate),购买价格(buycost)
-
[hdp@hdp02 demo]$ vi
-
张飞,2019-01-01,10
-
关羽,2019-01-02,15
-
张飞,2019-02-03,23
-
关羽,2019-01-04,29
-
张飞,2019-01-05,46
-
张飞,2019-04-06,42
-
关羽,2019-01-07,50
-
张飞,2019-01-08,55
-
刘备,2019-04-08,62
-
刘备,2019-04-09,68
-
诸葛亮,2019-05-10,12
-
刘备,2019-04-11,75
-
诸葛亮,2019-06-12,80
-
刘备,2019-04-13,94
-
-
hive > create database demodb03;
-
hive > use demodb03;
-
hive > create table demo01 (username string,buydate string,buycost string)
-
row format delimited fields terminated by ',' lines terminated by '\n';
-
hive > load data local inpath '/home/hdp/demo/' into table demo01;
-
hive > select * from demo01 limit 10;
2、函数操作
2.1、聚合型窗口函数的使用[count(...) over()/sum(...) over()/ avg(...) over()/...]
注意：sum(...)over()、avg(...)over()等窗口函数的使用有特殊要求。
over中可以写分组、排序，
但是分组（按组计算）只能用partition by，排序(逐步计算)使用的是order by。
对单个字段去重用：distinct，他的执行在窗口函数执行之后执行，
对多字段合并的去重，需要再建立一次新的查询再使用group by。group by在窗口函数之前先执行。
-
-- 查询购买明细，及合计总购买次数、总购买金额。
-
select username,buydate,buycost, -- 购买明细
-
count(*) over() c, --合计购买次数
-
sum(buycost) over() s -- 合计购买金额
-
from demo01;
-
-
-- 查询购买明细，及每个用户的合计购买金额。
-
select username,buydate,buycost, -- 购买明细
-
sum(buycost) over(partition by username) s -- 每个用户的合计购买金额
-
from demo01;
-
-
-- 查询购买明细，及每个用户每个月购买总额。
-
select username,buydate,buycost, -- 购买明细
-
sum(buycost) over(partition by username,month(buydate)) s -- 每个用户每个月购买总额
-
from demo01;
-
-
-- 查询购买明细，及按时间升序的累计的购买金额and累计购买次数。
-
select username,buydate,buycost, -- 购买明细
-
sum(buycost) over(order by buydate asc) s, -- 累计的购买金额
-
count(*) over(order by buydate asc) c -- 累计购买次数
-
from demo01;
-
-
-- 查询按月累计的购买金额及累计购买次数。
-
select distinct substr(buydate,1,7) t,
-
sum(buycost) over(order by substr(buydate,1,7) asc) s, -- 按月累计的购买金额
-
count(*) over(order by substr(buydate,1,7) asc) c -- 按月累计购买次数
-
from demo01;
-
-
-- 统计每个用户每月的购买总额，和截止到当月的累计购买总额。
-
select ,,max() v,max() sv from
-
(select username,substr(buydate,1,7) d,
-
sum(buycost) over(partition by username,month(buydate)) v,
-
sum(buycost) over(partition by username order by month(buydate) asc) sv
-
from demo01 ) a
-
group by ,;
-
-
-- 查询购买明细，及每个用户按月累计的购买金额and按月累计的购买次数。
-
select username,buydate,buycost, -- 购买明细
-
sum(buycost) over(partition by username order by month(buydate) asc) s, -- 每个用户按月累计的购买金额
-
count(*) over(partition by username order by month(buydate) asc) c --按月累计的购买次数
-
from demo01;
-
-
-- 查询在2019年4月份，购买过产品的顾客及每个顾客购买次数。
-
select distinct username,
-
count(*) over(partition by username) as c
-
from demo01 where substr(buydate,1,7)="2019-04";
-
-
-- 统计每个用户的总消费额、消费次数、平均消费金额，单次最高消费金额和最低消费金额。
-
select distinct username,
-
sum(buycost) over (partition by username),
-
count(buydate) over (partition by username),
-
round(avg(buycost) over (partition by username),2),
-
max(buycost) over (partition by username),
-
min(buycost) over (partition by username)
-
from demo01;
2.2、over()函数中的窗口范围使用
over()函数中的窗口范围说明：
current row ：当前行
n percending ：往前n行数据
n following ：往后n行数据
unbounded ：起点
unbounded perceding ：表示从前面的起点
unbounded following ：表示到后面的终点
-
-- 按照顾客分组、日期升序排序、组内每条数据将之前的金额累加
-
select username,buydate,buycost, -- 购买明细
-
sum(buycost) over(partition by username order by buydate rows between unbounded preceding and current row) cumulative_amount
-
from demo01;
-
-- 等价于
-
select username,buydate,buycost, -- 购买明细
-
sum(buycost) over(partition by username order by buydate) cumulative_amount
-
from demo01;
-
-
-- 按照客户分组，每个客户的合计购买金额
-
select distinct username,
-
sum(buycost) over(partition by username order by buydate rows between unbounded preceding and unbounded following) cumulative_amount
-
from demo01;
-
-- 等价于
-
select distinct username,
-
sum(buycost) over(partition by username) cumulative_amount
-
from demo01;
2.3、lag(col,n)、lead(col,n)、ntile(n) 、first_value、last_value分析函数的使用
lag(列名,往前的行数n,[行数为null时的默认值，不指定为null]) --往前第n行
lead(列名,往后的行数n,[行数为null时的默认值，不指定为null]) --往后第n行
first_value取分组内排序后，截止到当前行，第一个值。
last_value取分组内排序后，截止到当前行，最后一个值。
ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号
-
-- 查询出明细数据,及顾客上次的购买时间
-
select username,buydate,buycost, -- 购买明细
-
lag(buydate,1,'起始') over(partition by username order by buydate asc) t
-
from demo01;
-
-
-- 查询出明细数据,及顾客下次的购买时间
-
select username,buydate,buycost, -- 购买明细
-
lead(buydate,1,'最后') over(partition by username order by buydate asc) t
-
from demo01;
-
-
-- 查询显示每个用户的第一次购买时间和最后一次购买时间。
-
select username,
-
first_value(buydate) over(partition by username order by buydate) first_date,
-
last_value(buydate) over(partition by username order by buydate) last_date
-
from demo01;
-
-
-- 查询前50%时间的订单信息
-
select * from
-
(select username,buydate,buycost, -- 购买明细
-
ntile(2) over(order by buydate asc) ar --50%分为2个组（25%则分4个组）
-
from demo01) a where = 1 ;
三、hive窗口函数应用案例(2)
1、准备数据(2)
-
-- 字段说明：姓名：name ,考试时间：dates,科目：subject,成绩：score
-
[hdp@hdp02 demo]$ vim
-
张三 2019-01-01 语文 77
-
张三 2019-01-02 数学 80
-
张三 2019-01-03 外语 70
-
张三 2019-01-04 物理 55
-
张三 2019-01-05 化学 40
-
张三 2019-01-06 生物 45
-
李四 2019-01-01 语文 89
-
李四 2019-01-02 数学 78
-
李四 2019-01-03 外语 69
-
李四 2019-01-04 物理 45
-
李四 2019-01-05 化学 40
-
李四 2019-01-06 生物 50
-
王五 2019-01-01 语文 90
-
王五 2019-01-02 数学 80
-
王五 2019-01-03 外语 80
-
王五 2019-01-04 物理 59
-
王五 2019-01-05 化学 48
-
王五 2019-01-06 生物 45
-
-
hive> create table demo02(
-
name string,dates string,subject string,score int
-
)row format delimited fields terminated by '\t';
-
hive> load data local inpath '/home/hdp/demo/' into table demo02;
-
hive> select * from demo02 limit 20;
2、函数操作
2.1、row_number()函数、rank()函数、dense_rank()函数与over()函数合用。
row_number() 按照值排序时产生一个自增编号，不会重复。（常用于排序）
rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位。（最常用用于排名）
dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位（常常会用来分组）
-
-- 使用上述3个函数，对每个学生的各科成绩，进行不同的排名。
-
select subject, name,score,
-
rank() over(partition by subject order by score desc) rank,
-
row_number() over(partition by subject order by score desc) row_number,
-
dense_rank() over(partition by subject order by score desc) dense_rank
-
from demo02;
2.2、over()函数中的窗口范围使用
over()函数中的窗口范围说明：
current row ：当前行
n percending ：往前n行数据
n following ：往后n行数据
unbounded ：起点
unbounded perceding ：表示从前面的起点
unbounded following ：表示到后面的终点
-
-- 求出每个同学的考试明细：姓名、日期、科目、分数、截止到当天的累计分数、截止到最后一天的总分。
-
-- 测试preceding、following、current row、unbounded函数。
-
select name ,dates,subject,score,
-
sum(score) over(partition by name order by dates ) s1,
-
sum(score) over(partition by name order by dates rows between unbounded preceding and current row) s2, -- s1=s2
-
sum(score) over(partition by name) s3,
-
sum(score) over(partition by name order by dates rows between unbounded preceding and unbounded following) s4 -- s3=s4
-
from demo02;
