hadoop - 如何查找配置单元中每个组的最新记录

Question

这是我的 hive 表

       id                    name                   starttime(datatype string )

    0000031               workflows_status       Thu, 18 Feb 2016 14:21:38 GMT  
    0000030               workflows_status       Thu, 18 Feb 2016 14:16:28 GMT  
    0000029               workflows_status       Thu, 18 Feb 2016 14:07:25 GMT  
    0000336               hive_test              Tue, 16 Feb 2016 09:27:54 GMT  
    0000335               hive_test              Tue, 16 Feb 2016 09:17:52 GMT  
    0000334               hive_test              Tue, 16 Feb 2016 09:00:26 GMT

我希望 hive 查询得到以下结果

    id               name                   starttime

    0000031          workflow_status        Thu, 18 Feb 2016 14:21:38 GMT
    0000336          hive_test              Tue, 16 Feb 2016 09:27:54 GMT             

Answer 1

您可以使用以下查询获取所需的输出:

select * from (select id, name, starttime, rank() over(partition by name order by unix_timestamp(starttime, 'EEE, dd MMM yyyy hh:mm:ss z') desc) as rnk from hive_table) a where a.rnk=1;