hive 分组topN

来源：互联网发布：秋之风腊肠淘宝编辑：程序博客网时间：2024/06/05 01:51

Time taken: 0.008 seconds, Fetched: 44 row(s)
hive> show create table jxl_report;
OK
CREATE TABLE `jxl_report`(
`id` bigint COMMENT '主键',
。。。
`user_name` string COMMENT '用户名',
`phone_no` string COMMENT '用户手机号',
`create_by` bigint COMMENT '创建用户',
`update_by` bigint COMMENT '修改用户',
`valid` boolean,
`create_time` string COMMENT '创建时间',

`row_key` bigint,
`cid` bigint COMMENT '进件ID')
COMMENT '报告详情'
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
'hdfs://nwdservice/user/hive/warehouse/dataimport.db/jxl_report'
TBLPROPERTIES (
'COLUMN_STATS_ACCURATE'='true',
'numFiles'='18',
'numRows'='4589964',
'rawDataSize'='5764994784',
'totalSize'='613551501',
'transient_lastDdlTime'='1512378856')
Time taken: 0.064 seconds, Fetched: 34 row(s)

# row_number() over

create table tmp_distinct_rpt as select id from ( select *,row_number() over (partition by cid order by report_update_time desc ) as od from jxl_report ) t1 where od <=1;

#rank() over

select *, rank() over (partition by sub order by score) as od from t;

create table tmp_distinct_rpt as select id from ( select *,rank() over (partition by cid order by report_update_time desc ) as od from jxl_report ) t1 where od <=1;

#dense_ran

create table tmp_distinct_rpt as select id from ( select *,dense_ran() over (partition by cid order by report_update_time desc ) as od from jxl_report ) t1 where od <=1;

安装cid 分组，按照时间获取报告！

参考：http://www.mamicode.com/info-detail-849458.html

阅读全文

0 0