Hive-窗口函数-聚合函数

最新推荐文章于 2025-01-13 17:36:47 发布

原创最新推荐文章于 2025-01-13 17:36:47 发布 · 719 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hadoop #数据仓库

本文详细介绍了SQL中的窗口函数，包括SUM、MAX、MIN和AVG在窗口函数中的用法。讨论了PARTITION BY和ORDER BY子句的影响，解释了没有PARTITION BY时的全局聚合与分组聚合的区别，以及没有ORDER BY和有ORDER BY时默认的范围区别。通过实例展示了如何使用窗口函数进行行范围聚合，并对比了ROWS和RANGE两种不同的划分方式。

语法：sum|max|min|avg OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])

重点：有PARTITION BY 没有PARTITION BY的区别；有ORDER BY没有ORDER BY的区别。首先说下有没有partition by 影响的是全局聚合还是分组之后每个组内聚合。有没有order by的区别：

没有order by，默认是rows between，首行到最后行，这里的"行"是物理上的行；

有order by，默认是range between，首行到当前行，这里的"行"是逻辑上的行，由字段的值的区间range来划分范围。

--1、求出每个用户总pv数  sum+group by普通常规聚合操作
select cookieid,sum(pv) as total_pv from website_pv_info group by cookieid;
+-----------+-----------+
| cookieid  | total_pv  |
+-----------+-----------+
| cookie1   | 26        |
| cookie2   | 35        |
+-----------+-----------+


--2、sum+窗口函数 
需要注意：这里都没有使用window子句指定范围，那么默认值是rows还是range呢？？？

--当没有order by也没有window子句的时候，默认是rows between,从第一行到最后一行，即分组内的所有行聚合。
--sum(...) over( )
select cookieid,createtime,pv,
       sum(pv) over() as total_pv
from website_pv_info;

--sum(...) over( partition by... )
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid) as total_pv 
from website_pv_info;


--当有order by但是缺失window子句的时候，默认是range between，为第一行的值到当前行的值构成的区间
--sum(...) over( partition by... order by ... )
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime) as current_total_pv
from website_pv_info;

--上述是根据creattime排序，因此range区间是由createtime的字段值来划分，第一行到当前行。



--下面是根据pv排序，因此range区间是由pv的字段值来划分，第一行到当前行。
--下面两个sql等价
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by pv) as current_total_pv
from website_pv_info; --这属于有order by没有 window子句 默认range

select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by pv range between unbounded preceding and current row) as current_total_pv
from website_pv_info;


--这是手动指定根据rows来划分范围
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by pv rows between unbounded preceding and current row ) as current_total_pv
from website_pv_info;