HIVE窗口函数

原文地址：http://yugouai.iteye.com/blog/1908121

下面是几个示例：

ROW_NUMBER的使用（rank和dense_rank类似）：

select col_source, col_uid, col_price,row_number() over(partition by col_source order by cast(col_price as int) desc) as rownumber 

from ads_tb_consumed_paysdk_package

where p_date='20150524' and col_source is not null and col_source = 'UCPP_market'

NTIL的使用：

select col_source, col_uid, col_price,ntile(5) over(partition by col_source order by cast(col_price as int) desc) as rank 

from ads_tb_consumed_paysdk_package

where p_date='20150524' and col_source is not null and col_source = 'UCPP_market'

累计的使用：

select col_source,sum(col_price),sum(sum(col_price)) over (order by col_source rows between unbounded preceding and current row) as cumulative

from ads_tb_consumed_paysdk_package

where p_date='20150524' and col_source is not null 

group by col_source

order by col_source

LAG和LEAD的使用：

select col_source,sum(col_price) as sum_price, 

lag(sum(col_price),1) over(order by col_source), 

lead(sum(col_price),1) over(order by col_source)

from ads_tb_consumed_paysdk_package

where p_date='20150524' and col_source is not null 

group by col_source

order by col_source

FIRST_VALUE和LAST_VALUE的使用：

select col_source, col_price, first_value(col_price) over(partition by col_source order by col_price), last_value(col_price) over(partition by col_source order by col_price)

from ads_tb_consumed_paysdk_package

where p_date='20150524' and col_source is not null and col_source != 'NULL'

分享到：

  最后修改于 2015-05-25 14:51   阅读( 9) 评论(0) 编辑删除

上一篇： 序列化java对象 下一篇：该日志被锁定