hive中求top k的两种方式

来源：互联网发布：淘宝上买cpu可靠吗编辑：程序博客网时间：2024/06/05 18:37

一、用rank() over()

select *from (  select event_id,      event_name,      channel,      pv,      uv,      rank() over (partition by channel order by pv desc,uv desc) as rank  from (    select event_id,          channel,          event_name,          sum(pv) pv,          sum(uv) uv    from tablename    where hp_cal_dt = '2015-06-09'    group by event_id,          channel,          event_name    ) a)awhere rank < 4

二、用row_number()

select event_id,        event_name,        channel,        pv,        uvfrom (  select event_id,        event_name,        channel,        pv,        uv  from (      select event_id,            channel,            event_name,            sum(pv) pv,            sum(uv) uv      from tablename      where hp_cal_dt = '2015-06-09'      group by event_id,            channel,            event_name  )a  distribute by channel  sort by channel,pv desc, uv desc)a where row_number(channel)< 4

0 0