hive窗口函数
野柳绘诗
自身的价值才是硬道理。
- 简介
- 窗口函数又名开窗函数，属于分析函数的一种，用于解决复杂报表统计需求的功能强大的函数。窗口函数用来计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
- 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
归纳：就是从现有的表上，额外多加一列出来展示，这也是从视觉上来讲最直观的体验。
在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。
- 准备数据
saml,2018-01-01,10
tony,2018-01-02,15
saml,2018-02-03,23
tony,2018-01-04,29
saml,2018-01-05,46
saml,2018-04-06,42
tony,2018-01-07,50
saml,2018-01-08,55
mart,2018-04-08,62
mart,2018-04-09,68
neil,2018-05-10,12
mart,2018-04-11,75
neil,2018-06-12,80
mart,2018-04-13,94
-- 创建表t_order
-- 注意：不能创建order表
create table if not exists t_order(
name string,-- 姓名
orderedate string,-- 购买日期
cost int -- 购买数量
)
row format delimited fields terminated by ',';
-- 加载数据
-- 1、本地加载
load data local inpath '/testHive/day04/order.csv' overwrite into table t_order;
-- 2、HDFS加载
load data inpath '/user/hive/order.csv' overwrite into table t_order;
语法
over开窗
-- 查询所有明细
select *
from t_order;
-- 查询总量
select count(*)
from t_order;
-- 同时查出明细和总量
-- 错误的（未使用窗口函数）
select *,count(*)
from t_order;
-- 使用窗口函数
select *,count(*) over()
from t_order;
-- 注意：
-- 窗口函数是针对每一行数据的
-- 如果over中没有参数，默认的全部结果集
-- 需求：查询在2018年1月份购买的顾客购买明细及总人数
-- 方法一
select *,count(*) over ()
from t_order -- 18 s 844 ms (execution: 18 s 799 ms, fetching: 45 ms)
where substr(orderedate,1,7)='2018-01';
-- 方法二
select *,count(*) over ()
from t_order -- 18 s 160 ms (execution: 18 s 100 ms, fetching: 60 ms)
where month(orderedate)=1 and year(orderedate)=2018;
partition by子句
- 在over窗口中进行分区，对么一列进行分区统计，窗口的大小就是分区的大小
-- partition by 子句
-- 需求：查看顾客的购买明细及月购买总额
select *,sum(cost) over (partition by year(orderedate),month(orderedate))
from t_order;order by
- -order by子句会对输入的数据强制排序
-- order by子句
select *,sum(cost) over (partition by month(orderedate) order by orderedate)
from t_order;window 子句
- n preceding：往前n条
- n following:往后n条
- current row：当前行
- unbounded:起点
- unbounded preceding：从前面的起点开始
- unbounded following：表示到后面的终点结束
-- Window 子句
-- 对窗口的结果做更细粒度的划分
select *,
sum(cost) over() as co1,-- 所有行相加
sum(cost) over(partition by name) as co2,-- 按name分组，组内数据相加
sum(cost) over(partition by name order by orderedate) as co3,-- 按name分组，组内数据累加
sum(cost) over(partition by name order by orderedate rows between UNBOUNDED PRECEDING AND current row ) as co4,-- 和col3一样，由起点 到当前行的聚合
sum(cost) over(partition by name order by orderedate rows between 1 PRECEDING AND current row ) as co5,-- 当前行和前面一行做聚合
sum(cost) over(partition by name order by orderedate rows between 1 PRECEDING and 1 following ) as co6,-- 但行和前边一行及后面一行
sum(cost) over(partition by name order by orderedate rows between current row and unbounded following) as co7-- 当前行及及后面所有行
from t_order;
-- 解释
-- co1:661[co1,*]=sum(cost)
-- co2:299[co2,1-4]=sum([cost,1]:[cost,3])
-- co3:205[co3,3]=[cost,1]+[cost,2]+[cost,3], 134[co3,10]=[cost,7]+[cost,8]+[cost,9]+[cost,10]
-- co4:与co3类型
-- co5:62[co5,1]=[cost,1], 130[co5,2]=[cost,1]+[cost,2], 169[co5,4]=[cost,3]+[cost,4]
-- co6:130[co6,1]=[cost,1]+[cost,2], 205[co6,2]=[cost,1]+[cost,2]+[cost,3], 169[cot6,4]=[cost,3]+[cost,4]
-- co7:299[co7,1]=[cost,1]+[cost,2]+[cost,3]+[cost,4], 237[co7,2]=[cost,2]+[cost,3]+[cost,4]
序列函数
ntile
- 用于对分组数据按照顺序切片分成n片，返回当前切片值
select *,
ntile(3) over (partition by name) as sliceValue
from t_order;
-- 例题：打印出截止目前每个人一半(50%)的购买细节
select *
from
(select *,ntile(2) over (partition by name) as sliceValue from t_order) as t_order1
where sliceValue=1;
lag和lead函数
- lag返回当前数据行的上一行数据
- lead返回当前数据行的下一行数据
-- 需求：查询顾客上次购买的时间
select *,
lag(orderedate) over (partition by name order by orderedate) as time
from t_order;
-- 需求：查询顾客下次购买的时间
select *,
lead(orderedate) over (partition by name order by orderedate) as time
from t_order;
first_value和last_value
- first_value取分区内排序后，截止到当前行，第一个值
- ast_value分组内排序后，截止到当前行，最后一个值
select *,
-- 获取第一次购买的时间
first_value(orderedate) over (partition by name order by orderedate) as first,
-- 获取最后一次购买的时间
last_value(orderedate) over (partition by name order by orderedate) as last
from t_order;
编辑于 2020-03-14 23:13
