美文网首页
Hive分组取TOPN数据

Hive分组取TOPN数据

作者: 一刀YiDao | 来源:发表于2016-08-31 11:46 被阅读5764次

    1、ROW_NUMBER,RANK(),DENSE_RANK()

    语法格式:row_number() OVER (partition by COL1 order by COL2 desc ) rank
    partition by:类似hive的建表,分区的意思;
    order by :排序,默认是升序,加desc降序;
    rank:表示别名
    表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

    1.1 案例

    1.1.1 样本数据

    浙江,杭州,300
    浙江,宁波,150
    浙江,温州,200
    浙江,嘉兴,100
    江苏,南京,270
    江苏,苏州,299
    江苏,某市,200
    江苏,某某市,100

    1.1.2导入数据

    --执行下述语句
    hive (temp)> hive -f 'HQL/loaddata.hql'>out/tmp;  
    -----------------------------------------------
    drop table datatable;
    CREATE table datatable (
      province string,
      city string,
      people int)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    STORED AS TEXTFILE;
    
    load data local inpath 'home/loaddata'
    overwrite into table temp.datatable;
    ----------------------------------------------
    --查看结果
    hive (temp)> select * from datatable;
    OK
    province      city  people
    浙江    杭州    300
    浙江    宁波    150
    浙江    温州    200
    浙江    嘉兴    100
    江苏    南京    270
    江苏    苏州    299
    江苏    某市    200
    江苏    某某市  100
    
    

    1.2 按照人口降序排列,生成衍生变量

    select province,city,
    rank() over (order by people desc) rank,
    dense_rank() over (order by people desc) dense_rank,
    row_number() over(order by people desc) row_number
    from datatable
    group by province,city,people;
    
    --结果
    province        city    rank    dense_rank      row_number
    浙江    杭州    1       1       1
    江苏    苏州    2       2       2
    江苏    南京    3       3       3
    浙江    温州    4       4       4
    江苏    某市    4       4       5
    浙江    宁波    6       5       6
    江苏    某某市  7       6       7
    浙江    嘉兴    7       6       8
    
    

    主要注意打圈的:
    row_number:顺序下来
    rank:在遇到数据相同项时,会留下空位5,(第一列4,4,6)
    dense_rank:在遇到数据相同项时,不会留下空位,(红框内第一列,4,4,5)

    1.3 分组按照省份分区,再按照人口降序排列,生成衍生变量

    select province,city,
    rank() over (partition by province order by people desc) rank,
    dense_rank() over (partition by province order by people desc) dense_rank,
    row_number() over(partition by province order by people desc) row_number
    from datatable
    group by province,city,people;
    
    --结果
    province        city    rank    dense_rank      row_number
    江苏    苏州    1       1       1
    江苏    南京    2       2       2
    江苏    某市    3       3       3
    江苏    某某市  4       4       4
    浙江    杭州    1       1       1
    浙江    温州    2       2       2
    浙江    宁波    3       3       3
    浙江    嘉兴    4       4       4
    
    

    2取TOPN数据

    2.1 按照国家提取TOP3

    2.1.1 样本数据:

    国家 城市 Visitors
    阿联酋,阿布扎比,137
    阿联酋,阿布扎比,146
    阿联酋,阿布扎比,178
    阿联酋,阿布扎比,337
    阿联酋,阿布扎比,178
    阿联酋,阿布扎比,227
    阿联酋,阿布扎比,157
    阿联酋,迪拜,144
    阿联酋,迪拜,268
    阿联酋,迪拜,103
    阿联酋,迪拜,141
    阿联酋,迪拜,108
    阿联酋,迪拜,266
    澳大利亚,悉尼,141
    澳大利亚,悉尼,122
    澳大利亚,悉尼,153
    澳大利亚,悉尼,128
    澳大利亚,墨尔本,294
    澳大利亚,墨尔本,230
    澳大利亚,墨尔本,159
    澳大利亚,墨尔本,188
    澳大利亚,堪培拉,249
    澳大利亚,堪培拉,378
    澳大利亚,堪培拉,255
    澳大利亚,堪培拉,240

    2.1.2导入数据

    --执行下述语句
    hive (temp)> hive -f 'HQL/loaddata.hql'>out/tmp;  
    -----------------------------------------------
    drop table temp.tripdata;
    CREATE table datatable (
      country string,
      city string,
      Visitors int)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    STORED AS TEXTFILE;
    
    load data local inpath 'home/loaddata'
    overwrite into table temp.tripdata;
    ----------------------------------------------
    
    ----------------------------------------------
    --查看结果
    hive (temp)> select * from tripdata;
    country        city   visitors
    阿联酋  阿布扎比        137
    阿联酋  阿布扎比        146
    阿联酋  阿布扎比        178
    阿联酋  阿布扎比        337
    阿联酋  阿布扎比        178
    阿联酋  阿布扎比        227
    阿联酋  阿布扎比        157
    阿联酋  迪拜    144
    阿联酋  迪拜    268
    阿联酋  迪拜    103
    阿联酋  迪拜    141
    阿联酋  迪拜    108
    阿联酋  迪拜    266
    澳大利亚        悉尼    141
    澳大利亚        悉尼    122
    澳大利亚        悉尼    153
    澳大利亚        悉尼    128
    澳大利亚        墨尔本  294
    澳大利亚        墨尔本  230
    澳大利亚        墨尔本  159
    澳大利亚        墨尔本  188
    澳大利亚        堪培拉  249
    澳大利亚        堪培拉  378
    澳大利亚        堪培拉  255
    澳大利亚        堪培拉  240
    
    ---格式:select 品牌,count/sum/其它() as num  from table_name order by num limit 10;
    
    
    select country,city,visitors
    from tripdata 
    order by visitors desc 
    limit 5;
    
    country city    visitors
    澳大利亚        堪培拉  378
    阿联酋  阿布扎比        337
    澳大利亚        墨尔本  294
    阿联酋  迪拜    268
    阿联酋  迪拜    266
    
    

    2.2 按照国家、城市提取TOP3

    --取top10品牌下各品牌的top10渠道 ,格式:       
    select 
    a.*
    from
    (
    select 品牌,渠道,count/sum/其它() as num row_number() over (partition by 品牌 order by num desc ) rank  
    from table_name
    where 品牌限制条件
    group by 品牌,渠道
    )a
    where a.rank<=10
    
    select a.*
    from (
    select country,city,visitors, row_number() over (partition by country order by visitors desc ) rank  
    from tripdata
    order by country,visitors desc
    ) a
    where a.rank<=3;
    
    --结果
    a.country       a.city  a.visitors      a.rank
    澳大利亚        堪培拉  378     1
    澳大利亚        墨尔本  294     2
    澳大利亚        堪培拉  255     3
    阿联酋  阿布扎比        337     1
    阿联酋  迪拜    268     2
    阿联酋  迪拜    266     3
    
    

    2.3 按照国家提取TOP5

    --取top10品牌下各品牌的top10渠道中各渠道的top10档期 ,格式:
    select  a.*
    from
    (
    select 品牌,渠道,档期,count/sum/其它() as num row_number() over (partition by 品牌,渠道 order by num desc ) rank  
    from table_name
    where 品牌,渠道 限制条件
    group by 品牌,渠道,档期
    )a
    where  a.rank<=10
    
    select a.*
    from (
    select country,city,visitors, row_number() over (partition by city order by visitors desc ) rank  
    from tripdata
    order by country,city,visitors desc
    ) a
    where a.rank<=3;
    
    --结果
    a.country       a.city  a.visitors      a.rank
    澳大利亚        堪培拉  378     1
    澳大利亚        堪培拉  255     2
    澳大利亚        堪培拉  249     3
    澳大利亚        墨尔本  294     1
    澳大利亚        墨尔本  230     2
    澳大利亚        墨尔本  188     3
    澳大利亚        悉尼    153     1
    澳大利亚        悉尼    141     2
    澳大利亚        悉尼    128     3
    阿联酋  迪拜    268     1
    阿联酋  迪拜    266     2
    阿联酋  迪拜    144     3
    阿联酋  阿布扎比        337     1
    阿联酋  阿布扎比        227     2
    阿联酋  阿布扎比        178     3
    
    
    

    相关文章

      网友评论

          本文标题:Hive分组取TOPN数据

          本文链接:https://www.haomeiwen.com/subject/xdmqettx.html