Hive窗口函数
在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。
聚合函数+over
查询在2015年4月份购买过的顾客及总人数,使用窗口函数实现
select name,count(*) over ()
from t_window
where substring(orderdate,1,7) = '2015-04'
partition by子句
Over子句之后第一个提到的就是Partition By。Partition By子句也可以称为查询分区子句,非常类似于Group By,都是将数据按照边界值分组,而Over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算。
查看顾客的购买明细及月购买总额,可以执行如下的sql
select name,orderdate,cost,
sum(cost) over(partition by month(orderdate))
from t_window
order by子句
order by子句会让输入的数据强制排序(窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。Order By子句对于诸如Row_Number(),Lead(),LAG()等函数是必须的,因为如果数据无序,这些函数的结果就没有任何意义。因此如果有了Order By子句,则Count(),Min()等计算出来的结果就没有任何意义。
sum() 中加order by 和不加order by的区:加了的话是一个累加的过程,不加是分组汇总,比如有10条记录,算的每个值都一样是(是和),加了order by 就是每一行数据依次累加。一般求sum,不加order by
select prov_name, city_name, val_cnt,
sum(val_cnt) over(partition by prov_name ORDER BY val_cnt) AS rst
from test_orer_partition_by ;
select prov_name, city_name, val_cnt,
sum(val_cnt) over(partition by prov_name) AS rst
from test_orer_partition_by ;
window子句
已经通过使用partition by子句将数据进行了分组的处理。如果想要更细粒度的划分,需要引入window子句。首先要理解两个概念:
- 如果只使用partition by子句,未指定order by的话,聚合是分组内的聚合