hive 窗口函数Over

Yz9876

于 2024-11-26 14:09:34 发布

阅读量397

点赞数 5

文章标签： hive hadoop 数据仓库数据库大数据 big data 数据库架构

本文链接：https://blog.youkuaiyun.com/Yz9876/article/details/144057599

版权

来一个需求：求每个部门的员工信息以及部门的平均工资。在mysql中如何实现呢。

-- 第一种写法
SELECT emp.*, avg_sal
FROM emp
	JOIN (
		SELECT deptno
			, round(AVG(ifnull(sal, 0))) AS avg_sal
		FROM emp
		GROUP BY deptno
	) t
	ON emp.deptno = t.deptno
ORDER BY deptno;

-- 第二种写法
select A.*,(select avg(ifnull(sal,0)) from emp B where B.deptno = A.deptno ) from emp A;

以后看见这种既要明细信息，也要聚合信息的题目，直接开窗！

上案例：

数据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;

指标一：需求：查询每个订单的信息，以及订单的总数

不使用开窗函数的写法
select *,(select count(1) from t_order) as `订单总数` from t_order ;

使用开窗函数的写法：
select *, count(*) over() from t_order;

开窗函数一般不单独使用，而是跟另外一些函数一起使用，比如 count, over() 这个的窗口是多大呢？over() 是整个数据集。

窗口其实就是范围，比如统计男女比例？必须知道窗口，是统计整个班级还是统计整个学校，班级和学校就是窗口。

窗口函数是针对每一行数据的.
如果over中没有指定参数,默认窗口大小为全部结果集

指标二：查询在2018年1月份购买过的顾客购买明细及总次数。

select *,count(*) over()
from t_order
where substr(orderdate,1,7) = '2018-01';

指标三：查询在2018年1月份购买过的顾客购买明细及总人数。

select *,count(distinct name) over()
from t_order
where substr(orderdate,1,7) = '2018-01';

还有没有其他的写法：
错误的写法：group by 语句，select 后面只能跟分组字段和聚合函数
select *,count(distinct name) over()
from t_order
where substr(orderdate,1,7) = '2018-01' group by name;
正确写法：
-- 假如使用group by
select *,(select count(1) from (
    select name from t_order where substr(orderdate,1,7) ='2018-01' group by name
  ) t) from t_order  where substr(orderdate,1,7) ='2018-01';
结论是：太麻烦了

distribute by子句:

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

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

指标四：查看顾客的购买明细及月购买总额

错误写法：明细信息是不能跟聚合函数一起使用的，聚合多个信息变一个。
select *,sum(cost) from t_order ;
可以这么写：
select *,(select sum(cost) from t_order) from t_order ;

如果非要按照第一个写法，需要开窗
select *,sum(cost) over() from t_order ;
底层原理：先查询到第一条数据，saml,2018-01-01,10，然后进行 sum统计，统计的窗口是整个数据集。
接着查询第二条数据 xxxxx,然后进行 sum统计，统计的窗口是整个数据集,依次类推。

saml    2018-01-01      10      661
saml    2018-01-08      55      661
tony    2018-01-07      50      661
saml    2018-01-05      46      661
tony    2018-01-04      29      661
tony    2018-01-02      15      661
saml    2018-02-03      23      661
mart    2018-04-13      94      661
saml    2018-04-06      42      661
mart    2018-04-11      75      661
mart    2018-04-09      68      661
mart    2018-04-08      62      661
neil    2018-05-10      12      661
neil    2018-06-12      80      661

接着继续编写咱们的需求：
select *,sum(cost) over(distribute by substr(orderdate,1,7) ) from t_order ;

t_order.name    t_order.orderdate       t_order.cost    sum_window_0
saml    2018-01-01      10      205
saml    2018-01-08      55      205
tony    2018-01-07      50      205
saml    2018-01-05      46      205
tony    2018-01-04      29      205
tony    2018-01-02      15      205
saml    2018-02-03      23      23
mart    2018-04-13      94      341
saml    2018-04-06      42      341
mart    2018-04-11      75      341
mart    2018-04-09      68      341
mart    2018-04-08      62      341
neil    2018-05-10      12      12
neil    2018-06-12      80      80
Time taken: 2.128 seconds, Fetched: 14 row(s)

指标5：需求:查看顾客的购买明细及每个顾客的月购买总额

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

mart    2018-04-13      94      299
mart    2018-04-08      62      299
mart    2018-04-09      68      299
mart    2018-04-11      75      299
neil    2018-05-10      12      12
neil    2018-06-12      80      80
saml    2018-01-01      10      111
saml    2018-01-05      46      111
saml    2018-01-08      55      111
saml    2018-02-03      23      23
saml    2018-04-06      42      42
tony    2018-01-04      29      94
tony    2018-01-07      50      94
tony    2018-01-02      15      94

窗口期：这个词我们经常听到，比如：穷人的孩子，上升渠道的窗口期马上就过去了。

sort by子句

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

语法：

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

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

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 ;

注意：也可以在窗口函数中，只写排序，窗口大小是全表记录

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

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;

运行结果：

name    orderdate       cost    sample1 sample2 sample3 sample4 sample5 sample6 sample7
mart    2018-04-08      62      661     299     62      62      62      130     299
mart    2018-04-09      68      661     299     130     130     130     205     237
mart    2018-04-11      75      661     299     205     205     143     237     169
mart    2018-04-13      94      661     299     299     299     169     169     94
.....

需求7：查看顾客到目前为止的购买总额

select *,sum(cost) over(rows between UNBOUNDED PRECEDING and current row)  
   from t_order;

需求8：求每个顾客最近三次的消费总额

select *,sum(cost) over(partition by name order by orderdate   rows between 2 PRECEDING and current row)  from t_order;

t_order.name    t_order.orderdate       t_order.cost    sum_window_0
mart    2018-04-08      62      62
mart    2018-04-09      68      130
mart    2018-04-11      75      205
mart    2018-04-13      94      237
neil    2018-05-10      12      12
neil    2018-06-12      80      92
saml    2018-01-01      10      10
saml    2018-01-05      46      56
saml    2018-01-08      55      111
saml    2018-02-03      23      124
saml    2018-04-06      42      120
tony    2018-01-02      15      15
tony    2018-01-04      29      44
tony    2018-01-07      50      94

回过头来看以前的需求：求每个部门的员工信息以及部门的平均工资

select *,avg(sal) over(partition by deptno) from emp;

注意：默认mysql老版本没有支持,在最新的8.0版本中支持, Oracle和Hive中都支持窗口函数。