Hive 查询获取表中插入的最后一条记录

Question

 a                date             time         b
35573407        20170412        140930  310260453908912
35573407        20170412        140930  310260453908912
35573407        20170412        141054  310260453908912
35573407        20170412        025339  310260453908912
35573407        20170412        072918  310260453908912
35573407        20170412        091105  310260453908912
35573422        20170412        193605  310260453908912
35573407        20170412        121105  310260453908912
35573407        20170412        032439  310260453908912
35573407        20170412        032605  310260453908912

我正在尝试找出一个 hive 查询，该查询获取带有 b 的表中插入的最后一条记录。需要将记录按时间列排序并获取最后一条记录。假设上面的记录

35573422  20170412  193605  310260453908912

是最后一条记录。

Answer 1

select  a,date,time,b

from   (select  *
               ,row_number() over 
                (
                    partition by    b
                    order by        date desc
                                   ,time desc
                ) as rn

        from    mytable
        ) t

where   t.rn = 1

<小时/>

+----------+----------+--------+-----------------+
|   a      |   date   |  time  |      b          |
+----------+----------+--------+-----------------+
| 35573422 | 20170412 | 193605 | 310260453908912 |
+----------+----------+--------+-----------------+