hive窗口函数的案例

--（1）查询在2017年4月份购买过的顾客及总人数
SELECT 
name ,count(*) over() --如果不写order by和窗口从句：默认就是rows between unbounded preceding and unbounded following。这里要统计总人数，所以不能分区，就对group by 分组之后的数据，进行总体统计即可。
FROM 
business
where subString(orderdate ,1,7) = '2017-04'
GROUP by name;

--（2）查询顾客的购买明细及月购买总额
SELECT 
*,
sum(cost) over(PARTITION by name order by orderdate rows between unbounded preceding and unbounded FOLLOWING )
--如果只写了order by 没有写窗口从句，那么窗口从句默认为rows between unbounded preceding and current row
FROM 
business ;

--（3）上述的场景, 将每个顾客的cost按照日期进行累加
SELECT 
*,
sum(cost ) over(partition by name order by orderdate )
from 
business ;
--（4）查询顾客购买明细以及上次的购买时间和下次购买时间
SELECT 
*,
lag(orderdate ,1,"无") over(PARTITION by name order by orderdate) as before_buytime,
lead(orderdate ,1,"无") over(partition by name order by orderdate ) as next_buytime
--lag() , lead(),rank()等不能写窗口从句
FROM 
business ;
--（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
SELECT 
*,
FIRST_value(orderdate) over(partition by name,MONTH(orderdate) order by orderdate rows BETWEEN unbounded preceding and unbounded FOLLOWING ) as first_buytime,
last_value(orderdate) over(partition by name ,MONTH (orderdate ) order by orderdate rows BETWEEN unbounded preceding and unbounded FOLLOWING ) as last_buytime
from 
business ;
--（6）查询前20%时间的订单信息
SELECT 
*,ntile(5) over(order by orderdate)
from 
business ;

Rank, NTile, DenseRank, CumeDist, PercentRank。Lead 和 Lag不用写窗口从句。

---rank()会添加一列序号，遇到字段值相等的时，序号会重复，序号有可能不连续
---dense_rank()同样会添加一列序号，序号会重复，序号连续
---row_number()相当于添加一行行号
SELECT 
* ,
rank() over(partition by subject order by score DESC ) rk,
dense_rank() over(partition by subject order by score DESC ) drk,
ROW_NUMBER () over(partition by subject order by score DESC ) rowNo
from 
score ;